Enable SSL encryption

Eugenia Kuzina

ADPG supports SSL to encrypt traffic between the client and server. To use this feature, OpenSSL must be installed on both the server and the client systems. The SSL support is enabled in ADPG during its build. To use SSL, you need to obtain certificates and configure SSL via ADCM UI.

To start in the SSL mode, files containing the server certificate and a private key must exist. By default, these files are expected to be named server.crt and server.key, respectively, and are located in the ADPG data directory (/pg_data1/adpg16). Other names and locations can be specified using the ssl_cert_file and ssl_key_file configuration parameters in ADCM UI.

If a private key is protected by a passphrase, the server will prompt for the passphrase and will not start until it is entered. Using a passphrase disables the ability to change the server’s SSL configuration without a server restart.

Use client certificates

To require the client to provide a trusted certificate, place the certificates of the root certification authorities (CAs) that you trust in a file in the data directory, set the ssl_ca_file parameter in ADCM UI, and add the authentication option clientcert=verify-ca or clientcert=verify-full to the appropriate hostssl records in the PG_HBA section as described in the Configure SSL via ADCM section. A certificate will be requested from the client during the SSL connection startup. For information on how to set up certificates on the client, see SSL Support.

For a hostssl entry with clientcert=verify-ca, the server verifies that the client’s certificate is signed by one of the trusted certificate authorities. If clientcert=verify-full is used, the server does not only verify the certificate chain, but it also checks whether the user name or its mapping matches cn (Common Name) of the provided certificate. Note that certificate chain validation is always ensured when the cert authentication method is used.

Intermediate certificates that chain up to existing root certificates can also be included in the file specified by the ssl_ca_file parameter if you wish to avoid storing them on clients. Certificate Revocation List (CRL) entries are also checked if the ssl_crl_file or ssl_crl_dir parameter is set via ADCM in the same way as other SSL parameters.

The clientcert authentication option is available for all authentication methods, but only in PG_HBA records specified as hostssl. When clientcert is not used, the server verifies the client certificate against its CA file only if a client certificate is provided and the CA is configured.

There are two approaches to ensuring that users provide a certificate when logging in:

  • Use the cert authentication method for hostssl entries in the PG_HBA records. The certificate will be used for both authentication and security of the SSL connection. See Certificate authentication. (It is not necessary to specify any clientcert options explicitly when using the cert authentication method.) In this case, cn (Common Name) provided in the certificate is checked against the user name or an applicable mapping.

  • Use any authentication method for hostssl entries with the verification of client certificates by setting the clientcert authentication option to verify-ca or verify-full. The former option only enforces that the certificate is valid, while the latter also ensures that cn (Common Name) in the certificate matches the user name or an applicable mapping.

Create SSL certificates

Instead of CA certificates, you can also create your own self-signed certificates, which can be used for development and testing, but are not recommended for use in a production environment.

Generate an SSL certificate for the server

For the server, you can create a self-signed certificate (in which Issuer and Subject have the same value) or a certificate signed by a root certificate authority that is also created on your server.

To create server certificates, change the current working directory to the ADPG data directory (by default, /pg_data1/adpg16):

$ cd /pg_data1/adpg16

Generate a self-signed SSL certificate

To create a simple self-signed certificate for the server that is valid for 365 days, use the following OpenSSL command, replacing <dbhost.yourdomain.com> with the server’s host name, for example,CN=ees-adpg-adpg:

$ openssl req -new -x509 -days 365 -nodes -text -out server.crt \
  -keyout server.key -subj "/CN=<dbhost.yourdomain.com>"

The following files are created:

  • server.crt — a server certificate file;

  • server.key — a private key file.

On Unix systems, the permissions on server.key must disallow any access by a group and other users. Also, it is necessary to set the ownership of the server.key and server.crt files to the postgres user and group. You can use the following commands:

$ chmod 600 server.key
$ chown postgres:postgres server.key server.crt

Generate a root certificate and sign the server certificate with it

To create a server certificate that clients can verify, create a certificate request (Certificate Signing Request, CSR) and public/private key files. It is also necessary to set the required permissions.

