Postgres Pro AXE : Documentation: 1: 30.1. Configuring Secrets for an S3 Storage : Postgres Professional

30.1. Configuring Secrets for an S3 Storage
Prev	Up	Chapter 30. S3 Storages	Home	Next

30.1. Configuring Secrets for an S3 Storage #

Consider a scenario where the analyst role must be able to read the data from an S3 storage, the etl_user role must be able to write the data to this storage, and other roles cannot have access to this storage.

To configure this scenario:

Create a simple secret.

For example:

  SELECT duckdb.create_simple_secret(
      type     := 'S3',
      key_id   := 'AKIAIOSFODNN7EXAMPLE',
      secret   := 'wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY',
      endpoint := 'storage.example.ru',
      region   := 'us-east-1'
  ) ON CONFLICT DO NOTHING;

Initialize the metadata catalog.

For example:

  SELECT metastore.init(true) ON CONFLICT DO NOTHING;

Create an S3 storage.

For example:

  SELECT metastore.add_storage('data_storage', 's3://data-bucket/', 's3://data-bucket/tmp/') ON CONFLICT DO NOTHING;

Create an analytical table, and then create a view for this table.

For example:

  SELECT metastore.add_table('sales_data', 'data_storage', 'public.sales') ON CONFLICT DO NOTHING;

  SELECT metastore.create_view('sales_data') ON CONFLICT DO NOTHING;

Grant privileges on the analytical table:
- Grant the SELECT privilege to the analyst role.
  For example:
```
  GRANT SELECT ON sales_data TO analyst;
```
- Grant the INSERT privilege to the role that writes the data to the analytical table.
  For example:
```
  SELECT metastore.mgrant('INSERT', 'TABLE', 'sales_data', 'etl_user') ON CONFLICT DO NOTHING;
```

If the analyst role executes the following command:

  SELECT * FROM sales_data;

Since the analyst role is granted the SELECT privilege on the 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') ON CONFLICT DO NOTHING;

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 view, the command is rejected with an access error.

Prev	Up	Next
Chapter 30. S3 Storages	Home	30.2. Configuring a Connection to an S3 Storage

epub pdf