Search code examples
postgresqlssljdbcx509certificateclient-certificates

postgres jdbc client cert user vs db user

Backstory:

I've gotten jdbc to connect to postgres using a client cert. In java I set the user in the properties, and the driver looks it up in the keystore and sends it along. All was good.

But I just found out that I won't be getting certs with a CN of pg-user. The certs I'll be getting will have a CN of pg-user.XYZ.foo.com & pg-user.ABC.foo.com. This looks like a job for username maps. Hey they even have regexp, it'll be perfect.

I got unix user root logging in to postgres as pg-user using a username map and local ident authentication using psql -d db -U pg-user. But in that case postgres knows BOTH that the user is root, AND is trying to log in as pg-user.

Problem:

What I can't figure out is how to tell the postgres jdbc driver to grab the cert from the keystore with a CN of pg-user.XYZ.foo.com, but present to postgres as user pg-user. It appears to be the single argument of user that controls both. Does anyone know how to do this?

This page includes a list of the connection options, but it doesn't seem to offer a way to split the user names. The closest I'm seeing is the option to write my own sslfactory, and I'm really hoping to avoid that...

Solution

  • Thanks to @harmic's comment I was able to solve this.

    Starting with to following three files:

    • pg-user.pem which has a CN of pg-user.XYZ.foo.com
    • pg-user-chain.pem which contains the chain certs
    • pg-user.private_key which contains the private key for the cert

    Then I created the pkcs12 file like this:

    cat pg-user.pem pg-user-chain.pem > cert-and-chain.pem
openssl pkcs12 -export -out ssl_cert.p12 -in cert-and-chain.pem -name pg-user -inkey private_key.pem -passout pass:{password here}

    After that I declared the user property for the connection to be pg-user, and it worked. In order to test, I altered the regexp in pg_ident to not match, then I could no longer log in, I changed it back and I could.