def test_analyze_privileges_skips_superuser(cursor):
desired_spec = yaml.load("""
{role0}:
privileges:
tables:
write:
- foo.bar
sequences:
write:
- baz.bip
""".format(role0=ROLES[0]))
actual = privs.analyze_privileges(desired_spec, cursor, verbose=False)
expected = [
privs.SKIP_SUPERUSER_PRIVILEGE_CONFIGURATION_MSG.format(ROLES[0])
]
assert actual == expected
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 configure(spec_path, host, port, user, password, dbname, prompt, attributes, memberships,
ownerships, privileges, live, verbose, attributes_source_table):
"""
Configure the role attributes, memberships, object ownerships, and/or privileges of a
database cluster to match a desired spec.
Note that attributes and memberships are database cluster-wide settings, i.e. they are the
same across multiple databases within a given Postgres instance. Ownerships and privileges
are specific to each individual database within a Postgres instance.
Inputs:
spec_path - str; the path for the configuration file
host - str; the database server host
port - str; the database server port
user - str; the database user name
password - str; the database user's password
dbname - str; the database to connect to and configure
prompt - bool; whether to prompt for a password
attributes - bool; whether to configure the role attributes for the specified
database cluster
memberships - bool; whether to configure the role memberships for the specified
database cluster
ownerships - bool; whether to configure the ownerships for the specified database
privileges - bool; whether to configure the privileges for the specified database
live - bool; whether to apply the changes (True) or just show what changes
would be made without actually appyling them (False)
verbose - bool; whether to show all queries that are executed and all debug log
messages during execution
attributes_source_table - str; the table to read use attributes from (pg_authid or pg_roles)
"""
if verbose:
root_logger = logging.getLogger('')
root_logger.setLevel(logging.DEBUG)
if prompt:
password = getpass.getpass()
db_connection = common.get_db_connection(host, port, dbname, user, password)
cursor = db_connection.cursor(cursor_factory=psycopg2.extras.DictCursor)
spec = load_spec(spec_path, cursor, verbose, attributes, memberships, ownerships, privileges)
sql_to_run = []
password_changed = False # Initialize this in case the attributes module isn't run
if attributes:
sql_to_run.append(create_divider('attributes'))
# Password changes happen within the attributes.py module itself so we don't leak
# passwords; as a result we need to see if password changes occurred
module_sql, all_password_sql_to_run = analyze_attributes(spec, cursor, verbose, attributes_source_table)
run_module_sql(module_sql, cursor, verbose)
if all_password_sql_to_run:
password_changed = True
run_password_sql(cursor, all_password_sql_to_run)
sql_to_run.extend(module_sql)
if memberships:
sql_to_run.append(create_divider('memberships'))
module_sql = analyze_memberships(spec, cursor, verbose)
run_module_sql(module_sql, cursor, verbose)
sql_to_run.extend(module_sql)
if ownerships:
sql_to_run.append(create_divider('ownerships'))
module_sql = analyze_ownerships(spec, cursor, verbose)
run_module_sql(module_sql, cursor, verbose)
sql_to_run.extend(module_sql)
if privileges:
sql_to_run.append(create_divider('privileges'))
module_sql = analyze_privileges(spec, cursor, verbose)
run_module_sql(module_sql, cursor, verbose)
sql_to_run.extend(module_sql)
changed = password_changed or has_changes(sql_to_run)
if changed and live:
logger.debug('Committing changes')
db_connection.commit()
else:
db_connection.rollback()
# Make sure there is at least 1 line with a real change (vs. all headers)
if changed:
click.secho(HEADER.format('LIVE' if live else 'CHECK'), fg='green')
for statement in sql_to_run:
click.secho(statement, fg='green')
else:
click.secho(SUCCESS_MSG, fg='green')
