CREATE SECRET
MotherDuck enables you to store your cloud storage credentials for convenience, using the familiar DuckDB CREATE SECRET
syntax. See DuckDB CREATE SECRET documentation.
Make sure to add either PERSISTENT
or IN MOTHERDUCK
keyword to create MotherDuck secrets. Secrets stored in MotherDuck are fully encrypted.
You can use the PERSISTENT
keyword to create a local file persistent secret in DuckDB as well. It gets stored unencrypted in the ~/.duckdb/stored_secrets
directory.
When you've loaded the MotherDuck extension, PERSISTENT
secrets are stored encrypted in MotherDuck. Locally persisted secrets are not impacted.
You can still create locally persisted secrets when using MotherDuck by specifying the secret storage backend: CREATE SECRET IN LOCAL_FILE
.
When using MotherDuck, the statement below creates a cloud-persistent secret stored in MotherDuck.
Syntax
CREATE [OR REPLACE] PERSISTENT SECRET [secret_name] (
TYPE <S3, GCS, R2, AZURE, HUGGINGFACE>,
<storage-specific properties>
);
CREATE [OR REPLACE] SECRET [secret_name] IN MOTHERDUCK (
TYPE <S3, GCS, R2, AZURE, HUGGINGFACE>,
<storage-specific properties>
);
Secret Paramters
Below is a complete list of the supported parameters for S3, GCS, and R2 secrets:
Name | Description | Secret | Type | Default |
---|---|---|---|---|
ENDPOINT | Specify a custom S3 endpoint | S3, GCS, R2 | STRING | s3.amazonaws.com for S3, |
KEY_ID | The ID of the key to use | S3, GCS, R2 | STRING | - |
REGION | The region for which to authenticate (should match the region of the bucket to query) | S3, GCS, R2 | STRING | us-east-1 |
SECRET | The secret of the key to use | S3, GCS, R2 | STRING | - |
SESSION_TOKEN | Optionally, a session token can be passed to use temporary credentials | S3, GCS, R2 | STRING | - |
URL_COMPATIBILITY_MODE | Can help when URLs contain problematic characters | S3, GCS, R2 | BOOLEAN | true |
URL_STYLE | Either vhost or path | S3, GCS, R2 | STRING | vhost for S3, path for R2 and GCS |
USE_SSL | Whether to use HTTPS or HTTP | S3, GCS, R2 | BOOLEAN | true |
ACCOUNT_ID | The R2 account ID to use for generating the endpoint URL | R2 | STRING | - |
KMS_KEY_ID | AWS KMS (Key Management Service) key for Server Side Encryption S3 | S3 | STRING | - |
SCOPE | Scope of secret resolution; In the case of multiple matching secrets, the longest prefix is chosen | S3, GCS, R2 | STRING | - |
Because of SSL certificate verification requirements, S3 bucket names that contain dots (.) cannot be accessed using vhost style URLs. This is due to AWS's SSL wildcard certificate (*.s3.amazonaws.com) which only validates single-level subdomains. To resolve this SSL Issue: use URL_STYLE path
in your secret.
Example Usage
To manually create a S3 secret in MotherDuck:
CREATE SECRET IN MOTHERDUCK (
TYPE S3,
KEY_ID 's3_access_key',
SECRET 's3_secret_key',
REGION 'us-east-1',
SCOPE 'my-bucket-path'
);
This creates a new secret with a default name (i.e. __default_s3
) and a default scope (i.e. [s3://, s3n://, s3a://]
) used for path matching explained below.
DuckDB uses the SCOPE
parameter to determine which secret to use. When using persistent secrets or public buckets, scoping the secrets is important so that the databases use the correct secret. Imprecise scoping will lead to authentication errors.
You can learn more in the DuckDB documentation.
Secret Providers
MotherDuck supports the same secret providers as DuckDB.
To create a secret by automatically fetching credentials using mechanisms provided by the AWS SDK, see AWS CREDENTIAL_CHAIN provider.
To create a secret by automatically fetching credentials using mechanisms provided by the Azure SDK, see Azure CREDENTIAL_CHAIN provider.
To create a secret by automatically fetching credentials using mechanisms provided by the Hugging Face CLI, see Hugging Face CREDENTIAL_CHAIN provider.
To store a secret from a given secret provider in MotherDuck, simply specify PERSISTENT
or IN MOTHERDUCK
keyword in addition.
Example Usage
To store a secret configured through aws configure
:
CREATE PERSISTENT SECRET aws_secret (
TYPE S3,
PROVIDER CREDENTIAL_CHAIN
);
To store a secret configured through az configure
:
CREATE SECRET azure_secret IN MOTHERDUCK (
TYPE AZURE,
PROVIDER CREDENTIAL_CHAIN,
ACCOUNT_NAME 'some-account'
);
Querying with Secrets
Secret scope is supported in the same way as in DuckDB to allow multiple secrets of the same type to be stored in MotherDuck. When there are multiple local (i.e. in memory and store in local file) and remote (i.e. MotherDuck) secrets of the same type, scope matching (secret scope against the file path) happens to determine which secret to use to open a file. Both local and remote secrets are considered in scope matching.
In the case of multiple matching secrets, the secret with the longest matching scope prefix is chosen.
In the case of multiple secrets stored in different secret storages sharing the same scope (e.g. the default scope if not specified), matching secret is chosen based on the following order: local temp secret > local_file secret > MotherDuck secret.
To see which secret (either local or remote) is being used by MotherDuck, the DuckDB which_secret
table function can be used, which takes a path and the secret type.
Example Usage
To see which secret is used to open a file:
FROM which_secret('s3://my-bucket/my_dataset.parquet', 's3');
┌───────────────────────┬────────────┬────────────┐
│ name │ persistent │ storage │
│ varchar │ varchar │ varchar │
├───────────────────────┼────────────┼────────────┤
│ __default_s3 │ PERSISTENT │ motherduck │
└───────────────────────┴────────────┴────────────┘