Unable to load an AWS profile by name using either config or process

[deinspanjer]

When I try creating a secret as follows:

CREATE SECRET s3secret (
    TYPE S3,
    PROVIDER CREDENTIAL_CHAIN,
    CHAIN 'process',
    PROFILE 'testprof'
);

I get a return of true but the secret doesn't seem to be valid because I get a 403 when I try to read an S3 parquet file.

Here is an example of what my .aws/config looks like for this profile:

[profile testprof]
granted_sso_start_url      = https://d-xxx.awsapps.com/start
granted_sso_region         = us-east-1
granted_sso_account_id     = xxx
granted_sso_role_name      = SSO-AdministratorAccess
granted_sso_registration_scopes = sso:account:access
common_fate_generated_from = aws-sso
credential_process         = granted credential-process --profile testprof

What additional information can I provide to try to debug this?

[deinspanjer]

Oh, just to be clear, I tried this with both the standard 1.0 duckdb and aws that comes with it as well as trying the latest version of the extension using force install aws from core_nightly. I don't know how to get an actual version number from that, but I tried it today, 2024-09-04.

[samansmink]

you can get the versions of extensions with select * from duckdb_extensions()

and i assume you have tried a different tool like the aws cli to confirm the sso profile is working?

Also just wondering, why are you specifying the process chain? could you try it with the default chain by omitting the chain parameter?

[deinspanjer]

Yes, I use the aws cli and several other tools and utilities with these profiles on a daily basis.

I was trying to use the process chain because currently, DuckDB is not able to get a valid secret and use it with the default chain because I don't currently generate the AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY environment variables, only the AWS_PROFILE variable when I authenticate.

Other tools such as the aws cli will use the credential process of the specified profile if there aren't an id and secret in the environment, and that is what I was attempting to get DuckDB to use as well.

I was able to work around this by passing an argument to granted's authentication telling it to export a temporary id and secret and then launch DuckDB inside that shell, but it isn't optimal because it means I don't get automatic renewal of the credentials and I can't launch DuckDB inside another process such as my database explorer.