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()