AWS SSO not working

[dobesv]

I was trying to use AWS SSO to access files on S3 from my development machine. I looked at the other issues previously opened for this and tried to use the information there to resolve it, but no luck so far. So I'm reaching out for a bit more guidance.

First I run SSO login to make sure I have fresh credentials:

$ aws sso login --profile dev
Successfully logged into Start URL: https://xxx.awsapps.com/start#

This is my redacted config:

$ cat ~/.aws/config 
[profile dev]
sso_session = xxx
sso_account_id = xxx
sso_role_name = xxx
region = us-east-1
output = json
[sso-session Formative]
sso_start_url = https://xxx.awsapps.com/start#
sso_region = us-east-1
sso_registration_scopes = sso:account:access

I run duckdb, you can see the version in the prompt:

$ duckdb
v1.1.3 19864453f7
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

I create the credentials per the documentation:

D CREATE OR REPLACE SECRET s3 (TYPE S3, PROVIDER CREDENTIAL_CHAIN, PROFILE 'dev');
100% ▕████████████████████████████████████████████████████████████▏ 
┌─────────┐
│ Success │
│ boolean │
├─────────┤
│ true    │
└─────────┘

I try to use an S3 operation that requires permissions:

D SELECT filename, last_modified FROM read_text('s3://bucket/path/*.js');
HTTP Error: HTTP GET error on '/?encoding-type=url&list-type=2&prefix=js%2Fvnd%2Fi18next%2F' (HTTP 403)

Now I check whether the secret was created with credentials in it; it does not:

