3.1. Access Management #
Since pgpro_metastore has its own objects, a system based on proxy tables is used to differentiate privileges and granularly manage access to these objects.
Proxy tables are empty tables without columns created in the pgpro_metastore schema. Each proxy table name is in the prefix_name format, where:
prefix: The prefix determining the object encapsulated by the proxy table.Possible values:
sfor storagestfor analytical tablesffor directories
name: The name of the object encapsulated by the proxy table.
For example, the name of a proxy table can be s_localstorage.
Proxy tables are created and deleted simultaneously with objects that they encapsulate.
For more information about configuring access to the Postgres Pro AXE DBMS, refer to the official Postgres Pro documentation.
3.1.1. Configuring the Administrator Role #
In Postgres Pro AXE, the administrator role is called metastore_admin. It can interact with pgpro_axe, execute ETL operations, and grant other users privileges to manage objects.
To configure the administrator role:
Create the
metastore_adminrole:ALTER SYSTEM SET duckdb.postgres_role TO 'metastore_admin';
Grant the
metastore_adminrole full access to the pgpro_metastore schema and reload the pgpro_metastore server.
3.1.2. Privilege Management Scenario #
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).
Figure 3.1.
Where:
Superuser is the Postgres Pro superuser with full privileges.
metastore_adminis the main administrator with themetastore_adminrole 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_auser privileges to theschema_aanalytical 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_adminuser privileges to themetastoreanalytical 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_adminuser 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_alpha.pg_table');The
metastore_adminuser grants privileges to theadmin_auser: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 OPTIONparameter allows theadmin_auser to grant privileges to other users if required.The
admin_auser 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');As object owner, the
metastore_adminuser grants theadmin_auser 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.t_mt_table1_00001 TO admin_a WITH GRANT OPTION ;
The
admin_auser 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.t_mt_table1_00001 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)
3.1.3. ETL Operations and Privileges #
This section explains what privileges are required for the existing ETL operations.
cleanup#Only the
metastore_adminuser.define_catalog_connection()#Only the
metastore_adminuser.This operation is
SECURITY DEFINERand can only be executed with superuser privileges. You may have to update the Postgres Pro AXE DBMS system catalog, to whichmetastore_admindoes not have access.init()#Only the
metastore_adminuser.add_storage/remove_storage#Only the
metastore_adminuser.When one of these ETL operations is executed, a proxy table owned by the
metastore_adminuser and corresponding to the storage is created or deleted from the pgpro_metastore schema.add_table/remove_table#Only the
metastore_adminuser.When one of these ETL operations is executed, a proxy table owned by the
metastore_adminuser and corresponding to the table is created or deleted from the pgpro_metastore schema.add_folder/remove_folder#Only the
metastore_adminuser.When one of these ETL operations is executed, a proxy table owned by the
metastore_adminuser and corresponding to the shared directory is created or deleted from the pgpro_metastore schema.add_files#Users that are granted privileges to:
insert in the
pga_tablemetadata tableread the
pga_foldermetadata table if Parquet files are added from a shared directory specified in this table
-
copy_table# Users that are granted privileges to:
insert in the
pga_tablemetadata tableexecute the query specified by the second parameter
This ETL operation is
SECURITY DEFINER.-
create_view# Users that are granted privileges to:
read the
pga_tablemetadata tablecreate views in the Postgres Pro schema
This ETL operation is
SECURITY DEFINER.
3.1.4. Use Case Examples #
Example 3.1.
To create the mt_admin role, execute the following query:
CREATE USER mt_admin; GRANT ALL ON SCHEMA metastore TO mt_admin; ALTER SYSTEM SET duckdb.postgres_role TO 'mt_admin';
Then, reload the server.
Example 3.2.
To grant the user1 user the privilege to insert the OLAP data in the mt_table1 analytical table from the mt_storage shared directory, execute the following query:
postgres=# GRANT INSERT ON metastore.t_mt_table1_00001 TO user1; postgres=# GRANT SELECT ON metastore.f_mt_folder to user1;
3.1.5. Secrets #
DuckDB secrets can be configured either using utility functions or with a foreign data wrapper for more advanced cases.
Secrets are stored in a combination of SERVER and USER MAPPING on the DuckDB foreign data wrapper. USER MAPPING hosts sensitive elements, such as token, session_token, and secret. Each time a DuckDB instance is created by pgpro_axe, and when a secret is modified, secrets are loaded as non-persistent into the DuckDB secrets manager.
Important
Do not grant USAGE permission on the duckdb foreign data wrapper to regular users.
The owner of a foreign server can create user mappings for this server for any user, so only grant this access privilege to administrators. Otherwise, a regular user can create secrets for certain scopes for unsuspecting users.
Postgres Pro AXE supports the following types of secrets:
You can manage secrets using pgpro_axe functions.
3.1.5.1. Simple Secrets #
The easiest way to configure credentials is using utility functions:
Example 3.3.
-- Basic S3 secret (most common)
SELECT duckdb.create_simple_secret(
type := 'S3',
key_id := 'your_access_key_id',
secret := 'your_secret_access_key',
region := 'us-east-1'
);
This function has more parameters:
Example 3.4.
SELECT duckdb.create_simple_secret(
type := 'S3', -- Type: one of (S3, GCS, R2)
key_id := 'access_key_id',
secret := 'xxx',
session_token := 'yyy', -- (optional)
region := 'us-east-1', -- (optional)
url_style := 'xxx', -- (optional)
provider := 'xxx', -- (optional)
endpoint := 'xxx', -- (optional)
scope := 'xxx', -- (optional)
validation := 'xxx', -- (optional)
use_ssl := 'xxx' -- (optional)
)
3.1.5.2. Secrets with the credential_chain Provider #
For more advanced use-cases, you can define secrets with SERVER and USER MAPPING on the DuckDB foreign data wrapper:
Example 3.5.
CREATE SERVER my_s3_secret
TYPE 's3'
FOREIGN DATA WRAPPER duckdb
OPTIONS (PROVIDER 'credential_chain');
3.1.5.3. Secrets with secret_access_key #
When your secret contains sensitive information, you need to create an additional USER MAPPING:
Example 3.6.
CREATE SERVER my_s3_secret TYPE 's3' FOREIGN DATA WRAPPER duckdb;
CREATE USER MAPPING FOR CURRENT_USER SERVER my_s3_secret
OPTIONS (KEY_ID 'my_secret_key', SECRET 'my_secret_value');
You can use any of the supported DuckDB secret types as long as the related extension is installed. For more information, refer to the official DuckDB documentation.