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