Scenario: Authenticating PostgreSQL Server

Expand all | Collapse all

We recommend that you use a TLS certificate to authenticate the PostgreSQL server. You can use a certificate from a trusted certification authority (CA) or a self-signed certificate. Use a certificate from a trusted CA because a self-signed certificate provides only limited protection.

Administration Server supports both one-way and two-way SSL authentication for PostgreSQL.

Follow these steps to configure SSL authentication for PostgreSQL:

1. Generate a certificate for the PostgreSQL server.

   Run the following commands:

   openssl req -new -x509 -days 365 -nodes -text -out psql.crt -keyout psql.key -subj "/CN=psql"

   chmod og-rwx psql.key

2. Generate a certificate for the Administration Server.

   Run the following commands. The CN value should match the name of the user that connects to PostgreSQL on behalf of the Administration Server. The username is set to postgres by default.

   openssl req -new -x509 -days 365 -nodes -text -out postgres.crt -keyout postgres.key -subj "/CN=postgres"

   chmod og-rwx postgres.key

3. Configure client certificate authentication.

   Modify pg_hba.conf as follows:

   hostssl all all 0.0.0.0/0 md5

   Ensure that pg_hba.conf doesn't include a record that starts with host.

4. Specify the PostgreSQL certificate.

   One-way SSL authentication

   Modify postgresql.conf as follows (specify the correct path to the .crt and .key files):

   listen_addresses ='*'

   ssl = on

   ssl_cert_file = 'psql.crt'

   ssl_key_file = 'psql.key'

   Two-way SSL authentication

   Modify postgresql.conf as follows (specify the correct path to the .crt and .key files):

   listen_addresses ='*'

   ssl = on

   ssl_ca_file = '<postgres.crt>'

   ssl_cert_file = '<psql.crt>'

   ssl_key_file = '<psql.key>'

5. Restart the PostgreSQL daemon.

   Run the following command:

   systemctl restart postgresql-14.service

6. Specify the server flag for the Administration Server.

   One-way SSL authentication

   Navigate to the ServerFlags directory and create a file that corresponds to the KLSRV_POSTGRES_OPT_SSL_CA server flag:

   cd /etc/opt/kaspersky/klnagent_srv/1093/1.0.0.0/ServerFlags/

   mkfile KLSRV_POSTGRES_OPT_SSL_CA

   In the created file, specify the path to the psql.crt file.

   Two-way SSL authentication

   Navigate to the ServerFlags directory and create files that correspond to the server flags:

   cd /etc/opt/kaspersky/klnagent_srv/1093/1.0.0.0/ServerFlags/

   mkfile KLSRV_POSTGRES_OPT_SSL_CA

   mkfile KLSRV_POSTGRES_OPT_SSL_CERT

   mkfile KLSRV_POSTGRES_OPT_SSL_KEY

   Edit the created files as follows:

   • KLSRV_POSTGRES_OPT_SSL_CA: specify the path to the psql.crt file.
   • KLSRV_POSTGRES_OPT_SSL_CERT: specify the path to the postgres.crt file.
   • KLSRV_POSTGRES_OPT_SSL_KEY: specify the path to the postgres.key file.

   If the postgres.key requires a passphrase, create a KLSRV_POSTGRES_OPT_TLS_PASPHRASE file in the ServerFlags folder and specify the passphrase in it.

7. Restart the Administration Server service.