Installing secure QHB database

When installing a DBMS in an IT infrastructure for testing purposes, you can go with the default settings. However, if you plan to use the DBMS for a long time and/or in a production environment, you should follow several recommendations for installing basic protection for a database instance. All necessary actions can be divided into several stages:

  • Setting up a secure (SSL) connection;
  • Meaningful delimiting of privileges of roles, groups and users in the database;
  • Getting rid of all "default" passwords;
  • Protecting the environment by other means if necessary.

This guide contains:

  • basic protection of the QHB system itself;
  • instruction for setting up a connection via SSL;
  • instruction for issuing certificates for connecting via SSL;
  • examples and recommendations for delimiting the privileges of roles, groups and users.

Setting up a Secure Connection

QHB allows you to configure encryption of data exchanged between nodes using SSL.

Installing the Required Libraries

First, you need to make sure that the required packages/libraries are installed in the system. You can check for openssl using the command:

openssl version

If the required package is missing, you should install it. For example, on CentOS 7, you need to execute the command:

yum install openssl

Example command for installation on Ubuntu:

apt-get install openssl

The openssl package must be installed both on the DBMS server and on the computer from which the client connection is made.

SSL-Encrypting of Connection

To encrypt the connection between the client and the server, the corresponding server and client(s) certificates must be created. In this context, the key is regarded as the private key, and the certificate is regarded as the public key.

IMPORTANT!
It is important to understand that the private key must be kept secret and not distributed.

Creating a Root Key and Root Certificate

# This command creates a 1024-bit root key file.
openssl genrsa -out rootCA.key 1024
# This command creates a certificate 'rootCA.crt' with a lifetime of 365 days
openssl req -x509 -new -key rootCA.key -days 365 -out rootCA.crt -subj '/C=XX/L=Default City/O=Default Company Ltd/CN=root'

The first command, genrsa, generates a private key. -out rootCA.key 1024 is the file that will be generated after the command is executed. The number at the end means the size of the key sequence in bits, you can use 2048 or 4096.

The second command, req, generates the certificate. It requires the following arguments:

  • -x509 — specifies the need to create a root or test certificate.
  • -new — specifies the need to create a new certificate.
  • -key rootCA.key — specifies the private key file.
  • -days 365 — certificate lifetime; if this parameter is not specified, a period of 30 days will be specified.
  • -out rootCA.crt — public key file that will be created when the command completes.
  • -subj — information for the certificate being created. Contains the following fields:
    • C=XX — country code.
    • L=Default City — city.
    • O=Default Company Ltd — company name.

You can type any data, but for future convenience it is advisable to enter the real ones.

Generating a rootCA Signed Server Certificate

# This command creates a 1024-bit server key file
openssl genrsa -out server.key 1024
# An unsigned server certificate (certificate request) is created
openssl req -new -key server.key -out server.csr -subj '/C=XX/L=Default City/O=Default Company Ltd/CN=servername'
# The server certificate is signed by the root certificate
openssl x509 -req -in server.csr -CA rootCA.crt -CAkey rootCA.key -CAcreateserial -out server.crt -days 365

The arguments used in the first two commands are similar to the previous block, but the CN=servername field is added to the -subj argument of the req command, which is the object's name. You can type any, but for future convenience it is advisable to use the server name.

The third command, x509, is used in this context to sign the certificate. It requires the following arguments:

  • -req — means that an unsigned certificate is supplied as input.
  • -in server.csr — the file that needs to be signed.
  • -CA rootCA.crt — the file that will be used to create the signature.
  • -CAkey rootCA.key — private key file, the certificate of which will be used to sign.
  • -CAcreateserial — creating a CA serial number file.
  • -out server.crt — public key file that will be created when the command completes
  • -days 365 — certificate lifetime; if this parameter is not specified, a period of 30 days will be specified.

Generate a rootCA Signed Client Certificate

openssl genrsa -out qhb.key 1024
openssl req -new -key qhb.key -out qhb.csr -subj '/C=XX/L=Default City/O=Default Company Ltd/CN=servername'
openssl x509 -req -in qhb.csr -CA rootCA.crt -CAkey rootCA.key -CAcreateserial -out qhb.crt -days 365

The arguments used in the command are similar to the previous block. This certificate will be required when setting up a client connection to the database.

Copying the Server Certificate, Server Private Key and rootCA

Copy the server certificate, server private key and rootCA to the QHB cluster catalog and after copying, assign them read-only access privileges:

cd $PGDATA
chmod 0400 server.crt server.key rootCA.crt

