---
sidebar_position: 1
title: CREATE SECRET
description: Create a secret in MotherDuck
---
import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';

# CREATE SECRET

MotherDuck lets you store your cloud storage credentials for convenience, using the familiar DuckDB `CREATE SECRET` syntax. See [DuckDB CREATE SECRET documentation](https://duckdb.org/docs/sql/statements/create_secret.html).

Ensure you add the `PERSISTENT` or `IN MOTHERDUCK` keyword to create MotherDuck secrets. Secrets stored in MotherDuck are fully encrypted and scoped to the user who created them. They are not shared with other users in your organization.

:::note
You can use the `PERSISTENT` keyword to create a local file persistent secret in local 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

```sql
CREATE [OR REPLACE] (PERSISTENT SECRET [secret_name] | SECRET [secret_name] IN MOTHERDUCK)
(
    TYPE <S3 | GCS | R2 | AZURE | HUGGINGFACE>,
    <storage-specific properties>
);
```

## Secret parameters

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 used for authentication. For S3, this should match the region of the bucket. For R2, use `auto` since R2 buckets are regionless. | S3, GCS, R2 | STRING | Orgs will default to the region that was chosen at signup: us-east-1 or eu-central-1. For R2, defaults to `auto`. |
| 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 | - |

::::info
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.
::::

## Examples

### Manually defined S3 secret

To manually create an S3 secret in MotherDuck:

```sql
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 (for S3, `__default_s3`) and a default scope (i.e., `[s3://, s3n://, s3a://]`) used for path matching explained below.

::::info
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 database uses the correct secret. Imprecise scoping will lead to authentication errors.

Learn more in the [DuckDB documentation](https://duckdb.org/docs/stable/configuration/secrets_manager.html#creating-multiple-secrets-for-the-same-service-type).
::::

### Secret providers

MotherDuck supports the same [secret providers](https://duckdb.org/docs/configuration/secrets_manager.html#secret-providers) as DuckDB.

To create a secret by automatically fetching credentials using mechanisms provided by the AWS SDK, see [AWS CREDENTIAL_CHAIN provider](https://duckdb.org/docs/extensions/httpfs/s3api#credential_chain-provider).

To create a secret by automatically fetching credentials using mechanisms provided by the Azure SDK, see [Azure CREDENTIAL_CHAIN provider](https://duckdb.org/docs/extensions/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](https://duckdb.org/docs/extensions/httpfs/hugging_face#authentication).

To store a secret from a given secret provider in MotherDuck, specify the `PERSISTENT` or `IN MOTHERDUCK` keyword in addition.

### Provider examples

To store a secret configured through `aws configure`:

```sql
CREATE PERSISTENT SECRET aws_secret (
      TYPE S3,
      PROVIDER CREDENTIAL_CHAIN
  );
```

To store a secret using AWS SSO credentials:

```sql
-- if you use AWS SSO, run `aws sso login --profile <your_sso_profile>` first
CREATE SECRET aws_secret IN MOTHERDUCK (
      TYPE S3,
      PROVIDER CREDENTIAL_CHAIN,
      CHAIN 'sso',
      PROFILE '<your_sso_profile>'
  );
```

:::note Secret validation
Starting with DuckDB v1.4.0, credentials are validated at secret creation time. If your credentials are not resolvable locally (for example, expired SSO tokens or missing `~/.aws/credentials`), `CREATE SECRET` will fail with a `Secret Validation Failure` error. The recommended fix is to use the correct `CHAIN` and `PROFILE` for your credential type (see the SSO example above) and confirm your SSO session is active. If you need to bypass local validation, you can add `VALIDATION 'none'`, but keep in mind that this skips the local check that confirms your credentials are valid before storing them in MotherDuck.
:::

To store a secret configured through `az configure`:

```sql
CREATE SECRET azure_secret IN MOTHERDUCK (
      TYPE AZURE,
      PROVIDER CREDENTIAL_CHAIN,
      ACCOUNT_NAME 'some-account'
  );
```

## Querying with secrets

[Secret scope](https://duckdb.org/docs/configuration/secrets_manager.html#creating-multiple-secrets-for-the-same-service-type) 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.

:::info Local secrets and cloud execution
When you query cloud storage (S3, GCS, Azure, R2) while connected to MotherDuck, the query is routed to MotherDuck's [cloud execution engine](/concepts/architecture-and-capabilities#dual-execution), not your local machine. MotherDuck can use **any** matching secret to authenticate with your storage provider, including temporary, in-memory secrets from your local DuckDB session. Your local DuckDB client does not connect to cloud storage directly.
:::

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:

```sql
FROM which_secret('s3://my-bucket/my_dataset.parquet', 's3');
┌───────────────────────┬────────────┬────────────┐
│         name          │ persistent │  storage   │
│        varchar        │  varchar   │  varchar   │
├───────────────────────┼────────────┼────────────┤
│ __default_s3          │ PERSISTENT │ motherduck │
└───────────────────────┴────────────┴────────────┘
```

## Discovering buckets and files

If you want to inspect cloud storage from SQL before querying files directly:

- Use [`MD_LIST_BUCKETS_FOR_SECRET()`](/sql-reference/motherduck-sql-reference/md-list-buckets-for-secret) to list buckets visible to an S3/AWS secret.
- Use [`MD_LIST_FILES()`](/sql-reference/motherduck-sql-reference/md-list-files) to list files in `s3://`, `azure://`, or `az://` paths.

:::note
`MD_LIST_FILES()` supports S3 and Azure paths only. It does not accept `r2://`, `gcs://`, or `gs://` paths.
:::

## Troubleshooting

If you encounter issues creating or using secrets, check out our troubleshooting guides:

- **[AWS S3 Secrets Troubleshooting](/documentation/troubleshooting/aws-s3-secrets.md)** - Common issues with AWS S3 authentication and credentials
- **[Error Messages](/documentation/troubleshooting/error_messages.md)** - Understanding MotherDuck error messages