"Update Your Amazon RDS SSL/TLS Certificates by October 31, 2019"

Solution 1:

The default sslmode for PostgreSQL is prefer which means it will encrypt the connection with the certificate provided by the server but will not verify it. If I were to change the sslmode setting to verify-ca or verify-full then I would need to install the intermediate certs in a particular directory and then it would do proper verification.

As I'm not concerned about a MITM attack on my VPC, I don't think I'll bother switching to the 'verify' modes.

Solution 2:

The RDS certificate in question is an intermediate certificate. You might also know it as a CA certificate. When I use MySQL Workbench, for instance, I have to specify that

1. I want to use SSL(TLS)
2. Use the RDS CA chain file to verify the certificate

How did the SSL/TLS properly work initially if I had never installed the [certificate]?

Depends how your system is set up. CA certificates simply provide a trusted authority for the presented certificate. It's quite possible to set up something that will accept any certificate at all, without attempting to verify it (i.e. you use a self-signed certificate). Another option is there is something already in your CA store that trusts it implicitly. This is less likely, but not impossible.

If you're doing this locally (such as you have an EC2 instance in the same VPC as your RDS instance) you might not even need SSL.

If I've changed the RDS database instance to use rds-ca-2019 and it seems to "just work" is there anything more I need to do?

No. It's confusing, but if you're connecting and not getting any certificate errors, I wouldn't worry about it.

---

Solution 3:

In ubuntu, add the ca-cert as described here: https://askubuntu.com/questions/73287/how-do-i-install-a-root-certificate

wget https://s3.amazonaws.com/rds-downloads/rds-ca-2019-root.pem
sudo mkdir /usr/local/share/ca-certificates/aws
sudo mv rds-ca-2019-root.pem /usr/local/share/ca-certificates/aws
sudo openssl x509 \ 
    -in /usr/local/share/ca-certificates/aws/rds-ca-2019-root.pem \
    -inform PEM \
    -out /usr/local/share/ca-certificates/aws/rds-ca-2019-root.crt
sudo update-ca-certificates

: sudo update-ca-certificates
Updating certificates in /etc/ssl/certs...
1 added, 0 removed; done.
Running hooks in /etc/ca-certificates/update.d...

Adding debian:rds-ca-2019-root.pem
done.
done.

This link describes how to set the ssl cert location for a django application: https://www.digitalocean.com/community/questions/how-to-connect-managed-database-postgres-with-ssl-mode-varify-full-in-django-app

DATABASES = {
'default': {
    'ENGINE': 'django.db.backends.postgresql_psycopg2',
    'NAME': '<name>',
    'USER': '<user>',
    'PASSWORD': '<password>',
    'HOST' : '<host>',
    'PORT' : '25060',   
    'OPTIONS':{
        'sslmode':'verify-full',
        'sslrootcert': os.path.join(BASE_DIR, 'ca-certificate.crt')

}

This post in Stackoverflow https://stackoverflow.com/a/58214922/1415254 describes how to connect using commandline parameters for psql.

psql "host={hostname} sslmode=prefer sslrootcert={ca-cert.pem} \
sslcert={client-cert.pem} sslkey={client-key.pem} port={port} user={user} \
dbname={db}"

Also

psqlrc and ~/.psqlrc

Unless it is passed an -X or -c option, psql attempts to read and execute commands
from the system-wide startup file (psqlrc) and then the user's personal startup
file (~/.psqlrc), after connecting to the database but before accepting normal
commands. These files can be used to set up the client and/or the server to taste,
typically with \set and SET commands.

And more detail here (right at the end): https://info.crunchydata.com/blog/ssl-certificate-authentication-postgresql-docker-containers

# the first parameter specifies which TLS mode to use to connect
export PGSSLMODE="verify-full"
# the following two parameters point to the client key/certificate
export PGSSLCERT="`pwd`/certs/client.crt"
export PGSSLKEY="`pwd`/keys/client.key"
# this parameter points to the trusted root CA certificate
export PGSSLROOTCERT="`pwd`/certs/ca.crt"

Full list of environment variables here: https://www.postgresql.org/docs/9.2/libpq-envars.html

---

Solution 4:

Just like the post OP answered himself, postgres has the default sslmode set to prefer, and this is the excerpt from the doc:

I don't care about encryption, but I wish to pay the overhead of encryption if the server supports it.

So by default, the pg driver will not verify the certificates unless specified; and this is exactly why to OP's original questions, it works out of the box at the beginning, and also works after the RDS is upgraded to rds-ca-2019.

One of the environment variables for connecting to postgres is via DATABASE_URL, in the form of

postgres://username:password@host/database?sslmode=verify-full&sslrootcert=config/ca/rds-combined-ca-bundle.pem

and this is where you can specify the sslmode and sslrootcert if you decide to verify the intermediate certs. The content of sslrootcert should be one of the following...

https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem
https://s3.amazonaws.com/rds-downloads/rds-ca-2019-root.pem

HTH

---

Solution 5:

There are two sides to this certificate update:

1. On the server side: Any RDS instances created before 2019-11-01 require a restart. This incurs an outage of ~5 minutes. This will update them to have the new certificates in place.
2. If your clients use SSL (off/prefer by default), then you need to update the certificates (intermediate+chain) from AWS on wherever your clients run.

Postgres uses "prefer" as the default way for clients to connect, which means they will try SSL if available, but fall back if not. So existing clients with default connection configuration will continue to operate.