Detections
Analytics

6.8 Ensure SSL is enabled and configured correctly

Warning! Audit Deprecated

This audit has been deprecated and will be removed in a future update.

View Next Audit Version

Information

SSL on a PostgreSQL server should be enabled (set to on) and configured to encrypt TCP traffic to and from the server.

Rationale:

If SSL is not enabled and configured correctly, this increases the risk of data being compromised in transit.

Impact:

A self-signed certificate can be used for testing, but a certificate signed by a certificate authority (CA) (either one of the global CAs or a local one) should be used in production so that clients can verify the server's identity. If all the database clients are local to the organization, using a local CA is recommended.

To ultimately enable and enforce ssl authentication for the server, appropriate hostssl records must be added to the pg_hba.conf file. Be sure to reload PostgreSQL after any changes (restart not required).

Note: The hostssl record matches connection attempts made using TCP/IP, but only when the connection is made with SSL encryption. The host record matches attempts made using TCP/IP, but allows both SSL and non-SSL connections. The hostnossl record matches attempts made using TCP/IP, but only those without SSL. Care should be taken to enforce SSL as appropriate.

Solution

For this example, and ease of illustration, we will be using a self-signed certificate for the server generated via openssl, and the PostgreSQL defaults for file naming and location in the PostgreSQL $PGDATA directory.

$ whoami
postgres
$ # create new certificate and enter details at prompts
$ openssl req -new -text -out server.req
Generating a 2048 bit RSA private key
.....................+++
..................................................................+++
writing new private key to 'privkey.pem'
Enter PEM pass phrase:
Verifying - Enter PEM pass phrase:
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:US
State or Province Name (full name) []:Ohio
Locality Name (eg, city) [Default City]:Columbus
Organization Name (eg, company) [Default Company Ltd]:Me Inc
Organizational Unit Name (eg, section) []:IT
Common Name (eg, your name or your server's hostname) []:my.me.inc
Email Address []:[email protected]

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

$ # remove passphrase (required for automatic server start up)
$ openssl rsa -in privkey.pem -out server.key && rm privkey.pem
Enter pass phrase for privkey.pem:
writing RSA key

$ # modify certificate to self signed, generate .key and .crt files
$ openssl req -x509 -in server.req -text -key server.key -out server.crt

$ # copy .key and .crt files to appropriate location, here default $PGDATA
$ cp server.key server.crt $PGDATA

$ # restrict file mode for server.key
$ chmod og-rwx server.key

Edit the PostgreSQL configuration file postgresql.conf to ensure the following items are set. Again, we are using defaults. Note that altering these parameters will require restarting the cluster.

# (change requires restart)
ssl = on

# allowed SSL ciphers
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'

# (change requires restart)
ssl_cert_file = 'server.crt'

# (change requires restart)
ssl_key_file = 'server.key'

password_encryption = scram-sha-256

Finally, restart PostgreSQL and confirm ssl using commands outlined in Audit Procedures:

postgres=# show ssl;
 ssl
-----
 on
(1 row)

See Also

https://workbench.cisecurity.org/files/3170

Item Details

References: CSCv6|14.2, CSCv7|14.4

Plugin: PostgreSQLDB

Control ID: 9695070952cdc81cc3d66e15f8b432fb937b4b135015c9603f5dcd6f8db6d0dc