How to Secure PostgreSQL Connections
As the size of data grows, it frequently becomes necessary to store it in an actual database. That data may travel across a network or even the internet to reach the database. As such, it’s important to consider whether the connection is encrypted. But how do you tell for sure? Let's take a look using PostgreSQL.
Why is Encryption Important?
When data is sent over a network, especially the internet, it is possible for someone to intercept it. Encryption scrambles the data so that only the sender and receiver can correctly understand it. This ensures that anyone covertly intercepting the data only gets meaningless garbage.
Many protocols and methods exist for encrypting data, but databases and websites typically utilize Transport Layer Security (TLS). TLS is the successor to Secure Sockets Layer (SSL) and provides the encryption any time you access a website beginning with https://. The two names are often used interchangeably but almost always actually refer to the more modern TLS.
It is best practice to assume that internal networks as well as the internet are potentially insecure. Often, hackers do not even need physical access to an internal network to intercept data. Any vulnerable system can leave your network open to the outside world. The well-known breach of Target's internal network and theft of payment information in 2013 was done via a remotely-accessible HVAC system. As this example illustrates, there are simply too many possible weak points to ignore encryption entirely.
Are Database Connections Encrypted?
Many people assume that databases enable or even require TLS by default. Afterall, most websites now default to https, so why would database connections be any less secure?
The PostgreSQL documentation states that native support for TLS is included but disabled by default. In other words, data moving to and from the database is transmitted in plain text and is easily readable if intercepted. Even if the client requests TLS, it won't work until configured.
A quick check with a packet analyzer, a program that intercepts and logs data sent over a network, confirms this. Send a simple PostgreSQL query and view the raw data. Notice how the data is clearly readable in the bottom right column.
How to Enable TLS in PostgreSQL
In order to actually use TLS, we must obtain a certificate. Ideally, a certificate authority would issue this, but a self-signed certificate is adequate for testing and non-internet use.
To create a self-signed certificate, run the following OpenSSL commands in the Windows command prompt:
openssl req -new -text -out server.req
openssl rsa -in privkey.pem -out server.key
rm privkey.pem
openssl req -x509 -in server.req -text -key server.key -out server.crt
This will create server.key and server.crt files in your current directory. Move these files to the PostgreSQL data directory (C:\Program Files\PostgreSQL\12\data on Windows).
To use the certificate, we need to enable TLS in PostgreSQL itself. This is done in the main configuration file postgresql.conf, which can be found in the PostgreSQL data directory. It is only necessary to change the first option in the SSL section to "on", but you can also set the key and certificate names if desired.
After changing the configuration file, save it and be sure to restart PostgreSQL.
To check your work, you can run a query again and see if you can intercept it. Notice how the data is now completely garbled.
Now the data is encrypted and completely unreadable to someone intercepting it.
Requiring Encryption
The steps above enable TLS encryption in PostgreSQL, but clients can still choose to connect without it. To truly be secure, the server should mandate that clients connect securely and refuse unencrypted connections. Note that many clients do not actually provide an explicit TLS option—they simply use whatever method the server allows or mandates.
This can be done by editing pg_hba.conf, which can be found in the data directory. Simply change the word "host" to "hostssl" for all connections.
Once this is done, save the file and restart PostgreSQL. Now all connections to your database must be secure.
Conclusion
Enabling and requiring encryption for PostgreSQL connections is an easy way to improve data security. You never really know if networks are secure from the outset, so it's always best to proactively take steps to keep valuable data safe.
Post by Andy Kerfonta, Senior Software Engineer at WISER Systems, Inc. Andy works primarily with C++ and Qt. He is a veteran of multiple software startups and enjoys running, woodworking, and dabbling in AI in his spare time.