Query Service SSL options
For increased security, Adobe Experience Platform Query Service provides native support for SSL connections to encrypt client/server communications. This document covers the available SSL options for third-party client connections to Query Service and how to connect using the verify-full SSL parameter value.
Prerequisites
This document assumes that you have already downloaded a third-party desktop client application for use with your Platform data. Specific instructions on how to incorporate SSL security when connecting with a third-party client are found in their respective connection guide documentation. For a list of all Query Service supported clients, see the client connections overview.
Available SSL options
Platform supports various SSL options to suit your data security needs and balance the processing overhead of encryption and key exchange.
The different sslmode parameter values provide different levels of protection. By encrypting your data in motion with SSL certificates, it helps to prevent “man-in-the-middle” (MITM) attacks, eavesdropping, and impersonation. The table below provides a breakdown of the different SSL modes available and the level of protection they provide.
NOTEdisable is not supported by Adobe Experience Platform due to the requisite data protection compliance.| sslmode | Eavesdropping protection | MITM protection | Description |
|---|---|---|---|
allow | Yes | No | Encryption is required on all communications. The network is trusted to connect to the correct server. |
prefer | Yes | No | Encryption is required on all communications. The network is trusted to connect to the correct server. |
require | Yes | No | Encryption is required on all communications. The network is trusted to connect to the correct server. Server SSL certificate validation is not required. |
verify-ca | Yes | Depends on CA-policy | Encryption is required on all communications. Server validation is required before data is shared. This requires you to set up a root certificate in your PostgreSQL home directory. Details are provided below |
verify-full | Yes | Yes | Encryption is required on all communications. Server validation is required before data is shared. This requires you to set up a root certificate in your PostgreSQL home directory. Details are provided below. |
NOTEverify-ca and verify-full depends on the policy of the root certificate authority (CA). If you have created your own local CA to issue private certificates for your applications, using verify-ca often provides enough protection. If using a public CA, verify-ca allows connections to a server that somebody else may have registered with the CA. verify-full should always be used with a public root CA.When establishing a third-party connection to a Platform database, you are recommended to use sslmode=require at a minimum to ensure a secure connection for your data in motion. The verify-full SSL mode is recommended for use in most security-sensitive environments.
Set up a root certificate for sever verification
IMPORTANTAlthough this is an annual requirement, on this occasion the root certificate in the chain has also changed as Adobe’s TLS/SSL certificate provider have updated their certificate hierarchy. This can impact certain Postgres clients if their list of Certificate Authorities are missing the root cert. For example, a PSQL CLI client may need to have the root certs added to an explicit file
~/postgresql/root.crt, otherwise this can result in an error. For example, psql: error: SSL error: certificate verify failed. See the official PostgreSQL documentation for more information on this issue.The root certificate to add can be downloaded from https://cacerts.digicert.com/DigiCertGlobalRootG2.crt.pem.
To ensure a secure connection, SSL usage must be configured on both the client and the server before the connection is made. If the SSL is only configured on the server, the client might send sensitive information such as passwords before it is established that the server requires high security.
By default, PostgreSQL does not perform any verification of the server certificate. To verify the server’s identity and ensure a secure connection before any sensitive data is sent (as part of the SSL verify-full mode), you must place a root (self-signed) certificate on your local machine (root.crt) and a leaf certificate signed by the root certificate on the server.
If the sslmode parameter is set to verify-full, libpq will verify that the server is trustworthy by checking the certificate chain up to the root certificate stored on the client. It then verifies that the hostname matches the name stored in the server certificate.
To allow server certificate verification, you must place one or more root certificates (root.crt) in the PostgreSQL file in your home directory. The file path would be similar to ~/.postgresql/root.crt.
