Can we make RDS IAM usage easier?

[seancorfield]

Background reading:

• https://blog.jdriven.com/2021/06/configure-hikari-connection-pool-when-using-rds-iam/
• https://kwill.dev/posts/hikari-rds-iam-auth/

[brookeswanson]

Some of the aws docs that have been frequented while getting this working:

• Explicitly calls out requirement for using SSL with IAM authentication: https://aws.amazon.com/premiumsupport/knowledge-center/users-connect-rds-iam/#:%7E:text=IAM%20database%20authentication%20requires%20a,credentials%20to%20access%20the%20database
• AWS certificate bundles: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.SSL.html
• V2 aws-java-sdk rds auth token example (the clojure hikari post uses v1): https://github.com/awsdocs/aws-doc-sdk-examples/blob/main/javav2/example_code/rds/src/main/java/com/example/rds/GenerateRDSAuthToken.java

[devn]

It turns out we did not have forced SSL on the RDS DB we were testing with, so SSL turned out to not be an issue (for now). Forced SSL scenarios should be considered/tested.

For now, something close to https://kwill.dev/posts/hikari-rds-iam-auth/ worked. The sketch of our solution looks more like the following:

(defn fetch-iam-token [db-spec]
  ;; ...does the dance with AWS to grab a token using our db-spec...)

(defn get-connection [db-spec]
    (jdbc/get-connection (assoc db-spec :password (fetch-iam-token db-spec))))

(def hikari-config
  {:max-lifetime (.toMillis (Duration/ofMinutes 14))
    ...})

(defn mk-datasource [db-spec]
  (let [login-timeout (atom nil)
        datasource (reify DataSource
                     (getConnection [_]
                       (get-connection db-spec))
                     (getConnection [_ _user _password])
                     (getLoginTimeout [_]
                       (or @login-timeout 0))
                     (setLoginTimeout [_ seconds]
                       (reset! login-timeout seconds)))]
    (hikari/make-datasource (assoc hikari-config
                                   :datasource datasource))))

It is worth noting that :max-lifetime on the pool configuration correctly refreshes the underlying credentials at the appropriate interval.

[viesti]

I ran into here while looking for something else (insert-multi! fails with empty rows), but AWS has this JDBC wrapper nowadays, which has a plugin for IAM auth (and plugins for other stuff too): https://github.com/awslabs/aws-advanced-jdbc-wrapper, and I have been using that for IAM auth in my current project for few months now, with Hikari.

I have deps like these, the important part are the software.amazon.jdbc/aws-advanced-jdbc-wrapper and software.amazon.awssdk/rds, but included the db part anyway here:

         ;; PostgreSQL database deps
         org.postgresql/postgresql {:mvn/version "42.5.2"}
         com.github.seancorfield/next.jdbc {:mvn/version "1.3.847"}
         com.layerware/hugsql-core {:mvn/version "0.5.3"}
         com.layerware/hugsql-adapter-next-jdbc {:mvn/version "0.5.3"}
         ;; Hikari moved to slf4j 2.x, but timbre adapter hasn't yet caught up so trying to deal with that via not pulling the new dep
         ;; See https://github.com/fzakaria/slf4j-timbre/issues/45
         com.zaxxer/HikariCP {:mvn/version "5.0.1" :exclusions [org.slf4j/slf4j-api]}
         camel-snake-kebab/camel-snake-kebab {:mvn/version "0.4.3"}

         software.amazon.jdbc/aws-advanced-jdbc-wrapper {:mvn/version "2.1.2"}
         software.amazon.awssdk/rds {:mvn/version "2.20.69"}

Then I have code to create the datasource like this:

(defn configure-aws-wrapper-datasource [hikari config host]
  (let [targetDataSourceProps (doto (Properties.)
                                (.setProperty "serverName" host) ;; This is the host to connect to
                                (.setProperty "wrapperPlugins" "iam")
                                (.setProperty "iamHost" (:host config)) ;; This is the hostname used in the password signature
                                (.setProperty "portNumber" (:port config))
                                (.setProperty "user" (:user config))
                                (.setProperty "databaseName" (:database-name config)))]
    (when-let [read-only (:read-only config)]
      (log/info "Configuring read only mode")
      (.setReadOnly hikari read-only)
      (.setProperty targetDataSourceProps "readOnlyMode" "always"))
    (doto hikari
      (.setDataSourceClassName (.getName AwsWrapperDataSource))
      (.addDataSourceProperty "jdbcProtocol" "jdbc:postgresql:")
      (.addDataSourceProperty "serverPropertyName" "serverName")
      (.addDataSourceProperty "databasePropertyName" "databaseName")
      (.addDataSourceProperty "portPropertyName" "port")
      (.addDataSourceProperty "targetDataSourceClassName" "org.postgresql.ds.PGSimpleDataSource")
      (.addDataSourceProperty "targetDataSourceProperties" targetDataSourceProps))))

(defn make-datasource [{:keys [connection-type] :as config}]
  (let [hikari (doto (HikariDataSource.)
                 (.setPoolName "db")
                 (.setMaximumPoolSize 5)
                 (.setConnectionTimeout 60000)
                 ;; To allow starting without a valid database connection
                 ;; Useful in Lambda Snapstart, where the process is initialized for creating checkpoint only
                 (.setInitializationFailTimeout -1))
        hikari (case connection-type
                 :rds (configure-aws-wrapper-datasource hikari config (:host config))
                 :rds-tunnel (configure-aws-wrapper-datasource hikari config (:tunnel-host config))
                 :local-postgres (doto hikari
                                   (.setDataSourceClassName "org.postgresql.ds.PGSimpleDataSource")
                                   (.addDataSourceProperty "serverName" (:host config))
                                   (.addDataSourceProperty "portNumber" (:port config))
                                   (.setUsername (:user config))
                                   (.setPassword (:password config))
                                   (.addDataSourceProperty "databaseName" (:database-name config)))
                 (throw (ex-info "Unknown connection type" {:connection-type connection-type})))]
    hikari))

The code above has some options for using local postgresql, which we use when developing locally, and also using RDS over a tunnel (which we do via SSM agent forwarding over a bastion sitting in a private subnet), where when using IAM, the hostname needs to be different to the host that we are making a TCP connection to, in order to make a signature with the RDS hostname, since IAM authentication, is, IIRC, basically a password that is a mocked HTTP request signed with the AWS auth signature v4.

If I understood this right (I did some digging into what the password that IAM hands out looks like), I guess one could make a Clojure lib that crafts a signature that is used as a password, since that doesn't require a network call, only requires the secrets (access key id, secret access key and session token) and crafting the signature. But the AWS JDBC wrapper might have other use, maybe, so I figured out to just go use it :)

[seancorfield]

Given the code examples from @devn and @viesti I'm going to declare this out of scope for next.jdbc and hopefully this issue will be discoverable via search for others trying to use this library with AWS and IAM.