Aws-toolkit-jetbrains: Datagrip - RDS IAM authentication support

[pasali]

Hi,

We use DataGrip for database management tool and i want to use IAM authentication to give access users over RDS databases. Problem is when you want to connect to database you must generate a token with aws cli and paste it to the DataGrip. Since tokens are valid for 15 minutes, you have to do these steps nearly evertime you want to connect.

So i was wondering if there is a way to make DataGrip generate token with aws cli and use it as password when connecting to db.

[ddadon10]

I would totally love to see this feature.

With an auto-renew of the token every 15min so we can seamless use the DB with the IAM authentication.

It would be a tremendous new feature for DataGrip and AWS

[vascop]

Not to make this just a +1, we'd definitely have a much easier time if this feature was available. Right now we need to keep a bunch of things in place to create/delete database roles, keep them in sync with IAM users because most of our developers enjoy using Datagrip - this would allow us to delete some code and make the process much simpler.

[justinmk3]

We are working on a plan for this, but there is no timeline yet.

[beanaroo]

I'd also like to include that we are using IAM Role Assumption on top of IAM Authentication for connection to various databases in multiple organization accounts. It would be great if this capability is included in the implementation.

[abrooksv]

I'd also like to include that we are using IAM Role Assumption on top of IAM Authentication for connection to various databases in multiple organization accounts. It would be great if this capability is included in the implementation.

Do you mean you are using source_profile in the CLI credential file?

[abrooksv]

@vascop @dadon-david @pasali I am trying to better understand this, are you connecting to things like Postgres on RDS with temp tokens generated using aws rds generate-db-auth-token? Is the database in a VPC?

[vascop]

@abrooksv we're trying to connect to Postgres databases on RDS (Aurora in our case), following this guide, which does use generate-db-auth-token: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.Connecting.AWSCLI.PostgreSQL.html

Everything works correctly if you use psql or similar tools. But like @pasali mentions in his original post, if you follow this procedure, your tokens will expire in 15mins and so you need to keep generating them and re-pasting them into Datagrip, which is not ideal.

We did find a driver for Redshift, which seems to do a similar thing to what we'd need done here, for RDS: https://docs.aws.amazon.com/redshift/latest/mgmt/configure-jdbc-connection.html#download-jdbc-driver

The idea is that something, somewhere on the Datagrip side, should be able to dynamically refresh these tokens as needed, relying on the AWS system configuration in ~/.aws.

[abrooksv]

@abrooksv we're trying to connect to Postgres databases on RDS (Aurora in our case), following this guide, which does use generate-db-auth-token: docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.Connecting.AWSCLI.PostgreSQL.html

Everything works correctly if you use psql or similar tools. But like @pasali mentions in his original post, if you follow this procedure, your tokens will expire in 15mins and so you need to keep generating them and re-pasting them into Datagrip, which is not ideal.

We did find a driver for Redshift, which seems to do a similar thing to what we'd need done here, for RDS: docs.aws.amazon.com/redshift/latest/mgmt/configure-jdbc-connection.html#download-jdbc-driver

The idea is that something, somewhere on the Datagrip side, should be able to dynamically refresh these tokens as needed, relying on the AWS system configuration in ~/.aws.

Thank you for the details

[beanaroo]

Do you mean you are using source_profile in the CLI credential file?

@abrooksv Alternative to that. Instead of having credentials file set up with profiles and roles, being able to specify role ARN (instead of keys) and having the sts role assumption handled natively for each connection would be ideal.

This is not directly related to IAM authentication for PGSQL, but rather a preliminary step.

Setting up credentials file with relevant profiles and then specifying a profile for each connection is also an alternative approach, but sharing configuration through vcs and having connections with role arns is a bit more convenient.

[mjuszczak]

Are there any known workarounds for this? Using a shell script to update credentials in keychain and/or generate a file on disk prior to connecting?

[casperbiering]

