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

29.1. Configuring Secrets for an S3 Storage
Prev	Up	Chapter 29. S3 Storages	Home	Next

29.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.

Example 29.1.

  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 29.2.
```
  SELECT metastore.init(true);
```

Create an S3 storage.

Example 29.3.

  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 29.4.

  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 29.5.
  GRANT SELECT ON sales_data TO analyst;
- Grant the INSERT privilege to the role that writes the data to the analytical table.
  Example 29.6.
  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
Chapter 29. S3 Storages	Home	29.2. Configuring a Connection to an S3 Storage

epub pdf