Security

Certificate Authentication Recipe for PostgreSQL Docker Containers

Avatar for Jonathan S. KatzJonathan S. Katz

Jonathan S. Katz

11 min read

One of the reasons that PostgreSQL supports many authentication methods is to help ensure that it can work with multiple external identity management providers. While a lot of people are familiar with having PostgreSQL request a password for logging in, there are other ways to facilitate the management of user authentication depending on your deployment requirements.

One method that can be used in larger enterprise environments is using certificates to authenticate between a PostgreSQL client (or user) and the server. Using certificates for authentication can have a lot of advantages, as they can provide a way to verify a user without requiring a password! In other words, you can avoid some of the challenges of having to secure shared passwords that are distributed across your infrastructure.

This articles on getting your own certificate-based authentication system set up in a PostgreSQL container with OpenSSL by going through the steps of setting up such a configuration and then exploring the extra variables you need to set in order to authenticate. If you're interested in setting up a production-level system, my colleague Stephen Frost gave an excellent talk at PGCon 2019 on how to set up an enterprise certificate-based authentication system (while also spending significant time on authenticating to PostgreSQL via the GSS-API and Kerberos),

tl;dr: What's the recipe?

Below is the recipe for certificate authentication with PostgreSQL in a a container:

Wait, I want to understand the recipe!

I was hoping you'd ask!

Setting up certificate-based authentication in a PostgreSQL container boils down to five steps:

  1. Creating the root certificate authority (CA)
  2. Generating the PostgreSQL server key and certificate that it will use
  3. Configuring the PostgreSQL server to enable TLS (SSL) connections and use certificate-based authentication
  4. Generate the PostgreSQL client key and certificate that you will use to connect
  5. Connect!

As part of this process, you must ensure that you secure all of your keys and certificates. In fact, if PostgreSQL detects that the permissions for any of its TLS components are not secure enough, it will not enable TLS connections. Additionally, if the settings on the client side are insecure as well, you will be unable to connect to the PostgreSQL server!

NOTE: Even though the PostgreSQL configuration nomenclature uses ssl, modern versions of PostgreSQL only uses TLS as connecting over SSL is disabled. In fact, in PostgreSQL 12 you will be able to specify a minimum and maximum TLS protocol version!

Before we begin, let's pick a username that we want to connect as. For convenience, I set an environmental variable to use the name of the user logged into the terminal:

export MY_USER_NAME_FOR_CERT=whoami

This is optional for you to do; you can always overwrite this variable manually later in the recipe.

Step 1: Creating a Root Certificate Authority (CA)

The first step to setting up certificate-based authentication for your PostgreSQL environment is to create a trusted root certificate authority (CA). Both the PostgreSQL server and client will reference the root CA when determining if they can trust each other as part of the connection process. Note that in a production environment, the root CA may already be established, and in fact, the certificates for the client and server may be signed by an intermediate CA. For the purposes of the environment that we are building, we can generate our own root CA.

mkdir keys certs
chmod og-rwx keys certs
openssl req -new -x509 -days 365 -nodes -out certs/ca.crt \
  -keyout keys/ca.key -subj "/CN=root-ca"

We'll also take this opportunity to create a directory called pgconf that will be mounted to the container and copy the root CA certificate into there. The pgconf is used to store the configuration files and other assets, such as certificates and server keys.

mkdir pgconf
cp certs/ca.crt pgconf/ca.crt

NOTE: In a production system, you would not leave your root CA key in the same place as your other keys (as you will see as we continue). This is just to help you get up and running.

Step 2: Generate the PostgreSQL server key and certificate

In order for certificate-based authentication to work in PostgreSQL (as well as any TLS connections), you will need to give your PostgreSQL server a private key as well as a certificate that is signed by the root CA.

The key element of this certificate is the CN, or "common name" field, which should match the hostname of the server. This is important when a client requests to connect to PostgreSQL using verify-full mode (which we will do): the PostgreSQL client checks that the certificate the server presents is both signed by a trusted CA (in this case our root CA) and that the CN on the server's certificate matches the host (-h) that the client requested to connect to.

