Your Web News in One Place

Help Webnuz

Referal links:

Sign up for GreenGeeks web hosting
April 24, 2022 05:04 am GMT

PostgreSQL 14: TLS Connection

Summary

To use PostgreSQL as external database servers, it's better to use TLS/SSL connection. This post shows how to generate certificates, configure servers and verify them.

There are just 3 steps.

  1. Prepare for server certificates
    • Generate self-signed certificates
    • (Optional) Generate CA-signed certificates for clients to verify
  2. Edit server config files
    • postgresql.conf : Edit options, listen_address / ssl
    • pg_hba.conf : Add hostssl definition
  3. Verify in a client machine
    • Use psql with sslmode

Environment

Reference

Tutorial

1. Prepare for server certificates

Generate self-signed certificates

Create server certificates in the PostgreSQL data directory as _postgresql user.

$ doas su - _postgresql$ whoami_postgresql$ cd /var/postgresql/data

Create a self-signed certificate with openssl command line tool.
Of course, -days 36500 below can be modified, which means it will be valid within 36500 days = almost 100 years.

$ # ksh$ DB_HOST_DOMAIN="(...)"$ openssl req -new -x509 -days 36500 -nodes -text -out server.crt -keyout server.key -subj "/CN=$DB_HOST_DOMAIN"

The output was:

Generating a 2048 bit RSA private key..............................................................................+++++................+++++writing new private key to 'server.key'-----

You will see:

$ ls -l server\.*-rw-r--r--  1 _postgresql  _postgresql  3660 Apr 24 13:17 server.crt-rw-r--r--  1 _postgresql  _postgresql  1704 Apr 24 13:17 server.key

Modify permission of the key:

$ chmod 400 server.key

Stay in /var/postgresql/data as _postgresql.

(Optional) Generate CA-signed certificates for clients to verify

If you don't hesitate to edit /etc/ssl/openssl.cnf to use v3_ca extensions, it is able to create a server certificate whose identity can be validated by clients. It creates root and intermediate certificates. The detail about editing is in the official docs:

$ # create a certificate signing request (CSR) and a public/private key file$ openssl req -new -nodes -text -out root.csr -keyout root.key -subj "/CN=$ROOT_CA_DOMAIN"$ chmod 400 root.key$ # create a root certificate authority$ openssl x509 -req -in root.csr -text -days 36500 -extfile /etc/ssl/openssl.cnf -extensions v3_ca -signkey root.key -out root.crt$ # ... might be end with "Error Loading extension section v3_ca"$ # create a server certificate signed by the new root certificate authority$ openssl req -new -nodes -text -out server.csr -keyout server.key -subj "/CN=$DB_HOST_DOMAIN"$ chmod 400 server.key$ openssl x509 -req -in server.csr -text -days 36500 -CA root.crt -CAkey root.key -CAcreateserial -out server.crt

Here, you might meet "Error Loading extension section v3_ca". In this case, try to modify /etc/ssl/openssl.cnf following this post.

$ ls {root,server}*root.crt   root.csr   root.key   root.srl   server.crt server.csr server.key

2. Edit server config files

You are in /var/postgresql/data as _postgres. Right?

$ whoami_postgresql$ pwd  /var/postgresql/data

Edit postgresql.conf:

$ nvim postgresql.conf

so as to enable requests from remote clients and also ssl connection:

  #listen_addresses = 'localhost' ...+ listen_addresses = '*'  ...  #port = 5432+ port = {$DB_PORT} # (optional) for security  ...  #ssl = off+ ssl = on  #ssl_cert_file = 'server.crt'  #ssl_ca_file = ''  #ssl_crl_file = ''  #ssl_key_file = 'server.key'

Besides, when you use not-self-signed certificates and have root.crt, ssl_ca_file must be filled.

Next, edit pg_hba.conf:

$ nvim pg_hba.conf

to add a line on hostssl to the bottom so as to allow ssl connection from clients:

+ hostssl all             all             0.0.0.0/0               md5

As of 14, you don't have to set clientcert to 0 when you use self-signed certificates. It is one of "auth-options", which can be set to verify-ca or verify-full when you have valid client certificates. }}">In 13 and smaller, it can be set to 1 (defalut, then)/0/no-verify.

Besides, when something fails on the way, /var/postgresql/logfile is surely useful to get the detail.

Let's come back to your user:

$ # end of behavior as _postgresql$ exit

Restart the database server:

$ doas rcctl restart postgresql

Done.

3. Verify in a client machine

In a client machine, use psql with "sslmode=require":

$ psql "sslmode=require host=$DB_HOST port=$DB_PORT user=$DB_USER dbname=$DB_NAME"Password for user ...: 

Enter db user's password.

psql (14.2)SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)Type "help" for help.postgres=#

Here, TLS connection based on "protocol: TLSv1.3" etc. is acquired :)


Original Link: https://dev.to/nabbisen/postgresql-14-tls-connection-184l

Share this article:    Share on Facebook
View Full Article

Dev To

An online community for sharing and discovering great ideas, having debates, and making friends

More About this Source Visit Dev To