Support embedded username and password in JDBC URLs

[Kauko]

Slack thread

clojure.java.jdbc supported a format for JDBC URLs where the user and password are embedded into the URL, like this:

jdbc:postgresql://bobby:password123@127.0.0.1:1234/my-db

Currently next.jdbc only supports the following format:

jdbc:postgresql://127.0.0.1:1234/my-db?user=bobby&password=password123

The format is listed here, but it's not listed in the jdbc docs, so it's not clear cut if this should be supported or not.

Finally, code samples of using connection strings without user+password, embedded into the string, and as parameters:

;; Missing user+password results in a failed login
(clojure.java.jdbc/get-connection "jdbc:postgresql://127.0.0.1:1234/my-db")
Execution error (PSQLException) at org.postgresql.core.v3.AuthenticationPluginManager/lambda$withEncodedPassword$0 (AuthenticationPluginManager.java:110).
The server requested password-based authentication, but no password was provided by plugin null

;; c.j.j can read the embedded details
(clojure.java.jdbc/get-connection "jdbc:postgresql://bobby:password123@127.0.0.1:1234/my-db")
=> #object[org.postgresql.jdbc.PgConnection 0x3eb958cd "org.postgresql.jdbc.PgConnection@3eb958cd"]

;; c.j.j also understands the parameters
(clojure.java.jdbc/get-connection "jdbc:postgresql://127.0.0.1:1234/my-db?user=bobby&password=password123")
=> #object[org.postgresql.jdbc.PgConnection 0xea7a221 "org.postgresql.jdbc.PgConnection@ea7a221"]

;; Missing user+password results in a failed login
(next.jdbc/get-connection "jdbc:postgresql://127.0.0.1:1234/my-db")
Execution error (PSQLException) at org.postgresql.core.v3.AuthenticationPluginManager/lambda$withEncodedPassword$0 (AuthenticationPluginManager.java:110).
The server requested password-based authentication, but no password was provided by plugin null

;; next.jdbc does not parse the embedded details
(next.jdbc/get-connection "jdbc:postgresql://bobby:password123@127.0.0.1:1234/my-db")
Execution error (UnknownHostException) at sun.nio.ch.NioSocketImpl/connect (NioSocketImpl.java:567).
docker:docker@127.0.0.1

;; next.jdbc only understands the parameters
(next.jdbc/get-connection "jdbc:postgresql://127.0.0.1:1234/my-db?user=bobby&password=password123")
=> #object[org.postgresql.jdbc.PgConnection 0x786c7b26 "org.postgresql.jdbc.PgConnection@786c7b26"]

[seancorfield]

I asked on Slack, but I'll ask here for the record:

For production use, where you use HikariCP (or whatever connection pool), are you using a JDBC URL with embedded user:password@host? I'm curious whether that is accepted (the docs aren't very clear for HikariCP and I don't feel like trawling through the source).

My initial feeling with this is that I could wrap the existing DriverManager/getConnection call in a try/catch and if an exception occurs and it looks like the URL contains credentials, next.jdbc could try to convert the URL to java.net.URI and pick it apart and retry a URL without user:password but with them added to the properties -- which is what JDBC actually supports.

It just feels like a lot of work in next.jdbc for what appears to be edge case support for an inadvisable feature in clojure.java.jdbc -- I think this is the first time anyone has raised the user:password@host format?

[Kauko]

It looks like the services that use a JDBC URL parse the string into a map that is passed to HikariCP.

(defn- parse-db-uri
  "Returns the database configuration as a map."
  [db-uri]
  (let [uri (URI. db-uri)
        user-info (str/split (.getUserInfo uri) #":")]
    {:adapter (.getScheme uri)
     :server-name (.getHost uri)
     :port-number (.getPort uri)
     :database-name (subs (.getPath uri) 1)
     :username (first user-info)
     :password (second user-info)}))

[seancorfield]

next.jdbc depends on java.data so this is a note about destructuring a URI:

user=> (java.net.URI. "mysql://foo:bar@quux:1234/wibble/wobble?a=1&b=2")
#object[java.net.URI 0x6dd1c3ed "mysql://foo:bar@quux:1234/wibble/wobble?a=1&b=2"]
user=> (d/from-java *1)
{:path "/wibble/wobble", :rawQuery "a=1&b=2", :fragment nil, :authority "foo:bar@quux:1234", 
 :rawAuthority "foo:bar@quux:1234", :port 1234, :absolute true, :host "quux", :rawPath "/wibble/wobble", 
 :opaque false, :rawSchemeSpecificPart "//foo:bar@quux:1234/wibble/wobble?a=1&b=2",
 :rawUserInfo "foo:bar", :query "a=1&b=2", :rawFragment nil, :scheme "mysql", :userInfo "foo:bar", 
 :schemeSpecificPart "//foo:bar@quux:1234/wibble/wobble?a=1&b=2"}
user=>

The mapping is:

• path -> dbname
• port
• host
• query -> properties (tricky due to encodings)
• scheme -> dbtype
• userInfo -> split : -> user & password

To reconstitute, it's:

• jdbc:
• scheme
• ://
• host
• :
• port
• path
• ?
• query
• (&) user=user&password=password

[seancorfield]

I believe this will solve your problem but it was hard to do this in a way that would work in a portable manner across multiple databases so I do not plan to document this as a "supported" feature...