(There is a very detailed section in the PostgreSQL documentation on the different TLS connections modes and what level of protection they provide. Note only verify-full protects against eavesdropping and man-in-the-middle attacks, which is why we use it for this example).

The following code generates the server key and a certificate signing request (CSR), has the CSR signed by the root CA, and adds the server key and certificate to the pgconf directory with the appropriate permissions:

openssl req -new -nodes -out server.csr \
  -keyout pgconf/server.key -subj "/CN=localhost"
openssl x509 -req -in server.csr -days 365 \
  -CA certs/ca.crt -CAkey keys/ca.key -CAcreateserial \
  -out pgconf/server.crt
rm server.csr

Step 3: Configure PostgreSQL to Enable TLS / Certificate-Based Authentication

The next step is to configure the PostgreSQL server to both use TLS for connections as well as certificate-based authentication. We can add a PostgreSQL server configuration file similar to this one into the pgconf directory:

# we will need to customize the postgresql.conf file to ensure SSL is turned on
cat << EOF > pgconf/postgresql.conf
# here are some sane defaults given we will be unable to use the container
# variables
# general connection
listen_addresses = '*'
port = 5432
max_connections = 20
# memory
shared_buffers = 128MB
temp_buffers = 8MB
work_mem = 4MB
# WAL / replication
wal_level = replica
max_wal_senders = 3
# these shared libraries are available in the crunchy-postgres container
shared_preload_libraries = 'pgaudit.so,pg_stat_statements.so'
# this is here because SCRAM is awesome, but it's not needed for this setup
password_encryption = 'scram-sha-256'
# here are the TLS specific settings
ssl = on # this enables TLS
ssl_cert_file = '/pgconf/server.crt' # this specifies the server certificate
ssl_key_file = '/pgconf/server.key' # this specifies the server private key
ssl_ca_file = '/pgconf/ca.crt' # this specific which CA certificate to trust
EOF

There are some additional parameters here note needed for enabling TLS, but as this configuration file overrides all of the settings in the PostgreSQL container, they are included to ensure PostgreSQL runs. For the purposes of our certificate-based authentication setup, some of the key configuration parameters to note are:

  • ssl: this enables TLS connections
  • ssl_cert_file: this specifies which certificate to use for the server
  • ssl_key_file: this specifies which private key to use for the server
  • ssl_ca_file: this specifies the root CA file, i.e. the trusted certificate authority to use when verifying a client certificate

