Configure TLS encryption on a PostgreSQL server

By default, PostgreSQL uses unencrypted connections. For more secure connections, you can enable Transport Layer Security (TLS) support on the PostgreSQL server and configure your clients to establish encrypted connections.

Before you begin

  • You created a TLS private key and a certificate authority (CA) issued a server certificate for your PostgreSQL server.
  • The PostgreSQL server is installed.
  • The database cluster is initialized.
  • If FIPS mode is enabled, clients must either support the Extended Master Secret (EMS) extension or use TLS 1.3. TLS 1.2 connections without EMS fail. For more information, see the Red Hat Knowledgebase solution TLS extension "Extended Master Secret" enforced on RHEL 9.2 and later.
  • You have root privileges, or you can temporarily obtain root privileges by using the sudo command.

Procedure

  1. Store the private key and the server certificate in the /var/lib/pgsql/data/ directory:
    # cp server.{key,crt} /var/lib/pgsql/data/
  2. Set the ownership of the private key and certificate:
    # chown postgres:postgres /var/lib/pgsql/data/server.{key,crt}
  3. Set permissions on the server certificate that enable only the PostgreSQL server to read the file:
    # chmod 0400 /var/lib/pgsql/data/server.key

    Because certificates are part of the communication before the server establishes a secure connection, any client can retrieve them without authentication. Therefore, you do not need to set strict permissions on the server certificate file.

  4. Edit the /var/lib/pgsql/data/postgresql.conf file and make the following changes:
    1. Set the password hashing algorithm to SCRAM-SHA-256:
      password_encryption = scram-sha-256
    2. Enable TLS encryption:
      ssl = on
    3. Configure TLS ciphers to follow the system-wide cryptographic profile:
      ssl_ciphers = 'PROFILE=SYSTEM'
  5. Edit the /var/lib/pgsql/data/pg_hba.conf file and update the authentication entries to use TLS encryption and the scram-sha-256 hashing algorithm. For example, change host entries to hostssl to enable TLS encryption, and set the scram-sha-256 hashing algorithm in the last column:
    hostssl    all    all    192.0.2.0/24    scram-sha-256
  6. Restart the postgresql service:
    # systemctl restart postgresql.service

Results

  • Use the postgres super user to connect to a PostgreSQL server and run the \conninfo meta command:
    # psql "postgresql://postgres@localhost:5432" -c '\conninfo'
    Password for user postgres:
    You are connected to database "postgres" as user "postgres" on host "192.0.2.1" at port "5432".
    SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)