D SELECT * FROM duckdb_secrets();
┌─────────┬─────────┬──────────────────┬────────────┬─────────┬─────────────────────────┬──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│  name   │  type   │     provider     │ persistent │ storage │          scope          │                                                          secret_string                                                           │
│ varchar │ varchar │     varchar      │  boolean   │ varchar │        varchar[]        │                                                             varchar                                                              │
├─────────┼─────────┼──────────────────┼────────────┼─────────┼─────────────────────────┼──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ s3      │ s3      │ credential_chain │ false      │ memory  │ [s3://, s3n://, s3a://] │ name=s3;type=s3;provider=credential_chain;serializable=true;scope=s3://,s3n://,s3a://;endpoint=s3.amazonaws.com;region=us-east-1 │
└─────────┴─────────┴──────────────────┴────────────┴─────────┴─────────────────────────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Just getting the extension versions for reference:

D SELECT * FROM duckdb_extensions();
┌──────────────────┬─────────┬───────────┬─────────────────────────────────────────────────────────────────────────────────────────────┬────────────────────────────────────────────────────────────────────────────────────┬───────────────────┬───────────────────┬───────────────────┬────────────────┐
│  extension_name  │ loaded  │ installed │                                        install_path                                         │                                    description                                     │      aliases      │ extension_version │   install_mode    │ installed_from │
│     varchar      │ boolean │  boolean  │                                           varchar                                           │                                      varchar                                       │     varchar[]     │      varchar      │      varchar      │    varchar     │
├──────────────────┼─────────┼───────────┼─────────────────────────────────────────────────────────────────────────────────────────────┼────────────────────────────────────────────────────────────────────────────────────┼───────────────────┼───────────────────┼───────────────────┼────────────────┤
│ arrow            │ false   │ false     │                                                                                             │ A zero-copy data integration between Apache Arrow and DuckDB                       │ []                │                   │                   │                │
│ autocomplete     │ true    │ true      │ (BUILT-IN)                                                                                  │ Adds support for autocomplete in the shell                                         │ []                │                   │ STATICALLY_LINKED │                │
│ aws              │ true    │ true      │ /home/dobes/snap/duckdb/9/.duckdb/extensions/v1.1.3/linux_amd64_gcc4/aws.duckdb_extension   │ Provides features that depend on the AWS SDK                                       │ []                │ f743d4b           │ REPOSITORY        │ core           │
│ azure            │ false   │ false     │                                                                                             │ Adds a filesystem abstraction for Azure blob storage to DuckDB                     │ []                │                   │                   │                │
│ delta            │ false   │ false     │                                                                                             │ Adds support for Delta Lake                                                        │ []                │                   │                   │                │
│ excel            │ false   │ false     │                                                                                             │ Adds support for Excel-like format strings                                         │ []                │                   │                   │                │
│ fts              │ true    │ true      │ (BUILT-IN)                                                                                  │ Adds support for Full-Text Search Indexes                                          │ []                │ v1.1.3            │ STATICALLY_LINKED │                │
│ httpfs           │ true    │ true      │ /home/dobes/snap/duckdb/9/.duckdb/extensions/v1.1.3/linux_amd64_gcc4/httpfs.duckdb_extens…  │ Adds support for reading and writing files over a HTTP(S) connection               │ [http, https, s3] │ v1.1.3            │ REPOSITORY        │ core           │
│ iceberg          │ false   │ false     │                                                                                             │ Adds support for Apache Iceberg                                                    │ []                │                   │                   │                │
│ icu              │ true    │ true      │ (BUILT-IN)                                                                                  │ Adds support for time zones and collations using the ICU library                   │ []                │ v1.1.3            │ STATICALLY_LINKED │                │
│ inet             │ false   │ false     │                                                                                             │ Adds support for IP-related data types and functions                               │ []                │                   │                   │                │
│ jemalloc         │ true    │ true      │ (BUILT-IN)                                                                                  │ Overwrites system allocator with JEMalloc                                          │ []                │ v1.1.3            │ STATICALLY_LINKED │                │
│ json             │ true    │ true      │ (BUILT-IN)                                                                                  │ Adds support for JSON operations                                                   │ []                │ v1.1.3            │ STATICALLY_LINKED │                │
│ motherduck       │ false   │ false     │                                                                                             │ Enables motherduck integration with the system                                     │ [md]              │                   │                   │                │
│ mysql_scanner    │ false   │ false     │                                                                                             │ Adds support for connecting to a MySQL database                                    │ [mysql]           │                   │                   │                │
│ parquet          │ true    │ true      │ (BUILT-IN)                                                                                  │ Adds support for reading and writing parquet files                                 │ []                │ v1.1.3            │ STATICALLY_LINKED │                │
│ postgres_scanner │ false   │ false     │                                                                                             │ Adds support for connecting to a Postgres database                                 │ [postgres]        │                   │                   │                │
│ shell            │ true    │ true      │                                                                                             │ Adds CLI-specific support and functionalities                                      │ []                │                   │ STATICALLY_LINKED │                │
│ spatial          │ false   │ false     │                                                                                             │ Geospatial extension that adds support for working with spatial data and functions │ []                │                   │                   │                │
│ sqlite_scanner   │ false   │ false     │                                                                                             │ Adds support for reading and writing SQLite database files                         │ [sqlite, sqlite3] │                   │                   │                │
│ substrait        │ false   │ false     │                                                                                             │ Adds support for the Substrait integration                                         │ []                │                   │                   │                │
│ tpcds            │ false   │ false     │                                                                                             │ Adds TPC-DS data generation and query support                                      │ []                │                   │                   │                │
│ tpch             │ true    │ true      │ (BUILT-IN)                                                                                  │ Adds TPC-H data generation and query support                                       │ []                │ v1.1.3            │ STATICALLY_LINKED │                │
│ vss              │ false   │ false     │                                                                                             │ Adds indexing support to accelerate Vector Similarity Search                       │ []                │                   │                   │                │
├──────────────────┴─────────┴───────────┴─────────────────────────────────────────────────────────────────────────────────────────────┴────────────────────────────────────────────────────────────────────────────────────┴───────────────────┴───────────────────┴───────────────────┴────────────────┤
│ 24 rows                                                                                                                                                                                                                                                                                      9 columns │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

I thought I'd try to update to nightly, but it doesn't work:

D force install aws from core_nightly;
HTTP Error: Failed to download extension "aws" at URL "http://nightly-extensions.duckdb.org/v1.1.3/linux_amd64_gcc4/aws.duckdb_extension.gz" (HTTP 403)
Extension "aws" is an existing extension.

Afterwards I had a couple ideas that I hoped would help:

1. My aws profile uses sso_session, which isn't supported by the kubernetes command line tools, do I thought maybe duckdb might also not support it. However, switching to a profile that doesn't use sso_session didn't fix the issue
2. I initially installed it using snap and I wondered if maybe duckdb was running in a sandbox and couldn't access my AWS credentials. I uninstalled and reinstalled using the ZIP file and it did not fix the issue

My understanding is that I can probably export temporary credentials to an env file and use it (or something along those files), but I thought maybe I should open an issue and see if this more convenient option can be made to work.

[dobesv]

I am able to access S3 if I export the credentials to env, e.g.:

$ eval '$(aws configure export-credentials --profile dev --format env)'
$ echo $AWS_ACCESS_KEY_ID
ASIA....
$ duckdb
v1.1.3 19864453f7
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D CREATE OR REPLACE SECRET s3 (TYPE S3, PROVIDER CREDENTIAL_CHAIN, CHAIN 'env');
100% ▕████████████████████████████████████████████████████████████▏ 
┌─────────┐
│ Success │
│ boolean │
├─────────┤
│ true    │
└─────────┘
D SELECT * FROM duckdb_secrets();
┌─────────┬─────────┬──────────────────┬────────────┬─────────┬─────────────────────────┬─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│  name   │  type   │     provider     │ persistent │ storage │          scope          │                                                                                            secret_string                                                                                            │
│ varchar │ varchar │     varchar      │  boolean   │ varchar │        varchar[]        │                                                                                               varchar                                                                                               │
├─────────┼─────────┼──────────────────┼────────────┼─────────┼─────────────────────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ s3      │ s3      │ credential_chain │ false      │ memory  │ [s3://, s3n://, s3a://] │ name=s3;type=s3;provider=credential_chain;serializable=true;scope=s3://,s3n://,s3a://;endpoint=s3.amazonaws.com;key_id=...;region=us-east-1;secret=redacted;session_token=redacted │
└─────────┴─────────┴──────────────────┴────────────┴─────────┴─────────────────────────┴─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
D SELECT filename, last_modified FROM read_text('s3://bucket/.../*.js');
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─────────────────────┐
│                                                   filename                                                    │    last_modified    │
│                                                    varchar                                                    │      timestamp      │
├───────────────────────────────────────────────────────────────────────────────────────────────────────────────┼─────────────────────┤
│ s3://bucket/path/xxx.js │ 2025-01-15 07:20:21 │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────┴─────────────────────┘

[liquidcarbon]

The route you tried initially - CREATE OR REPLACE SECRET s3 (TYPE S3, PROVIDER CREDENTIAL_CHAIN, PROFILE 'dev'); actually worked for me. Presumably it's looking for the named profile in .aws/credentials.

Unfortunately it's not documented here: https://duckdb.org/docs/extensions/httpfs/s3api.html

[dobesv]

Are you using the same version of duckdb and duckdb-aws as I am? I wonder if this is something that has been fixed/broken depending.

[dobesv]

I realized I was actually running an old version of duckdb so I tried again with 1.2.1 just now, but the results appear to be the same.

[dobesv]

Are you using a profile configured using aws configure sso and aws sso login ? As far as I know those SSO login sessions do not use or update ~/.aws/credentials. So your mention of that file makes me wondering if you're testing the same issue I am reporting.

[dobesv]

I tried installing the aws-sso-util and setting the credential_process in ~/.aws/config, and that actually worked. Perhaps this means that the AWS SDK used by duckdb doesn't support the new credentials storage format used by the AWS CLI v2?

[dobesv]

Actually there's a better credential_process built into the aws CLI now. So to get your AWS profile working with DuckDB if you are using AWS SSO sessions:

Add to the profile's config in ~/.aws/config:

credential_process = aws configure export-credentials --profile dev --format process

Make sure you run aws sso login --profile dev to login to that profile.

Run duckdb with AWS_PROFILE so the profile name, e.g.

AWS_PROFILE=dev duckdb

Now you can create the S3 secret very easily:

CREATE OR REPLACE SECRET s3 (TYPE S3, PROVIDER CREDENTIAL_CHAIN);

And now you'll be able to access S3:

SELECT filename, last_modified FROM read_text('s3://some-bucket/*');

Note that you'll want to do this again regularly to refresh the access key and secret, as they are cached in duckdb and not refresh automatically (at least at the time of writing).

[johnleuner-absa]

@dobesv I've been debugging some of the AWS SSO authentication logic and I found two things:

1. If the sso_start_url parameter ends in a hash, like this:

https://orgname.awsapps.com/start#

then for some reason the AWS SDK library computes a different hash to the one used by the aws sso login command.

So I had to remove the hash in the sso_start_url parameter.

2. I have to set the env var AWS_PROFILE=profilename before running duckdb, then I can create a secret like this:

CREATE SECRET secret2 (
TYPE s3,
PROVIDER credential_chain,
REGION 'myregion',
ENDPOINT 's3.myregion.amazonaws.com'
);

however, setting the PROFILE option for some reason does not work

CREATE SECRET secret2 (
TYPE s3,
PROVIDER credential_chain,
PROFILE 'myprofile',
REGION 'myregion',
ENDPOINT 's3.myregion.amazonaws.com'
);

I tested with duckdb v1.2.1

[nicornk]

Does not work for me :( duckdb 1.2.1 seems to not even start the credentials_process.

I have the following profile defined in ~/.aws/config:

[profile duckdb-test]
credential_process = 'touch /Users/user1/.duckdb/IwasHere'

and create the secret like this

con.execute("""
CREATE OR REPLACE SECRET processTest (
TYPE s3,
PROVIDER CREDENTIAL_CHAIN,
PROFILE 'duckdb-test'
);
""")

In addition, I have setup AWS_PROFILE environment variable to duckdb-test as well. The file /Users/user/.duckdb/IwasHere is never created though which makes me think that duckdb-aws is not executing the credential_process at all.

[dobesv]

Have you tried running duckdb like:

AWS_PROFILE=duckdb-test duckdb

And then running

CREATE OR REPLACE SECRET s3 (TYPE S3, PROVIDER CREDENTIAL_CHAIN);

Inside that duckdb prompt? Some had said that the PROFILE argument wasn't working for this case, so maybe try it without relying on that.

Also, I wonder whether an AWS profile with only credential_process set is valid? Were you able to use that profile using AWS CLI - e.g. if you run something like AWS_PROFILE=duckdb-test aws s3 ls s3://foo would it create the file in that case ?

[nicornk]

Yes, I tried various different way to set the profile, including setting the AWS_PROFILE environment variable before I started the duckdb prompt.

Yes, I can confirm that the file is created when I run aws s3 ls s3://somebucket --profile duckdb-test

Is there any other way to debug this further?

[nicornk]

Had some time to debug into this, documenting my findings:

aws_secret.cpp:

if (input.options.find("profile") != input.options.end()) {
	Aws::Auth::ProfileConfigFileAWSCredentialsProvider provider(profile.c_str());
	credentials = provider.GetAWSCredentials();
} else {
	Aws::Auth::DefaultAWSCredentialsProviderChain provider;
	credentials = provider.GetAWSCredentials();
}

This explains why setting the Profile only works via AWS_PROFILE environment variable. If set via configuration option in duckdb, the first clause in the condition is jumped into and the ProfileConfigFileAWSCredentialsProvideris used which does not use the ProcessCredentialsProvider at all and therefore does not execute the credentials_prcess. The issue seems that we can't pass a profile into DefaultAWSCredentialsProviderChain and therefore the logic explicitly uses the ProfileConfigFileAWSCredentialsProvider which does not use ProcessCredentialsProvider.

On this computer I used latest duckdb from master and the IwasHere is actually created when I specify the profile using the Environment Variable! Now, to fix this, trying the manual way with a CHAIN configuration option:

CREATE OR REPLACE SECRET s3 (TYPE S3, PROVIDER CREDENTIAL_CHAIN, CHAIN 'process');

This also creates the IwasHere file - and I think the profile can be passed if I change this line:

else if (item == "process") {
				AddProvider(std::make_shared<Aws::Auth::ProcessCredentialsProvider>());
} 

to:

else if (item == "process") {
	if (profile.empty()) {
		AddProvider(std::make_shared<Aws::Auth::ProcessCredentialsProvider>());
	} else {
		AddProvider(std::make_shared<Aws::Auth::ProcessCredentialsProvider>(profile.c_str()));
	}
}

I will prepare a PR for that fix.

[johnleuner-absa]

@nicornk

What do you think about the SSO path? Is there a way to use the PROFILE specified for the SSO provider (or when using the default chain)?

[nicornk]

When you use the sso path, the profile is already taken into consideration.

else if (item == "sso") {
	if (profile.empty()) {
		AddProvider(std::make_shared<Aws::Auth::SSOCredentialsProvider>());
	} else {
		AddProvider(std::make_shared<Aws::Auth::SSOCredentialsProvider>(profile));
	}

https://github.com/duckdb/duckdb-aws/blob/035c589a846b448d5c9cf3523ebfe439053a4406/src/aws_secret.cpp#L85C6-L90C6

In the default chain the AWS SDK does not allow to pass in a profile - that could be a feature request to the AWS c++ sdk?