Connecting to PostgreSQL via the SSL option, requires a setup on the PostgreSQL database first.
There are many articles on this topic, for more information go to the official PostgreSQL site
PostgreSQL supports different ways to connect via SSL.
The overhead really depends on the mode you are using. First let’s take a look at the general mechanism:
In the following example the prefer or require mode is shown. In this case you do need to verify the CA as this check is exclude. Add an environment action to you location. Add the location of the certificate, postgresql.crt & the key, postgresql.key.
Name=HVR_PQ_CONNECT_STRING_ADD /Value="sslmode=prefer sslcert=C:\postgresql.crt sslkey=C:\postgresql.key"
File name | Purpose of the file | Remarks |
ssl_cert_file ($PGDATA/server.crt) | server certificate | sent to client to indicate server’s identity |
ssl_key_file ($PGDATA/server.key) | server private key | proves server certificate was sent by the owner; does not indicate certificate owner is trustworthy |
ssl_ca_file | trusted certificate authorities | checks that client certificate is signed by a trusted certificate authority |
In case you want the sslmode: verify-ca or verify-full add to the connection string also the ca certificate file.
Name=HVR_PQ_CONNECT_STRING_ADD /Value="sslmode=prefer sslcert=C:\postgresql.crt sslkey=C:\postgresql.key sslrootcert=c:\serververroot.crt.pem"
Using SSL in cloud environment
To verify an SSL/TLS certificate, the client checks it against a root certificate. Your browser ships with root certificates to verify HTTPS websites. Postgres doesn’t come with any root certificates, so to use verify-full
, you must specify one.
Here are root certificates for a number of providers: