Encryption in Transit Add-On

SSL/TLS Encryption in Transit for PostgreSQL

The built-in SSL/TLS Encryption add-on protects PostgreSQL data while it moves between clients and database servers. It provides encryption before transmission, endpoint authentication, decryption, and verification when the data arrives.

In this article

Add-On Installation

The add-on can be installed only on PostgreSQL nodes and, for clustered databases, Pgpool-II nodes.

1

Open the PostgreSQL Add-Ons section

Locate the required PostgreSQL database layer, open Add-Ons, and click Install for SSL/TLS Encryption. The solution is also available from Marketplace or as an importable package.

PostgreSQL SSL TLS add-on
Install the SSL/TLS Encryption add-on on the PostgreSQL layer.
2

Select the target layers

Choose the target Environment and Node Group(s). For a PostgreSQL cluster with Pgpool-II, select both the PostgreSQL and Pgpool-II layers.

PostgreSQL SSL add-on installation
Select the target environment and PostgreSQL or Pgpool-II node groups.
3

Install the add-on

Click Install. In about a minute, the database is reconfigured to work over an encrypted connection.

PostgreSQL SSL add-on installed
The database is reconfigured to work over an encrypted connection.

Add-On Specifics

  • Certificates are generated with /usr/local/sbin/selfcertgen.
  • Certificates are self-signed and issued for the hostname of each individual node.
  • Use the certificate set that belongs to the exact PostgreSQL node being accessed.
  • Certificate files are stored under /var/lib/jelastic/keys/SSL-TLS.
serverServer certificates used to provide TLS encryption for PostgreSQL connections.
clientDownloadable client certificates used to authenticate client connections. Client-certificate functionality is not available for Pgpool-II nodes.
PostgreSQL SSL certificates
Server and client certificate sets are stored under the SSL-TLS keys directory.

PostgreSQL Authentication Changes

The add-on changes authentication from md5 to scram-sha-256 to improve security and support SSL in PostgreSQL cluster topologies with Pgpool-II. If md5 was the default before installation, the setting is changed globally for PostgreSQL.

The following SSL settings are added to /var/lib/pgsql/data/postgresql.conf:

ssl_cert_file = '/var/lib/jelastic/keys/SSL-TLS/server/server.crt'
ssl_ca_file = '/var/lib/jelastic/keys/SSL-TLS/server/root.crt'
ssl_key_file = '/var/lib/jelastic/keys/SSL-TLS/server/server.key'
ssl = on
PostgreSQL SSL postgresql.conf
SSL certificate paths and ssl=on are added to postgresql.conf.
!

Reset passwords for custom users

Because the authentication algorithm changes, existing users’ passwords must be reset. The default webadmin password is reset automatically to the same value, but existing custom users must be updated manually.

In /var/lib/pgsql/data/pg_hba.conf, hostssl is used instead of host to enforce SSL authentication:

hostssl all all 0.0.0.0/0 scram-sha-256
PostgreSQL pg_hba.conf SSL rule
hostssl and scram-sha-256 are used for PostgreSQL SSL authentication.

To authenticate PostgreSQL users with client certificates, manually add cert as the authentication method.

Pgpool-II Configuration

For PostgreSQL clusters, SSL is enabled automatically in /etc/pgpool-II/pgpool.conf:

ssl = on
enable_pool_hba = on
ssl_key = '/var/lib/jelastic/keys/SSL-TLS/server/server.key'
ssl_cert = '/var/lib/jelastic/keys/SSL-TLS/server/server.crt'
pool_passwd = 'pool_passwd'
Pgpool II SSL configuration
SSL settings are enabled automatically in pgpool.conf.

The Pgpool-II authentication file /etc/pgpool-II/pool_hba.conf also uses SSL:

hostssl all all 0.0.0.0/0 scram-sha-256
Pgpool II pool_hba.conf SSL rule
Pgpool-II uses hostssl with scram-sha-256.

The default webadmin and pgpool users are added to /etc/pgpool-II/pool_passwd automatically. Add custom users with:

pg_enc -m -f /etc/pgpool-II/pgpool.conf -u $USERNAME $PASSWORD
Pgpool II pool_passwd
Custom users must be added to pool_passwd.

Add-On Configuration

After installation, the add-on is available under the Add-Ons tab for the appropriate layer.

PostgreSQL SSL add-on configuration
Re-issue certificates, open the configuration tutorial, or uninstall the add-on.
Re-issue certificatesGenerate a new set of SSL certificates, for example when certificates are compromised or removed.
Configuration TutorialOpen the manual PostgreSQL SSL connection guide.
UninstallRemove the add-on, its custom configuration, and generated SSL certificates from the layer.

Secure Connection to PostgreSQL

Server-side encryption works immediately after installation. For external access, use an endpoint or public IP as the database entry point. The cluster example connects through an endpoint on a Pgpool-II node.

Pgpool II endpoint
Use an endpoint or public IP as the external database entry point.

Connect with psql using the database credentials supplied after database installation:

psql -U {userName} {dbName} -h {host} -p {port} -W
{userName}Database username.
{dbName}Database name.
{host}Database entry point, such as the endpoint hostname.
{port}Connection port, such as the endpoint public port.
PostgreSQL SSL connection
Connect to PostgreSQL with psql using the encrypted server-side connection.

Client Certificate Authentication

For server- and client-side authentication, download the SSL certificate files from the required PostgreSQL node:

/var/lib/jelastic/keys/SSL-TLS/client
PostgreSQL client certificates
Download client.crt, client.key, and root.crt from the PostgreSQL node.

Upload client.crt, client.key, and root.crt to the client machine or container and connect with:

psql "sslmode=verify-ca sslrootcert={path/to/root.crt} sslcert={path/to/client.crt} sslkey={path/to/client.key} host={host} port={port} user={userName} dbname={dbName}"
PostgreSQL client certificate connection
Use client certificates with sslmode=verify-ca.

Expected Result

PostgreSQL and, where applicable, Pgpool-II are configured for encrypted SSL/TLS connections. PostgreSQL authentication uses scram-sha-256, remote connections can be established through an endpoint or public IP, and client certificates can be used for PostgreSQL-node authentication.

Important Notes

  • The add-on is supported on PostgreSQL and Pgpool-II layers only.
  • For a Pgpool-II cluster, select both PostgreSQL and Pgpool-II node groups.
  • Certificates are self-signed and specific to each node hostname.
  • Client certificates are available for PostgreSQL nodes, not Pgpool-II nodes.
  • Existing custom PostgreSQL users need their passwords reset after the switch from md5 to scram-sha-256.
  • Custom Pgpool-II users must be added to pool_passwd with pg_enc.
  • Use sslmode=verify-ca when connecting with the generated client certificates.