def test_cross_project_attempt_to_grant_usage(self): admin = CLI(creds) db = "test_db" try: hacker_user = Expect({ **creds, **{ "PGUSER": '******', "PGDATABASE": db, "PGPASSWORD": Expect.TMP_PASSWORD } }) hacker_user.execute_template("sql/test_data_force_grant_1.sql.tpl", WORKSPACE=db, USER='******') con = Expect({ **creds, **{ "PGUSER": '******', "PGDATABASE": 'project_1', "PGPASSWORD": Expect.TMP_PASSWORD } }) con.expect_execute( "SELECT * from test_db.protected_data.table_1", 'cross-database references are not implemented') con.close() hacker_user.close() finally: admin.close() return self
def test_hacker_trying_to_create_schema(self): db = "test_db" admin_on_test_db = CLI({**creds, **{"PGDATABASE": db}}) try: # Create tables admin_on_test_db.execute( "GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA pg_catalog TO " + "user_x") hacker_user = Expect({ **creds, **{ "PGUSER": '******', "PGDATABASE": db, "PGPASSWORD": Expect.TMP_PASSWORD } }) hacker_user.execute_template("sql/test_table.sql.tpl", TABLE='user_x_table') admin_on_test_db.execute_template("sql/query_permissions.sql.tpl", USER='******') hacker_user.expect_execute( "CREATE SCHEMA %s;" % 'new_schema', 'permission denied for database test_db') hacker_user.close() finally: admin_on_test_db.close() return self
def test_user_connect_access(self): admin = CLI(creds) try: user = "******" db = "test_db" userdb = Expect(creds) admin.execute_template("sql/user.sql.tpl", USER=user, PASSWORD=Expect.TMP_PASSWORD) userdb.expect_connect(db, user, 'FATAL: permission denied for database') admin.execute_template("sql/user_connect.sql.tpl", APP_DATABASE=db, USER=user) userdb.expect_connect(db, user) admin_on_test_db = Expect({**creds, **{"PGDATABASE": db}}) admin_on_test_db.match_results( "sql/query_permissions.sql.tpl", "results/test_user_connect_access/perms.txt", USER='******') admin_on_test_db.close() userdb.close() finally: admin.close()
def test_hacker_granting_access_with_connect(self): db = "test_db" admin_on_test_db = CLI({**creds, **{"PGDATABASE": db}}) try: hacker_user = Expect({ **creds, **{ "PGUSER": '******', "PGDATABASE": db, "PGPASSWORD": Expect.TMP_PASSWORD } }) hacker_user.execute_template("sql/test_data_force_grant_1.sql.tpl", WORKSPACE=db, USER='******') # grant user connect admin_on_test_db.execute_template("sql/user_connect.sql.tpl", APP_DATABASE=db, USER='******') con = Expect({ **creds, **{ "PGUSER": '******', "PGDATABASE": db, "PGPASSWORD": Expect.TMP_PASSWORD } }) con.expect_execute("SELECT * from protected_data.table_1", 'permission denied for schema protected_data') admin_on_test_db.execute_template("sql/query_permissions.sql.tpl", USER='******') con.close() admin_on_test_db.close() finally: admin_on_test_db.close() return self
def test_single_project(self): admin = CLI(creds) try: db = "test_db" prep_db = Expect({**creds, **{"PGDATABASE": db}}) prep_db.execute_template("sql/test_data_project.sql.tpl") prep_db.close() user = Expect({ **creds, **{ "PGUSER": '******', "PGDATABASE": db, "PGPASSWORD": Expect.TMP_PASSWORD } }) user.expect_execute("SELECT * from pg_settings", 'permission denied for relation pg_settings') user.expect_success("SELECT * from protected_data.table_1") user.expect_execute( "CREATE TABLE protected_data.table_2 (name varchar(20));", 'permission denied for schema protected_data') user.expect_success( "CREATE TABLE working_data.table_2 (name varchar(20));") admin_on_test_db = Expect({**creds, **{"PGDATABASE": db}}) admin_on_test_db.match_results( "sql/query_permissions.sql.tpl", 'results/test_single_project/perms.txt', USER='******') admin_on_test_db.close() user.close() finally: admin.close()
def prepare_test_db(self): admin = CLI(creds, True) db = "test_db" try: admin.execute("CREATE DATABASE %s;" % db, True) admin.execute_template("sql/new_database.sql.tpl", APP_DATABASE=db) user = "******" admin_on_db = CLI({**creds, **{"PGDATABASE": db}}, True) admin_on_db.execute_template("sql/setup_new_database.sql.tpl") admin_on_db.execute_template("sql/setup_roles.sql.tpl", WORKSPACE=db) admin_on_db.execute_template("sql/user.sql.tpl", USER=user, PASSWORD=Expect.TMP_PASSWORD) admin_on_db.execute_template("sql/user_connect.sql.tpl", APP_DATABASE=db, USER=user) admin_on_db.execute_template("sql/setup_user.sql.tpl", WORKSPACE=db, USER=user) admin_on_db.execute_template("sql/setup_user_2.sql.tpl", WORKSPACE=db, USER=user) for n in range(10): admin_on_db.execute_template("sql/user.sql.tpl", USER="******" % n, PASSWORD=Expect.TMP_PASSWORD) admin_on_db.execute_template("sql/user_connect.sql.tpl", APP_DATABASE=db, USER="******" % n) finally: admin.close()
def test_incremental_user_access(self): db = "test_db" admin_on_test_db = CLI({**creds, **{"PGDATABASE": db}}) try: prep_db = Expect({**creds, **{"PGDATABASE": db}}) prep_db.execute_template("sql/test_data_project.sql.tpl") user = "******" admin_on_test_db.execute_template("sql/user.sql.tpl", USER=user, PASSWORD=Expect.TMP_PASSWORD) prep_db.expect_connect(db, user, 'User does not have CONNECT privilege.') admin_on_test_db.execute_template("sql/user_connect.sql.tpl", APP_DATABASE=db, USER=user) user_db = Expect({ **creds, **{ "PGUSER": user, "PGDATABASE": db, "PGPASSWORD": Expect.TMP_PASSWORD } }) user_db.expect_execute( "SELECT * from pg_settings", 'permission denied for relation pg_settings') user_db.expect_execute( "SELECT * from protected_data.table_1", 'permission denied for schema protected_data') admin_on_test_db.execute_template("sql/setup_user.sql.tpl", WORKSPACE=db, USER=user) user_db.expect_success("SELECT * from protected_data.table_1") user_db.expect_execute( "CREATE TABLE protected_data.table_2 (name varchar(20));", 'permission denied for schema protected_data') user_db.expect_execute( "CREATE TABLE working_data.table_2 (name varchar(20));", 'permission denied for schema pg_catalog') admin_on_test_db.execute_template("sql/setup_user_2.sql.tpl", WORKSPACE=db, USER=user) user_db.expect_success( "CREATE TABLE working_data.table_2 (name varchar(20));") prep_db.match_results( "sql/query_permissions.sql.tpl", 'results/test_incremental_user_access/perms.txt', USER=user) user_db.close() prep_db.close() finally: admin_on_test_db.close()
def clean(self): admin = CLI(creds, True) db = "test_db" admin.execute("DROP DATABASE IF EXISTS %s;" % db, False) databases = ["orig_db", "orig_db_2"] for rdb in databases: admin.execute("DROP DATABASE %s;" % rdb, False) admin.execute("DROP ROLE %s_admin;" % rdb, False) admin.execute("DROP ROLE %s_user;" % rdb, False) admin.execute("DROP ROLE %s_enduser;" % rdb, False) admin.execute("DROP ROLE %s_contribute;" % rdb, False) admin.execute("DROP ROLE %s_readonly;" % rdb, False) for n in range(1): rdb = "project_%s" % (n + 1) admin.execute("DROP DATABASE %s;" % rdb, False) admin.execute("DROP ROLE %s_contribute;" % rdb, False) admin.execute("DROP ROLE %s_readonly;" % rdb, False) admin.execute("DROP ROLE %s_min_public;" % rdb, False) for u in range(1): user = "******" % (rdb, (u + 1)) admin.execute("DROP ROLE %s;" % user, False) admin.execute("DROP ROLE %s;" % 'user_x', False) admin.execute("DROP ROLE %s;" % 'tmp_user', False) admin.execute("DROP ROLE %s;" % 'tmp_user_incremental', False) admin.execute("DROP DATABASE temp_db;", False) admin.execute("DROP ROLE %s;" % 'i_test_user', False) for n in range(10): admin.execute("DROP ROLE user_%s;" % n, False) admin.execute("DROP OWNED BY %s_contribute;" % db, False) admin.execute("DROP ROLE %s_contribute;" % db, False) admin.execute("DROP OWNED BY %s_readonly;" % db, False) admin.execute("DROP ROLE %s_readonly;" % db, False) admin.execute("DROP OWNED BY %s_min_public;" % db, False) admin.execute("DROP ROLE %s_min_public;" % db, False) admin.close() return self
def test_simple(self): admin = CLI(creds) db = "test_db" admin.execute("SELECT current_user", True)
def prepare(self): admin = CLI(creds, True) try: for n in range(1): rdb = "project_%s" % (n + 1) admin.execute("CREATE DATABASE %s;" % rdb, True) admin.execute_template("sql/new_database.sql.tpl", APP_DATABASE=rdb) admin_on_db = CLI({**creds, **{"PGDATABASE": rdb}}, True) admin_on_db.execute_template("sql/setup_new_database.sql.tpl") admin_on_db.execute_template("sql/setup_roles.sql.tpl", WORKSPACE=rdb) for u in range(1): user = "******" % (rdb, (u + 1)) admin_on_db.execute_template("sql/user.sql.tpl", USER=user, PASSWORD=Expect.TMP_PASSWORD) admin_on_db.execute_template("sql/user_connect.sql.tpl", APP_DATABASE=rdb, USER=user) admin_on_db.execute_template("sql/setup_user.sql.tpl", WORKSPACE=rdb, USER=user) admin_on_db.execute_template("sql/setup_user_2.sql.tpl", WORKSPACE=rdb, USER=user) admin_on_db.close() finally: admin.close() return self