PostgreSQL SSL authentication configuration

Preamble

Configuring PostgreSQL for OpenSSL

We must modify Postgresql.conf before configuring OpenSSL. Several factors are linked to encryption, including:

 ssl = on #ssl_ca_file = '' #ssl_cert_file = 'server.crt' #ssl_crl_file = '' #ssl_key_file = 'server.key' #ssl_ciphers = 'HIGH:MEDIUM:+3DES:!'aNULL' # allowed SSL ciphers'ssl_prefer_server_ciphers = on'ssl_ecdh_curve = 'prime256v1''ssl_min_protocol_version' = 'TLSv1.2''ssl_max_protocol_version' = '''ssl_dh_params_file' = '''s

Once ssl = on, the server will negotiate SSL connections in case they are possible. The remaining settings control things like where key files are located and how strong ciphers are. Please be aware that turning on SSL does not necessitate restarting the database. A straightforward reload will set the variable. However, PostgreSQL needs to be restarted otherwise it will reject SSL connections. The following is a significant problem that some users encounter quite frequently:

 postgres=# SELECT pg_reload_conf(); pg_reload_conf ---------------- t (1 row) postgres=# SHOW ssl; ssl ----- on (1 row) 

The SHOW command is an easy way to make sure that the setting has indeed been changed. Technically speaking, pg_reload_conf() is not required right now. In any case, you must start over later. We simply reloaded to show the effect on the variable.

The next step is to alter pg_hba.conf so that PostgreSQL will handle our connections securely:

 # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer host all all 127.0.0.1/32 scram-sha-256 host all all ::1/128 scram-sha-256 hostssl all all 10.0.3.0/24 scram-sha-256 

Restart the database instance after SSL has been enabled.

The next action is to create certificates. To keep things simple in this case, we’ll just create self-signed certificates. Of course, other certificates are also acceptable. The steps are as follows:

 [postgres@node1 data]$ openssl req -new -x509 -days 365 \ -nodes -text -out server.crt \ -keyout server.key \ -subj "/CN=cybertec-postgresql.com" Generating a RSA private key .......+++++ ....................................................+++++ writing new private key to 'server.key' ----- 

This certificate has a 365-day expiration date. After that, the certificate must be made active by adjusting permissions. If those permissions are too lenient, the server won’t accept the certificate:

[postgres@node1 data]$ chmod og-rwx server.key

It’s nice to have certificates that you self-sign. You must first create a certificate signing request and a public/private key file in order to create a server certificate whose identity and origin can be verified by clients:

 [postgres@node1 data]$ openssl req -new -nodes -text \ -out root.csr \ -keyout root.key \ -subj "/CN=cybertec-postgresql.com" Generating a RSA private key .................................+++++ ....................+++++ writing new private key to 'root.key' ----- 

We must confirm these permissions are exactly as they should be once more:

[postgres@node1 data]$ chmod og-rwx root.key

Then the request is signed. To use OpenSSL for that action, we must first find openssl.cnf. It’s not always in the same place, as we’ve seen, so make sure you’re taking the right path:

 [postgres@node1 data]$ find / -name openssl.cnf \ 2> /dev/null /etc/pki/tls/openssl.cnf

We carry out the following steps after signing the request:

 [postgres@node1 data]$ openssl x509 -req -in root.csr \ -text \ -days 3650 \ -extfile /etc/pki/tls/openssl.cnf \ -extensions v3_ca \ -signkey root.key -out root.crt Signature ok subject=CN = cybertec-postgresql.com Getting Private key

Let’s create a new certificate for the root authority:

 [postgres@node1 data]$ openssl req -new -nodes -text \ -out server.csr \ -keyout server.key \ -subj "/CN=cybertec-postgresql.com" Generating a RSA private key .....................+++++ ...........................+++++ writing new private key to 'server.key' [postgres@node1 data]$ chmod og-rwx server.key [postgres@node1 data]$ openssl x509 -req \ -in server.csr -text -days 365 \ -CA root.crt -CAkey root.key -CAcreateserial \ -out server.crt Signature ok subject=CN = cybertec-postgresql.com Getting CA Private Key 

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

But there is 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 kept offline so that it can be used when making new certificates.

The following files are needed:

Checking your setup

The servers should now be restarted after installing all the certificates:

Systemctl restart postgresql-13 [root@node1]

The connection would break down and display an error message without a restart.(“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:

 [root@node1 ~]# su - postgres [postgres@node1 ~]$ psql test -h 10.0.3.200 Password for user postgres: psql (13.2) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) Type "help" for help. test=# 

Psql claims that the connection is encrypted; to verify this, we must look at the information in pg_stat_ssl:

 postgres=# \d pg_stat_ssl View "pg_catalog.pg_stat_ssl" Column | Type | Collation | Nullable | Default ----------------+---------+-----------+----------+--------- pid | integer | | | ssl | boolean | | | version | text | | | cipher | text | | | bits | integer | | | compression | boolean | | | client_dn | text | | | client_serial | numeric | | | issuer_dn | text | | | 

Let’s query the system view to examine its contents:

 test=# \x Expanded display is on. test=# SELECT * FROM pg_stat_ssl; -[ RECORD 1 ]-+----------------------- pid | 16378 ssl | t version | TLSv1.3 cipher | TLS_AES_256_GCM_SHA384 bits | 256 compression | f client_dn | client_serial | issuer_dn | 

The connection has been successfully encrypted if “ssl = true.”

Different levels of SSL supported by PostgreSQL

Two SSL configurations are not always the same. You can pick the level of security and protection you want from a number of options. The SSL modes that PostgreSQL supports are listed in the following table:

The overhead is heavily influenced by the mode you are using.

The main issue at hand right now is how to specify the mode that will be used. The answer is that it needs to be hidden as part of the connect string, as shown in the next example:

 [postgres@node1 data]$ psql "dbname=test host=10.0.3.200 user=postgres password=1234 sslmode=verify-ca" psql: error: root certificate file "/var/lib/pgsql/.postgresql/root.crt" does not exist Either provide the file or change sslmode to disable server certificate verification. 

Verify-ca doesn’t work in this case because the root.* files need to be copied to the client and the certificates need to be able to validate the target server.

Encrypting your entire server: PostgreSQL TDE

The link between the client and the server has been encrypted so far. However, there are times when it’s imperative to completely encrypt the server, including the storage.

What might also interest you …

Materialized views are a vital part of the majority of databases, including PostgreSQL. They can, at the very least, be used to cache large calculations.

About Enteros

​​​​Enteros offers a patented database performance management SaaS platform. It finds the root causes of complex database scalability and performance problems that affect business across a growing number of cloud, RDBMS, NoSQL, and machine learning database platforms.