Updating applications to connect to PostgreSQL DB instances using new SSL/TLS certificates (original) (raw)

Certificates used for Secure Socket Layer or Transport Layer Security (SSL/TLS) typically have a set lifetime. When service providers update their Certificate Authority (CA) certificates, clients must update their applications to use the new certificates. Following, you can find information about how to determine if your client applications use SSL/TLS to connect to your Amazon RDS for PostgreSQL DB instance. You also find information about how to check if those applications verify the server certificate when they connect.

Note

A client application that's configured to verify the server certificate before SSL/TLS connection must have a valid CA certificate in the client's trust store. Update the client trust store when necessary for new certificates.

After you update your CA certificates in the client application trust stores, you can rotate the certificates on your DB instances. We strongly recommend testing these procedures in a nonproduction environment before implementing them in your production environments.

For more information about certificate rotation, see Rotating your SSL/TLS certificate. For more information about downloading certificates, see Using SSL/TLS to encrypt a connection to a DB instance or cluster. For information about using SSL/TLS with PostgreSQL DB instances, see Using SSL with a PostgreSQL DB instance.

Topics

Determining whether applications are connecting to PostgreSQL DB instances using SSL

Check the DB instance configuration for the value of the rds.force_ssl parameter. By default, the rds.force_ssl parameter is set to 0 (off) for DB instances using PostgreSQL versions before version 15. By default, rds.force_ssl is set to 1 (on) for DB instances using PostgreSQL version 15 and later major versions. If the rds.force_ssl parameter is set to 1 (on), clients are required to use SSL/TLS for connections. For more information about parameter groups, see Parameter groups for Amazon RDS.

If you are using RDS PostgreSQL version 9.5 or later major version and rds.force_ssl is not set to 1 (on), query the pg_stat_ssl view to check connections using SSL. For example, the following query returns only SSL connections and information about the clients using SSL.

SELECT datname, usename, ssl, client_addr 
  FROM pg_stat_ssl INNER JOIN pg_stat_activity ON pg_stat_ssl.pid = pg_stat_activity.pid
  WHERE ssl is true and usename<>'rdsadmin';

Only rows using SSL/TLS connections are displayed with information about the connection. The following is sample output.

 datname  | usename | ssl | client_addr 
----------+---------+-----+-------------
 benchdb  | pgadmin | t   | 53.95.6.13
 postgres | pgadmin | t   | 53.95.6.13
(2 rows)

This query displays only the current connections at the time of the query. The absence of results doesn't indicate that no applications are using SSL connections. Other SSL connections might be established at a different time.

Determining whether a client requires certificate verification in order to connect

When a client, such as psql or JDBC, is configured with SSL support, the client first tries to connect to the database with SSL by default. If the client can't connect with SSL, it reverts to connecting without SSL. The default sslmode mode used for both libpq-based clients (such as psql) and JDBC is set to prefer. The certificate on the server is verified only whensslrootcert is provided with sslmode set toverify-ca or verify-full. An error is thrown if the certificate is invalid.

Use PGSSLROOTCERT to verify the certificate with thePGSSLMODE environment variable, with PGSSLMODE set toverify-ca or verify-full.

PGSSLMODE=verify-full PGSSLROOTCERT=/fullpath/ssl-cert.pem psql -h pgdbidentifier.cxxxxxxxx.us-east-2.rds.amazonaws.com -U masteruser -d postgres

Use the sslrootcert argument to verify the certificate withsslmode in connection string format, with sslmode set toverify-ca or verify-full to verify the certificate.

psql "host=pgdbidentifier.cxxxxxxxx.us-east-2.rds.amazonaws.com sslmode=verify-full sslrootcert=/full/path/ssl-cert.pem user=masteruser dbname=postgres"

For example, in the preceding case, if you are using an invalid root certificate, then you see an error similar to the following on your client.

psql: SSL error: certificate verify failed

Updating your application trust store

For information about updating the trust store for PostgreSQL applications, see Secure TCP/IP connections with SSL in the PostgreSQL documentation.

For information about downloading the root certificate, see Using SSL/TLS to encrypt a connection to a DB instance or cluster.

For sample scripts that import certificates, see Sample script for importing certificates into your trust store.

Note

When you update the trust store, you can retain older certificates in addition to adding the new certificates.

Using SSL/TLS connections for different types of applications

The following provides information about using SSL/TLS connections for different types of applications: