I am trying to connect to a serverless Aurora serverless PostGRESQL RDS instance with SSL, but in vain.
My RDS lies on a private subnet group (2 private subnets) and I am trying to connect from an EC2 instance lying in public subnet.
What works:
psql -h "mydb.cluster-cyjoviss5z9y.eu-central-1.rds.amazonaws.com" -p 5432 -d "dbname=mydb user=postgres password=NVMXeyQRXIzW50xx"
psql (9.2.24, server 10.7)
WARNING: psql version 9.2, server version 10.0.
Some psql features might not work.
SSL connection (cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256)
Type "help" for help.
mydb=
However, this doesn't work: psql -h "mydb.cluster-cyjoviss5z9y.eu-central-1.rds.amazonaws.com" -p 5432 -d "dbname=mydb user=postgres password=NVMXeyQRXIzW50xx sslmode=verify-ca"
Error: psql: root certificate file "/home/ec2-user/.postgresql/root.crt" does not exist
OR psql: SSL error: certificate verify failed
The same above command with sudo works, but when I verify the connection it still says:
mydb=> select ssl_is_used();
ssl_is_used
-------------
f
(1 row)
So I downloaded the root certificate from here, as suggested in this page. It even mentions that AWS Serverless instances don't require the root certificate to be downloaded.
I converted the PEM file into CRT using this. I tried giving the root.crt file with 777 and 600 permissions, both didn't help.
Any hints on what can possibly go wrong here?
