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.
- Prepare for server certificates
- Generate self-signed certificates
- (Optional) Generate CA-signed certificates for clients to verify
- Edit server config files
- postgresql.conf : Edit options, listen_address / ssl
- pg_hba.conf : Add hostssl definition
- Verify in a client machine
- Use
psql
with sslmode
- Use
Environment
- OpenBSD 7.1
- PostgreSQL 14.2
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 :)
Top comments (0)