Introduction
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 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 excluded. Add an environment action to your 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 the client to indicate the 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 the 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:
|
Provider |
Certificate |
Docs |
|---|---|---|
|
Amazon RDS |
||
|
Google Cloud SQL |
In Account |
|
|
Digital Ocean |
In Account |
|
|
Citus Data |