Just an idea (I haven't tried it myself):
https://github.com/magJ/iam-jdbc-driver

[realshadow]

I stumbled upon this when I was migrating our database today from Redshift to Aurora Postgres. Previously we were using the redshift jdbc connector from amazon. And I can confirm that the https://github.com/magJ/iam-jdbc-driver does indeed work

[mjuszczak]

For Postgres RDS, the token expires after 15 minutes. How does the driver
handle that? Is it refreshing tokens behind the scenes?

On Tue, May 5, 2020 at 8:17 AM Lukáš Homza [e-mail] wrote:

I stumbled upon this when I was migrating our database today from Redshift
to Aurora Postgres. Previously we were using the redshift jdbc connector
from AWS. And I can confirm that the
https://github.com/magJ/iam-jdbc-driver does indeed work

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/aws/aws-toolkit-jetbrains/issues/1238#issuecomment-624049170,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/AAROOQ2QN2AXPZEG3CSF4VTRQAGXJANCNFSM4I334GPQ
.

[realshadow]

Just to avoid any confusion - I am using it to connect to database via PhpStorm and Datagrip and did not try anything else with it (and I am using it only for 3 hours at the time of writing)

The driver is just a wrapper around the default postgres driver which has to be loaded as well. It only overrides the connect method so it can generate the token. If the token expires and you run any query it will reconnect you with newly generated token. As far as I can tell from the source code.

This in essence should be the same thing amazons redshift driver does under the hood, because I had to download the driver with bundled AWS SDK to datagrip.

[mjuszczak]

Awesome. That’s exactly what we’d need. How did you get it working in
datagrip?

On Tue, May 5, 2020 at 9:30 AM Lukáš Homza [e-mail] wrote:

Just to avoid any confusion - I am using it to connect to database via
PhpStorm and Datagrip and did not try anything else with it (and I am using
it only for 3 hours at the time of writing)

The driver is just a wrapper around the default postgres driver which has
to be loaded as well. It only overrides the connect method so it can
generate the token. If the token expires and you run any query it will
reconnect you with newly generated token. As far as I can tell from the
source code.

This in essence should be the same thing amazons redshift driver does
under the hood, because I had to download the driver with bundled AWS SDK
to datagrip.

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/aws/aws-toolkit-jetbrains/issues/1238#issuecomment-624090282,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/AAROOQ4KMJPTLR43IFXYQYLRQAPIXANCNFSM4I334GPQ
.

[abrooksv]

Hey, I see a lot of activity here so wanted to give an update from AWS side.

We currently have a limitation in the toolkit that prevents it from being installed into DataGrip. This has to be resolved before we can start any DataGrip features.

The API we need to add the IAM signature generation was also added in DataGrip 2020.1

[realshadow]

@abrooksv Thats good to hear.
@mjuszczak In the meantime if anyone wants to try the mentioned driver I wrote a small gist on how to get it to work in Datagrip (also works for PHPStorm, etc.)

[abrooksv]

@pasali and others,

Is this what you had in mind? Want to make sure we are building the correct thing to support your needs.

[pasali]

This is exactly what i asked for. Thanks for the effort @abrooksv . Do you have ETA on when its going to be ready ?

[abrooksv]

@hunterwerlla is actually doing the development 😄

We don't have an ETA for when it will land in a stable release, development is going on in feature branches right now.

Edit: Oh, forgot to mention. This is using new APIs in DataGrip, so this feature will require 2020.1+

[milpog]

@abrooksv will it work with AWS SSO?

[pasali]

Another thanks for the @hunterwerlla 's effort then :D This is going to make user management a lot easier, i hope it will be ready soon 👍

[abrooksv]

@abrooksv will it work with AWS SSO?

Eventually. SSO support is in a different feature branch, so until they both get merged together into master branch they won't work together. You could merge the branches locally and produce a build yourself though.

[ddadon10]

This look awesome @abrooksv !

[hunterwerlla]

Hello, I made a preview release for people to try: https://github.com/aws/aws-toolkit-jetbrains/releases/tag/datagrip-alpha. It requires a paid JetBrains product 2020.1+, like Datagrip or Intellij Ultimate. Any comments or suggestions are appreciated, especially if you find issues with it working for your setup.

[hunterwerlla]

@pasali we have a preview build to try if you would like to help validate that it works, Thanks!

[MightyCrabKing]

@hunterwerlla We have downloaded and tested the preview build you have provided however we are unable to get the functionality working. We are using Intelij Ultimate 2020.1.2 (June build). We are currently using SSH port forwarding to make the connection to RDS as we don't present the DB over the internet or via a VPN. We get an error when connecting "[28000] FATAL: PAM authentication failed for user "USERNAME". However if we use the iam key generation method the connection works as expected. i.e. this: "aws rds generate-db-auth-token --hostname {db or cluster endpoint} --port 5432 --username {db username}" as detailed in AWS docs https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.html. Obviously keen to help and get this working as this would be great functionality.

[hunterwerlla]

@MightyCrabKing We force SSL on from the action, can you try turning that off/messing with the ssh settings on the connection? Also can you check that the username is actually setup in the DB? Is the url a custom URL?

[pasali]

Hi @hunterwerlla,
Sorry for the late reply. I just tried and i worked like i expected. I was able to connect database and modify tables.
By the way i just realize that we won't be able use custom domains with iam auth because generate-db-auth-token expects db endpoint. I don't know is there anything you can do about it but apart from this, it works like a charm. Thanks for the effort.

[hunterwerlla]

@pasali Actually we should be able to support this by getting the URL it expects back from the service, I'll look into that.

[MightyCrabKing]

@hunterwerlla So we have tried enforcing SSL and also using the RDS CA cert but still receing the same PAM auth error. I think @pasali has identified our issue though, as we are doing local port forwarding to connect to the DB via localhost. I beleive meaning the "Host" is used during the generate-db-auth-token however for us this is set as "localhost" i have tried pointing the full RDS url at localhost via the hosts file but that still doesnt work. Any more ideas?

[hunterwerlla]

@MightyCrabKing I posted an updated build on the release page that should work for you, please try it out. @pasali This should also work with custom domains, please try it out if you have any custom domains.

[pasali]

@hunterwerlla last release is working with custom domains 👍. Thanks!

[MightyCrabKing]

@hunterwerlla Thanks for this, i actually have it working this time. Slight issue in that we are using Aurora postgresql clusters so using the RDS write hostname (e.g. preprod-rds-dbcluster.cluster-crggnw0grmbx.us-west-2.rds.amazonaws.com) results in the "Database identifier" field being populated with "preprod-rds-dbcluster" however this doesnt work and we need to manually enter the current db writer identifier. It would be great if this could be autopopulated as the writer isn't always the same DB identifier.

[hunterwerlla]

@hunterwerlla Thanks for this, i actually have it working this time. Slight issue in that we are using Aurora postgresql clusters so using the RDS write hostname (e.g. preprod-rds-dbcluster.cluster-crggnw0grmbx.us-west-2.rds.amazonaws.com) results in the "Database identifier" field being populated with "preprod-rds-dbcluster" however this doesnt work and we need to manually enter the current db writer identifier. It would be great if this could be autopopulated as the writer isn't always the same DB identifier.

If we listed Aurora DBs in the explorer, we would be able to do this properly on an action. However, when configuring it manually, since the information is only coming from the URL, we only have an approximation.

[MightyCrabKing]

@hunterwerlla Thanks, not a problem and thanks for the feature. Just an FYI as to how we have acheived this locally through scripting so we always pull back the primary/writer:
Pulling back the writer:
aws rds describe-db-cluster-endpoints --db-cluster-identifier $DEPLOYMENT_TYPE-rds-dbcluster --filters "Name=db-cluster-endpoint-type,Values=WRITER" --query "DBClusterEndpoints[].Endpoint" --output text $REGION_FLAG $PROFILE_FLAG
Generating the password for connecting to the writer:
aws rds generate-db-auth-token --hostname=$RDSHOST --port=5432 --username=$RDSUSERNAME --region $RDSREGION --profile $RDSPROFILE

[lejeunen]

@hunterwerlla The plugin is not compatible with 2020.2. Do you plan to post an updated build? Thanks

[hunterwerlla]

@lejeunen Sure, added a 2020.2 build, and am updating the 2020.1 build as well. This is slightly newer code, and there were some updates to the Datagrip specific features.

[lejeunen]

@hunterwerlla Thanks, it works fine on 2020.2. Do you have an idea of when this feature will be released?

[sirocode]

Hi, I am using SSM session manager port forwarding feature and connecting to RDS Mysql instance via localhost:8888 and then via EC2 instance in the private subnet (Amazon Linux 2 machines with xinetd service that "streams" tcp connection to the RDS instance) (I am not using bastion hosts).

Now "Host" and "Ports" fields are in sync with the "URL" field in the data source and I can not get proper RDS IAM creds feature working.

But RDS IAM creds works just fine for me if my RDS instance is publicly available and I can connect to it using "IP:port" (via VPN).

So I am sure that "Host", "Port" and "URL" fields should be independent from each over. Not sure it's a question to the plugins developers. May be it's a question to DataGrip developers.

[hunterwerlla]

@sirocode Hello, host/port and URL being in sync are how DataGrip itself works. However, your use case should still work. What part is breaking down when you try to connect via localhost:8888 ?

[sirocode]

@hunterwerlla Once I have "localhost" in "Host", "8888" in "Port" - I get "jdbc:mysql://localhost:8888/mydb" in the "URL".

Then "Test Connection" button shows "DBInstance localhost not found. (Service: Rds, Status Code: 404, Request: XXXX".

It's all in "General" tab. I am not using ssh.

[kassak]

@hunterwerlla You're parsing url in IamAuth.kt to extract host and then use it to generate token.
That works for ssh tunnels as we rewrite urls later. But here @sirocode is have to specify local end manually.

[hunterwerlla]

@kassak We actually pull the endpoint URL from the service using Instance ID (which is a user provided field):

        // Get the endpoint so that we can get the correct URL and port. If a proxy is used,
        // or ip is used, we need to get the port and address the service expects
        val endpoint = project.awsClient<RdsClient>(awsConnection)
            .describeDBInstances { it.dbInstanceIdentifier(instanceId) }
            .dbInstances()
            .first()
            .endpoint()

@sirocode is the instance ID correct?

[sirocode]

@hunterwerlla Sorry, I don't get you. Where can I find this "Instance ID"?

If I set "URL" to "jdbc:mysql://myrds.xxxxxxxxxx.eu-west-1.rds.amazonaws.com:3306/mydb" the the "General" tab - DataGrip will sync "Host" field to "myrds.xxxxxxxxxx.eu-west-1.rds.amazonaws.com" and "Port" to "3306".

But I need "localhost" in "Host" and "8888" in "Port".

[kassak]

@hunterwerlla Sorry were looking into redshift's code

[sirocode]

I've finally managed to get it working.

Looks a bit strange for me: without RDS FQDN and without port number. Will it work with non-default port number?

[hunterwerlla]

@sirocode Non default port number where in the chain? Also, I think this is good feedback that maybe the Database Identifier field needs more description, would it have been easier for you to get this working if it had a (?) button without mouseover additional instructions along the lines of Database identifier is the database Instance ID of the database you want to connect to. This is required to sign requests properly, and must be manually set if using a proxy?

[sirocode]

My RDS instance has port 3306. But I can not see it on the screen above.
If I run RDS on port 13306 - will this RDS IAM auth work for me?

The "URL" parameter was/is more descriptive, like "jdbc:mysql://myrds.xxxxxxxxxx.eu-west-1.rds.amazonaws.com:3306/mydb" .

The AWS CLI command "aws --profile jpf-nonprod-jetbrains rds generate-db-auth-token --hostname "jetprofile-audt-rds.xxxxx.eu-west-1.rds.amazonaws.com" --port 3306 --username rds_iam_rw" also contains port number.

[hunterwerlla]

@sirocode That's a good point, adding a RDS URL field might be more straightforward for an existing user if they are using a proxy. I'll see if a change in that direction makes sense, thanks for the feedback!

To answer your first question, if you run RDS on any other port, we will still pull the port from the service and it will work as expected.

[abrooksv]

This has been released in version 1.18. If you encounter any issues please open a bug report

[asafpelegcodes]

I don't see the AWS IAM option when I create a new MySQL connection using Intellij 2020.2.3?

Do I need to update anything?

[hunterwerlla]

@asafpelegcodes Hello, if you are experiencing issues, please open another ticket. Please include details about your environment, thanks