Rails 7 adds SSL support for postgresql in bin/rails dbconsole


For increased security, PostgreSQL natively supports using SSL connections to encrypt client/server communications. PostgreSQL does not perform any server certificate verification by default.

When the sslmode parameter is set to verify-full, libpq will also check that the server hostname matches the name stored in the server certificate. If the server certificate cannot be verified, the SSL connection will fail.

To allow server certificate verification, one or more root certificates must be placed in the file ~/.postgresql/root.crt in the user’s home directory. (In Microsoft Windows the file is named %APPDATA%\postgresql\root.crt). Intermediate certificates are also added to the file if they are needed to link the certificate chain sent by the server to the root certificates stored on the client.

Certificate Revocation List (CRL) entries are also checked if the file ~/.postgresql/root.crl exists (%APPDATA%\postgresql\root.crl in Microsoft Windows).

Below are the files that are relevant to the SSL setup on the client.

File Contents Effect
~/.postgresql/postgresql.crt client certificate sent to server
~/.postgresql/postgresql.key client private key proves client certificate sent by the owner; does not indicate certificate owner is trustworthy
~/.postgresql/root.crt trusted certificate authorities checks that server certificate is signed by a trusted certificate authority
~/.postgresql/root.crl certificates revoked by certificate authorities server certificate must not be on this list
     

Before

In the previous versions of Rails, when we configure the database.yml file, rails dbconsole does not export the appropriate SSL-related environment variables for PostgreSQL, resulting in SSL authentication failure.

After

Rails 7 added PGSSLMODE, PGSSLCERT, PGSSLKEY and PGSSLROOTCERT to start method of DBConsole class to load SSL related environment variables from database config when running bin/rails dbconsole with postgresql.

# config/database.yml

production:
  sslmode: verify-full
  sslcert: client.crt
  sslkey: client.key
  sslrootcert: ca.crt
# Environment variables

PGSSLMODE=verify-full
PGSSLCERT=client.crt
PGSSLKEY=client.key
PGSSLROOTCERT=ca.crt