14.5. Secrets #
DuckDB secrets can be configured either using utility functions or with a foreign data wrapper for more advanced cases.
Secrets are stored in a combination of SERVER and USER MAPPING on the DuckDB foreign data wrapper. USER MAPPING hosts sensitive elements, such as token, session_token, and secret. Each time a DuckDB instance is created by pgpro_axe, and when a secret is modified, secrets are loaded as non-persistent into the DuckDB secrets manager.
Important
Do not grant USAGE permission on the duckdb foreign data wrapper to regular users.
The owner of a foreign server can create user mappings for this server for any user, so only grant this access privilege to administrators. Otherwise, a regular user can create secrets for certain scopes for unsuspecting users.
Postgres Pro AXE supports the following types of secrets:
You can manage secrets using pgpro_axe functions.
14.5.1. Simple Secrets #
The easiest way to configure credentials is using utility functions:
Example 14.3.
-- Basic S3 secret (most common)
SELECT duckdb.create_simple_secret(
type := 'S3',
key_id := 'your_access_key_id',
secret := 'your_secret_access_key',
region := 'us-east-1'
);
This function has more parameters:
Example 14.4.
SELECT duckdb.create_simple_secret(
type := 'S3', -- Type: one of (S3, GCS, R2)
key_id := 'access_key_id',
secret := 'xxx',
session_token := 'yyy', -- (optional)
region := 'us-east-1', -- (optional)
url_style := 'xxx', -- (optional)
provider := 'xxx', -- (optional)
endpoint := 'xxx', -- (optional)
scope := 'xxx', -- (optional)
validation := 'xxx', -- (optional)
use_ssl := 'xxx' -- (optional)
)
14.5.2. Secrets with the credential_chain Provider #
For more advanced use-cases, you can define secrets with SERVER and USER MAPPING on the DuckDB foreign data wrapper:
Example 14.5.
CREATE SERVER my_s3_secret
TYPE 's3'
FOREIGN DATA WRAPPER duckdb
OPTIONS (PROVIDER 'credential_chain');
14.5.3. Secrets with secret_access_key #
When your secret contains sensitive information, you need to create an additional USER MAPPING:
Example 14.6.
CREATE SERVER my_s3_secret TYPE 's3' FOREIGN DATA WRAPPER duckdb;
CREATE USER MAPPING FOR CURRENT_USER SERVER my_s3_secret
OPTIONS (KEY_ID 'my_secret_key', SECRET 'my_secret_value');
You can use any of the supported DuckDB secret types as long as the related extension is installed. For more information, refer to the official DuckDB documentation.