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