Handling multiple AWS credentials

[jakobludewig]

I need to frequently switch between multiple AWS accounts for querying Athena.

Right now my workaround is to create multiple environment variables in my .Renviron file and then overwrite the default environment variables like so

Sys.setenv(AWS_SECRET_ACCESS_KEY = Sys.getenv("AWS_SECRET_ACCESS_KEY_2"),
           AWS_ACCESS_KEY_ID = Sys.getenv("AWS_ACCESS_KEY_ID_2"))

con <- dbConnect(AWR.Athena::Athena(), 
                 region='eu-west-1', 
                 Schema='my_schema')

It's obviously not a big issue obviously but I'm wondering if there is a way to directly pass the credentials to the driver? I'm guessing the ~/.aws/credentials file is also not an option for me as it will always go for the default one? Happy to help working on a feature if you think it worthwhile, just need some pointers in the right direction

[nfultz]

Right now creds are fetched in Java, not passed through via R: https://github.com/nfultz/AWR.Athena/blob/e871e7c92781cf51b8edfd399f3f2e738ad2e637/R/athena.R#L77

If you can place different profiles into the default .aws/credentials file, can you try setting AWS_PROFILE, or you can change the location for the default file using AWS_CREDENTIAL_PROFILES_FILE.

https://docs.aws.amazon.com/sdk-for-java/v1/developer-guide/credentials.html

If those don't help, maybe you can figure out how to specify different sections in the default config file, or specify different config files that would be cool via java. See https://docs.aws.amazon.com/AWSJavaSDK/latest/javadoc/com/amazonaws/auth/profile/ProfileCredentialsProvider.html

I'm not super excited about anything that encourages/facilitates people to paste their AWS creds into an R script directly, there are plenty of better ideas. That said, you could try setting the java system properties, which are in between env vars and the config file in precedence. See https://docs.aws.amazon.com/AWSJavaSDK/latest/javadoc/com/amazonaws/auth/DefaultAWSCredentialsProviderChain.html

[jakobludewig]

Thanks for your response. I have tried your suggestions but unfortunately had no success. I specified the AWS_PROFILE and AWS_CREDENTIAL_PROFILES_FILE both as R environment variables and global OS environment variables (MacOSx). They do not seeem to be picked up by the "DefaultAWSCredentialsProviderChain" class. I'm a bit out of my depth with Java. Since there does not seem to be an easy fix and it is probably not an issue for many people feel free to close this.

[daroczig]

Make sure to set the env var either before starting R, but for sure no later than loading the AWR.Athena package, eg

library(rJava)
.jcall("java/lang/System", "S", "setProperty", "aws.profile", "foobar")
library(AWR.Athena)

[nfultz]

@daroczig I think credentials provider is not loaded until dbConnect. But it always good advice to set env vars early, because once the processes fork, they can't affect each other easily.

@jakobludewig here is an example of switching from non-existant "foobar" profile, getting a error about it not being found, and switching back to default.

> library(rJava)
> .jcall("java/lang/System", "S", "setProperty", "aws.profile", "foobar")
NULL
> library(AWR.Athena)
> .jcall("java/lang/System", "S", "getProperty", "aws.profile")
[1] "foobar"
> 
> ?AWR.Athena
No documentation for 'AWR.Athena' in specified packages and libraries:
you could try '??AWR.Athena'
> ?Athena
> require(DBI)
Loading required package: DBI
> con <- dbConnect(AWR.Athena::Athena(), region='us-west-2', 
+                  S3OutputLocation='s3://nfultz-athena-staging', 
+                  Schema='default')
log4j:ERROR Could not find value for key log4j.appender.stdout
log4j:ERROR Could not instantiate appender named "stdout".
Error in .jcall(drv@jdrv, "Ljava/sql/Connection;", "connect", as.character(url)[1],  : 
  java.sql.SQLException: [Simba][AthenaJDBC](100131) An error has been thrown from the AWS SDK client. Unable to load AWS credentials from any provider in the chain: [EnvironmentVariableCredentialsProvider: Unable to load AWS credentials from environment variables (AWS_ACCESS_KEY_ID (or AWS_ACCESS_KEY) and AWS_SECRET_KEY (or AWS_SECRET_ACCESS_KEY)), SystemPropertiesCredentialsProvider: Unable to load AWS credentials from Java system properties (aws.accessKeyId and aws.secretKey), com.simba.athena.amazonaws.auth.profile.ProfileCredentialsProvider@349c1daf: No AWS profile named 'foobar', com.simba.athena.amazonaws.auth.EC2ContainerCredentialsProviderWrapper@4795ded0: Unable to load credentials from service endpoint] [Execution ID not available]
> dbListTables(con)
Error in dbListTables(con) : object 'con' not found
> dbGetQuery(con, "Select count(*) from sampledb.elb_logs")
Error in dbGetQuery(con, "Select count(*) from sampledb.elb_logs") : 
  object 'con' not found
> 
> .jcall("java/lang/System", "S", "setProperty", "aws.profile", "default")
[1] "foobar"
> con <- dbConnect(AWR.Athena::Athena(), region='us-west-2', 
+                  S3OutputLocation='s3://nfultz-athena-staging', 
+                  Schema='default')
> dbListTables(con)
[1] "elb_logs" "elb_logs"
> dbGetQuery(con, "Select count(*) from sampledb.elb_logs")
    _col0
1 1356206

Maybe I'll think about adding a helper to set aws.profile, and/or saving it as metadata on the connection object. It's a bit difficult to debug because RJDBC is doing all the actual work.