Detections
Analytics
6.8 Ensure SSL is enabled and configured correctly
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.
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)
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.
See Also
Item Details
Audit Name: CIS PostgreSQL 11 DB v1.0.0
Category: SYSTEM AND COMMUNICATIONS PROTECTION
References: 800-53|SC-8, CSCv6|14.2, CSCv7|14.4
Plugin: PostgreSQLDB
Control ID: df5a3886f3e073e1de75e891041477935759deb4064f1ce7e056c254eb940159