profile param does not working with AWS SSO config

[bobcolner]

I am able to use load_aws_credentials() with my default profile but it does not work when I specify a different profile, e.g. load_aws_credentials('prod')

[bobcolner]

this is still not working for me

[samansmink]

@bobcolner thanks for reporting. SSO configs may not yet be fully supported

[mehd-io]

Hey @samansmink!
I've played around and using sso while specifying the profile still doesn't work.
BUT when doing this :

CREATE SECRET (
      TYPE S3,
      PROVIDER credential_chain,
      CHAIN 'sso',
      PROFILE 'my-profile'
  )

This does work. So, I guess we missed a small thing in the extension 🤔?
Maybe worth updating the documentation to mention how to use sso , I've heard a couple of users requesting this

[herebebeasties]

This all seems somewhat broken on v0.10 on Linux:

• CALL load_aws_crendentials(); no longer works on v0.10.0 20b1486d11 for me when AWS_PROFILE is set. It comes back with empty fields where there should be secret IDs, etc. This is regression from v0.9.2 3c695d7ba9. (Although even 0.9.2 is not reliable - see support loading credentials set by aws sso login #10.)
• CREATE SECRET does not work for me when specifying the profile name - it returns Success=true from the call, but I then get a 403 error when the S3 GET request is made
• If I set the AWS_PROFILE env var and use CREATE SECRET, omitting the profile parameter, it fails in the same way (and also if you omit the CHAIN parameter too)

I can't make either CALL load_aws_credentials() or CREATE SECRET work on v0.10. If I run aws configure export-credentials --profile foo and manually set the legacy s3_access_key_id, s3_secret_access_key and s3_session_token config variables then everything works properly, so there's nothing wrong with the current SSO session, just the wiring here in DuckDB.

[craig-latacora]

same issue as @herebebeasties but on macos

[samansmink]

Hey everyone, thanks for reporting. I will try to set up some SSO config in our CI testing environment to properly test the SSO login method and fix any issues.

Just for clarity here, @herebebeasties Did you also try the create secret syntax as described by @mehd-io?

[samansmink]

Afaict the workflow described by @mehd-io works. I will PR this to the docs.

Since load_aws_credentials is now deprecated, I did not test that and will not fix that if its broken

I will leave this open until this is properly tested in the aws extension ci

[herebebeasties]

Just for clarity here, @herebebeasties Did you also try the create secret syntax as described by @mehd-io?

Yes. It did not work for me - see the bullet points in my above message. What I've put in my above message is accurate. Adding the chain 'sso' param does nothing to help.

[samansmink]

@herebebeasties

Could you provide a reproducible example for the CREATE SECRET flow, describing the exact steps you've taken and the contents of your ~/.aws/config file? otherwise its a bit hard for me to reproduce this

[herebebeasties]

@herebebeasties

Could you provide a reproducible example for the CREATE SECRET flow, describing the exact steps you've taken and the contents of your ~/.aws/config file? otherwise its a bit hard for me to reproduce this

I'm not really sure what a "reproducible example" would look like here beyond what I've already described, other than scripting up the creation of a whole AWS environment, S3 bucket, AWS policy, role, policy mapping, Microsoft Azure AD domain creation, SSO setup, etc. which would be pretty complex and time-consuming. Do you have something smaller and more specific in mind?

I'll try to find some time to have a stab at debugging why this is failing in gdb, as tbh it's probably easier to do that than the above.

[samansmink]

Well, for example, I ran:

aws configure sso

now my ~/.aws/config looks like

[default]
region=eu-west-1

[profile duckdb-sso-test]
sso_session = ...
sso_account_id = ...
sso_role_name = ...
region = eu-west-1

[sso-session duckdb-sso-test]
sso_start_url = ...
sso_region =...
sso_registration_scopes = ...

Then to login I run:

aws sso login --profile=duckdb-sso-test

Now I get a pop up in my browser to login, which I do.

Then in DuckDB i run:

CREATE SECRET (
    TYPE s3, 
    PROVIDER credential_chain, 
    CHAIN 'sso', 
    PROFILE 'duckdb-sso-test'
);

And now my auth works fine, and when I query SELECT secret_string FROM duckdb_secrets() I get:

name=sso;type=s3;provider=credential_chain;serializable=true;scope=s3://,s3n://,s3a://;endpoint=s3.amazonaws.com;key_id=redacted;region=eu-west-1;secret=redacted;session_token=redacted

Alternatively, If you would give a stab at debugging this, that would be super useful for sure.

[herebebeasties]

That's exactly the same skeleton as my set-up and yet it doesn't work for me. I'll have a look in a debugger.

[samansmink]

Thanks! Be aware of the fact that I had to rerun the aws sso login --profile=duckdb-sso-test after some time. Also, DuckDB stores the raw temporary credentials in the secret, so when the session token expires, the secret is not automatically updated and needs to be manually re-created

[aweiher]

interesting, I have the same problem with duckdb under linux

what worked in the end:

1. write tmp credential to env: `$(AWS_PROFILE=dev-sso aws configure export-credentials --format env)`
2. start duckdb
3. load credential: `CALL load_aws_credentials();`
4. set the region: `SET s3_region = 'eu-central-1';`
5. select data: `SELECT * FROM 's3://bucket/path/file.parquet';` 

Using the CONFIG storage above, it returns "success" but does not store any secret:

D CREATE SECRET (
      TYPE S3,
      PROVIDER CREDENTIAL_CHAIN,
      REGION 'eu-central-1',
      PROFILE 'dev-sso',
      ENDPOINT 's3.eu-central-1.amazonaws.com'
  );
100% ▕████████████████████████████████████████████████████████████▏
┌─────────┐
│ Success │
│ boolean │
├─────────┤
│ true    │
└─────────┘

D SELECT secret_string FROM duckdb_secrets();
┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                       secret_string                                                                        │
│                                                                          varchar                                                                           │
├────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ name=__default_s3;type=s3;provider=credential_chain;serializable=true;scope=s3://,s3n://,s3a://;endpoint=s3.eu-central-1.amazonaws.com;region=eu-central-1 │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

(no secret=redacted or session_token in secret_string 🤔)

[samansmink]

@aweiher thx for this. I will look further into this once I find the time.

As a sidenote i created thisone #41 to address:

(no secret=redacted or session_token in secret_string 🤔)

[drmorr0]

I put up a PR (#49) to support SSO auth using the new method. It works on my machine ™️

[samansmink]

I will close this issue, it works now on at least 2 machines ;) Feel free to open a new issue if sso problems persist