The psql client accepts a new connection string option in Postgres 16: ?sslrootcert=system. This new option makes it simpler than ever to connect a secure interactive session to your Neon database:

You can use psql version 16 with this new option even if your Neon database is still on Postgres 14 or 15.

Read on to discover why this is important, especially if you’ve previously connected to Postgres using ?sslmode=require, which isn’t secure.

Securing connections in general

What does it mean for a connection to a server to be secure? Two things are required:

1. Encryption, which makes your connection safe from eavesdropping and potential takeover.
2. Authentication, which ensures the server you connect to is the one you meant to connect to.

When you access a website over https — which mostly, these days, you do — you get both of these things. 

For example, when you loaded this blog post from https://neon.tech, your browser encrypted the request with TLS. It checked that the certificate presented by the server was current; that it said ‘neon.tech’ on it; and that it was signed by a certificate authority (CA) it trusts. 

Your browser trusts the CA because the CA has promised only to provide a signed certificate that says ‘neon.tech’ on it to someone who can prove that they control ‘neon.tech’.

I know this because I made a page where you can see it all happen all in real-time.

This security model — public key infrastructure with a large set of widely trusted CAs — isn’t perfect. But we might regard it as the bare minimum level of security we’d want for the data in our databases, much of which will be more valuable and more sensitive than a blog post.

Securing connections to Postgres

Unfortunately, even this minimum level of security is not what you get by default when you use psql to connect to Postgres.

psql, reflecting the libpq library it’s built on, provides six different sslmode options. These are: disable, allow, prefer, require, verify-ca and verify-full. The default is sslmode=prefer, which doesn’t guarantee either encryption or authentication.

To quote the Postgres docs, sslmode=prefer means: “I don’t care about encryption, but I wish to pay the overhead of encryption if the server supports it”. To quote the same docs again: “this makes no sense from a security point of view”.

Commonly specified in preference to sslmode=prefer is sslmode=require. It is, for example, what Heroku has been recommending for years. And if you’re not an expert in Postgres security, it certainly sounds plausible that sslmode=require might do the right thing.

When you use sslmode=require you do, technically, get encryption: psql encrypts your connection to the server with TLS. And as part of this process, the server presents psql with a certificate. 

But this certificate does not get you authentication. Because, perhaps a little surprisingly, sslmode=require instructs psql not to care either whether the certificate has the server’s name on it or whether it was signed by any particular CA. Just about any certificate, self-signed by anyone, with any server’s name on it, will do.

This is a bad problem, because a connection that’s not authenticated is vulnerable to a pretty straightforward meddler-in-the-middle (MITM) attack that gives full access to your database.

Some years ago I was involved in a startup whose main API and database were hosted with a well-known platform-as-a-service (PaaS). The security issues with sslmode=require are nasty enough that they caused me to migrate us off this PaaS very shortly after confirming, a little incredulously, that sslmode=require was the best security this PaaS could provide.

What’s all the fuss about authentication?

The problem is this. When you connect to your database, psql issues a DNS query to turn the server’s domain name into the server’s IP address. But a bad actor can use DNS spoofing or cache poisoning to have some other IP address returned by the DNS query. This other IP address, of course, belongs to a machine running the bad actor’s software.

On receiving a spoofed DNS response containing the bad actor’s IP address, psql goes ahead and connects to the bad actor’s machine. Next, it negotiates TLS encryption, but without checking the certificate the bad actor’s server presents (as discussed, sslmode=require means the client doesn’t authenticate the server). Then it sends a startup message, and waits for the bad actor’s server to ask it to authenticate as a client.

Postgres has several methods for authenticating a client using a password. But you can be pretty sure the bad actor’s machine will simply ask psql to hand over its password in cleartext, and psql will cheerfully oblige. 

The bad actor now has free access to your database. They might begin by running, say, pg_dumpall, or DROP DATABASE main.

If the bad actor did run DROP DATABASE main, you might be happy to hear about Neon’s point-in-time restore functionality, but that’s not really the point here.

Perhaps you’re now saying: “But a-ha! My Postgres server uses SCRAM-SHA-256 to authenticate me as a user. This is a challenge-response scheme, so my password never gets sent over the network. Therefore the worst that can happen is that the bad actor gets to forward on and observe this particular Postgres session”.

