User credentials and other PostgreSQL database connection details can be stored using the DuckDB Secrets Manager. The following syntax can be used to create a secret:

CREATE SECRET (
    TYPE postgres,
    HOST '127.0.0.1',
    PORT 5432,
    DATABASE postgres,
    USER 'postgres',
    PASSWORD ''
);

The information from the secret will be used when ATTACH is called. We can leave the PostgreSQL connection string empty to use all of the information stored in the secret.

ATTACH '' AS postgres_db (TYPE postgres);

We can use the PostgreSQL connection string to override individual options. For example, to connect to a different database while still using the same credentials, we can override only the database name in the following manner.

ATTACH 'dbname=my_other_db' AS postgres_db (TYPE postgres);

By default, created secrets are temporary. Secrets can be persisted using the CREATE PERSISTENT SECRET command. Persistent secrets can be used across sessions.

Managing Multiple Secrets

Named secrets can be used to manage connections to multiple PostgreSQL database instances. Secrets can be given a name upon creation.

CREATE SECRET postgres_secret_one (
    TYPE postgres,
    HOST '127.0.0.1',
    PORT 5432,
    DATABASE postgres,
    USER 'postgres',
    PASSWORD ''
);

The secret can then be explicitly referenced using the SECRET parameter in the ATTACH.

ATTACH '' AS postgres_db_one (TYPE postgres, SECRET postgres_secret_one);

Warning Avoid including credentials directly in the connection string. If a connection error occurs, the full connection string (including your credentials) may be printed to the terminal output. For better security, store credentials using DuckDB-managed secrets.

Secret Configuration Options

Secrets of type postgres support a number of configuration options. The following options are named after the corresponding connection options of libpq:

• HOST
• HOSTADDR
• PORT
• DBNAME
• USER
• PASSWORD
• PASSFILE
• REQUIRE_AUTH
• CHANNEL_BINDING
• CONNECT_TIMEOUT
• CLIENT_ENCODING
• OPTIONS
• APPLICATION_NAME
• FALLBACK_APPLICATION_NAME
• KEEPALIVES
• KEEPALIVES_IDLE
• KEEPALIVES_INTERVAL
• KEEPALIVES_COUNT
• TCP_USER_TIMEOUT
• REPLICATION
• GSSENCMODE
• SSLMODE
• REQUIRESSL
• SSLNEGOTIATION
• SSLCOMPRESSION
• SSLCERT
• SSLKEY
• SSLKEYLOGFILE
• SSLPASSWORD
• SSLCERTMODE
• SSLROOTCERT
• SSLCRL
• SSLCRLDIR
• SSLSNI
• REQUIREPEER
• SSL_MIN_PROTOCOL_VERSION
• SSL_MAX_PROTOCOL_VERSION
• MIN_PROTOCOL_VERSION
• MAX_PROTOCOL_VERSION
• KRBSRVNAME
• GSSLIB
• GSSDELEGATION
• SCRAM_CLIENT_KEY
• SCRAM_SERVER_KEY
• SERVICE
• TARGET_SESSION_ATTRS
• LOAD_BALANCE_HOSTS
• OAUTH_ISSUER
• OAUTH_CLIENT_ID
• OAUTH_CLIENT_SECRET
• OAUTH_SCOPE

The following options are renamed to the corresponding keys:

• DATABASE – alias to DBNAME
• HOSTNAME – alias to HOST
• USERNAME – alias to USER

Instead of separate connection options, the full connection URI can be specified instead:

• URI – connection URI

Additional option is used for AWS RDS IAM authentication, see details in the next section below:

• AWS_RDS_SECRET – the name of the secret of type rds

AWS RDS IAM Authentication

Managed PostgreSQL databases running on RDS/Aurora services allow to use IAM authentication. In that case the authentication token is generated using AWS SDK and must be refreshed every 15 minutes.

The postgres extension supports IAM authentication, when the password is not specified in the secret, but instead one of the configured AWS Credential Providers is used to generate the password, that is refreshed by the postgres extension automatically.

Connecting with IAM Authentication from Command Line

This section illustrates the process with psql utility, not with DuckDB. This method can be used to check the configuration before setting up DuckDB secrets.

When IAM authentication is performed using psql command line tool, the following are the AWS-recommended connection steps:

• Generate authentication token using aws CLI.

• Pass this token as a password connection option:

  export RDSHOST="database-1-instance-1.xxx.eu-west-1.rds.amazonaws.com" 
  psql "host=$RDSHOST port=5432 dbname=postgres user=postgres sslmode=require password=$(aws rds generate-db-auth-token --hostname $RDSHOST --port 5432 --username postgres --region eu-west-1)"
  

Configuring Secrets for IAM Authentication

Authentication from the postgres extension uses the same logic as with psql:

• The secret of type rds is used to generate the authentication token, it takes the same configuration parameters as the aws rds generate-db-auth-token command in the example above:

  CREATE SECRET aws_rds_secret1 (
      TYPE rds,
      PROVIDER credential_chain,
      CHAIN 'env;sso;',
      REGION 'eu-west-1',
      RDS_USER 'postgres',
      RDS_HOST 'database-1-instance-1.xxxxxxxxxxxx.eu-west-1.rds.amazonaws.com',
      RDS_PORT '5432'
  );
  

• The secret of type postgres is used to create the remaining of the connection string. It takes the same parameters as the psql utility in the example above (and additionally any relevant additional libpq configuration options) and requires to specify the name of the rds secret, that is used to generate and periodically refresh (automatically) the authentication token that is passed to server as a password:

  CREATE SECRET pg_rds_secret1 (
      TYPE postgres,
      HOST 'database-1-instance-1.xxxxxxxxxxxx.eu-west-1.rds.amazonaws.com',
      PORT '5432',
      USER 'postgres',
      DATABASE 'postgres',
      SSLMODE require,
      AWS_RDS_SECRET aws_rds_secret1
  );
  

The secret of type rds requires the aws extension to be installed and allows to configure AWS Credential Chain the same way as with the secret of type s3, see details in the AWS extension documentation.

Storing Secrets inside a PostgreSQL Database

DuckDB Secrets Manager supports pluggable Storage Providers. The postgres extension implements storing the secrets (of any type) as records in the PostgreSQL database table.

The following example initializes the secrets storage and insers the secret into the duckdb_secrets table:

ATTACH 'postgres:' AS p1 (
    SECRET pg_rds_secret1,
    SECRET_STORAGE_TABLE duckdb_secrets
);

CREATE OR REPLACE SECRET s3_secret1 IN postgres_p1 (
    TYPE s3,
    PROVIDER credential_chain,
    CHAIN 'env;sso;',
    PROFILE 'DatabaseAdministrator-account_id',
    REGION 'eu-west-1'
);

When IN postgres_attached_database clause of CREATE SECRET is specified, the secret is persisted to the duckdb_secrets table in the specified attached database.

Warning Secrets are written to the database table in unencrypted binary format. It is advised to use this provider for secrets that do not include confidential credentials (like the s3 example above). For multi-tenant scenarios it is expected that Postgres Row-Level Security policies can be used to prevent users to see secrets of other users.

Different table name (for example, in a different schema) can be specified in the SECRET_STORAGE_TABLE parameter to the ATTACH command.

When the default duckdb_secrets table is used, it is not necessary to specify the SECRET_STORAGE_TABLE parameter. When a PostgreSQL database is attached, the table with the default name duckdb_secrets is probed automatically. If it exists then the Secret Storage instance is registerd for this attached database making all the stored secrets available in the current session. This allows to use such persistent secrets with “direct attach” scenarios, when only a connection string is specified to the duckdb command (other DuckDB clients may require slightly different syntax):

duckdb postgres:postgresql://username:[email protected]:5432/db1

This method can also be used with DuckLake (with PostgreSQL catalog), when an S3/object storage access secret is stored in the catalog database:

duckdb ducklake:postgres:postgresql://username:[email protected]:5432/db1

Secrets the are stored in the specific attached database can be listed using the following query:

FROM duckdb_secrets() WHERE storage = 'postgres_attached_database';

To disable the PostgreSQL Secret Storage completely pass the empty string '' in the SECRET_STORAGE_TABLE parameter.