Consider a privilege management 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 full privileges.
metastore_admin is the main administrator
with the metastore_admin role that
owns all pgpro_metastore objects.
Administrators A and B are local administrators of user groups that can access pgpro_metastore objects associated with these groups.
Privileges of these administrators are granted by the main administrator, and they 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 users in the database under the superuser:
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 analytical schemas for user groups:
postgres=# SELECT current_user; current_user -------------- root postgres=# CREATE SCHEMA schema_a; postgres=# CREATE SCHEMA schema_b;
Grant the admin_a user privileges to
the schema_a analytical schema:
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_admin user privileges to
the metastore analytical schema and make this
user 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';
Restart the server.
The metastore_admin user can now configure the
pgpro_metastore catalog:
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 Postgres Pro table in its schema, and this table must be inserted in pgpro_metastore for analytical queries:
The metastore_admin user creates a
storage and
analytical table:
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 metastore_admin user finds the name of the proxy
table encapsulating the created analytical table by executing
the query in the database that contains the
pgpro_metastore catalog, that is,
the axe_catalog schema:
SELECT proxy_table_name FROM axe_catalog.pga_proxy_table p JOIN axe_catalog.pga_table t ON p.proxy_table_id = t.proxy_table_id WHERE t.table_name = 'mt_table1'; proxy_table_name ------------------- t_mt_table1_00001 (1 row)
Further steps are performed on the server where pgpro_metastore is installed.
The metastore_admin user grants privileges
to the admin_a user:
postgres=> SELECT current_user; current_user ----------------- metastore_admin postgres=> GRANT ALL ON metastore.t_mt_table1_00001 TO admin_a WITH GRANT OPTION;
The WITH GRANT OPTION parameter allows the
admin_a user to grant privileges to
other users if required.
The admin_a user copies the
Postgres Pro table to the analytical table
and creates a Postgres Pro view:
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');
Note that any user calling copy_table must have
enough permissions to execute the query provided as a parameter;
in this case admin_a must have SELECT
permission for schema_a.pg_table.
As object owner, the metastore_admin user grants
the admin_a user the privilege to grant other
users the privilege to read the object:
postgres=> SELECT current_user; current_user ----------------- metastore_admin postgres=> GRANT SELECT ON schema_a.mt_table1 TO admin_a WITH GRANT OPTION ;
The admin_a user grants analysts the privilege
to read the Postgres Pro view:
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;
The analyst reads 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)