Consider a scenario where the analyst role must be
able to read the OLAP data from
an S3 storage, the etl_user role must be able to
write the OLAP data to this storage, and other roles must not have
access to this storage.
To configure this scenario:
Example 23.3.
SELECT duckdb.create_simple_secret(
type := 'S3',
key_id := 'AKIAIOSFODNN7EXAMPLE',
secret := 'wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY',
endpoint := 'storage.example.ru',
region := 'us-east-1'
);
Initialize the metadata catalog.
Example 23.4.
SELECT metastore.init(true);
Example 23.5.
SELECT metastore.add_storage('data_storage', 's3://data-bucket/', 's3://data-bucket/tmp/');
Create an analytical table, and then create a Postgres Pro view for this table.
Example 23.6.
SELECT metastore.add_table('sales_data', 'data_storage', 'public.sales');
SELECT metastore.create_view('sales_data');
Grant privileges on the analytical table:
Grant the SELECT privilege to the
analyst role.
Example 23.7.
GRANT SELECT ON sales_data TO analyst;
Grant the INSERT privilege to the role that
writes the OLAP data to the analytical table.
Example 23.8.
SELECT metastore.mgrant('INSERT', 'TABLE', 'sales_data', 'etl_user');
If the analyst role executes the following
command:
SELECT * FROM sales_data;
Since the analyst role is granted the
SELECT privilege on the Postgres Pro view, the
command is executed.
The simple secret is found in the user mapping for the
PUBLIC role.
The connection to the S3 storage is established.
If the etl_user role executes the following
command:
SELECT metastore.copy_table('sales_data', 'SELECT * FROM staging.sales');
Since the etl_user role is granted the
INSERT privilege on the analytical table, the
command is executed.
The simple secret is found in the user mapping for the
PUBLIC role.
The connection to the S3 storage is established.
If the random_user role attempts to execute the following
command:
SELECT * FROM sales_data;
Since the random_user role is not granted any privileges
on the Postgres Pro view, the command is rejected with an access error.