def test_ensure_no_dependent_object_is_owned_schema_expansion_skips_deps(
mockdbcontext):
mockdbcontext.get_all_object_attributes = lambda: {
'tables': {
'schema0': {
ObjectName('schema0', 'table1'): {
'owner': 'owner1',
'is_dependent': False
},
ObjectName('schema0', 'table2'): {
'owner': 'owner2',
'is_dependent': True
},
ObjectName('schema0', 'table3'): {
'owner': 'owner2',
'is_dependent': True
},
},
},
}
spec_yaml = """
role0:
owns:
tables:
- schema0.*
"""
unconverted_spec = yaml.load(spec_yaml)
spec = spec_inspector.convert_spec_to_objectnames(unconverted_spec)
errors = spec_inspector.ensure_no_dependent_object_is_owned(
spec, mockdbcontext, 'tables')
assert errors == []
def test_verify_spec_fails_object_referenced_read_write():
spec_yaml = """
margerie:
can_login: true
privileges:
{}:
read:
- big_bad
write:
- big_bad
danil:
can_login: true
privileges:
sequences:
read:
- hoop
write:
- grok
"""
privilege_types = ('schemas', 'sequences', 'tables')
for t in privilege_types:
unconverted_spec = yaml.load(spec_yaml.format(t))
spec = spec_inspector.convert_spec_to_objectnames(unconverted_spec)
errors = spec_inspector.ensure_no_redundant_privileges(spec)
err_string = "margerie: {'%s': ['big_bad']}" % t
expected = spec_inspector.OBJECT_REF_READ_WRITE_ERR.format(err_string)
assert [expected] == errors
def test_ensure_no_missing_objects_schema_expansion_works(mockdbcontext):
mockdbcontext.get_all_raw_object_attributes = lambda: {
ObjectAttributes('tables', 'schema0', ObjectName('schema0', 'table1'),
'owner1', False),
ObjectAttributes('tables', 'schema0', ObjectName('schema0', 'table2'),
'owner3', False),
}
mockdbcontext.get_all_object_attributes = lambda: {
'tables': {
'schema0': {
ObjectName('schema0', 'table1'): {
'owner': 'owner1',
'is_dependent': False
},
ObjectName('schema0', 'table2'): {
'owner': 'owner2',
'is_dependent': False
},
},
},
}
spec_yaml = """
role0:
owns:
tables:
- schema0.*
"""
unconverted_spec = yaml.load(spec_yaml)
spec = spec_inspector.convert_spec_to_objectnames(unconverted_spec)
errors = spec_inspector.ensure_no_missing_objects(spec, mockdbcontext,
'tables')
assert errors == []
def test_ensure_no_dependent_object_is_owned(mockdbcontext):
mockdbcontext.get_all_object_attributes = lambda: {
'tables': {
'schema0': {
ObjectName('schema0', 'table1'): {
'owner': 'owner1',
'is_dependent': False
},
ObjectName('schema0', 'table2'): {
'owner': 'owner2',
'is_dependent': True
},
ObjectName('schema0', 'table3'): {
'owner': 'owner2',
'is_dependent': True
},
},
},
}
spec_yaml = """
role0:
owns:
tables:
# Ensure function can handle some objects being quoted and some not
- schema0."table1"
- schema0.table2
- schema0.table3
"""
unconverted_spec = yaml.load(spec_yaml)
spec = spec_inspector.convert_spec_to_objectnames(unconverted_spec)
errors = spec_inspector.ensure_no_dependent_object_is_owned(
spec, mockdbcontext, 'tables')
expected = spec_inspector.DEPENDENT_OBJECTS_MSG.format(
objkind='tables', dep_objs='schema0."table2", schema0."table3"')
assert errors == [expected]
def test_ensure_no_object_owned_twice_schema_expansion_works(mockdbcontext):
mockdbcontext.get_all_object_attributes = lambda: {
'tables': {
'schema1': {
ObjectName('schema1', 'table1'): {'owner': 'owner1', 'is_dependent': False},
ObjectName('schema1', 'table2'): {'owner': 'owner2', 'is_dependent': False},
ObjectName('schema1', 'table3'): {'owner': 'owner3', 'is_dependent': False},
},
},
}
spec_yaml = """
role0:
owns:
tables:
- schema0.table0
- schema1.*
role1:
owns:
tables:
# Ensure function can handle some objects being quoted and some not
- schema1."table1"
- schema1.table3
"""
unconverted_spec = yaml.safe_load(spec_yaml)
spec = spec_inspector.convert_spec_to_objectnames(unconverted_spec)
errors = spec_inspector.ensure_no_object_owned_twice(spec, mockdbcontext, 'tables')
expected = set([
spec_inspector.MULTIPLE_OBJKIND_OWNER_ERR_MSG.format('Table', 'schema1."table1"', 'role0, role1'),
spec_inspector.MULTIPLE_OBJKIND_OWNER_ERR_MSG.format('Table', 'schema1."table3"', 'role0, role1')
])
assert set(errors) == expected
def test_ensure_no_missing_objects_with_personal_schemas(mockdbcontext):
mockdbcontext.get_all_raw_object_attributes = lambda: {
ObjectAttributes('tables', 'role0', ObjectName('role0', 'table1'),
'role0', False),
ObjectAttributes('tables', 'role0', ObjectName('role0', 'table2'),
'role0', False),
}
mockdbcontext.get_all_object_attributes = lambda: {
'tables': {
'role0': {
ObjectName('role0', 'table1'): {
'owner': 'role0',
'is_dependent': False
},
ObjectName('role0', 'table2'): {
'owner': 'role0',
'is_dependent': False
},
},
},
}
spec_yaml = """
role0:
has_personal_schema: true
"""
unconverted_spec = yaml.load(spec_yaml)
spec = spec_inspector.convert_spec_to_objectnames(unconverted_spec)
errors = spec_inspector.ensure_no_missing_objects(spec, mockdbcontext,
'tables')
assert errors == []
def test_ensure_no_missing_objects_missing_in_spec(mockdbcontext):
mockdbcontext.get_all_raw_object_attributes = lambda: {
ObjectAttributes('tables', 'schema0', ObjectName('schema0', 'table1'), 'owner1', False),
ObjectAttributes('tables', 'schema0', ObjectName('schema0', 'table2'), 'owner1', False),
ObjectAttributes('tables', 'schema0', ObjectName('schema0', 'table3'), 'owner3', False),
ObjectAttributes('tables', 'schema0', ObjectName('schema0', 'table4'), 'owner3', False),
# This should be skipped as it is dependent
ObjectAttributes('tables', 'schema0', 'schema0."table5"', 'owner3', True),
}
mockdbcontext.get_all_object_attributes = lambda: {
'tables': {
'schema0': {
ObjectName('schema0', 'table1'): {'owner': 'owner1', 'is_dependent': False},
ObjectName('schema0', 'table2'): {'owner': 'owner1', 'is_dependent': False},
ObjectName('schema0', 'table3'): {'owner': 'owner3', 'is_dependent': False},
ObjectName('schema0', 'table4'): {'owner': 'owner3', 'is_dependent': False},
ObjectName('schema0', 'table5'): {'owner': 'owner3', 'is_dependent': True},
},
},
}
spec_yaml = """
role0:
owns:
tables:
# Ensure function can handle some objects being quoted and some not
- schema0."table1"
- schema0.table3
"""
unconverted_spec = yaml.safe_load(spec_yaml)
spec = spec_inspector.convert_spec_to_objectnames(unconverted_spec)
errors = spec_inspector.ensure_no_missing_objects(spec, mockdbcontext, 'tables')
expected = spec_inspector.UNOWNED_OBJECTS_MSG.format(objkind='tables',
unowned_objects='schema0."table2", schema0."table4"')
assert errors == [expected]
def test_ensure_no_object_owned_twice_personal_schemas_expanded(mockdbcontext):
mockdbcontext.get_all_object_attributes = lambda: {
'tables': {
'role0': {
ObjectName('role0', 'table0'): {'owner': 'role0', 'is_dependent': False},
ObjectName('role0', 'table1'): {'owner': 'role0', 'is_dependent': False},
ObjectName('role0', 'table2'): {'owner': 'role0', 'is_dependent': False},
},
},
}
spec_yaml = """
role0:
has_personal_schema: true
role1:
owns:
tables:
# Ensure function can handle some objects being quoted and some not
- role0."table0"
- role0.table1
"""
unconverted_spec = yaml.safe_load(spec_yaml)
spec = spec_inspector.convert_spec_to_objectnames(unconverted_spec)
errors = spec_inspector.ensure_no_object_owned_twice(spec, mockdbcontext, 'tables')
expected = set([
spec_inspector.MULTIPLE_OBJKIND_OWNER_ERR_MSG.format('Table', 'role0."table0"', 'role0, role1'),
spec_inspector.MULTIPLE_OBJKIND_OWNER_ERR_MSG.format('Table', 'role0."table1"', 'role0, role1')
])
assert set(errors) == expected
def test_convert_spec_to_objectnames_empty_role_definition_ok():
yaml_spec = """
roleA:
"""
expected_spec = {
'roleA': None,
}
loaded_spec = yaml.load(yaml_spec)
actual_spec = spec_inspector.convert_spec_to_objectnames(loaded_spec)
assert actual_spec == expected_spec
def test_convert_spec_to_objectnames_privileges_subdict():
yaml_spec = """
roleA:
privileges:
schemas:
read:
- myschema1
- myschema2
write:
- myschema3
tables:
read:
- myschema1.*
- myschema2.mytable1
write:
- myschema3.mytable1
except:
- myschema1.mytable1
"""
expected_spec = {
'roleA': {
'privileges': {
'schemas': {
'read': [
ObjectName('myschema1'),
ObjectName('myschema2'),
],
'write': [
ObjectName('myschema3'),
],
},
'tables': {
'read': [
ObjectName('myschema1', '*'),
ObjectName('myschema2', 'mytable1'),
],
'write': [
ObjectName('myschema3', 'mytable1'),
],
'except': [
ObjectName('myschema1', 'mytable1'),
],
}
}
}
}
loaded_spec = yaml.safe_load(yaml_spec)
actual_spec = spec_inspector.convert_spec_to_objectnames(loaded_spec)
assert actual_spec == expected_spec
def test_ensure_no_schema_owned_twice():
spec_yaml = """
jfinance:
owns:
schemas:
- finance_documents
jfauxnance:
owns:
schemas:
- finance_documents
"""
unconverted_spec = yaml.load(spec_yaml)
spec = spec_inspector.convert_spec_to_objectnames(unconverted_spec)
errors = spec_inspector.ensure_no_schema_owned_twice(spec)
expected = spec_inspector.MULTIPLE_SCHEMA_OWNER_ERR_MSG.format(
'finance_documents', 'jfauxnance, jfinance')
assert [expected] == errors
def test_convert_spec_to_objectnames_owns_subdict():
yaml_spec = """
roleA:
owns:
schemas:
- myschema1
- myschema2
- myschema3
tables:
- myschema1.*
- '"myschema2".*'
- myschema3.mytable1
- 'myschema3."mytable2"'
- '"myschema3".mytable3'
- '"myschema3"."mytable4"'
"""
expected_spec = {
'roleA': {
'owns': {
'schemas': [
ObjectName('myschema1'),
ObjectName('myschema2'),
ObjectName('myschema3'),
],
'tables': [
ObjectName('myschema1', '*'),
ObjectName('myschema2', '*'),
ObjectName('myschema3', 'mytable1'),
ObjectName('myschema3', 'mytable2'),
ObjectName('myschema3', 'mytable3'),
ObjectName('myschema3', 'mytable4'),
],
}
}
}
loaded_spec = yaml.load(yaml_spec)
actual_spec = spec_inspector.convert_spec_to_objectnames(loaded_spec)
assert actual_spec == expected_spec
def test_ensure_no_object_owned_twice(mockdbcontext):
mockdbcontext.get_all_object_attributes = lambda: {}
spec_yaml = """
# No config
foo:
# Config but no 'owns'
bar:
has_personal_schema: True
# Has 'owns' but not for objkind
baz:
owns:
schemas:
- schema0
role0:
owns:
tables:
- schema0.table0
- schema1.table1
role1:
owns:
tables:
# Ensure function can handle some objects being quoted and some not
- schema1.table1
- schema1."table2"
"""
unconverted_spec = yaml.load(spec_yaml)
spec = spec_inspector.convert_spec_to_objectnames(unconverted_spec)
errors = spec_inspector.ensure_no_object_owned_twice(
spec, mockdbcontext, 'tables')
expected = spec_inspector.MULTIPLE_OBJKIND_OWNER_ERR_MSG.format(
'Table', 'schema1."table1"', 'role0, role1')
assert [expected] == errors
def test_convert_spec_to_objectnames_other_subdicts_untouched():
yaml_spec = """
roleA:
can_login: true
has_personal_schema: false
is_superuser: false
attributes:
- CREATEDB
- CREATEROLE
member_of:
- roleB
owns:
schemas:
- myschema
tables:
- myschema.mytable
privileges:
schemas:
read:
- myschema2
tables:
read:
- myschema2.mytable1
write:
- myschema2.mytable2
roleB:
owns:
sequences:
- myschema.mysequence
"""
expected_spec = {
'roleA': {
'can_login': True,
'has_personal_schema': False,
'is_superuser': False,
'attributes': [
'CREATEDB',
'CREATEROLE',
],
'member_of': [
'roleB',
],
'owns': {
'schemas': [
ObjectName('myschema'),
],
'tables': [
ObjectName('myschema', 'mytable'),
],
},
'privileges': {
'schemas': {
'read': [
ObjectName('myschema2'),
],
},
'tables': {
'read': [
ObjectName('myschema2', 'mytable1'),
],
'write': [
ObjectName('myschema2', 'mytable2'),
],
},
},
},
'roleB': {
'owns': {
'sequences': [ObjectName('myschema', 'mysequence')],
},
},
}
loaded_spec = yaml.load(yaml_spec)
actual_spec = spec_inspector.convert_spec_to_objectnames(loaded_spec)
assert actual_spec == expected_spec
def test_analyze_privileges(cursor):
"""
End-to-end test to assert a slew of high-level behavior. Note that this test is painful, but if
it breaks we _should_ have a lower-level unit test that breaks as well. This test is here to
make sure all the pieces fit together as expected.
We start with our roles' privileges in one state and then run analyze_roles() to end up in a
different state, asserting that the changes that were made are what we expect.
Starting state:
Role0:
tables:
read:
- schema0.table0
- schema1.*
- schema2.table5
write:
- schema1.* (only TRIGGER)
Role1:
sequences:
write:
- schema1.sequence2 (only UPDATE)
Role2:
privileges:
schemas:
write:
- schema0
- schema1
- schema2
Role3:
owns:
schemas:
- schema0
- schema1
- schema2
"""
unconverted_desired_spec = yaml.load("""
{role0}:
privileges:
tables:
write:
- {schema0}.*
- {schema1}.{table2}
{role1}:
privileges:
sequences:
read:
- {schema0}.{sequence1}
- {schema2}.*
{role2}:
privileges:
schemas:
write:
- {schema0}
- {schema1}
- {schema2}
{role3}:
owns:
schemas:
- {schema0}
- {schema1}
- {schema2}
""".format(role0=ROLES[0],
role1=ROLES[1],
role2=ROLES[2],
role3=ROLES[3],
schema0=SCHEMAS[0],
schema1=SCHEMAS[1],
schema2=SCHEMAS[2],
sequence1=SEQUENCES[1],
table2=TABLES[2]))
desired_spec = spec_inspector.convert_spec_to_objectnames(
unconverted_desired_spec)
expected_role0_changes = set(
[
# Revoke read schema2.table5 from role0
privs.Q_REVOKE_NONDEFAULT.format(
'SELECT', 'TABLE', quoted_object(SCHEMAS[2], TABLES[5]),
ROLES[0]),
# Revoke SELECT and TRIGGER from schema1.table3 from role0
privs.Q_REVOKE_NONDEFAULT.
format('SELECT', 'TABLE', quoted_object(SCHEMAS[1], TABLES[3]),
ROLES[0]),
privs.Q_REVOKE_NONDEFAULT.
format('TRIGGER',
'TABLE', quoted_object(SCHEMAS[1], TABLES[3]), ROLES[0]),
# Revoke default SELECT and TRIGGER privs on tables in schema1 from role0 (granted by role3)
privs.Q_REVOKE_DEFAULT.format(ROLES[3], SCHEMAS[1],
'SELECT', 'TABLES', ROLES[0]),
privs.Q_REVOKE_DEFAULT.format(ROLES[3], SCHEMAS[1], 'TRIGGER',
'TABLES', ROLES[0]),
# Grant default read on tables in schema0 to role0 from role3 and role2 (both own objects)
privs.Q_GRANT_DEFAULT.format(ROLES[3], SCHEMAS[0],
'SELECT', 'TABLES', ROLES[0]),
privs.Q_GRANT_DEFAULT.format(ROLES[2], SCHEMAS[0],
'SELECT', 'TABLES', ROLES[0]),
# Grant read on all tables in schema0 except schema0.table0 (it already has read)
privs.Q_GRANT_NONDEFAULT
.format('SELECT', 'TABLE', quoted_object(SCHEMAS[0], TABLES[1]),
ROLES[0]),
]
# Grant write on schema1.table2 to role0 (already has SELECT and TRIGGER)
+ [
privs.Q_GRANT_NONDEFAULT.format(
priv, 'TABLE', quoted_object(SCHEMAS[1], TABLES[2]), ROLES[0])
for priv in privs.PRIVILEGE_MAP['tables']['write']
if priv not in ('SELECT', 'TRIGGER')
]
# Grant default write on tables in schema0 to role0 from role3 and role2 (both own objects)
+ [
privs.Q_GRANT_DEFAULT.format(r, SCHEMAS[0], priv, 'TABLES',
ROLES[0])
for priv in privs.PRIVILEGE_MAP['tables']['write']
for r in (ROLES[2], ROLES[3])
]
# Grant write on all tables in schema0 to role0
+ [
privs.Q_GRANT_NONDEFAULT.format(
priv, 'TABLE', quoted_object(SCHEMAS[0], t), ROLES[0])
for priv in privs.PRIVILEGE_MAP['tables']['write']
for t in (TABLES[0], TABLES[1])
])
expected_role1_changes = set([
# Revoke UPDATE for schema1.sequence2 from role1
privs.Q_REVOKE_NONDEFAULT.format(
'UPDATE', 'SEQUENCE', quoted_object(SCHEMAS[1], SEQUENCES[2]),
ROLES[1]),
# Grant read for schema0.sequence1 to role1
privs.Q_GRANT_NONDEFAULT.format(
'SELECT', 'SEQUENCE', quoted_object(SCHEMAS[0], SEQUENCES[1]),
ROLES[1]),
# Grant default read for sequences in schema2 to role1 from role3 (schema owner)
# and role2 (owns all sequences in schema)
privs.Q_GRANT_DEFAULT.format(ROLES[2], SCHEMAS[2], 'SELECT',
'SEQUENCES', ROLES[1]),
privs.Q_GRANT_DEFAULT.format(ROLES[3], SCHEMAS[2], 'SELECT',
'SEQUENCES', ROLES[1]),
# Grant read on all sequences in schema2 since we're granting default read
privs.Q_GRANT_NONDEFAULT.format(
'SELECT', 'SEQUENCE', quoted_object(SCHEMAS[2], SEQUENCES[4]),
ROLES[1]),
privs.Q_GRANT_NONDEFAULT.format(
'SELECT', 'SEQUENCE', quoted_object(SCHEMAS[2], SEQUENCES[5]),
ROLES[1]),
])
expected_role2_changes = set([
# role2 has write access on schema0 but doesn't have read access; this will be granted
privs.Q_GRANT_NONDEFAULT.format('USAGE', 'SCHEMA', s, ROLES[2])
for s in SCHEMAS[:3]
])
expected = expected_role0_changes.union(expected_role1_changes).union(
expected_role2_changes)
all_sql_to_run = privs.analyze_privileges(desired_spec,
cursor,
verbose=False)
actual = set(all_sql_to_run)
expected_but_not_actual = expected.difference(actual)
actual_but_not_expected = actual.difference(expected)
assert expected_but_not_actual == set()
assert actual_but_not_expected == set()
