CYBERTEC PostgreSQL Logo

Setting up SSL authentication for PostgreSQL

03.2021 / Category: / Tags: |

PostgreSQL is a secure database and we want to keep it that way. It makes sense, then, to consider SSL to encrypt the connection between client and server. This posting will help you to set up SSL authentication for PostgreSQL properly, and hopefully also to understand some background information to make your database more secure.

SSL authetication with PostgreSQL

At the end of this post, you should be able to configure PostgreSQL and handle secure client server connections in the easiest way possible.

Configuring PostgreSQL for OpenSSL

The first thing we have to do to set up OpenSSL is to change postgresql.conf. There are a couple of parameters which are related to encryption:

Once ssl = on, the server will negotiate SSL connections in case they are possible. The remaining parameters define the location of key files and the strength of the ciphers. Please note that turning SSL on does not require a database restart. The variable can be set with a plain reload. However, you will still need a restart, otherwise PostgreSQL will not accept SSL connections. This is an important point which frequently causes problems for users:

The SHOW command is an easy way to make sure that the setting has indeed been changed. Technically, pg_reload_conf() is not needed at this stage. It is necessary to restart later anyway. We just reloaded to show the effect on the variable.

In the next step, we have to adjust pg_hba.conf to ensure that PostgreSQL will handle our connections in a secure way:

Then restart the database instance to make sure SSL is enabled.

The next thing you have to do is to create certificates.

In order to keep things simple, we will simply create self-signed certificates here. However, it is of course also possible with other certificates are. Here is how it works:

This certificate will be valid for 365 days.

Set permissions

Next we have to set permissions to ensure the certificate can be used. If those permissions are too relaxed, the server will not accept the certificate:

Self-signed certificates are nice. However, to create a server certificate whose identity and origin can be validated by clients, first create a certificate signing request and a public/private key file:

Again, we have to make sure that those permissions are exactly the way they should be:

Then we sign the request.

To do that with OpenSSL, we first have to find out where openssl.cnf can be found. We have seen that it is not always in the same place - so make sure you are using the right path:

We use this path when we sign the request:

Let’s create the certificate with the new root authority:

server.crt and server.key should be stored on the server in your data directory as configured on postgresql.conf.

But there's more: root.crt should be stored on the client, so the client can verify that the server's certificate was signed by the certification authority. root.key should be stored offline for use in creating future certificates.

The following files are needed:

File name Purpose of the file Remarks
ssl_cert_file ($PGDATA/server.crt) server certificate sent to client to indicate server's identity
ssl_key_file ($PGDATA/server.key) server private key proves server certificate was sent by the owner; does not indicate certificate owner is trustworthy
ssl_ca_file trusted certificate authorities checks that client certificate is signed by a trusted certificate authority
ssl_crl_file certificates revoked by certificate authorities client certificate must not be on this list

Checking your setup

Now that all the certificates are in place it is time to restart the servers:

Without a restart, the connection would fail with an error message (“psql: error: FATAL: no pg_hba.conf entry for host "10.0.3.200", user "postgres", database "test", SSL off”).

However, after the restart, the process should work as expected:

psql indicates that the connection is encrypted. To figure out if the connection is indeed encrypted, we need to check the content of pg_stat_ssl:

Let us query the system view and see what it contains:

The connection has been successfully encrypted. If “ssl = true”, then we have succeeded.

Different levels of SSL supported by PostgreSQL

Two SSL setups are not necessarily identical. There are various levels which allow you to control the desired level of security and protection. The following table outlines those SSL modes as supported by PostgreSQL:


sslmode

Eavesdropping protection

MITM (= man in the middle) protection

Statement
disable No No No SSL, no encryption and thus no overhead.
allow Maybe No The client attempts an unencrypted connection, but uses an encrypted connection if the server insists.
prefer Maybe No The reverse of the “allow” mode: the client attempts an encrypted connection, but uses an unencrypted connection if the server insists.
require Yes No Data should be encrypted and the overhead of doing so is accepted. The network is trusted and will send me to the desired server.
verify-ca Yes Depends on CA policy Data must be encrypted. Systems must be doubly sure that the connection to the right server is established.
verify-full Yes Yes Strongest protection possible. Full encryption and full validation of the desired target server.

 

