[Python] Connect DuckDB to S3 with Role-Based Credentials

In order to connect to S3, I have to have a config file in ~/.aws/config that looks like the following:

[profile my_profile]
output = json
region = us-east-1
role_arn = arn:aws:iam::yyyyyyyyyyyy:role/my_awesome_role

[profile my_profile_exec]
output = json
region = us-east-1
role_arn = arn:aws:iam::xxxxxxxxxxxx:role/my_awesome_role
source_profile = my_profile


PLEASE NOTE THE ARN BETWEEN THE TWO PROFILES IS DIFFERENT.

When producing credentials (aws_access_key_id, aws_secret_access_key and aws_session_token), all of which are in the credentials file at ~/.aws/credentials, they are for the profile my_profile.

With all that done, when I run the AWS CLI command: aws s3 ls --profile=my_profile_exec, I get proper results.

In pure Python, the connection works as well and looks like this (abbreviated):

>>> session = boto3.Session(profile_name=my_profile_exec)
>>> s3_client = session.client("s3")
>>> # do stuff with s3_client


I'm trying to get this to work with the DuckDB CLI but am having no luck at all. Here's about the closest I've been able to come to figuring it out:

D CREATE SECRET secret (
TYPE s3,
PROVIDER CREDENTIAL_CHAIN,
CHAIN 'config',
PROFILE 'my_profile_exec');
|----------|
| Success |
| boolean |
|----------|
| true |
|-----------|
D SELECT * FROM read_csv('s3://bucket/path/to/file');
HTTP Error: HTTP GET error on 'https://bucket/path/to/file' (HTTP 400)


I've searched high and low on the internet but haven't run into an example of someone with a setup like this.

Here's my environment. If anyone can help out, it'd be greatly appreciated:

• MacOS 15.2
• MacBook Pro, 2021, Apple M1 Pro
• duckdb --version = 1.1.3 19864453f7
• aws-cli/2.13.29 Python/3.11.6 Darwin/24.2.0 exe/x86_64 prompt/off

Python & 3rd Party Packages

• Python 3.13.1
• boto3 = 1.35.97
• botocore = 1.35.97
• duckdb = 1.1.3

Continue reading...