22.5. Privilege Management Scenario #
Consider a scenario where privileges are granted based on a hierarchy from the main administrator to local administrators of user groups and analysts (refer to the diagram below).
Where:
Superuser is the Postgres Pro superuser with all privileges.
metastore_adminis the Postgres Pro AXE administrator.Administrators A and B are local administrators of user groups that can access pgpro_metastore objects associated with these groups.
Privileges are granted to administrators A and B by the Postgres Pro AXE administrator, and these administrators can grant privileges to analysts within each group.
Analysts A, B, C, and D are users that work with the OLAP data.
To configure this privilege management scenario:
Create Postgres Pro roles on behalf of the superuser.
Example 22.7.
postgres=# SELECT current_user; current_user -------------- root postgres=# CREATE USER metastore_admin; postgres=# CREATE USER admin_a; postgres=# CREATE USER admin_b; postgres=# CREATE USER analyst_a; postgres=# CREATE USER analyst_b; postgres=# CREATE USER analyst_c; postgres=# CREATE USER analyst_d;
Create schemas for user groups.
Example 22.8.
postgres=# SELECT current_user; current_user -------------- root postgres=# CREATE SCHEMA schema_a; postgres=# CREATE SCHEMA schema_b;
Further scenario will be based on the
schema_aschema. The steps for theschema_bschema are similar.Grant the
metastore_adminandadmin_aroles privileges on theschema_aschema.Example 22.9.
postgres=# SELECT current_user; current_user -------------- root postgres=# GRANT ALL ON SCHEMA schema_a to metastore_admin WITH GRANT OPTION; postgres=# GRANT ALL ON SCHEMA schema_a to admin_a WITH GRANT OPTION;
Grant the
metastore_adminrole privileges on themetastoreschema and designate this role as the Postgres Pro AXE administrator:postgres=# SELECT current_user; current_user -------------- root postgres=# GRANT ALL ON SCHEMA metastore TO metastore_admin; postgres=# ALTER SYSTEM SET duckdb.postgres_role TO 'metastore_admin';
The Postgres Pro AXE administrator can now configure the metadata catalog.
Example 22.10.
postgres=> SELECT current_user;
current_user
-----------------
metastore_admin
postgres=> SELECT metastore.define_catalog_connection('localhost','5433','postgres', '', '');
postgres=> SELECT metastore.init();
If the group A has a heap table in its schema, and the OLAP data from this table must be used in Postgres Pro AXE for analytical queries:
The Postgres Pro AXE administrator creates a storage and analytical table.
Example 22.11.
postgres=> SELECT current_user; current_user ----------------- metastore_admin postgres=> SELECT metastore.add_storage('mt_storage', 'file:///tmp/mt_storage/', 'file:///tmp/mt_storage/tmp_dir/'); postgres=> SELECT metastore.add_table('mt_table1', 'mt_storage', 'schema_a.pg_table');The Postgres Pro AXE administrator grants the
admin_arole the privilege on the analytical table.Example 22.12.
postgres=> SELECT current_user; current_user ----------------- metastore_admin postgres=# SELECT metastore.mgrant('ALL','TABLE','mt_table1','admin_a', TRUE);The
admin_arole can now copy the OLAP data from the heap table to the analytical table and create a Postgres Pro AXE view for this table.Example 22.13.
postgres=> SELECT current_user; current_user -------------- admin_a postgres=> SELECT metastore.copy_table('mt_table1', 'select * from schema_a.pg_table'); postgres=> SELECT metastore.create_view('mt_table1', 'schema_a');As owner of the analytical table, the Postgres Pro AXE administrator must allow the
admin_arole to grant other roles the privilege.Example 22.14.
postgres=> SELECT current_user; current_user ----------------- metastore_admin postgres=> GRANT SELECT ON schema_a.mt_table1 TO admin_a WITH GRANT OPTION;Now the
admin_arole can grant other roles the privilege:Example 22.15.
postgres=> SELECT current_user; current_user -------------- admin_a (1 row) postgres=> GRANT USAGE ON SCHEMA schema_a to analyst_a; postgres=> GRANT SELECT ON schema_a.mt_table1 TO analyst_a;
Finally, the
analyst_arole can execute theSELECTcommand on the analytical table.postgres=> SELECT current_user; current_user -------------- analyst_a (1 row) postgres=> SELECT COUNT(*) FROM schema_a.mt_table1; count ------- 50 (1 row)