Amazon RDS with IAM Authentication

Managed PostgreSQL databases on Amazon RDS and Aurora can authenticate with short-lived IAM tokens instead of a static password. DuckDB supports this through the postgres and aws extensions: the aws extension generates the token from an AWS credential chain, and the postgres extension uses it as the connection password, refreshing it automatically.

Installation and Loading

INSTALL postgres;
INSTALL aws;
LOAD postgres;
LOAD aws;

Creating the Secrets

First, create a secret of type rds. It uses the AWS credential chain to mint the IAM token:

CREATE SECRET aws_rds_secret (
    TYPE rds,
    PROVIDER credential_chain,
    CHAIN 'env;sso',
    REGION '⟨eu-west-1⟩',
    RDS_USER '⟨postgres⟩',
    RDS_HOST '⟨instance⟩.⟨identifier⟩.⟨region⟩.rds.amazonaws.com',
    RDS_PORT '5432'
);

Then create a secret of type postgres that references it via AWS_RDS_SECRET. Because no password is set, the token generated by the rds secret is used (and refreshed) automatically:

CREATE SECRET pg_rds_secret (
    TYPE postgres,
    HOST '⟨instance⟩.⟨identifier⟩.⟨region⟩.rds.amazonaws.com',
    PORT '5432',
    USER '⟨postgres⟩',
    DATABASE '⟨postgres⟩',
    SSLMODE require,
    AWS_RDS_SECRET aws_rds_secret
);

Connecting and Querying

Attach the database using the postgres secret, then query it as a regular database:

ATTACH '' AS rds_db (TYPE postgres, SECRET pg_rds_secret);
SELECT * FROM rds_db.public.⟨tbl_name⟩;

See Also

• Postgres extension secrets — AWS RDS IAM Authentication — the full reference, including verifying the setup with psql first.
• AWS extension — the credential chain options available to the rds secret.