Not picking up AWS_PROFILE

[gregbacchus]

I'm playing around with duckdb, very new to it. I'm trying to connect to my s3 bucket. I have aws cli configured on my computer with a number of profiles. Access to my bucket requires that I use the profile sandbox. When I set the env var export AWS_PROFILE=sandbox, the aws cli can access the bucket no problems.

In duckdb I have created the following secret

CREATE OR REPLACE PERSISTENT SECRET s3_sandbox (
  TYPE s3,
  PROVIDER credential_chain
  CHAIN 'config'
);

but when I try to access my bucket it does not work

D SELECT * FROM read_parquet('s3://###/test.parquet');
HTTP Error:
HTTP GET error on 'https://###.s3.amazonaws.com/test.parquet' (HTTP 403)

Authentication Failure - this is usually caused by invalid or missing credentials.
* No credentials are provided.
* See https://duckdb.org/docs/stable/extensions/httpfs/s3api.html

Should this work? What am I doing wrong?

┌─────────────────┬────────────┬───────────┐
│ library_version │ source_id  │ codename  │
│     varchar     │  varchar   │  varchar  │
├─────────────────┼────────────┼───────────┤
│ v1.3.2          │ 0b83e5d2f6 │ Ossivalis │
└─────────────────┴────────────┴───────────┘

[Tmonster]

Thank you for filing the issue.

Potentially DuckDB is only picking up the default profile? Can you try adding a PROFILE option? As detailed here

CREATE OR REPLACE SECRET secret (
    TYPE s3,
    PROVIDER credential_chain,
    CHAIN config,
    PROFILE 'my_profile'
);

docs link

If that also doesn't work, investigating the logs may also provide some more information?

pragma enable_logging('HTTP');
-- select command
select * from duckdb_logs_parsed('HTTP');
select unnest(request), unnest(response) from duckdb_logs_parsed('HTTP');

the untested request object should say what secret is being used

[Tmonster]

Update: It seems there is an issue with declaring a profile that assumes a role/uses STS. So if your profile has that behavior I'm afraid the above won't solve your issue. Planning to have a fix before v1.4

[gregbacchus]

Thanks for the response. Yes, with the above suggestion still not working. Guess I'll have to wait for v1.4 😃

[Tmonster]

@gregbacchus can you also confirm then that the role assumes another role / uses STS? I believe someone else had the same issue

[gregbacchus]

Yes it does assume another role using STS.

[Tmonster]

Hi @gregbacchus,

I think one workaround (although cumbersome) is to declare sts in the create secret statement

CREATE SECRET s3table_secret (
    TYPE s3,
    PROVIDER credential_chain,
    CHAIN 'sts',
    ASSUME_ROLE_ARN 'arn:aws:iam::<account_id>:role/<assumed_role>'
);

This assumes the default role for the assumed_role is default in ~/.aws/credentials

[Tmonster]

Should be fixed with #102