The overhead really depends on the mode you are using. First let’s take a look at the general mechanism:

 SSL authentication with postgresql

The main question now is: How does one specify the mode to be used? The answer is: It has to be hidden as part of the connect string as shown in the next example:

In this case, verify-ca does not work because to do that the root.* files have to be copied to the client, and the certificates have to be ones which allow for proper validation of the target server.

Encrypting your entire server: PostgreSQL TDE

So far, you have learned how to encrypt the connection between client and server. However, sometimes it is necessary to encrypt the entire server, including storage. PostgreSQL TDE does exactly that:

Transparent Data Encryption of PostgreSQL

To find out more, check out our website about PostgreSQL TDE. We offer a fully encrypted stack to help you achieve maximum security. PostgreSQL TDE is available for free (Open Source).

What might also interest you ...

More recent information about encryption keys: Manage Encryption Keys with PostgreSQL TDE

Materialized views are an important feature in most databases, including PostgreSQL. They can help to speed up large calculations, or at least to cache them.

If you want to make sure that your materialized views are up-to-date, and if you want to read more about PostgreSQL right now, check out our blog about pg_timetable, which shows you how to schedule jobs in PostgreSQL. Why is pg_timetable so useful? Our scheduler makes sure that identical jobs cannot overlap, but simply don’t execute again in case the same job is already running. In the case of long jobs, using a scheduler is super important - especially if you want to use materialized views.
 


In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.

7 responses to “Setting up SSL authentication for PostgreSQL”

  1. Thank you very much for sharing this sir. I have been trawling the web with no success trying to find this information. Whats worse, information on how to perform SSL authentication on official POSTGRESQL website is sparse and very poor

  2. This has helped me quite a lot. However I am not sure what 'root' refers to. If I have multiple clients (developers and applications). Can I use the same root.crt for all of them, or do I need to use different certificates for each of them (e.g. username.crt, developer.crt, or application.crt)?

    Additionally how do I use the root.crt in a db-uri connection string? Thanks a lot!

    • Hey Michael,

      first, you need to decide what you want to accomplish:
      1) do you want TLS only?
      2) do you want 1) and your clients to verify the authenticity of the server?
      3) do you want 2) and your server to verify the authenticity of the clients?

      In the first case, it would suffice to provide the server with a certificate and key, and tell the clients to use sslmode=require. That way the traffic will be encrypted.

      In the second case, you'd need to give the clients the certificate, so that they can verify the authenticity of the server. If you have a certificate from a third party, e.g. LetsEncrypt, you'll need to make sure that the clients can verify the whole chains, from the root certificate of LetsEncrypt to your server's certificate.

      In the third case, you should have different certificates and keys, ideally one for each client, so that no client can impersonate the server or other clients. If you give somebody/something else the private key for your server, you'll throw any authenticity guarantees for your server out of the window.

      To clarify the terms a little better:
      The "root" refers to a certificate (and key) that is used either on its own or that was used to sign off any "child" certificates. In that case, the root is usually called CA (certificate authority). If you want to verify any child certificates you'll need to check the CA certificate as well.
      A common place where the root certificate is used on its own is in SSH key authentication. For (web)servers and clients it is rather uncommon to have them use the root certificate. Usually you'll buy or generate a child certificate (and key) that can be given to your servers or clients, while the root key is kept secret. This allows you to exchange the certificates and keys of servers or clients, without needing to give the parties on the other end a new certificate to verify against. They can continue verifying against the CA certificate.

      Hope this clears some things up!
      Julian

  3. 1. is it also possible to access a postgres-server that has ssl=on set up from a client without ssl?
    2. @julianmarkwort:disqus in regards to 1) sslmode=require is set in postgres.conf of the client? I never came accros such a setting. that's why I am asking

      • Hey, ssl=on is a configurable for the server, it just enables the server to _additionally_ serve SSL encrypted connections, but you can still use normal connections.
        Any restrictions in being able to use only SSL are tied to whatever is written in the pg_hba.conf 🙂 .

        sslmode=require is an option that you can pass in the connection string when initiating a connection using a libpq based client (e.g. `psql -d 'dbname=mydb host=myhost sslmode=require' ), see: https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS

Leave a Reply

Your email address will not be published. Required fields are marked *

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram