AWS Credentials Profiles

[cabarria]

Shouldn't passing Profile=Profile_Name in the Additional Athena connection string options field work to select credentials stored in the ~/.aws/credentials file? We have a tool that generates our temporary credentials and automatically refreshes them.

[ProfileName] aws_access_key_id= aws_secret_access_key= aws_session_token= region=

It doesn't appear that the driver is picking up these creds; however, if I export them as environment variables instead, it works. export AWS_ACCESS_KEY_ID export AWS_SECRET_ACCESS_KEY export AWS_SESSION_TOKEN

[dacort]

Hm, that should work but I haven't tested it. It's possible the driver is always attempting to override the credentials explicitly in connection-details->spec

Are you leaving the access key blank when setting up the connection?

[cabarria]

Yeah, I'm leaving those empty and everything works if I use an instance profile under EC2 or setup the environment variables before launching metabase export AWS_ACCESS_KEY_ID export AWS_SECRET_ACCESS_KEY export AWS_SESSION_TOKEN

However, if I try to use the credentials stored in ~/.aws/credentials it doesn't work. I found out that the Athena 2.0.9 JDBC driver doesn't support the Profile argument, so I rebuild the driver using the Athena JDBC preview driver here: https://athena-downloads.s3.amazonaws.com/drivers/JDBC/athena-preview/SimbaAthenaJDBC_2.0.11_preview/AthenaJDBC42_preview.jar which not only does it let me specify a profile to use, but also a Catalog which should then allow the configuration of multiple Athena databases located in different AWS accounts using non-default catalogs as well. To keep the current test as simple as possible I'm only testing the Profile argument so the connection screen has this value for "Additional Athena connection string options" Profile=AWSAcct2

That profile is in the ~/.aws/credentials file with the 3 values for access_key, secret, and session token. These same values work if I export them as environment variables, but not when I try to make the driver read them from the credentials file. Those are the only 3 values in the file. This is the error message shown when I try to add the database connection:

java.lang.Exception: [Simba]AthenaJDBC Failed to create AWS Credentials Provider class: The values of the connection properties profile is in conflict with AwsCredentialsProviderClass.

It seems to me that it should work given all the docs I've read as I don't believe we need to implement the Custom Credentials Provider shown on page 37 here: https://athena-downloads.s3.amazonaws.com/drivers/JDBC/athena-preview/SimbaAthenaJDBC_2.0.11_preview/docs/Simba+Athena+JDBC+Driver+Install+and+Configuration+Guide.pdf

I'm assuming this should be all that's needed using the preview driver and passing the Profile as the additional arguments:

   (when (str/blank? access_key)
     {:AwsCredentialsProviderClass "com.simba.athena.amazonaws.auth.DefaultAWSCredentialsProviderChain"})
   (dissoc details :db))
  (sql-jdbc.common/handle-additional-options details, :seperator-style :semicolon)))

Thoughts?

[dacort]

Hm, so I see this in the docs:

If the Profile property is set, then you must specify AwsCredentialsProviderArguments (or its aliases) in the profile instead of the connection URL.

That's a little confusing, though and I'm not sure what it means.

Also, in the section titled "Example: Using a Profile to Provide a Session Token", it mentions a different approach:

• Set the AwsCredentialsProviderClass property to com.simba.athena.amazonaws.auth.profile.ProfileCredentialsProvider.
• Set the AwsCredentialsProviderArguments property to the name of the profile that you want to use. In this case, simba_session.

For example:

jdbc:awsathena://AwsRegion=us-east-1;S3OutputLocation=s3://my-athena-resultbucket/test/;AwsCredentialsProviderClass=com.simba.athena.amazonaws.auth.profile.ProfileCredentialsProvider;AwsCredentialsProviderArguments=simba_session;

So yea, if Profile is set, then I think AwsCredentialsProviderClass needs to be set to com.simba.athena.amazonaws.auth.profile.ProfileCredentialsProvider and another argument (AwsCredentialsProviderArguments) needs to be added with the actual profile name.

[cabarria]

Got side-tracked, but I think you are right. I'm not familiar with clojure, but will take a look and see if I can get it working.

[cabarria]

I finally got some time to look at this and got the code working with profiles. I added a new connection property aws_profile and updated the method as shown below. I was also able to test with the preview driver and both Catalog/Schema properties are working too. I'm trying to get someone else to help with https://github.com/dacort/metabase-athena-driver/issues/11 before I get more free time to look at it.

(defmethod sql-jdbc.conn/connection-details->spec :athena [_ {:keys [region aws_profile access_key secret_key s3_staging_dir workgroup db], :as details}] (-> (merge {:classname "com.simba.athena.jdbc.Driver" :subprotocol "awsathena" :subname (str "//athena." region ".amazonaws.com:443") :user access_key :password secret_key :s3_staging_dir s3_staging_dir :workgroup workgroup :AwsRegion region ; :LogLevel 6 } (if (and (str/blank? access_key) (str/blank? aws_profile)) ({:AwsCredentialsProviderClass "com.simba.athena.amazonaws.auth.DefaultAWSCredentialsProviderChain"}) (if (not (str/blank? aws_profile)) {:AwsCredentialsProviderClass "com.simba.athena.amazonaws.auth.profile.ProfileCredentialsProvider" :AwsCredentialsProviderArguments aws_profile})) (dissoc details :aws_profile :db)) (sql-jdbc.common/handle-additional-options details, :seperator-style :semicolon)))

[dacort]

Closing as part of cleanup now that Athena is officially supported by Metabase. Any future issues can be asked about on their forum or with a detailed bug report.