Support setting profile, provider, and chain for RDS IAM authentication

[kinghuang]

What happens?

#457 added support for RDS IAM authentication, where the database password comes from calling the GenerateRDSAuthToken API. However, there's no parameters like profile, provider, and chain to set the underlying credentials used to call the API, like with S3 secrets. Only the default credentials provider chain and values in AWS_PROFILE, AWS_ACCESS_KEY_ID, and AWS_SECRET_ACCESS_KEY environment variables are supported. I am unable to configure the postgres secret to work with my AWS config.

To Reproduce

1. Have a local AWS profile that uses SSO sessions. Set that profile as the AWS_PROFILE environment variable.
2. Create a postgres secret for an RDS/Aurora instance with IAM-based authentication enable with aws_rds_iam_auth_enabled true.
3. Attempt to attach to the PostgreSQL instance.

Example statements using the README values to hide connection details.

create or replace secret rds_secret (
  type postgres,
  host 'my-db-instance.xxxxxx.us-west-2.rds.amazonaws.com',
  port 5432,
  database 'postgres',
  user 'my_iam_user',
  aws_rds_iam_auth_enabled true,
  aws_region 'us-west-2'
);

attach '' as rds_db (type postgres, secret rds_secret);

An IO Error occurs.

IO Error: Unable to connect to Postgres at "": connection to server at "my-db-instance.xxxxxx.us-west-2.rds.amazonaws.com" (10.x.x.x), port 5432 failed: fe_sendauth: no password supplied

With S3 secrets, I typically set the chain to 'env;sso to work with my AWS profiles.

create or replace secret s3 (
  type s3,
  provider credential_chain,
  chain 'env;sso',
  …
);

OS:

macOS

PostgreSQL Version:

17.5

DuckDB Version:

1.5.2

DuckDB Client:

DuckDB

Full Name:

King Chung Huang

Affiliation:

SensorUp

Have you tried this on the latest main branch?

• I agree

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• I agree

[staticlibs]

Hi, thanks for the report! Configurable provider chain was intended to be implemented after the 1.5.3. But the SSO setup was expected to work with the default chain. It seems that the SSO support is not included. In an attempt to fix that I filed #465 to explicitly add the SSO dependency to AWS SDK.

I wonder whether you can try the same setup on the extension version d48d642979, that should be published to core_nightly repo in about 30 min?

[kinghuang]

Tried, but no luck.

force install postgres from core_nightly;

select extension_name, extension_version from duckdb_extensions() where extension_name = 'postgres_scanner';

┌──────────────────┬───────────────────┐
│  extension_name  │ extension_version │
│     varchar      │      varchar      │
├──────────────────┼───────────────────┤
│ postgres_scanner │ d48d642           │
└──────────────────┴───────────────────┘

Attempting to attach still results in an IO Error with fe_sendauth: no password supplied.

[staticlibs]

Thanks for checking!

I set up AWS SSO and can connect with it successfully. Just when setting up I've seen a few different errors, but not the fe_sendauth: no password supplied you are seeing.

I am not convinced that my login actually uses SSO, so I just dump the setup that was working for me, would appreciate any insights on it:

$ aws configure sso
...
$ aws sso login
...

$ cat ~/.aws/config
[profile DatabaseAdministrator-<account_id>]
sso_session = session1
sso_account_id = <account_id>
sso_role_name = DatabaseAdministrator
region = eu-west-1
[sso-session session1]
sso_start_url = https://ssoins-xxx.portal.eu-west-1.app.aws
sso_region = eu-west-1
sso_registration_scopes = sso:account:access

$ env | grep AWS
AWS_PROFILE=DatabaseAdministrator-<account_id>

DuckDB v1.5.2 (Variegata)
Enter ".help" for usage hints.
memory D FORCE INSTALL postgres FROM core_nightly;
memory D SELECT extension_name, extension_version FROM duckdb_extensions() WHERE extension_name = 'postgres_scanner';
┌──────────────────┬───────────────────┐
│  extension_name  │ extension_version │
│     varchar      │      varchar      │
├──────────────────┼───────────────────┤
│ postgres_scanner │ d48d642           │
└──────────────────┴───────────────────┘
memory D LOAD postgres;
memory D CREATE OR REPLACE SECRET pg_rds_secret (TYPE postgres, HOST 'database-1-instance-1.xxx.eu-west-1.rds.amazonaws.com', PORT 5432, USER 'postgres', DATABASE 'postgres', SSLMODE require, AWS_RDS_IAM_AUTH_ENABLED TRUE, AWS_REGION 'eu-west-1');
┌─────────┐
│ Success │
│ boolean │
├─────────┤
│ true    │
└─────────┘
memory D ATTACH '' as p1 (TYPE postgres, SECRET pg_rds_secret);
memory D FROM postgres_query('p1', 'SELECT pg_backend_pid()');
┌────────────────┐
│ pg_backend_pid │
│     int32      │
├────────────────┤
│          17014 │
└────────────────┘

It was failing for me with PAM authentication failed for user "postgres" until I added the following inline policy in AWS console:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": "rds-db:connect",
            "Resource": "arn:aws:rds-db:eu-west-1:<account_id>:dbuser:cluster-<cluster_id>/postgres"
        }
    ]
}

Behaviour with the Postgres extension and with the following CLI DB login was the same for me:

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)"

[kinghuang]

I'll check my setup when I'm back at work on Monday. I do have the policy allowing the rds-db:connect actions, as I'm able to call the action using the AWS CLI manually, and use the resulting value as the password.

[staticlibs]

@kinghuang

Please note, the IAM auth logic was reworked in #467, now secrets of type rds are created in duckdb-aws extension and ue exactly the same provider chain setup as S3 secrets. So if S3 works with SSO - RDS is expected to work too.

Note, that to check this it is required to have updated version of duckdb-aws that includes duckdb/duckdb-aws#144, that is not yet merged. I will try to expedite its review and publishing to core_nightly tomorrow morning.

[staticlibs]

@kinghuang

Just FYI, all the changes went into core_nightly, postgres_scanner version should be 4e14d2e and aws version - 4bb2f03 (still building, should be ready in a few minutes).

[kinghuang]

I installed the aws and postgres_scanner extensions, verified that the versions match, and created new rds and postgres type secrets. I am able to connect using IAM based credentials through my AWS SSO profile, now!

Thanks so much for your work, @staticlibs!

[staticlibs]

Thanks for checking! If you find any problems with rds secrets - please don't hesitate to open an issue!