Configure SSL on RDS
You can require that connections to your PostgreSQL DB instance use SSL by using the rds.force_ssl parameter. By default, the rds.force_ssl parameter is set to 0 (off).
You can set the rds.force_ssl parameter to 1 (on) to require SSL for connections to your DB instance. Updating the rds.force_ssl parameter also sets the PostgreSQL ssl parameter to 1 (on) and modifies your DB instance’s pg_hba.conf file to support the new SSL configuration.
You can set the rds.force_ssl parameter value by updating the parameter group for your DB instance. If the parameter group for your DB instance isn’t the default one, and the SSL parameter is already set to 1 when you set rds.force_ssl to 1, you don’t need to reboot your DB instance. Otherwise, you must reboot your DB instance for the change to take effect.
Check if the SSL is being used by loading the sslinfo extension and then call the ssl_is_used() function. Find teh steps below to do that: On the psq; terminal, run the below:
postgres=> create extension sslinfo;
CREATE EXTENSION postgres=> select ssl_is_used();
ssl_is_used — — — — — t (1 row)
If you want to check the ssl_cipher, please use the below command: postgres=> select ssl_cipher();
ssl_cipher — — — — — — — — — — DHE-RSA-AES256-SHA (1 row)
After you are done with the above steps, in order to connect to the RDS instance that has rds.force_ssl parameter set to 1, download the certificate from https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.SSL.html , for your desired region
Import the certificate into your operating system or ec2 where your client is.
If you are using psql in ec2, download the certificate into the ec2.
If you are using pgadmin in your local, please download the cert in your local.
After the above steps, connect to your PostgreSQL DB instance over SSL. When you connect using SSL, your client can choose whether to verify the certificate chain.
If your connection parameters specify sslmode=verify-ca or sslmode=verify-full, then your client requires the RDS CA certificates to be in their trust store or referenced in the connection URL.
This requirement is to verify the certificate chain that signs your database certificate. Configuring the client: https://jdbc.postgresql.org/documentation/head/ssl-client.html SSL Support Postgres: https://www.postgresql.org/docs/11/libpq-ssl.html 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 is different between libpq-based clients (such as psql) and JDBC. The libpq-based clients default to prefer, and JDBC clients default to verify-full.
Use the sslrootcert parameter to reference the certificate, for example sslrootcert=rds-ssl-ca-cert.pem
Please find the example connection string using psql here:
psql -h testpg.cdhmuqifdpib.us-east-1.rds.amazonaws.com -p 5432 “dbname=testpg user=testuser sslrootcert=rds-ca-2019-root.pem sslmode=verify-full”
Please find the example connection string using pgadmin here:
Steps to connect using ssl via pgadmin is documented here with steps: https://www.pgadmin.org/docs/pgadmin4/latest/server_dialog.html
As an additional reference, please refer to this doc too: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/ssl-certificate-rotation-postgresql.html