Can I get some helps to solve a puzzle: I have noticed that the ca certificate on AWS RDS has expired on 8/22/2024. However, I am still able to make fresh connection through psql and dbeaver, and the connections show as SSL true from pg_stat_ssl. the parameter group for the RDS has rds.force_ssl=1 and ssl=1. How come the connections are all SSL enabled with an expired SSL certificate?
select s.*, query from pg_stat_ssl s inner join pg_stat_activity a using(pid) where usename<>'rdsadmin' and application_name='psql';
pid | ssl | version | cipher | bits | client_dn | client_serial | issuer_dn | query
------+-----+---------+-----------------------------+------+-----------+---------------+-----------+--------------------------------------------------------------------------------------------------------------------------------------
3075 | t | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 | 256 | | | | select s.*, query from pg_stat_ssl s inner join pg_stat_activity a using(pid) where usename<>'rdsadmin' and application_name='psql';
(1 row)
See the different SSL modes for PostgreSQL connections here.
You are apparently connecting with an SSL mode setting of allow, prefer, or require. None of which actually check that the SSL certificate of the database server is valid.
You would have to be connecting with verify-ca or perhaps verify-full in order to get an error about the expired RDS SSL certificate.