Well, it’s bad news, I’m afraid. Neon uses SCRAM-SHA-256 too, but SCRAM-SHA-256 will only help you if you remembered to specify require_auth=scram-sha-256 for your connection. Since the require_auth option is also new as of Postgres 16, and not yet widely trailed, that seems pretty unlikely. 

If you didn’t specify a require_auth method, then psql lets the server call the shots. So it doesn’t matter that your database wouldn’t have asked for a cleartext password. If the bad actor’s machine asks for a cleartext password, that’s what psql will give it, and BOOM: game over.

Given the right circumstances, DNS-spoofing MITM attacks of this sort really aren’t very difficult. They’re easily within reach of a competent command-line user on the same local network.

To check that this claim stands up, I tried mounting a DNS spoofing attack on myself. I’m not a networking or security expert, and it took me less than an hour — including the necessary research into network tools like bettercap — to make a domain of my choice resolve to an IP address of my choice on a PC elsewhere on my home network.

Spurred on by this success, I decided to try the actual MITM attack too. I spent about an hour writing a short Ruby script using the openssl gem. This poses as a TLS-enabled Postgres server in order to request the client’s cleartext password. I spent roughly a further hour making the script then connect to the real server and proxy all subsequent communication, so that the MITM victim sees nothing out of the ordinary. The final script is under 100 lines of code.

I generated a self-signed certificate, ran the script, and arranged for the target machine to resolve *.eu-central-1.aws.neon.tech to the IP address of the machine the script was running on.

On the spoofing target machine, connecting to and querying my database with sslmode=require appeared to work perfectly normally. But, over on the attacker machine, bettercap had logged the following:

192.168.1.0/24 > 192.168.1.188 » [10:54:01] [sys.log] [inf] dns.spoof sending spoofed DNS reply for ep-long-grass-595339.eu-central-1.aws.neon.tech (->192.168.1.119) to 192.168.1.88 : d8:cb:8a:22:59:bf (Micro-Star INTL CO., LTD.) - GM-WIN10.broadband.
 

And my short Ruby script had picked up the rest:

The log continues by displaying every byte transmitted between client and server. All in all, this was worryingly straightforward, and only a short morning’s work.

Doing it right

The solution to all this nastiness, as you probably guessed, is to ensure that your psql connection uses both encryption and authentication.

Before version 16, that meant (a) providing psql with one or more trusted CA certificates for it to verify against using the sslrootcert option (or a file at ~/.postgresql/root.crt), and (b) telling it to actually check it, using sslmode=verify-full.

To make that work with Neon from a Mac looks something like this:

On Linux distributions, you alter the sslrootcert location: see our docs. On Windows, you download a root cert bundle and point sslrootcert to that.

If you’re using a psql version below 16, that’s how you should connect. It’s fine, but it’s pretty verbose and not so easy to remember (where are those SSL certs again?).

The new sslrootcert=system option in psql 16 provides a nice, easy shortcut for this behavior. Rather than you having to provide psql with trusted CA certificates, sslrootcert=system instructs psql both to use the trusted CA certificates built into your OS as root certs and to do proper authentication via sslmode=verify-full.

As a reminder, on all platforms, it looks like this:

As expected, this breaks my proof-of-concept MITM attack. Using psql with sslmode=verify-full from my DNS-spoofing target machine gets me:

psql: error: connection to server at "ep-long-grass-595339.eu-central-1.aws.neon.tech" (192.168.1.119), port 5432 failed: SSL error: certificate verify failed

Note: at the time of writing there appears to be a problem with the EnterpriseDB Postgres 16 builds for Windows, such that using sslrootcert=system always results in the message SSL error: unregistered scheme. I’ve reported this: let’s hope it’s fixed soon.

To insure yourself against errors or omissions, it’s probably also a good idea to change the insecure libpq defaults by setting the following values in .bash_profile, .zprofile, or wherever you define your environment variables:

Then the only way you’ll get a connection that’s vulnerable to MITM attacks is if you specifically ask for one.

Lastly, if you’re connecting to Postgres using other clients or libraries — e.g. from a web server or API — make sure you know how to specify at least an equivalent level of security to sslrootcert=system. For example, in JavaScript, I’m pleased to report that the node-postgres TLS implementation defaults to sslrootcert=system behavior unless you explicitly specify the option rejectUnauthorized=false.