Ubuntu 24.04
Sponsored Link

PostgreSQL 16 : PostgreSQL over SSL/TLS2024/05/30

 

SSL/TLS connection to PostgreSQL.

[1] SSL/TLS is enabled by default, so it does not need to change settings if you don't have specific requirements to the system.
So SSL/TLS connection is enabled automatically if you connect to PostgreSQL via TCP.
# settings for SSL/TLS
# certificate is from the OS bundle

root@www:~#
grep -n ^ssl /etc/postgresql/16/main/postgresql.conf

108:ssl = on
110:ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
113:ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'

# settings for connection method

root@www:~#
grep -v -E '^#|^$' /etc/postgresql/16/main/pg_hba.conf

local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256


# to connect via socket that is the default, connection is not encrypted

ubuntu@www:~$
psql testdb

psql (16.2 (Ubuntu 16.2-1ubuntu4))
Type "help" for help.

testdb=> \q

# to connect via TCP, connection is encrypted

ubuntu@www:~$
psql -h 127.0.0.1 -d testdb -U ubuntu

Password for user ubuntu:
psql (16.2 (Ubuntu 16.2-1ubuntu4))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

testdb=> select name as "Parameter name", setting as value, short_desc from pg_settings where name like '%ssl%';
             Parameter name             |                 value                  |                               short_desc     
----------------------------------------+----------------------------------------+-------------------------------------------------------------------------
 ssl                                    | on                                     | Enables SSL connections.
 ssl_ca_file                            |                                        | Location of the SSL certificate authority file.
 ssl_cert_file                          | /etc/ssl/certs/ssl-cert-snakeoil.pem   | Location of the SSL server certificate file.
 ssl_crl_dir                            |                                        | Location of the SSL certificate revocation list directory.
 ssl_crl_file                           |                                        | Location of the SSL certificate revocation list file.
 ssl_key_file                           | /etc/ssl/private/ssl-cert-snakeoil.key | Location of the SSL server private key file.
 ssl_library                            | OpenSSL                                | Shows the name of the SSL library.
 ssl_passphrase_command_supports_reload | off                                    | Controls whether ssl_passphrase_command is called during server reload.
 ssl_prefer_server_ciphers              | on                                     | Give priority to server ciphersuite order.
(9 rows)
Matched Content