feat: allow restricted users to access all databases in clones created with the "--restricted" flag (#356)
Description
Update of logic for clone creation with --restricted flag to allow access for all databases in created clone
Related issue
Examples
Checklist
-
MR description has been reviewed -
MR changes are functionally tested -
MR does NOT have text changes OR there are text changes and they have been reviewed -
MR does NOT have API/CLI changes OR there are API/CLI changes and they have been reviewed -
MR does NOT have UI changes OR there are UI changes and they have been reviewed
Closes #356 (closed)
Edited by Nikolay Samokhvalov
Merge request reports
Activity
added Feature request Priorityhigh good first issue techbackend labels
assigned to @denis256
requested review from @akartasov and @vitabaks
Resolved by Vitaliy Kukhariktest
Result: not passed
- The owner has been changed for all databases except template (expected behavior)
- The owner of the objects inside the database has not been changed
- the code "
restrictionTemplate" was executed only in the postgres database. Execution is expected in each database
- the code "
ubuntu@ip-172-31-11-169:~$ dblab clone create --username test --password test-pass --id clone_01 --restricted { "id": "clone_01", "protected": false, "deleteAt": null, "createdAt": "2022-05-19T13:41:18-07:00", "status": { "code": "OK", "message": "Clone is ready to accept Postgres connections." }, "db": { "connStr": "host=localhost port=6000 user=test dbname=postgres", "host": "localhost", "port": "6000", "username": "test", "password": "", "dbName": "" }, "snapshot": { "id": "dblab_pool@snapshot_20220519203850", "createdAt": "2022-05-19T13:40:44-07:00", "dataStateAt": "2022-05-19T13:38:50-07:00", "pool": "dblab_pool", "numClones": 1, "physicalSize": "0 B", "logicalSize": "358 MiB" }, "metadata": { "cloningTime": 1.937443263, "maxIdleMinutes": 120, "cloneDiffSize": "232 KiB", "logicalSize": "358 MiB" } } ubuntu@ip-172-31-11-169:~$ PGPASSWORD=test-pass psql "host=localhost port=6000 user=test dbname=test" psql (12.9 (Ubuntu 12.9-0ubuntu0.20.04.1), server 14.2 (Debian 14.2-1.pgdg110+1)) WARNING: psql major version 12, server major version 14. Some psql features might not work. Type "help" for help. test=> \l+ List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description -----------+----------+----------+------------+------------+-----------------------+---------+------------+-------------------------------------------- postgres | test | UTF8 | en_US.utf8 | en_US.utf8 | | 8681 kB | pg_default | default administrative connection database template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +| 8401 kB | pg_default | unmodifiable empty database | | | | | postgres=CTc/postgres | | | template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +| 8521 kB | pg_default | default template for new databases | | | | | postgres=CTc/postgres | | | test | test | UTF8 | C.UTF-8 | C.UTF-8 | | 158 MB | pg_default | test2 | test | UTF8 | C.UTF-8 | C.UTF-8 | | 23 MB | pg_default | (5 rows) test=> \c test psql (12.9 (Ubuntu 12.9-0ubuntu0.20.04.1), server 14.2 (Debian 14.2-1.pgdg110+1)) WARNING: psql major version 12, server major version 14. Some psql features might not work. You are now connected to database "test" as user "test". test=> -- objects that are not owned by the user "test" test=> select test-> nsp.nspname as SchemaName test-> ,cls.relname as ObjectName test-> ,rol.rolname as ObjectOwner test-> ,case cls.relkind test-> when 'r' then 'TABLE' test-> when 'm' then 'MATERIALIZED_VIEW' test-> when 'i' then 'INDEX' test-> when 'S' then 'SEQUENCE' test-> when 'v' then 'VIEW' test-> when 'c' then 'TYPE' test-> else cls.relkind::text test-> end as ObjectType test-> from pg_class cls test-> join pg_roles rol test-> on rol.oid = cls.relowner test-> join pg_namespace nsp test-> on nsp.oid = cls.relnamespace test-> where nsp.nspname not in ('information_schema', 'pg_catalog') test-> and nsp.nspname not like 'pg_toast%' test-> and rol.rolname <> 'test' test-> order by nsp.nspname, cls.relname test-> ; schemaname | objectname | objectowner | objecttype ------------+-----------------------+-------------+------------ public | pgbench_accounts | postgres | TABLE public | pgbench_accounts_pkey | postgres | INDEX public | pgbench_branches | postgres | TABLE public | pgbench_branches_pkey | postgres | INDEX public | pgbench_history | postgres | TABLE public | pgbench_tellers | postgres | TABLE public | pgbench_tellers_pkey | postgres | INDEX public | tbl | postgres | TABLE (8 rows) test=> \c test2 psql (12.9 (Ubuntu 12.9-0ubuntu0.20.04.1), server 14.2 (Debian 14.2-1.pgdg110+1)) WARNING: psql major version 12, server major version 14. Some psql features might not work. You are now connected to database "test2" as user "test". test2=> -- objects that are not owned by the user "test" test2=> select test2-> nsp.nspname as SchemaName test2-> ,cls.relname as ObjectName test2-> ,rol.rolname as ObjectOwner test2-> ,case cls.relkind test2-> when 'r' then 'TABLE' test2-> when 'm' then 'MATERIALIZED_VIEW' test2-> when 'i' then 'INDEX' test2-> when 'S' then 'SEQUENCE' test2-> when 'v' then 'VIEW' test2-> when 'c' then 'TYPE' test2-> else cls.relkind::text test2-> end as ObjectType test2-> from pg_class cls test2-> join pg_roles rol test2-> on rol.oid = cls.relowner test2-> join pg_namespace nsp test2-> on nsp.oid = cls.relnamespace test2-> where nsp.nspname not in ('information_schema', 'pg_catalog') test2-> and nsp.nspname not like 'pg_toast%' test2-> and rol.rolname <> 'test' test2-> order by nsp.nspname, cls.relname test2-> ; schemaname | objectname | objectowner | objecttype ------------+-----------------------+-------------+------------ public | pgbench_accounts | postgres | TABLE public | pgbench_accounts_pkey | postgres | INDEX public | pgbench_branches | postgres | TABLE public | pgbench_branches_pkey | postgres | INDEX public | pgbench_history | postgres | TABLE public | pgbench_tellers | postgres | TABLE public | pgbench_tellers_pkey | postgres | INDEX (7 rows)Edited by Vitaliy Kukharik- Last reply by Vitaliy Kukharik
added 1 commit
- ec6f8c46 - Add logic to set restricted user as owner for objects in all dbs
Updated MR with execution of
restrictionTemplatefor all DBsExample clone with 2 DBs
testandtest2test2=> select test2-> nsp.nspname as SchemaName test2-> ,cls.relname as ObjectName test2-> ,rol.rolname as ObjectOwner test2-> ,case cls.relkind test2-> when 'r' then 'TABLE' test2-> when 'm' then 'MATERIALIZED_VIEW' test2-> when 'i' then 'INDEX' test2-> when 'S' then 'SEQUENCE' test2-> when 'v' then 'VIEW' test2-> when 'c' then 'TYPE' test2-> else cls.relkind::text test2-> end as ObjectType test2-> from pg_class cls test2-> join pg_roles rol test2-> on rol.oid = cls.relowner test2-> join pg_namespace nsp test2-> on nsp.oid = cls.relnamespace test2-> where nsp.nspname not in ('information_schema', 'pg_catalog') test2-> and nsp.nspname not like 'pg_toast%' test2-> and rol.rolname <> 'test' test2-> order by nsp.nspname, cls.relname test2-> ; schemaname | objectname | objectowner | objecttype ------------+-----------------------+-----------------------+------------ public | pgbench_accounts | dblab_user_restricted | TABLE public | pgbench_accounts_pkey | dblab_user_restricted | INDEX public | pgbench_branches | dblab_user_restricted | TABLE public | pgbench_branches_pkey | dblab_user_restricted | INDEX public | pgbench_history | dblab_user_restricted | TABLE public | pgbench_tellers | dblab_user_restricted | TABLE public | pgbench_tellers_pkey | dblab_user_restricted | INDEX (7 rows) test=> select test-> nsp.nspname as SchemaName test-> ,cls.relname as ObjectName test-> ,rol.rolname as ObjectOwner test-> ,case cls.relkind test-> when 'r' then 'TABLE' test-> when 'm' then 'MATERIALIZED_VIEW' test-> when 'i' then 'INDEX' test-> when 'S' then 'SEQUENCE' test-> when 'v' then 'VIEW' test-> when 'c' then 'TYPE' test-> else cls.relkind::text test-> end as ObjectType test-> from pg_class cls test-> join pg_roles rol test-> on rol.oid = cls.relowner test-> join pg_namespace nsp test-> on nsp.oid = cls.relnamespace test-> where nsp.nspname not in ('information_schema', 'pg_catalog') test-> and nsp.nspname not like 'pg_toast%' test-> and rol.rolname <> 'test' test-> order by nsp.nspname, cls.relname test-> ; schemaname | objectname | objectowner | objecttype ------------+-----------------------+-----------------------+------------ public | pgbench_accounts | dblab_user_restricted | TABLE public | pgbench_accounts_pkey | dblab_user_restricted | INDEX public | pgbench_branches | dblab_user_restricted | TABLE public | pgbench_branches_pkey | dblab_user_restricted | INDEX public | pgbench_history | dblab_user_restricted | TABLE public | pgbench_tellers | dblab_user_restricted | TABLE public | pgbench_tellers_pkey | dblab_user_restricted | INDEX (7 rows)added 1 commit
- b848a3ce - Add logic to set restricted user as owner for objects in all dbs
-
test2
Result: test passed
ubuntu@ip-172-31-11-169:~$ PGPASSWORD=test-pass psql "host=localhost port=6000 user=test_user dbname=test" psql (12.9 (Ubuntu 12.9-0ubuntu0.20.04.1), server 14.2 (Debian 14.2-1.pgdg110+1)) WARNING: psql major version 12, server major version 14. Some psql features might not work. Type "help" for help. test=> \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+-----------+----------+------------+------------+----------------------- postgres | test_user | UTF8 | en_US.utf8 | en_US.utf8 | template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres + | | | | | postgres=CTc/postgres test | test_user | UTF8 | C.UTF-8 | C.UTF-8 | test2 | test_user | UTF8 | C.UTF-8 | C.UTF-8 | (5 rows) test=> \dt List of relations Schema | Name | Type | Owner --------+------------------+-------+----------- public | pgbench_accounts | table | test_user public | pgbench_branches | table | test_user public | pgbench_history | table | test_user public | pgbench_tellers | table | test_user public | tbl | table | test_user (5 rows) test=> \c test2 psql (12.9 (Ubuntu 12.9-0ubuntu0.20.04.1), server 14.2 (Debian 14.2-1.pgdg110+1)) WARNING: psql major version 12, server major version 14. Some psql features might not work. You are now connected to database "test2" as user "test_user". test2=> \dt List of relations Schema | Name | Type | Owner --------+------------------+-------+----------- public | pgbench_accounts | table | test_user public | pgbench_branches | table | test_user public | pgbench_history | table | test_user public | pgbench_tellers | table | test_user (4 rows) test2=> -- objects that are not owned by the database owner test2=> select test2-> nsp.nspname as SchemaName test2-> ,cls.relname as ObjectName test2-> ,rol.rolname as ObjectOwner test2-> ,case cls.relkind test2-> when 'r' then 'TABLE' test2-> when 'm' then 'MATERIALIZED_VIEW' test2-> when 'i' then 'INDEX' test2-> when 'S' then 'SEQUENCE' test2-> when 'v' then 'VIEW' test2-> when 'c' then 'TYPE' test2-> else cls.relkind::text test2-> end as ObjectType test2-> from pg_class cls test2-> join pg_roles rol test2-> on rol.oid = cls.relowner test2-> join pg_namespace nsp test2-> on nsp.oid = cls.relnamespace test2-> where nsp.nspname not in ('information_schema', 'pg_catalog') test2-> and nsp.nspname not like 'pg_toast%' test2-> and rol.rolname <> (select pg_catalog.pg_get_userbyid(datdba) from pg_catalog.pg_database where datname = current_database()) test2-> order by nsp.nspname, cls.relname test2-> ; schemaname | objectname | objectowner | objecttype ------------+------------+-------------+------------ (0 rows) test2=> \qPostgreSQL (clone) log:
Please register or sign in to reply