Chapter 29. Configuring Privileges
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 data.
To configure this scenario:
Create Postgres Pro roles on behalf of the superuser.
For example:
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.
For example:
postgres=# SELECT current_user; current_user -------------- root postgres=# CREATE SCHEMA schema_a; postgres=# CREATE SCHEMA schema_b;
The following scenario is based on the
schema_aschema. The steps for theschema_bschema are similar.Grant privileges on the
schema_aschema tometastore_adminandadmin_aroles.For example:
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 privileges on the
metastoreschema to themetastore_adminrole and designate this role as the Postgres Pro AXE administrator.For example:
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.
For example:
postgres=> SELECT current_user;
current_user
-----------------
metastore_admin
postgres=> SELECT metastore.define_catalog_connection('localhost','5433','postgres', '', '') ON CONFLICT DO NOTHING;
postgres=> SELECT metastore.init() ON CONFLICT DO NOTHING;
If group A has a heap table in its schema, and the 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.
For example:
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/') ON CONFLICT DO NOTHING; postgres=> SELECT metastore.add_table('mt_table1', 'mt_storage', 'schema_a.pg_table') ON CONFLICT DO NOTHING;The Postgres Pro AXE administrator grants the privilege on the analytical table to the
admin_arole.For example:
postgres=> SELECT current_user; current_user ----------------- metastore_admin postgres=# SELECT metastore.mgrant('ALL','TABLE','mt_table1','admin_a', TRUE) ON CONFLICT DO NOTHING;The
admin_arole can now copy the data from the heap table to the analytical table and create a view for this table.For example:
postgres=> SELECT current_user; current_user -------------- admin_a postgres=> SELECT metastore.copy_table('mt_table1', 'select * from schema_a.pg_table') ON CONFLICT DO NOTHING; postgres=> SELECT metastore.create_view('mt_table1', 'schema_a') ON CONFLICT DO NOTHING;As the owner of the analytical table, the Postgres Pro AXE administrator must allow the
admin_arole to grant the privilege to other roles.For example:
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 the privilege to other roles.For example:
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.For example:
postgres=> SELECT current_user; current_user -------------- analyst_a (1 row) postgres=> SELECT COUNT(*) FROM schema_a.mt_table1; count ------- 50 (1 row)