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 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)