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_admin is 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_a
schema. The steps for the schema_b schema
are similar.
Grant the metastore_admin and admin_a
roles privileges on the schema_a schema.
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_admin role privileges on
the metastore schema 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_a
role 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_a role 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_a role 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_a role 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_a role can execute the
SELECT command 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)