rds secrets unusable from ECS/Fargate: no CHAIN value resolves container credentials, and CHAIN is mandatory (blocking the default chain)

[thefotios]

Summary

TYPE rds secrets cannot authenticate from ECS/Fargate tasks: no CHAIN value resolves ECS container credentials (AWS_CONTAINER_CREDENTIALS_RELATIVE_URI / AWS_CONTAINER_CREDENTIALS_FULL_URI), and rds secrets require CHAIN, which makes the SDK default chain — the only place the container provider lives — unreachable. Every DuckLake/postgres RDS-IAM attach from a Fargate task therefore fails with:

Unable to generate RDS IAM token for secret with name: "<secret>"

(the presigned token comes back unsigned — no credentials resolved — so the X-Amz-Algorithm check in duckdb-postgres src/postgres_aws.cpp throws).

Why

In src/aws_secret.cpp:

• DuckDBCustomAWSCredentialsProviderChain maps each chain name to exactly one SDK provider: env → EnvironmentAWSCredentialsProvider, config → ProfileConfigFileAWSCredentialsProvider, instance → InstanceProfileCredentialsProvider (EC2 IMDS — Fargate does not serve it), plus sts/sso/process/web_identity. Aws::Auth::TaskRoleCredentialsProvider (ECS container credentials) appears nowhere in the vocabulary, and unknown names throw.
• For input.type == "rds", an empty chain throws "Invalid RDS secret parameters, 'CHAIN' option must be specified" — so unlike s3 secrets, rds secrets can never fall back to DefaultAWSCredentialsProviderChain, which does include the container provider.

So the standard ECS/Fargate credential mechanism — task-role credentials via the container endpoint, the default for any containerized AWS workload — has no supported path.

Reproduction

Probed against @duckdb/node-api 1.5.4-r.1 (latest published) with a local mock of the container-credentials endpoint (AWS_CONTAINER_CREDENTIALS_FULL_URI=http://127.0.0.1:8899/creds serving valid-shape JSON creds; HOME isolated; no env keys):

CHAIN	endpoint hits	outcome
config, env, instance, process, sso, web_identity, config;env;instance	0	Unable to generate RDS IAM token
omitted / ''	—	rejected: 'CHAIN' option must be specified
sts + ASSUME_ROLE_ARN	2	credentials resolved (inner STSClient in AddSTSProvider is built without an explicit provider, so it uses the SDK default chain — the one code path that reaches container credentials)

INSTALL postgres; LOAD postgres; INSTALL aws; LOAD aws;
CREATE SECRET t (TYPE rds, PROVIDER credential_chain, CHAIN 'config',
  RDS_USER 'u', RDS_HOST 'h', RDS_PORT 5432, REGION 'us-east-1');
CREATE SECRET tp (TYPE postgres, HOST 'h', PORT '5432', USER 'u',
  DATABASE 'd', SSLMODE require, AWS_RDS_SECRET t);
ATTACH 'ducklake:postgres:' AS m (META_SECRET 'tp');
-- in any ECS/Fargate task: Unable to generate RDS IAM token for secret with name: "t"

Workaround

CHAIN 'sts' + ASSUME_ROLE_ARN pointing at the task role itself (requires adding a self-assume trust statement + an identity-policy sts:AssumeRole on the role). This works because the STS provider's inner client resolves the container credentials via the default chain — but a self-assuming IAM role is an odd requirement for what should be ambient credentials.

Suggested fixes (either resolves it)

1. Allow CHAIN to be omitted (or empty) for rds secrets and fall back to Aws::Auth::DefaultAWSCredentialsProviderChain, matching the s3 secret behavior.
2. Add a chain entry for container credentials (e.g. container → Aws::Auth::TaskRoleCredentialsProvider, or the newer GeneralHTTPCredentialsProvider).

[kinghuang]

Confirming this on EC2-backed ECS (not just Fargate), where it's arguably worse: there instance does resolve — but to the EC2 container-instance role via IMDS, not the task role. So a token is minted by the wrong principal and RDS rejects it with FATAL: PAM authentication failed for user "<db_user>", rather than failing at generation — easy to misdiagnose as a DB-permissions problem. Same root cause (no CHAIN token maps to TaskRoleCredentialsProvider, and rds mandates a non-empty CHAIN so the default-chain fallback is unreachable).

A point for fix #2 (a container chain token) over fix #1 (rds empty-chain fallback): the custom chain has no explicit container token for s3/aws secrets either — s3 reaches container credentials only via the implicit empty-CHAIN default fallback, so an s3 secret with an explicit CHAIN can't use them. A container → TaskRoleCredentialsProvider token would close the gap uniformly across all secret types, not just unblock rds.

(We worked around it by minting the RDS IAM token out-of-band with boto3 — whose default chain resolves the task role — and passing it as the postgres secret PASSWORD. Native support would be cleaner.)