For increased security, PostgreSQL natively supports using SSL connections to encrypt client/server communications. PostgreSQL does not perform any server certificate verification by default.
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
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.
|~/.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|
In the previous versions of Rails,
when we configure the
rails dbconsole does not export the appropriate SSL-related environment variables for PostgreSQL,
resulting in SSL authentication failure.
Rails 7 added PGSSLMODE, PGSSLCERT, PGSSLKEY and PGSSLROOTCERT to
start method of
DBConsole class to
load SSL related environment variables from database config
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