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