Note that the configuration files as well as the keys and certificates in the pgconf directory are locked down in a later step in the script with the chmod og-rwx pgconf/* command.

We also need to set up the PostgreSQL host-based authentication file (pg_hba.conf) to use certificate-based authentication for non-local connections:

cat << EOF > pgconf/pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
# do not let the "postgres" superuser login via a certificate
hostssl all             postgres        ::/0                    reject
hostssl all             postgres        0.0.0.0/0               reject
#
hostssl all             all             ::/0                    cert
hostssl all             all             0.0.0.0/0               cert
EOF

A few things to note here:

  • Certificate-based authentication can only take place if the connection is over TLS, and as such, we have to specify the connection type to be hostssl.
  • I allow the postgres superuser account to connect only over a local connection using the peer authentication method and disallow any remote connections for the postgres user. This is primarily for good practice and does not affect this example.
  • We could add an explicit rule in here to reject all non-TLS connections if we wanted, but based on this ruleset, they are disallowed.

As promised, let's explicitly lock down the configuration and TLS-related files:

chmod og-rwx pgconf/*

Finally, let's get the PostgreSQL server up and running! We will borrow an example from the easy PostgreSQL 10 and pgAdmin 4 setup recipe to get this container deployed, though here we will be deploying PostgreSQL 11. You can substitute ${MY_USER_NAME_FOR_CERT} with your preferred username:

docker volume create --driver local --name=pgvolume docker network create --driver bridge pgnetwork

cat << EOF > pg-env.list
PG_MODE=primary
PG_PRIMARY_PORT=5432
PG_PRIMARY_USER=postgres
PG_DATABASE=testdb
PG_PRIMARY_PASSWORD=does
PG_PASSWORD=not
PG_ROOT_PASSWORD=matter
PG_USER=${MY_USER_NAME_FOR_CERT}
EOF

docker run --publish 5432:5432 \
  --volume=pgvolume:/pgdata \
  --volume=`pwd`/pgconf:/pgconf \
  --env-file=pg-env.list \
  --name="postgres" \
  --hostname="postgres" \
  --network="pgnetwork" \
  --detach \
  crunchydata/crunchy-postgres:centos7-11.4-2.4.1

Step 4: Generating the Client Key and Certificate

We're so close to connecting! The last step in this process is to generate the client key and certificate that our user will use to connect. Given the setup of our certificate environment, setting up our client key is very similar to our server key. The main difference is that the CN for the client certificate must match the username of the client in the database, which is the value of ${MY_USER_NAME_FOR_CERT}. For example, if I created a user named jkatz, then the CN value must also be jkatz.

openssl req -new -nodes -out client.csr \
  -keyout keys/client.key -subj "/CN=${MY_USER_NAME_FOR_CERT}"
chmod og-rwx keys/*
openssl x509 -req -in client.csr -days 365 \
    -CA certs/ca.crt -CAkey keys/ca.key -CAcreateserial \
    -out certs/client.crt
rm client.csr

Step 5: Connecting

PostgreSQL lets you place the client key and certificate as well as well as the root certificate in a special directory within your home directory. However, it only lets you keep one client/certificate/root combination in there at a time, and if you are like me and connect to many different PostgreSQL databases, this could be problematic.

There are a few ways to handle managing multiple PostgreSQL client key/certificate (let alone root CA) combinations:

For the purposes of this example, I opted to use environmental variables:

# the first parameter specifies which TLS mode to use to connect
export PGSSLMODE="verify-full"
# the following two parameters point to the client key/certificate
export PGSSLCERT="`pwd`/certs/client.crt"
export PGSSLKEY="`pwd`/keys/client.key"
# this parameter points to the trusted root CA certificate
export PGSSLROOTCERT="`pwd`/certs/ca.crt"

As mentioned earlier, I opt to use the verify-full TLS mode for connecting to the database as it:

  • Requires TLS by the PostgreSQL server in order to connect
  • Determines if this is a trustworthy PostgreSQL server, i.e. the client validates the server's certificate based on the root CA certificate the client posses
  • Validates that this PostgreSQL server's certificate's CN matches the value provided in the host parameter of the connection, (e.g. -h).

Conversely, given the PostgreSQL server is performing certificate based authentication (thanks to the cert setting in the host-based authentication file), the server will ensure that the client:

  • Presents a certificate that is trusted by the trusted root CA (i.e. the value of ssl_ca_file)
  • The CN on the client's certificate matches the user the client is trying to connect as (i.e. -U) and said user is allowed to log into the server

So how does this look in practice? Try connecting to the PostgreSQL server, substituting $MY_USER_NAME_FOR_CERT for your preferred username (or not, if you still have your environmental variable set).

psql -h localhost -p 5432 -U $MY_USER_NAME_FOR_CERT postgres

You should now be connected, no password required! You should play around with a few different connection combinations and see what happens: try to vary the username, the host, the PGSSLMODE variable, etc. and see what happens!

Conclusion: This Is Just the Beginning

The purpose of this exercise is to get you started on using certificate-based authentication in PostgreSQL, but it's only the tip of the iceberg. There are a lot of other considerations in production systems, including:

  • Certificate revocation
  • Certificate expiration and rotation
  • Integrating with an organizational PKI system

and more.

With that said, even with this example you should have a recipe for getting your applications connect to PostgreSQL using certificates instead of passwords (though as mentioned earlier in this article, please ensure your trusted root CA key is kept somewhere separate and secure from everything else in your infrastructure).

If you're interested in learning more about certificate-based authentication, I do recommend you watch Stephen's talk, which also contains highlights and in-depth explanations on the other authentication methods available in PostgreSQL.

Avatar for Jonathan S. Katz

Written by

Jonathan S. Katz

July 22, 2019 •More by this author