$ openssl req -new -nodes -text -out root.csr  -keyout root.key -subj "/CN=<root.yourdomain.com>"
$ chmod 600 root.key

Sign the request with the key to create a root certificate authority. Note that the path to the openssl.cnf file (/etc/ssl/openssl.cnf) can be different in your environment:

$ openssl x509 -req -in root.csr -text -days 3650 \
  -extfile /etc/ssl/openssl.cnf -extensions v3_ca \
  -signkey root.key -out root.crt

$ chmod 600 root.crt

Create a server certificate signed by the new root certificate authority:

$ openssl req -new -nodes -text -out server.csr -keyout server.key -subj "/CN=<dbhost.yourdomain.com>"
$ chmod 600 server.key
$ openssl x509 -req -in server.csr -text -days 365 -CA root.crt -CAkey root.key -CAcreateserial -out server.crt

Remove the CSR file:

$ rm server.csr

The server.crt and server.key files should be stored on the server, and root.crt should be stored on the client at the following path: ~/.postgresql/root.crt. (See SSL client file usage.) So, the client can verify that the server’s certificate was signed by its trusted root certificate.

The root.key should be stored offline for use in creating future certificates.

Generate an SSL certificate for clients

This example uses the server certificate from the Generate a root certificate and sign the server certificate with it example.

Create a certificate request (CSR) where CN is a database user name:

$ openssl req -days 3650 -new -nodes -subj "/CN=<database_username>" -keyout client.key -out client.csr
$ chmod 600 client.key

Create a signed certificate for the client using the root certificate:

$ openssl x509 -days 3650 -req  -CAcreateserial -in client.csr -CA root.crt -CAkey root.key -out client.crt

Remove the CSR file:

$ rm client.csr

The client.crt and client.key files should be stored on the client at the following path: ~/.postgresql/. See SSL client file usage.

Configure SSL via ADCM

To configure SSL, you need to use the parameters listed in the table below. The Value field of the table contains the file names from the example above.

Name Value Description

ssl

on

Enables SSL connections

ssl_ca_file

root.crt

Specifies the name and path of the file containing the SSL server certificate authority (CA). The path should be relative to the ADPG data directory. The parameter is only required if the client certificate verification is expected. The default path is /pg_data1/adpg16

ssl_cert_file

server.crt

Specifies the name of the file containing the SSL server certificate. The path should be relative to the ADPG data directory. The default path is /pg_data1/adpg16

ssl_key_file:

server.key

Specifies the name and path of the file containing the SSL server private key. The path should be relative to the ADPG data directory. The default path is /pg_data1/adpg16

To configure SSL via ADCM, go to the Clusters page, select an ADPG cluster, and switch to the Services tab. Select the ADPG service, expand the ADPG configurations node in the Configuration tree, and click the postgresql.conf field.

The postgresql.conf field
The postgresql.conf field

Add SSL parameters to the text field that opens. The parameters with values from the example above will look like this:

ssl_cert_file = '/pg_data1/adpg16/server.crt'
ssl_key_file = '/pg_data1/adpg16/server.key'
ssl_ca_file = '/pg_data1/adpg16/root.crt'
ssl = on
SSL settings in the postgresql.conf field
SSL settings in the postgresql.conf field

Click Apply to apply the changes. After the postgresql.conf field closes, click Save to store the configuration.

Next, run the Reconfigure & Restart action to apply changes. This should be done before adding the SSL connection settings to the PG_HBA section.

Allow SSL connections for the ADPG server in PG_HBA. For example, add the following line to the PG_HBA node of the Configuration tree:

hostssl     all     all     0.0.0.0/0     cert

This record allows all users (all) to utilize SSL connections (hostssl) to all databases (all) from all IPv4 addresses (0.0.0.0/0). Authentication is performed using SSL client certificates (cert).

Save the changes and run the Reconfigure and Restart action to apply new settings.

It is also possible to run the Reconfigure and Restart action only once after configuring PG_HBA by setting the Force reconfigure and restart flag. Be careful when using this flag, as it disables validation of configuration files, and if there is an error in the configuration settings, an error message will not be displayed.

Found a mistake? Seleсt text and press Ctrl+Enter to report it