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