PostgreSQL + pgAdmin + mTLS + certificate-based authentication + docker-compose in one package

What cannot be taken away from me is the mastery of the title…

At some point, during local development (and, in general, when testing on other benches), you think about how to get rid of rather monotonous actions. One of them is to enter a password as part of the authentication process in PostgreSQL. In this article I will tell you how to slightly automate this process.

This article is a slight rethinking of what I wrote on medium. Because I continue to think in Russian (:

TL;DR sources to yours services.

As part of any interaction, we encounter such entities as authorization and authentication. I won’t repeat 100,500 times what is what (but I’m not too lazy to type such a long remark, yeah). Within PostgreSQL, the first is provided through Rolesand the second through Privileges.

If you delve into the PostgreSQL documentation, you will find that this database supports quite a few types of authorization. However, we are interested in something that could replace the login and password combination. And at this moment authorization based on certificates comes on Google mind. In general, if configured correctly, we not only get rid of the need to enter a password for our user, but also increase the level of security (certificate forgery a little bit more difficult than guessing my favorite password, which is “qwerty”).

Let's think about what we want to achieve. But just think like that boringso let’s write down our requirements in user story format!

1. As a user, I want to have a PostgreSQL instance available.

2. As a user, I want to be able to log in there and execute the commands available to me.

3. As a user, I'm lazy and don't want to enter a password (so be it, I agree to check the “Remember password” flag in the corresponding pgAdmin dialog).

4. As a user, I want all interactions to be coated in certificates.

Let's start preparing our cocktail. We will need:

1. Docker-compose.

2. OpenSSL.

3. Favorite text editor, or IDE.

Given the ingredients for our cocktail, let's think about the network interaction of elements. First, of course, let's define them:

1. PostgreSQL service

2. pgAdmin service

3. Favorite browser

And let's try to draw their interaction:

1-2 – TLS channel, both the server and the client check the certificates of the opposite party. This is an mTLS connection

3-4 – Standard request / response sequence executed within the channel established in step 1-2.

5-6 – TLS channel, certificate verification is carried out only on the client (browser) side. This connection is simply TLS, because certificate validation is performed by only one of the parties.

7-8 – Similar to step 3-4, but performed within the channel established in step 5-6.

Now we see what types of certificates we will need:

• Root certificate (1) for signing certificates associated with an mTLS connection

• Certificate for PostgreSQL service, signed by root certificate (1)

• Certificates for each user who will log in. Each certificate must be signed by a certificate (1)

• Root certificate (2) for signing certificates associated with an HTTPS connection

• HTTPS certificate signed by root certificate (2)

It has been empirically determined that Apple Keychain does not support root certificates with a key size greater than 8192 bits (which obviously follows from the error text “Error: -67762”), so let's limit ourselves to this dimension.

Let's go generate certificates. We are programmers, so we write script and necessary config.

First, let's define some common variables:

Now you can generate a root certificate for mTLS purposes:

We generate a certificate for the PostgreSQL service and sign it with the previously received one:

And we generate a certificate for each user:

Generating certificates for an HTTPS connection is exactly the same, except that you do not need to generate user certificates.

Let's look at the configuration a little. Configuration files in this case have an ini-like structure, i.e. There are sections, and in them are keys and values. In this case, the values ​​can refer to one of the sections. For example:

In this case, the key value basicConstraints refers to a section mtls_root_basic_constraints which states that we generate a Certificate Authority (I couldn’t remember the sane translation), which cannot issue intermediate certificates.

We pass the applied section from the configuration file as the parameter value -extensions: -extensions v3_mtls_root.

So, all certificates have been generated. It's time to configure PostgreSQL. Let's start with file pg_hba.conf. This file is part of the system authentication.

Our requirements are quite obvious:

1. Allow any local connection from inside the container

2. Any SSL connection must undergo full validation of the user certificate

3. Other connections are prohibited

Actually, the contents of this configuration file reflect this:

Next, we need to configure the service launch parameters, environment variables, forward the port and volumes. Actually, this part docker-compose.yml might look like this:

We carry out a similar configuration, namely: environment variables, port forwarding and volumes for our pgAdmin:

Actually, the main thing is to forward user certificates and the mTLS root certificate. We also forward the server HTTPS certificate. Well, since we know all the locations of the certificates, we can forward them right away file with server settings (servers.json):

Total what we have at the output:

• Configured PostgreSQL performing validation based on the CN certificate.

• Configured pgAdmin, which can establish an mTLS connection with PostgreSQL and a TLS connection with the browser

I would like to think that you found this content useful. Always ready to answer you by DEFAULT_EMAIL – do not be shy.