Postgres Pro AXE : Documentation: 1: 23.4. Scenario: Secret Management : Postgres Professional

23.4. Scenario: Secret Management
Prev	Up	Chapter 23. Secrets	Home	Next

23.4. Scenario: Secret Management #

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:

Create a simple secret.

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

Create an S3 storage.

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.

Prev	Up	Next
23.3. Deleting a Simple Secret (`duckdb.drop_simple_secret`)	Home	23.5. Scenario: Separating Read and Write Access to an S3 Storage

epub pdf