Re: How to enable SSL for PostgreSQL running on Windows 10 using certificates created by openssl
Please find below in-line comments regarding your concern :
1 :- Why is that we need not configure anything from client end when we are using OpenSSL? Ans : PostgreSQL has native support for using SSL connections to encrypt client/server communications for increased security. This requires that OpenSSL is installed on both client and server systems and that support in PostgreSQL is enabled at build time.
2:- Without the CA's certificate how's client verifying the Server's certificate? Ans : If the server requests a trusted client certificate, libpq/client will send the certificate stored in file ~/.postgresql/postgresql.crt in the user's home directory. The certificate must be signed by one of the certificate authorities (CA) trusted by the server. A matching private key file ~/.postgresql/postgresql.key must also be present. The private key file must not allow any access to world or group; achieve this by the command chmod 0600 ~/.postgresql/postgresql.key. On Microsoft Windows these files are named %APPDATA%\postgresql\postgresql.crt and %APPDATA%\postgresql\postgresql.key, and there is no special permissions check since the directory is presumed secure. The location of the certificate and key files can be overridden by the connection parameters sslcert and sslkey or the environment variables PGSSLCERT and PGSSLKEY.
3:- Shouldn't we keep the CA's certificate in the client machine? Ans : Presuming the client is on Windows : To allow server certificate verification, the certificate(s) of one or more trusted CAs must be placed in the file %APPDATA%\postgresql\root.crt. Certificate Revocation List (CRL) entries are also checked if the file %APPDATA%\postgresql\root.crl exists
File : %APPDATA%\postgresql\postgresql.crt Contents : client certificate Effect : requested by server
File : %APPDATA%\postgresql\postgresql.key Contents : client private key Effect : proves client certificate sent by owner; does not indicate certificate owner is trustworthy
4:- If we are using Trusted Root CA signed certificate how's the procedure different? Ans : By default, PostgreSQL will not perform any verification of the server certificate. The client must be able to verify the server's identity via a chain of trust. A chain of trust is established by placing a root (self-signed) certificate authority (CA) certificate on one computer and a leaf certificate signed by the root certificate on another computer. It is also possible to use an "intermediate" certificate which is signed by the root certificate and signs leaf certificates.
Once a chain of trust has been established, there are two ways for the client to validate the leaf certificate sent by the server. 1. If the parameter sslmode is set to verify-ca, libpq will verify that the server is trustworthy by checking the certificate chain up to the root certificate stored on the client. 2. If sslmode is set to verify-full, libpq/client will also verify that the server host name matches the name stored in the server certificate.
The SSL connection will fail if the server certificate cannot be verified. verify-full is recommended in most security-sensitive environments.