Enabling SSL in QHB

In the psql interactive terminal execute the commands:

qhb=# ALTER SYSTEM SET ssl TO 'ON';
qhb=# ALTER SYSTEM SET ssl_ca_file TO 'rootCA.crt';
qhb=# ALTER SYSTEM SET ssl_cert_file TO 'server.crt';
qhb=# ALTER SYSTEM SET ssl_key_file TO 'server.key';

These parameters can also be changed in the qhb.conf file. See Section SSL for details.

Using a Specific Cipher

Note
This step is optional. Perform it if there is a need to use a specific cipher.

In the psql interactive terminal execute the command:

qhb=# ALTER SYSTEM SET ssl_ciphers TO 'desired_cipher';

In the desired_cipher parameter, you must write a set of parameters separated by a colon (:). By default, this string contains a set of parameters HIGH:MEDIUM:+3DES:!aNULL. The default value is the most optimal, if you do not have special security requirements.

Explanations on standard parameters:

  • HIGH — cipher suites that use ciphers from the HIGH group (e.g. AES, Camellia, 3DES).
  • MEDIUM — cipher suites using ciphers from the MEDIUM group (e.g. RC4, SEED).
  • +3DES — The default HIGH cipher order in OpenSSL is incorrectly defined. It places 3DES above AES128, which is incorrect since it is considered less secure than AES128 and is much slower. This entry changes the cipher order so that Triple DES is used after the High and Medium cipher suites.
  • !aNULL — Disables anonymous cipher suites that do not require authentication. Such suites are vulnerable to man-in-the-middle attacks and should not be used.

These parameters can also be changed in the qhb.conf file. See Section SSL for details.

Restarting QHB Server

psql -c "select pg_reload_conf();"

или:

qhb_ctl -D DATADIR restart -mf

Editing qhb_hba.conf File

To enable ssl, you need to replace host with hostssl in the file $PGDATA/qhb_hba.conf:

On the server side:

# IPv4 local connections:
# host all all 127.0.0.1/32 trust
hostssl all all 127.0.0.1/32 trust

Similar actions must be performed on the client side:

# IPv4 local connections:
# host all all 127.0.0.1/32 trust
hostssl all all 127.0.0.1/32 trust
hostssl all all app_server_1/32 md5
hostssl all all app_server_2/32 md5

A detailed description of the qhb_hba.conf file is provided in the pg_hba_file_rules catalog description.

Applying the Updated Configuration

After editing the qhb_hba.conf file, in the Editing qhb_hba.conf File step, execute:

psql -c "select pg_reload_conf();"

Testing the Connection

If everything is set up correctly, the connection should look like this:

psql -h localhost

psql (10.4)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
#

If it looks like this:

psql (10.4)
Type "help" for help.
#

i.e. there is no string specifying the SSL protocol: SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off), then the configuration was not completed.

Enabling Client Certificate Authentication

Copying the Certificate to the Client Side

In this step, you need to copy the client certificate that was created in the Creating a Client Certificate step. For example, the following code string shows copying to a remote machine with the address 192.168.0.13:

scp qhb.crt qhb.key rootCA.crt qhb@192.168.0.13:/usr/local/qhb

Next, you should grant the read-only permissions for these files on the remote machine:

cd /usr/local/qhb
chmod 0400 qhb.crt qhb.key rootCA.crt

Setting up qhb_hba.conf

To use client authentication by certificate, you need to add the string on the server with the client connection from the remote machine qhb_hba.conf. For example, the IP of the machine from the previous step is used:

hostssl all all 192.168.0.13/32 cert clientcert=1

The difference from the Editing qhb_hba.conf File step of the previous part is that in this step a certificate is required to confirm that the connection is between two trusted parties. In order for the changes to take effect, you need to restart QHB.

qhb_ctl -D DATADIR reload

Checking the Remote Connection

As in the Testing the Connection step of the previous section, after entering the command:

psql "port=5432 host=192.168.0.12 user=qhb sslcert=/usr/local/qhb/qhb.crt sslkey=/usr/local/qhb/qhb.key sslrootcert=/usr/local/qhb/rootCA.crt sslmode=require"

the text in the console should look like this:

psql (10.4)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-DES-CBC3-SHA, bits: 168, compression: off)
Type "help" for help.
#

If there is no mention of SSL or the connection does not occur, then most likely not all steps were performed correctly.

Creating Group and User Roles

In QHB, as in any other DBMS, it is necessary to ensure the delimiting of privileges. To understand this issue, below are described the methods for creating users, groups and roles.

