Postgres Pro AXE : Documentation: 1: 23.5. Scenario: Separating Read and Write Access to an S3 Storage : Postgres Professional

23.5. Scenario: Separating Read and Write Access to an S3 Storage
Prev	Up	Chapter 23. Secrets	Home	Next

23.5. Scenario: Separating Read and Write Access to an S3 Storage #

By default, the duckdb.create_simple_secret() function creates a user mapping for the PUBLIC role, allowing all roles to connect to the S3 storage. However, Postgres Pro searches for a user mapping in the following order: first for the specific role, then for PUBLIC. If a user mapping for the specific role is found, it takes precedence over PUBLIC.

You can use this mechanism to provide different roles with different privileges on an S3 storage. For example, you can restrict the write privilege on an S3 storage by providing the read-only privilege on the S3 storage to the PUBLIC role and the read-write privilege on the S3 storage to a specific role.

Important

Do not grant the USAGE privilege on the duckdb foreign data wrapper to roles other than the Postgres Pro AXE administrator. A role with this privilege can create a foreign server with any S3 storage connection parameters and create user mappings for any role, which allows overriding the user mapping of another role with a different access key and secret access key.

To configure this scenario:

Create a foreign server and a user mapping for the PUBLIC role with the read-only privilege on the S3 storage:
```
  CREATE SERVER simple_s3_secret
    TYPE 'S3'
    FOREIGN DATA WRAPPER duckdb
    OPTIONS (
      region      'region',
      url_style   'URL_style',
      provider    'credentials_provider',
      endpoint    'endpoint_URL',
      scope       'scope_restriction',
      validation  '',
      use_ssl     'true_or_false'
    );

  CREATE USER MAPPING FOR PUBLIC
    SERVER simple_s3_secret
    OPTIONS (
      key_id        'read_only_access_key',
      secret        'read_only_secret_access_key',
      session_token 'session_security_token'
    );
```
Where:
- region: The region where the S3 storage is located.
  Optional parameter.
- URL_style: The URL format used to access the bucket within the S3 storage.
  Possible values:
  path: The bucket name is included in the path of the URL, for example, storage.example.com/my-bucket.
  Optional parameter.
- credentials_provider: Specifies how the access key and secret access key are provided.
  Possible values:
  '' (empty string): Use the provided read_only_access_key and read_only_secret_access_key.
  Optional parameter.
- endpoint_URL: The endpoint URL of the S3 storage.
  Optional parameter.
- scope_restriction: A bucket path prefix that restricts the scope of the secret to a specific location in the S3 storage.
  Default value: '' (empty string) — the secret applies to all paths in the S3 storage.
  Optional parameter.
- validation: A service parameter, leave it empty.
- true_or_false: Specifies whether to encrypt communication between Postgres Pro AXE and the S3 storage.
  Optional parameter.
- read_only_access_key and read_only_secret_access_key: The access key and secret access key for the read-only privilege on the S3 storage.
- session_security_token: A temporary security session token required when using short-lived credentials (STS).
  Default value: '' (empty string) — a permanent access key is used.
  Optional parameter.
Create a user mapping for the role with the read-write privilege on the S3 storage:
```
  CREATE USER MAPPING FOR role_name
    SERVER simple_s3_secret
    OPTIONS (
      key_id        'read-write_access_key',
      secret        'read-write_secret_access_key',
      session_token 'session_security_token'
    );
```
Where:
- role_name: The role with the read-write privilege on the S3 storage.
- read-write_access_key and read-write_secret_access_key: The access key and secret access key for the read-write privilege on the S3 storage.
- session_security_token: A temporary security session token required when using short-lived credentials (STS).
  Default value: '' (empty string) — a permanent access key is used.
  Optional parameter.

As a result:

If the role_name role initiates an operation in the S3 storage, Postgres Pro applies the user mapping for this role with the read-write privilege on the S3 storage.
If any role other than role_name initiates an operation in the S3 storage, Postgres Pro applies the user mapping for the PUBLIC role with the read-only privilege on the S3 storage.

Prev	Up	Next
23.4. Scenario: Secret Management	Home	Chapter 24. Storages

epub pdf