I hate postgres ssl configuration

Community
1

I really like that neon.tech’s servers are secured with let’s encrypt certs and that the cert chain including the root cert is sent down when I connect. I really hate configuring these postgres clients though.

$ psql -hyoung-bonus-431397.cloud.neon.tech sslmode=verify-full
psql: error: connection to server at "young-bonus-431397.cloud.neon.tech" (52.35.3.79), port 5432 failed: root certificate file "/home/sixcorners/.postgresql/root.crt" does not exist
Either provide the file or change sslmode to disable server certificate verification.

I mean… Why does it want the cert stored there? Why doesn’t it just use the OS’s cert store?
That’s what you can configure the jdbc driver to do by changing the sslfactory:

jdbc:postgresql://young-bonus-431397.cloud.neon.tech/main?sslmode=verify-full&sslfactory=org.postgresql.ssl.DefaultJavaSSLFactory

but this seems like such a long length to go to just get a secure connection going.
I really wish there was a ?secure=true or something that could just set everything up correctly. I know a lot of people use certs that aren’t signed by a public CA. In that case make it take a thumbprint or something like: ?secure=da39a3ee5e6b4b0d3255bfef95601890afd80709. I don’t want to have to tweak files in my docker image to get something connected…

Does anyone else feel the same way?

As an aside do you all think it would be a good idea if the sample jdbc url in the dashboard included the sslmode and sslfactory parameters I mentioned?

3 Likes
2

I might be wrong but I’m thinking you should be complaining on the Postgres issue tracker.

3

I’ve talked on a postgresql slack. They said it’s not possible to poison dns servers that run on ec2 instances. They said using a thumbprint instead of a file would be insecure. :roll_eyes: They also seem to look down on people using postgres servers that are exposed over the internet.

Also that didn’t really answer the question. Do you feel the same way? Do you think it would be a good idea to add those two parameters to the sample code generated on the dashboard?

As an aside what is the right way to use neon securely with the psql command line? The advice I see seems to suggest to set sslmode to required which isn’t secure. I kind of doubt everyone knows to put isrg’s x1 cert into that weird location. I think more needs to be said somewhere about connecting to neon securely. It kind of feels like the blind leading the blind wrt secure database connections.

1 Like
4

Yes, psql client doesn’t know where to look for os-level certificates. On macOS to use system certificates:

PGSSLROOTCERT=/etc/ssl/cert.pem psql 'postgres://stas@ep-divine-wave-432123.us-east-2.aws.neon.tech/neondb?sslmode=verify-full'

For other operation systems:

MacOS / FreeBSD / OpenBSD
  => /etc/ssl/cert.pem

Debian / Ubuntu / Gentoo / Arch / Slackware
  => /etc/ssl/certs/ca-certificates.crt

RedHat / Fedora / CentOS / Mageia / Vercel / Netlify
  => /etc/pki/tls/certs/ca-bundle.crt

Alpine
  => /etc/ssl/cert.pem

OpenSUSE
  => /etc/ssl/ca-bundle.pem

(that list is maintained on Connecting to PlanetScale securely - PlanetScale Documentation for mysql connection)

3 Likes
5

Here is an example for connecting on windows from a powershell prompt.

set-alias -name psql -value 'C:\Program Files\PostgreSQL\15\bin\psql'
psql postgres://user@host/neondb sslmode=verify-full

I also hate windows and the way it manages its PATH.

I’m mostly posting this here for the benefit of future me.