Page Comparison

The article describes how to configure Central Server and Security Server to use mutual TLS (mTLS) for database connection so it would be more secure.

Prerequisites

To configure Central Server or Security Server to use mTLS for the database connections, signed certificates should be prepared to all sides.

For development purposes the self signed root CA can be used. The sample instructions how to generate certificates is provided at the bottom.

PostgreSQL

1. Modify postgresql.conf configuration file to enable ssl and provide paths to certificates:

   Code Block
   language none
   ssl = on ssl_cert_file = '<path to server certificate>' ssl_key_file = '<path to server key>' ssl_ca_file = '<path to root CA>'

2. Modify pg_hba.conf configuration file. Add the hostssl (or change the existing host to hostssl)entries with clientcert authentication option. This option can be set to verify-ca or verify-full. Both options require the client to present a valid (trusted) SSL certificate. More details can be found here https://www.postgresql.org/docs/current/auth-pg-hba-conf.html

   Code Block
   language none
   hostssl all all 127.0.0.1/32 scram-sha-256 clientcert=verify-ca

3. Restart PostgreSQL service

   Code Block
   language bash
   sudo service postgresql restart

Central Server (< 7.3.0)

Steps to configure Central Server to use mTLS for database connections:

1. Modify /etc/xroad/db.properties. Provide paths to root CA and client certificates.

   Code Block
   database=centerui_production?sslmode=verify-ca&sslrootcert=<ROOT_CA>&sslcert=<PATH_TO_CLIENT_CERT>&sslkey=<PATH_TO_CLIENT_KEY>

   Note. JDBC client supports key file format PKCS-12 or PKCS-8. To convert PEM key the following command can be used:

   Code Block
   language bash
   openssl pkcs8 -topk8 -inform PEM -in client.key -outform DER -out client.pk8 -nocrypt

   Note:

2. We should consider to update db.properties file and provide jdbc connection url (like in Security Server). The database property is not supposed to be used in this way.Restart the Central Server

   Code Block
   supervisorctl restart xroad-center

3. Verify Central Server started successfully.

Security Server / Central Server (>= 7.3.0)

Steps to configure Security Server to use mTLS for database connections:

1. Edit /etc/xroad/db.properties. Modify the connection.url and provide paths to client certificates.

   Code Block
   serverconf.hibernate.connection.url = jdbc:postgresql://127.0.0.1:5432/serverconf?sslmode=verify-ca&sslrootcert=<ROOT_CA>&sslcert=<PATH_TO_CLIENT_CERT>&sslkey=<PATH_TO_CLIENT_KEY>

   Note. JDBC client supports key file format PKCS-12 or PKCS-8.

2. Restart Security Server

   Code Block
   supervisorctl restart xroad-proxy xroad-proxy-ui-api

3. Verify Security Server started successfully.

Creating certificates

Instructions how to create certificates with self signed root CA.

1. Create root CA private/public keys and certificate signing request (CSR):

   Code Block
   language bash
   openssl req -new -nodes -text -out root.csr -keyout root.key -subj "/CN=rootCA"

   Sign the root certificate:

   Code Block
   language bash
   openssl x509 -req -in root.csr -text -days 3650 -signkey root.key -out root.crt

2. Create a server certificate and sign by root CA:

   Code Block
   language bash
   openssl req -new -nodes -text -out server.csr -keyout server.key -subj "/CN=localhost" openssl x509 -req -in server.csr -text -days 365 -CA root.crt -CAkey root.key -CAcreateserial -out server.crt

   server.key and server.crt will be used on PostgreSQL server.

3. Create and sign client certificate for client. Create a separate certificate for each client.

   Code Block
   openssl req -new -nodes -text -out client.csr -keyout client.key -subj "/CN=localhost" openssl x509 -req -in client.csr -text -days 365 -CA root.crt -CAkey root.key -CAcreateserial -out client.crt

   client.key and client.crt will be used on the client.

Links

More details on configuring PostgreSQL can be found in official PostgreSQL documentation:

• https://www.postgresql.org/docs/15/ssl-tcp.html

\uD83D\uDCCB Related articles