Create a Role or User

The QHB user is a role with the CONNECT privilege. The CREATE USER and CREATE ROLE commands are suitable for creation a new QHB user. However, the user must have the LOGIN role. The login role is assigned to the user using any of the following three approaches in the psql interactive terminal:

qhb=# CREATE USER percuser WITH ENCRYPTED PASSWORD 'secret';

qhb=# CREATE ROLE percuser WITH ENCRYPTED PASSWORD 'secret';

qhb=# CREATE ROLE percuser;
qhb=# ALTER ROLE percuser WITH LOGIN ENCRYPTED PASSWORD 'secret';

After entering any of the above examples QHB automatically adds the code to the one below

qhb=# CREATE ROLE percuser
WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS
ENCRYPTED PASSWORD 'secret';

In this example, you can see that there are no roles that would allow you to modify the database. After creating a user or role, you can use the following query to find out if the user can log in (has the permission to connect):

qhb=# select rolcanlogin from pg_roles where rolname = 'percuser';
rolcanlogin
-------------
t
(1 row)

We see that the query returned true, meaning that this role/user can connect to the database. For example, the query below shows a role that cannot connect:

qhb=# CREATE ROLE percrole;
CREATE ROLE

qhb=# select rolcanlogin from pg_roles where rolname = 'percrole';
rolcanlogin
-------------
f
(1 row)

Differences Between Creating a Role and Creating a User

When using CREATE USER the login capability role is automatically added.

qhb=# CREATE USER percuser WITH ENCRYPTED PASSWORD 'secret';
CREATE ROLE

qhb=# select rolcanlogin from pg_roles where rolname = 'percuser';
rolcanlogin
-------------
t
(1 row)

And when using CREATE ROLE it must be added manually:

qhb=# CREATE ROLE percuser WITH ENCRYPTED PASSWORD 'secret';
CREATE ROLE

qhb=# select rolcanlogin from pg_roles where rolname = 'percuser';
rolcanlogin
-------------
f
(1 row)

qhb=# ALTER ROLE percuser WITH LOGIN;
ALTER ROLE

qhb=# select rolcanlogin from pg_roles where rolname = 'percuser';
rolcanlogin
-------------
t
(1 row)

Creating a Group and Group Role

In QHB, a role can inherit another role. This means that one role can gain access using the privileges of another role. This can be achieved using the GRANT or INHERIT keywords.

It is always recommended to grant access privileges to users using group roles.

Let's look at an example:

  • 1 database db1;
  • 2 schemas sh1 и sh2.

Let's say we need to create 10 individual users and 10 application users with the following requirements:

  • The five application users must have read-only access to the tables in the sh1 schema and read/write access to the tables in the sh2 schema.
  • The remaining five application users must have read/write access to the tables in the sh1 schema and read-only access to the tables in the sh2 schema.
  • All 10 individual user accounts can have read-only access to tables in both schemas.

In this situation, you can create four group roles, for example:

sh1_readonly:

qhb=# CREATE ROLE sh1_readonly;
qhb=# GRANT USAGE, SELECT ON ALL TABLES IN SCHEMA sh1 TO sh1_readonly;

sh1_readwrite:

qhb=# CREATE ROLE sh1_readwrite;
qhb=# GRANT USAGE, SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA sh1 TO sh1_readwrite;

sh2_readonly:

qhb=# CREATE ROLE sh2_readonly;
qhb=# GRANT USAGE, SELECT ON ALL TABLES IN SCHEMA sh2 TO sh2_readonly;

sh2_readwrite:

qhb=# CREATE ROLE sh2_readwrite;
qhb=# GRANT USAGE, SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA sh2 TO sh2_readwrite;

Now you can grant these group roles to users according to the three requirements above.

qhb=# GRANT sh1_readonly,sh2_readonly TO user1, user2, user3, ..., user10;
qhb=# GRANT sh1_readonly,sh2_readwrite TO appuser1, appuser2, ..., appuser5;
qhb=# GRANT sh1_readwrite,sh2_readonly TO appuser6, appuser7, ..., appuser10;

When assigning group roles to users, it is possible to separate which privileges are allowed to which user.

Privilege Distribution

QHB supports multiple privileges.

For example:

  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  • TRUNCATE
  • REFERENCES
  • TRIGGER
  • CREATE
  • USAGE
  • EXECUTE

These privileges can be granted using the GRANT command.

Copyright © KVANTOM LLC, 2019-2026