PostgreSQL SSL Settings Query Guide

PostgreSQL SSL Settings Query Guide

This PostgreSQL query retrieves all SSL and TLS configuration settings from the pg_settings system view. It surfaces whether SSL is enabled, which certificates and keys are in use, what cipher suites are allowed, and all other SSL-related parameters.

Purpose and Overview

Encrypting connections to PostgreSQL with SSL/TLS is a baseline security requirement for any server that handles sensitive data or accepts connections over a network. This query gives administrators a fast way to audit the current SSL configuration without reading postgresql.conf directly.

SQL Script

 1SELECT
 2    name,
 3    setting,
 4    -- category
 5    vartype,
 6    short_desc,
 7    enumvals,
 8    boot_val,
 9    reset_val,
10    -- not available on PostgreSQL < 9.5
11    pending_restart
12FROM
13    pg_settings
14WHERE
15    name ILIKE '%ssl%'
16      OR
17    -- Connections and Authentication / SSL
18    category ILIKE '%SSL%'
19      OR
20    short_desc ILIKE '%SSL%';

Code Breakdown

Three-Part Filter

Unlike the other settings scripts that filter on category alone, this query uses a three-part OR condition to capture all SSL-relevant settings regardless of where SSL is mentioned:

1name ILIKE '%ssl%'

Catches parameters with ssl in their name, such as ssl, ssl_cert_file, and ssl_ciphers.

1category ILIKE '%SSL%'

Catches parameters categorised under Connections and Authentication / SSL.

1short_desc ILIKE '%SSL%'

Catches any parameters whose description mentions SSL — a safety net for parameters that relate to SSL but are not named or categorised with that term.

enumvals Column

This script includes enumvals in the SELECT list. SSL parameters such as ssl_min_protocol_version are enum-type settings, and enumvals shows the full list of allowed values, making it easy to verify that the configured value is valid.

source and min_val / max_val Omitted

The SSL-focused script omits source, min_val, and max_val compared to other settings scripts. SSL parameters are mostly string or enum types where range values are not applicable.

Key SSL Parameters

ssl — Master switch to enable or disable SSL connections. When off, no encrypted connections are accepted.

ssl_cert_file — Path to the server's SSL certificate file (typically server.crt). This certificate is presented to connecting clients.

ssl_key_file — Path to the server's private key file (typically server.key). Must be readable only by the postgres user.

ssl_ca_file — Path to the certificate authority file used to verify client certificates. Required for mutual TLS (mTLS).

ssl_crl_file — Path to a certificate revocation list. Connections using a revoked certificate are rejected.

ssl_ciphers — The list of SSL cipher suites accepted for new connections. Defaults to HIGH:MEDIUM:+3DES:!aNULL.

ssl_prefer_server_ciphers — When enabled, the server's cipher preferences take priority over the client's. Recommended to keep on.

ssl_ecdh_curve — The elliptic curve used for ECDH key exchange. Default is prime256v1.

ssl_min_protocol_version — The minimum TLS protocol version accepted. Set to TLSv1.2 or higher to block older vulnerable protocols.

ssl_max_protocol_version — The maximum TLS protocol version accepted. Usually left at the default to allow the highest supported version.

ssl_passphrase_command — A shell command that retrieves the passphrase for an encrypted SSL private key file.

ssl_passphrase_command_supports_reload — Whether the passphrase command supports being called during a pg_reload_conf() without a restart.

Practical Applications

Security Audit

Run this query to verify SSL is enabled (ssl = on), a minimum protocol version is enforced, and weak cipher suites are excluded. Include the output in security audit reports.

Certificate Rotation Verification

After rotating SSL certificates, run this query to confirm the new certificate and key file paths are correctly configured before testing connections.

Compliance Review

Many compliance frameworks require TLS 1.2 or higher. Use ssl_min_protocol_version to verify and enforce the minimum protocol version.

Mutual TLS Setup

When configuring mTLS for client certificate authentication, use this query to verify ssl_ca_file points to the correct CA and ssl_crl_file is configured if revocation checking is required.

Checking SSL from the Client

From psql, you can also check the SSL status of the current connection:

1SELECT ssl, version, cipher, bits, client_dn
2FROM pg_stat_ssl
3WHERE pid = pg_backend_pid();

pg_stat_ssl provides per-connection SSL details including the protocol version and cipher actually in use.

Version Compatibility

Requires PostgreSQL 9.5 or later due to the pending_restart column. ssl_min_protocol_version and ssl_max_protocol_version were added in PostgreSQL 12. Tested on PostgreSQL 9.5, 9.6, 10.x, 11.x, 12.x, and 13.0.

Best Practices

Always enable SSL on network-accessible servers: Any PostgreSQL instance that accepts connections from outside localhost should have ssl = on.

Enforce TLS 1.2 or higher: Set ssl_min_protocol_version = 'TLSv1.2' to block TLS 1.0 and 1.1, which have known vulnerabilities.

Protect the private key: The ssl_key_file must have permissions of 0600 and be owned by the postgres user. PostgreSQL will refuse to start if the key is world-readable.

Prefer server cipher order: Keep ssl_prefer_server_ciphers = on so the server's security policy governs cipher selection rather than the client.

Rotate certificates before expiry: Set up monitoring for certificate expiration dates. Expired certificates will prevent new connections.

References

• PostgreSQL Documentation - SSL Support - Guide to configuring SSL connections in PostgreSQL
• PostgreSQL Documentation - Connection Settings - Official reference for SSL configuration parameters
• PostgreSQL Documentation - pg_stat_ssl - Reference for the pg_stat_ssl view showing per-connection SSL status
• Source SQL Script - postgres_settings_ssl.sql - Original script by Hari Sekhon