def test_schemaanalyzer_existing_personal_schema_change_object_owners(
mockdbcontext):
personal_schema = ROLES[0]
mockdbcontext.get_schema_owner = lambda x: ROLES[0]
mockdbcontext.get_schema_objects = lambda x: [
ObjectInfo('tables', ObjectName(personal_schema, TABLES[0]), ROLES[0],
False),
ObjectInfo('sequences', ObjectName(personal_schema, SEQUENCES[0]),
ROLES[0], False),
ObjectInfo('tables', ObjectName(personal_schema, TABLES[1]), ROLES[1],
False),
ObjectInfo('sequences', ObjectName(personal_schema, SEQUENCES[1]),
ROLES[1], False),
]
schemaconf = own.SchemaAnalyzer(ROLES[0],
objname=ObjectName(personal_schema),
dbcontext=mockdbcontext,
is_personal_schema=True)
actual = schemaconf.analyze()
expected = [
own.Q_SET_OBJECT_OWNER.format('TABLE',
quoted_object(ROLES[0], TABLES[1]),
ROLES[0], ROLES[1]),
own.Q_SET_OBJECT_OWNER.format('SEQUENCE',
quoted_object(ROLES[0], SEQUENCES[1]),
ROLES[0], ROLES[1]),
]
assert actual == expected
def test_analyze_ownerships_nonschemas(cursor):
spec = {
ROLES[0]: {
'owns': {
'tables': ['{}.*'.format(SCHEMAS[0])]
},
},
ROLES[1]: {
'owns': {
'sequences': [
quoted_object(SCHEMAS[1], SEQUENCES[0]),
quoted_object(SCHEMAS[1], SEQUENCES[1]),
]
},
},
}
actual = own.analyze_ownerships(spec, cursor, verbose=False)
expected = set([
own.Q_SET_OBJECT_OWNER.format('TABLE',
quoted_object(SCHEMAS[0], TABLES[1]),
ROLES[0], ROLES[1]),
own.Q_SET_OBJECT_OWNER.format('TABLE',
quoted_object(SCHEMAS[0], TABLES[2]),
ROLES[0], ROLES[1]),
own.Q_SET_OBJECT_OWNER.format('SEQUENCE',
quoted_object(SCHEMAS[1], SEQUENCES[1]),
ROLES[1], ROLES[0]),
])
assert set(actual) == expected
def test_get_schema_objects_sequences(mockdbcontext):
""" While this test is almost identical to test_get_schema_objects_tables(), it's here because
we want to ensure we have coverage over more than just tables """
objattributes = {'owner': ROLES[0], 'is_dependent': False}
all_attributes = {
quoted_object(SCHEMAS[0], seq): objattributes
for seq in SEQUENCES
}
mockdbcontext.get_all_object_attributes = lambda: {
'sequences': {
SCHEMAS[0]: all_attributes
}
}
privconf = privs.PrivilegeAnalyzer(rolename=DUMMY,
access=DUMMY,
object_kind='sequences',
desired_items=DUMMY,
schema_writers=DUMMY,
personal_schemas=DUMMY,
dbcontext=mockdbcontext)
# Assert that we get back the sequences that are in the context object
actual = privconf.get_schema_objects(SCHEMAS[0])
expected = set([quoted_object(SCHEMAS[0], seq) for seq in SEQUENCES])
assert actual == expected
def test_get_all_nonschema_objects_and_owners(cursor):
dbcontext = context.DatabaseContext(cursor, verbose=True)
expected = {
SCHEMAS[0]: [
context.ObjectInfo('tables', quoted_object(SCHEMAS[0], TABLES[0]),
ROLES[0], False),
context.ObjectInfo('sequences',
quoted_object(SCHEMAS[0], SEQUENCES[1]),
ROLES[0], False),
],
SCHEMAS[1]: [
context.ObjectInfo('tables', quoted_object(SCHEMAS[1], TABLES[0]),
ROLES[1], False),
context.ObjectInfo('sequences',
quoted_object(SCHEMAS[1], SEQUENCES[2]),
ROLES[1], False),
],
}
actual = dbcontext.get_all_nonschema_objects_and_owners()
# We are deliberately not checking pg_catalog or information_schema here since that's a
# lot of work and those should not be touched
for k, v in expected.items():
assert set(v) == set(actual[k])
# Make sure that this data is cached for future use
cursor.close()
actual_again = dbcontext.get_all_nonschema_objects_and_owners()
assert actual_again == actual
def test_nonschemaanalyzer_expand_schema_objects(mockdbcontext):
mockdbcontext.get_all_object_attributes = lambda: {
'tables': {
SCHEMAS[0]: {
quoted_object(SCHEMAS[0], TABLES[0]): {
'owner': DUMMY,
'is_dependent': False
},
quoted_object(SCHEMAS[0], TABLES[1]): {
'owner': DUMMY,
'is_dependent': False
},
quoted_object(SCHEMAS[0], TABLES[2]): {
'owner': DUMMY,
'is_dependent': True
},
},
},
}
nsa = own.NonschemaAnalyzer(rolename=ROLES[0],
objname=DUMMY,
objkind='tables',
dbcontext=mockdbcontext)
actual = nsa.expand_schema_objects(SCHEMAS[0])
expected = [
quoted_object(SCHEMAS[0], TABLES[0]),
quoted_object(SCHEMAS[0], TABLES[1])
]
assert set(actual) == set(expected)
def test_get_object_owner(mockdbcontext, object_kind, item, expected):
mockdbcontext.get_all_object_owners = lambda: {
'schemas': {
SCHEMAS[0]: {
SCHEMAS[0]: ROLES[0]
},
},
'sequences': {
SCHEMAS[0]: {
quoted_object(SCHEMAS[0], SEQUENCES[0]): ROLES[1]
},
},
'tables': {
SCHEMAS[0]: {
quoted_object(SCHEMAS[0], TABLES[1]): ROLES[2]
},
},
}
privconf = privs.PrivilegeAnalyzer(rolename=DUMMY,
access=DUMMY,
object_kind=object_kind,
desired_items=DUMMY,
schema_writers=DUMMY,
personal_schemas=DUMMY,
dbcontext=mockdbcontext)
actual = privconf.get_object_owner(item)
assert actual == expected
def test_get_schema_objects_no_entry():
dbcontext = context.DatabaseContext(cursor=DUMMY, verbose=False)
dbcontext._cache['get_all_nonschema_objects_and_owners'] = lambda: {
'foo': [
context.ObjectInfo('tables', quoted_object(SCHEMAS[0], TABLES[0]), ROLES[0], False),
context.ObjectInfo('sequences', quoted_object(SCHEMAS[0], SEQUENCES[1]), ROLES[0], False),
],
}
actual = dbcontext.get_schema_objects('key_not_in_response')
assert actual == []
def test_get_schema_objects():
schema = 'foo'
expected = [
context.ObjectInfo('tables', quoted_object(SCHEMAS[0], TABLES[0]), ROLES[0], False),
context.ObjectInfo('sequences', quoted_object(SCHEMAS[0], SEQUENCES[1]), ROLES[0], False),
]
dbcontext = context.DatabaseContext(cursor=DUMMY, verbose=False)
dbcontext._cache['get_all_nonschema_objects_and_owners'] = lambda: {schema: expected}
actual = dbcontext.get_schema_objects(schema)
assert actual == expected
def test_get_role_objects_with_access(access, expected):
dbcontext = context.DatabaseContext(cursor=DUMMY, verbose=True)
dbcontext._cache['get_all_current_nondefaults'] = lambda: {
ROLES[0]: {
'tables': {
'read': set([
(quoted_object(SCHEMAS[0], TABLES[0]), 'SELECT'),
(quoted_object(SCHEMAS[0], TABLES[1]), 'SELECT'),
])
}
}
}
actual = dbcontext.get_role_objects_with_access(ROLES[0], SCHEMAS[0], 'tables', access)
assert actual == expected
def test_schemaanalyzer_get_improperly_owned_objects(mockdbcontext):
mockdbcontext.get_schema_owner = lambda x: ROLES[0]
mockdbcontext.get_schema_objects = lambda x: [
# Properly owned
ObjectInfo('tables', quoted_object(ROLES[0], TABLES[0]), ROLES[0],
False),
ObjectInfo('sequences', quoted_object(ROLES[0], SEQUENCES[0]), ROLES[
0], False),
# Improperly owned
ObjectInfo('tables', quoted_object(ROLES[0], TABLES[1]), ROLES[1],
False),
ObjectInfo('sequences', quoted_object(ROLES[0], SEQUENCES[1]), ROLES[
1], False),
# Improperly owned but dependent (i.e. should be skipped)
ObjectInfo('sequences', quoted_object(ROLES[0], SEQUENCES[2]), ROLES[
1], True),
]
schema = ROLES[0]
schemaconf = own.SchemaAnalyzer(rolename=ROLES[0],
schema=schema,
dbcontext=mockdbcontext,
is_personal_schema=True)
actual = schemaconf.get_improperly_owned_objects()
expected = [('tables', quoted_object(schema, TABLES[1]), ROLES[1]),
('sequences', quoted_object(schema, SEQUENCES[1]), ROLES[1])]
assert set(actual) == set(expected)
def test_alter_object_owner(mockdbcontext):
previous_owner = ROLES[1]
owner = ROLES[0]
schema = SCHEMAS[0]
table_name = quoted_object(schema, TABLES[0])
mockdbcontext.get_schema_owner = lambda x: owner
schemaconf = own.SchemaAnalyzer(owner, schema=schema, dbcontext=mockdbcontext)
schemaconf.alter_object_owner('tables', table_name, previous_owner)
assert schemaconf.sql_to_run == [own.Q_SET_OBJECT_OWNER.format('TABLE', table_name, owner, previous_owner)]
def test_get_schema_objects_tables(mockdbcontext):
ownership = {quoted_object(SCHEMAS[0], t): ROLES[0] for t in TABLES}
mockdbcontext.get_all_object_owners = lambda: {
'tables': {
SCHEMAS[0]: ownership
}
}
privconf = privs.PrivilegeAnalyzer(rolename=DUMMY,
access=DUMMY,
object_kind='tables',
desired_items=DUMMY,
schema_writers=DUMMY,
personal_schemas=DUMMY,
dbcontext=mockdbcontext)
# Assert that we get back the tables that are in the context object
actual = privconf.get_schema_objects(SCHEMAS[0])
expected = set([quoted_object(SCHEMAS[0], t) for t in TABLES])
assert actual == expected
def test_get_all_current_nondefaults(cursor):
dbcontext = context.DatabaseContext(cursor, verbose=True)
expected = {
ROLES[0]: {
'tables': {
'read':
set([
(quoted_object(SCHEMAS[0], TABLES[1]), 'SELECT'),
(quoted_object(SCHEMAS[1], TABLES[3]), 'SELECT'),
]),
'write':
set(),
}
},
ROLES[2]: {
'schemas': {
'read': set(),
'write': set([
(SCHEMAS[0], 'CREATE'),
(SCHEMAS[1], 'CREATE'),
]),
}
},
ROLES[3]: {
'schemas': {
'read': set(),
'write': set([
(SCHEMAS[0], 'CREATE'),
(SCHEMAS[1], 'CREATE'),
]),
}
}
}
actual = dbcontext.get_all_current_nondefaults()
assert actual == expected
# Make sure that this data is cached for future use
cursor.close()
actual_again = dbcontext.get_all_current_nondefaults()
assert actual_again == expected
def test_analyze_ownerships_schemas_and_nonschemas(cursor):
"""
This is just a combination of the related schema and nonschema tests to make sure the pieces
fit together.
"""
spec = {
ROLES[0]: {
'has_personal_schema': True,
'owns': {
'tables': ['{}.*'.format(SCHEMAS[0])],
'schemas': [SCHEMAS[2]],
},
},
ROLES[1]: {
'owns': {
'sequences': [
quoted_object(SCHEMAS[1], SEQUENCES[0]),
quoted_object(SCHEMAS[1], SEQUENCES[1]),
]
},
},
}
actual = own.analyze_ownerships(spec, cursor, verbose=False)
expected = set([
own.Q_SET_OBJECT_OWNER.format('TABLE',
quoted_object(SCHEMAS[0], TABLES[1]),
ROLES[0], ROLES[1]),
own.Q_SET_OBJECT_OWNER.format('TABLE',
quoted_object(SCHEMAS[0], TABLES[2]),
ROLES[0], ROLES[1]),
own.Q_SET_OBJECT_OWNER.format('SEQUENCE',
quoted_object(SCHEMAS[1], SEQUENCES[1]),
ROLES[1], ROLES[0]),
own.Q_CREATE_SCHEMA.format(ROLES[0], ROLES[0]),
own.Q_CREATE_SCHEMA.format(SCHEMAS[2], ROLES[0]),
])
assert set(actual) == expected
def test_analyze_defaults(mockdbcontext):
mockdbcontext.get_role_current_defaults = lambda x, y, z: set([
(ROLES[3], SCHEMAS[0], 'SELECT'),
])
mockdbcontext.get_role_current_nondefaults = lambda x, y, z: set()
mockdbcontext.get_all_object_attributes = lambda: {
'tables': {
SCHEMAS[0]: {
quoted_object(SCHEMAS[0], TABLES[0]): {
'owner': ROLES[2],
'is_dependent': False
},
},
},
'schemas': {
SCHEMAS[0]: {
SCHEMAS[0]: {
'owner': ROLES[1],
'is_dependent': False
},
},
},
}
desired_items = ['{}.*'.format(SCHEMAS[0])]
schema_writers = {
SCHEMAS[0]: set([ROLES[1], ROLES[2]]),
}
privconf = privs.PrivilegeAnalyzer(rolename=ROLES[0],
access='read',
object_kind='tables',
desired_items=desired_items,
schema_writers=schema_writers,
personal_schemas=DUMMY,
dbcontext=mockdbcontext)
# Run analyze_defaults(); note that we have to do identify_desired_objects()
# first to set things up
privconf.identify_desired_objects()
privconf.analyze_defaults()
expected = [
privs.Q_REVOKE_DEFAULT.format(ROLES[3], SCHEMAS[0], 'SELECT', 'TABLES',
ROLES[0]),
privs.Q_GRANT_DEFAULT.format(ROLES[1], SCHEMAS[0], 'SELECT', 'TABLES',
ROLES[0]),
privs.Q_GRANT_DEFAULT.format(ROLES[2], SCHEMAS[0], 'SELECT', 'TABLES',
ROLES[0]),
]
assert set(expected) == set(privconf.sql_to_run)
def test_revoke_nondefault(mockdbcontext):
table = quoted_object(SCHEMAS[0], TABLES[0])
rolename = ROLES[0]
# Revoke the privilege
privconf = privs.PrivilegeAnalyzer(rolename=rolename,
access=DUMMY,
object_kind='tables',
desired_items=DUMMY,
schema_writers=DUMMY,
personal_schemas=DUMMY,
dbcontext=mockdbcontext)
privconf.revoke_nondefault(table, 'SELECT')
expected = [
privs.Q_REVOKE_NONDEFAULT.format('SELECT', 'TABLE', table, rolename)
]
assert privconf.sql_to_run == expected
def test_nonschemaanalyzer_analyze_no_changed_needed(mockdbcontext):
objname = quoted_object(SCHEMAS[0], TABLES[0])
mockdbcontext.get_all_object_attributes = lambda: {
'tables': {
SCHEMAS[0]: {
objname: {
'owner': ROLES[0],
'is_dependent': False
},
},
},
}
nsa = own.NonschemaAnalyzer(rolename=ROLES[0],
objname=objname,
objkind='tables',
dbcontext=mockdbcontext)
actual = nsa.analyze()
assert actual == []
def test_nonschemaanalyzer_analyze_without_schema_expansion(mockdbcontext):
objname = quoted_object(SCHEMAS[0], TABLES[0])
mockdbcontext.get_all_object_attributes = lambda: {
'tables': {
SCHEMAS[0]: {
objname: {
'owner': ROLES[1],
'is_dependent': False
},
},
},
}
nsa = own.NonschemaAnalyzer(rolename=ROLES[0],
objname=objname,
objkind='tables',
dbcontext=mockdbcontext)
actual = nsa.analyze()
expected = [
own.Q_SET_OBJECT_OWNER.format('TABLE', objname, ROLES[0], ROLES[1])
]
assert actual == expected
def test_get_all_object_owners(cursor):
dbcontext = context.DatabaseContext(cursor, verbose=True)
expected = {
'tables': {
SCHEMAS[0]: {
quoted_object(SCHEMAS[0], TABLES[0]): ROLES[1],
quoted_object(SCHEMAS[0], TABLES[1]): ROLES[1],
quoted_object(SCHEMAS[0], TABLES[2]): ROLES[3],
}
},
'sequences': {
SCHEMAS[0]: {
quoted_object(SCHEMAS[0], SEQUENCES[0]): ROLES[1],
quoted_object(SCHEMAS[0], SEQUENCES[1]): ROLES[2],
quoted_object(SCHEMAS[0], SEQUENCES[2]): ROLES[2],
}
},
'schemas': {
SCHEMAS[0]: {
SCHEMAS[0]: ROLES[0],
},
'public': {
'public': 'postgres',
}
}
}
actual = dbcontext.get_all_object_owners()
# We do this to avoid having to look at / filter out entries from
# information_schema or pg_catalog
for key in expected.keys():
expected_entries = expected[key][SCHEMAS[0]]
actual_entries = actual[key][SCHEMAS[0]]
assert expected_entries == actual_entries
# Make sure that this data is cached for future use
cursor.close()
actual_again = dbcontext.get_all_object_owners()
assert actual_again == actual
def test_nonschemaanalyzer_analyze_with_schema_expansion(mockdbcontext):
mockdbcontext.get_all_object_attributes = lambda: {
'sequences': {
SCHEMAS[0]: {
quoted_object(SCHEMAS[0], SEQUENCES[0]): {
'owner': ROLES[1],
'is_dependent': False
},
quoted_object(SCHEMAS[0], SEQUENCES[1]): {
'owner': ROLES[2],
'is_dependent': False
},
# This will be skipped as the owner is correct
quoted_object(SCHEMAS[0], SEQUENCES[2]): {
'owner': ROLES[0],
'is_dependent': False
},
# This will be skipped as it is dependent
quoted_object(SCHEMAS[0], SEQUENCES[3]): {
'owner': ROLES[1],
'is_dependent': True
},
},
},
}
nsa = own.NonschemaAnalyzer(rolename=ROLES[0],
objname='{}.*'.format(SCHEMAS[0]),
objkind='sequences',
dbcontext=mockdbcontext)
actual = nsa.analyze()
expected = [
own.Q_SET_OBJECT_OWNER.format('SEQUENCE',
quoted_object(SCHEMAS[0], SEQUENCES[0]),
ROLES[0], ROLES[1]),
own.Q_SET_OBJECT_OWNER.format('SEQUENCE',
quoted_object(SCHEMAS[0], SEQUENCES[1]),
ROLES[0], ROLES[2]),
]
assert set(actual) == set(expected)
privconf = privs.PrivilegeAnalyzer(rolename=DUMMY,
access=DUMMY,
object_kind='sequences',
desired_items=DUMMY,
schema_writers=DUMMY,
personal_schemas=DUMMY,
dbcontext=mockdbcontext)
# Assert that we get back the sequences that are in the context object
actual = privconf.get_schema_objects(SCHEMAS[0])
expected = set([quoted_object(SCHEMAS[0], seq) for seq in SEQUENCES])
assert actual == expected
@pytest.mark.parametrize('object_kind, item, expected', [
('schemas', SCHEMAS[0], ROLES[0]),
('sequences', quoted_object(SCHEMAS[0], SEQUENCES[0]), ROLES[1]),
('tables', quoted_object(SCHEMAS[0], TABLES[1]), ROLES[2]),
])
def test_get_object_owner(mockdbcontext, object_kind, item, expected):
mockdbcontext.get_all_object_owners = lambda: {
'schemas': {
SCHEMAS[0]: {
SCHEMAS[0]: ROLES[0]
},
},
'sequences': {
SCHEMAS[0]: {
quoted_object(SCHEMAS[0], SEQUENCES[0]): ROLES[1]
},
},
'tables': {
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()
def test_analyze_nondefaults(mockdbcontext):
"""
Test that:
- schema0.* is expanded out and all are granted
- an existing and desired grant is skipped (schema0.table1)
- a desired but non-existent grant is made (schema0.table0 and schema1.table2)
- an existing but undesired grant is revoked (schema1.table3)
Setup:
- schema0.table0 (owned by role2) - DESIRED --> GRANT
- schema0.table1 (owned by role3) - GRANTED DESIRED
- schema1.table2 (owned by role2) - DESIRED --> GRANT
- schema1.table3 (owned by role3) - GRANTED --> REVOKE
"""
mockdbcontext.get_role_current_nondefaults = lambda x, y, z: set([
(ObjectName(SCHEMAS[0], TABLES[1]), 'SELECT'),
(ObjectName(SCHEMAS[1], TABLES[3]), 'SELECT'),
])
mockdbcontext.get_all_object_attributes = lambda: {
'schemas': {
SCHEMAS[0]: {
ObjectName(SCHEMAS[0]): {
'owner': ROLES[1],
'is_dependent': False
}
},
SCHEMAS[1]: {
ObjectName(SCHEMAS[1]): {
'owner': ROLES[1],
'is_dependent': False
}
},
},
'tables': {
SCHEMAS[0]: {
ObjectName(SCHEMAS[0], TABLES[0]): {
'owner': ROLES[2],
'is_dependent': False
},
ObjectName(SCHEMAS[0], TABLES[1]): {
'owner': ROLES[3],
'is_dependent': False
},
},
SCHEMAS[1]: {
ObjectName(SCHEMAS[1], TABLES[2]): {
'owner': ROLES[2],
'is_dependent': False
},
ObjectName(SCHEMAS[1], TABLES[3]): {
'owner': ROLES[3],
'is_dependent': False
},
},
}
}
desired_items = [
ObjectName(SCHEMAS[0], '*'),
ObjectName(SCHEMAS[1], TABLES[2]),
]
dummy_schema_writers = defaultdict(set)
privconf = privs.PrivilegeAnalyzer(rolename=ROLES[0],
access='read',
object_kind='tables',
desired_items=desired_items,
schema_writers=dummy_schema_writers,
personal_schemas=DUMMY,
dbcontext=mockdbcontext)
expected = set([
# Grant for schema0.table0
privs.Q_GRANT_NONDEFAULT.format('SELECT', 'TABLE',
quoted_object(SCHEMAS[0], TABLES[0]),
ROLES[0]),
# Grant for schema1.table2
privs.Q_GRANT_NONDEFAULT.format('SELECT', 'TABLE',
quoted_object(SCHEMAS[1], TABLES[2]),
ROLES[0]),
# Revoke for schema1.table3
privs.Q_REVOKE_NONDEFAULT.format('SELECT', 'TABLE',
quoted_object(SCHEMAS[1], TABLES[3]),
ROLES[0]),
])
# Run analyze_nondefaults(); note that we have to do identify_desired_objects()
# first to set things up
privconf.identify_desired_objects()
privconf.analyze_nondefaults()
actual = privconf.sql_to_run
assert expected.difference(actual) == set()
dbcontext._cache['get_all_current_nondefaults'] = lambda: {
'role1': {
'object_kind1': {
'access1': set([1, 2, 3])
}
}
}
assert dbcontext.get_role_current_nondefaults(rolename, object_kind,
access) == expected
@pytest.mark.parametrize('access, expected', [
('write', set()),
('read',
set([
quoted_object(SCHEMAS[0], TABLES[0]),
quoted_object(SCHEMAS[0], TABLES[1])
])),
])
def test_get_role_objects_with_access(access, expected):
dbcontext = context.DatabaseContext(cursor=DUMMY, verbose=True)
dbcontext._cache['get_all_current_nondefaults'] = lambda: {
ROLES[0]: {
'tables': {
'read':
set([
(quoted_object(SCHEMAS[0], TABLES[0]), 'SELECT'),
(quoted_object(SCHEMAS[0], TABLES[1]), 'SELECT'),
])
}
}
def test_identify_desired_objects_personal_schemas_object_kind_is_not_schema(
mockdbcontext):
""" Make sure that if we desire 'personal_schemas.*' and the object_kind is something
other than 'schema' that items in personal schemas show up in the the desired_nondefaults and
the personal schemas show up in the desired_defaults """
mockdbcontext.get_all_object_owners = lambda: {
'tables': {
SCHEMAS[0]: {
quoted_object(SCHEMAS[0], TABLES[0]): ROLES[1],
},
ROLES[2]: {
quoted_object(ROLES[2], TABLES[2]): ROLES[2],
quoted_object(ROLES[2], TABLES[3]): ROLES[2],
},
ROLES[3]: {
quoted_object(ROLES[3], TABLES[4]): ROLES[3],
quoted_object(ROLES[3], TABLES[5]): ROLES[3],
},
},
'schemas': {
SCHEMAS[0]: {
SCHEMAS[0]: ROLES[1]
},
ROLES[2]: {
ROLES[2]: ROLES[2]
},
ROLES[3]: {
ROLES[3]: ROLES[3]
},
},
}
personal_schemas = ROLES[2:4]
access = 'read'
object_kind = 'tables'
desired_items = [
'{}.{}'.format(SCHEMAS[0], TABLES[0]), 'personal_schemas.*'
]
schema_writers = {
ROLES[2]: set([ROLES[2], ROLES[1]]),
ROLES[3]: set([ROLES[3]]),
}
privconf = privs.PrivilegeAnalyzer(ROLES[0],
access=access,
object_kind=object_kind,
desired_items=desired_items,
schema_writers=schema_writers,
personal_schemas=personal_schemas,
dbcontext=mockdbcontext)
privconf.identify_desired_objects()
# Check default privileges
possible_privs = privs.PRIVILEGE_MAP[object_kind][access]
expected_defaults = set([(ROLES[2], ROLES[2], possible_privs[0]),
(ROLES[1], ROLES[2], possible_privs[0]),
(ROLES[3], ROLES[3], possible_privs[0])])
actual_defaults = privconf.desired_defaults
assert actual_defaults == expected_defaults
# Check non-default privileges
expected_nondefault_items = [
quoted_object(SCHEMAS[0], TABLES[0]),
quoted_object(ROLES[2], TABLES[2]),
quoted_object(ROLES[2], TABLES[3]),
quoted_object(ROLES[3], TABLES[4]),
quoted_object(ROLES[3], TABLES[5]),
]
expected_nondefaults = set(
itertools.product(expected_nondefault_items, possible_privs))
actual_nondefaults = privconf.desired_nondefaults
assert actual_nondefaults == expected_nondefaults
def test_identify_desired_objects(rolename, mockdbcontext):
"""
Verify a variety of aspects of the PrivilegeAnalyzer.identify_desired_objects()
method, including:
* We properly deal with schema.*
* When a schema owner doesn't have any objects it shows up in our default privileges set
* When a schema owner doesn't have any objects it does not show up in our non default
privileges set
* It doesn't matter to this method whether the role in question owns the schema, tables, or
anything else (that's the mark.parametrize part)
"""
# Using sequence-write because it has 2 types of privileges (i.e. >1 but not a ton)
object_kind = 'sequences'
access = 'write'
mockdbcontext.get_all_object_owners = lambda: {
'sequences': {
SCHEMAS[0]: {
quoted_object(SCHEMAS[0], SEQUENCES[0]): ROLES[1],
quoted_object(SCHEMAS[0], SEQUENCES[1]): ROLES[2],
quoted_object(SCHEMAS[0], SEQUENCES[2]): ROLES[2],
},
SCHEMAS[1]: {
quoted_object(SCHEMAS[1], SEQUENCES[0]): ROLES[2],
quoted_object(SCHEMAS[1], SEQUENCES[1]): ROLES[1],
},
},
'schemas': {
SCHEMAS[0]: {
SCHEMAS[0]: ROLES[0]
},
SCHEMAS[1]: {
SCHEMAS[1]: ROLES[0]
},
},
}
desired_items = [
'{}.*'.format(SCHEMAS[0]), '{}.{}'.format(SCHEMAS[1], SEQUENCES[0]),
'{}.{}'.format(SCHEMAS[1], SEQUENCES[1])
]
schema_writers = {SCHEMAS[0]: set(ROLES[:3])}
privconf = privs.PrivilegeAnalyzer(rolename,
access=access,
object_kind=object_kind,
desired_items=desired_items,
schema_writers=schema_writers,
personal_schemas=DUMMY,
dbcontext=mockdbcontext)
privconf.identify_desired_objects()
# We don't grant default privileges when the grantor is the role itself because in that case
# the role already can access the objects (since it owns them). As a result, we need to remove
# the grantee role from this list. We need this `if` check because there is one role (role3)
# that doesn't own anything and shouldn't show up in our list
possible_privs = privs.PRIVILEGE_MAP[object_kind][access]
roles = list(ROLES[:3])
# We have to remove this role since we don't grant default privileges to ourselves
roles.remove(rolename)
expected_defaults = set(
itertools.product(roles, [SCHEMAS[0]], possible_privs))
actual_defaults = privconf.desired_defaults
assert actual_defaults == expected_defaults
nondefault_items = [quoted_object(SCHEMAS[0], t) for t in SEQUENCES[0:3]] \
+ [quoted_object(SCHEMAS[1], t) for t in SEQUENCES[:2]]
# Remove things owned by the given role
if rolename == ROLES[1]:
nondefault_items.remove(quoted_object(SCHEMAS[0], SEQUENCES[0]))
nondefault_items.remove(quoted_object(SCHEMAS[1], SEQUENCES[1]))
elif rolename == ROLES[2]:
nondefault_items.remove(quoted_object(SCHEMAS[0], SEQUENCES[1]))
nondefault_items.remove(quoted_object(SCHEMAS[0], SEQUENCES[2]))
nondefault_items.remove(quoted_object(SCHEMAS[1], SEQUENCES[0]))
expected_nondefaults = set(
itertools.product(nondefault_items, possible_privs))
actual_nondefaults = privconf.desired_nondefaults
assert actual_nondefaults == expected_nondefaults
