B is not for Madness, but for Safety part 3 - The Last Element

DevOps FM and get to know YouTube — you will find a lot of useful things there 🙂

Chapter 1: The end is also part of the journey

Sooner or later, there comes a time in every customer's life when they need to implement security processes into the most critical part of their infrastructure – the database. A similar incident did not bypass our project.

One beautiful sunny morning the task was set to jirawithin which terrible words sounded:

Improving database security and designing its infrastructure in accordance with basic security requirements.

The first thing you might think is that almost any infrastructure component that acts as a backend, interacts with the database directly, and with the slightest interference in the mechanisms of the database, a cascade of problems can arise. They will be associated both with infrastructure incidents (for example, an increase in broker queues or user authentication errors), and with financial and reputational losses of the client itself (for example, the loss of legitimate user traffic at the time downtime or an increase in user requests to Technical Support).

– What helps us avoid this?
— The answer is simple – this is a huge test environment that completely duplicates the product environment.

Yes, now there are no projects without test environments, but the time spent switching all epics Developers, QA And DevOpsengineers, will be labor-intensive and, as a result, will lead to new functionality being released more slowly.

Speaking in order, we were faced with a number of tasks and steps to complete them. Our project's database was PostgreSQL version 14, so the minimum required requirements could be described as follows:

Deployment of a new test database in the form of a cluster;
Implementation of security libraries in database operation;
Control of access rights to database files;
Implementation of a complete mechanism for logging processes of interaction with the database;
Setting up encryption processes.

The list looks quite large, so let’s start the discussion in order and look at each item in a little more detail.

The process of deploying a new test database in the form of a cluster does not represent for us difficultiessince for this we only need the current OS and the security and event monitoring software previously installed on it.

That is why we move on to the list of additional libraries PostgreSQL to improve safety, which should be included in shared_preload_libraries.

passwordcheck – module for checking the strength of the password given to users when using ALTER ROLE And CREATE USER;
auto_explain – This module does not completely improve the security of your data, but it allows you to automate the process of logging unoptimized or long queries. This mechanism can significantly reduce the time required for analysis.
pg_stat_statements – collects statistics of all executed SQL statements.
auth_delay – The module adds a delay time before responding to any authorization request, which in the process makes the brute force process more difficult, even if an attacker breaks into a closed system.
pgaudit – module extends PostgreSQL logging capabilities and logs certain statements described in the settings parameter pgaudit.log. In our case, the necessary ones are READ (SELECT and COPY operations), DDL (all Data Definition Language statements that are not included in the ROLE class – such commands include CREATE, ALTER, TRUNCATE, DROP) and ROLE (statements related to changing or granting privileges, for example: CREATE ROLE, ALTER ROLE, DROP ROLE GRANT, REVOKE).

Controlling access rights to database files during installation PostgreSQL happens on its own. The rights to directories are 700, and the rights to files are 600, but at the same time the user's user file creation mode mask PostgreSQL does not meet the safety standard.

postgres@ubuntu:~$ umask
0002

This means that all files created by the user postgres will have invalid privileges for directories 775 and for files 664. Our desired result should match the initial rights 700 and 600, which means we need to set the profile settings bash for the user by creating the necessary file with superuser rights in his directory:

root@ubuntu:~$ echo "umask 077" >> /var/lib/postgresql/.bash_profile; chown root: /var/lib/postgresql/.bash_profile

After adding the appropriate libraries, you need to go through a number of parameters responsible for logging. The key parameters that affect the logs within the task are:

log_filename – format of the file containing logs, taking into account timestamp (example value “postgresql-%Y-%m-%d_%H%M%S.log”)
log_rotation_age – lifetime of logging files (example value “7d”)
log_rotation_size – maximum size of the logging file (example value “0”)
pgaudit.log – previously described parameter (example value “ddl,role,read”)
log_directory – path to the log storage directory. It is important to note here that, for security and optimization reasons, this should be a separate directory with a mounted disk dedicated only to files postgresql-%Y-%m-%d_%H%M%S.log. This approach is necessary because if at the time of the influx of traffic the level of event logging increases, then the exhaustion of free space will be difficult to predict. This situation may cause the database to stop working.
log_connections – enable logging of authentication attempts to the server (by default this parameter is disabled, but it can be turned “on”)

Great, the simplest thing has already been implemented. But here comes the problem of encryption. A number of questions arise regarding how this process will affect the work of microservices with the database.

Chapter 2: Security for the sake of “security”

Having made sure that we have protected ourselves from accidental intrusions and increased the speed of analysis of potential incidents, we need to move on to the second stage – disk encryption. In this case, there are several ways to implement it.

First of all, the process can be implemented by encryption on top of the file system; secondarily, it can be implemented during the encryption process of the block device. Let's consider both options and decide which mechanism suits us best.

The names of the encryption processes speak for themselves. File system encryption is a method of encoding individual files or directories on a system and then writing them to disk. Block encryption works one level lower and, using a key, encrypts the entire disk and all files located on it at once. Each option has its advantages.

Advantages of file system encryption:

Selective encryption of files or directories;
Ability to control the volume of encrypted information;
Fast encryption speed (because files are selective);
High level of security, since this process provides detailed control over the encryption of each file individually using a separate key, which reduces the risk factors in the event of a disk hack;
Easy access to other files that do not require encryption on the system;
Ease of file recovery in case of partial disk failure.

Benefits of block disk encryption:

No need for selective file monitoring. The entire disk is encrypted and decrypted at once;
Any files and folders created by the user on the disk are automatically encrypted, which avoids possible user errors;
This type encrypts file system metadata such as directory structure, file names, timestamps, or size.

Since we are talking about PostgreSQL with control via patroni, then most likely the second option will be the most correct in implementation. At a minimum, the practice of using a separately mounted disk to store database system files on it is not something new or strange. Let's move on to the settings:

If we are talking about our original OS in the form Ubuntuthen we need to install the encryption utility:

apt update; apt install cryptsetup

We switch the master if PostgreSQL is already pre-installed and we are working on a live infrastructure.

patronictl -c /etc/patroni.yml list
patronictl -c /etc/patroni.yml switchover $REPLICA_NAME

We stop patroni on the source node and unmount the disk that is mounted in pgsql:

systemctl stop patroni
umount  /var/lib/pgsql

Let's create an encrypted partition luxafter which we add the encryption key to the file located on the main disk of the file system:

cryptsetup luksFormat /dev/$DISK_NAME /root/cfs.key
cryptsetup luksAddKey /dev/$DISK_NAME /root/cfs.key --key-file=/root/cfs.key

To open our new section you need to apply luksOpen specifying the encryption key and then creating a file system within it:

cryptsetup luksOpen /dev/vdb pgsql-data --key-file=/root/cfs.key
mkfs.xfs /dev/mapper/pgsql-data

The last step is to add our new partition to the file, mount the system and set the correct directory permissions. Later, when the server is started, patroni will initialize the synchronization process with data from Master-servers.

export UUID=$(ls -l /dev/disk/by-uuid | grep "vdb" | awk '{print $9}')
echo "pgsql-data UUID=${UUID}  /root/cfs.key luks" >> /etc/crypttab
export UUID=$(ls -l /dev/disk/by-uuid | grep "dm-0" | awk '{print $9}')
echo "UUID=${UUID} /var/lib/pgsql xfs defaults 0 0" >> /etc/fstab
mount -a
chown postgres:postgres /var/lib/pgsql
chmod 700 /var/lib/pgsql
systemctl start patroni

Setting up encryption works great, but at this point it’s worth thinking: what are we protecting ourselves from? The only time such encryption might be useful to us is if someone gets into the office at the server rack and pulls out the disk, because we have a completely closed infrastructure with limited access to the servers with the database.

It follows that our option would be an excellent solution, but other potential holes remain. For example, traffic coming to the database from microservices still remains in an unencrypted state. How then?

Chapter 3: Something in Between

Let's consider two cases. The first case is the potential for MITM (Man-in-the-middle) within our infrastructure.

The essence of the Man-in-the-Middle attack is that the attacker “passes” the victim’s web traffic (perhaps by changing the parameters of the DNS server or the hosts file on the victim’s machine) “through himself.”

Talk about this fairly common attack is not new, but the product developers themselves PostgreSQL We prepared for such situations a long time ago and added authentication SSL certificate between client and server.

apt install openssl -y

Then you need to generate a request CSR (Certificate Signing Request):

cd /root
openssl req -new -nodes -text -out root.csr \
  -keyout root.key -subj "/CN=root.yourdomain.com"
chmod og-rwx root.key

And issue the root certificate itself, signed with the key for this request:

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

Finally, we already have the opportunity to issue a trusted certificate by first issuing a request for the server certificate and key:

openssl req -new -nodes -text -out server.csr \
  -keyout server.key -subj "/CN=dbhost.yourdomain.com"
chmod og-rwx server.key

We sign it for 365 days with our root certification authority:

openssl x509 -req -in server.csr -text -days 60 \
  -CA root.crt -CAkey root.key -CAcreateserial \
  -out server.crt

And we generate a client certificate for the application on a residual basis.

openssl req -new -nodes -text -out client.csr \
  -keyout client.key -subj "/CN=client.yourdomain.com"
chmod og-rwx client.key
openssl x509 -req -in client.csr -text -days 60 \
  -CA root.crt -CAkey root.key -CAcreateserial \
  -out client.crt

What can we do?

We generated certificates for the database, but it still cannot work with ssl, to solve this problem we will need a number of parameters and security settings.

In the main configuration settings, we enable the traffic encryption mode for incoming connections:

ssl – enable the ability to work with ssl connections (“true”)
ssl_ca_file – path to the file containing the Certification Authority certificate, to confirm the client certificate (for example, “/root/root.crt”)
ssl_cert_file – path to the file containing ssl server certificate (for example “/root/server.crt”)
ssl_key_file – path to the file containing the key to the issued server certificate (for example, “/root/server.key”)

And we prohibit through the client authentication configuration file any that do not contain ssl-logging the connection from our internal hosts:

hostssl    replication     all             10.100.10.2/18            scram-sha-256

Okay, but what about the application side?

Everything is just as simple here, it is only important to pass the microservice its client certificate (client.crt), the key to the certificate (client.key) and the root certificate to confirm the authenticity of the certificate from the server side. In the future, when connecting to the database from any application that has the above list and the set parameter sslmode=verify-fulla check will take place SSL certificates on both sides, which eliminates any interference in the traffic of any potential attackers.

Chapter 4: Something somewhere

When the administrator has established all the basic mechanisms, the last task comes into his hands – the cherry on the cake. DB backups: what to do with them? If we backup files that are located within our infrastructure to some kind of backup disk, then we already know how to re-encrypt this disk. But there is one significant disadvantage: businesses like to insure themselves, which means that the number of backup copies of your database, even in a compressed state, can significantly exceed the size of the main database volume. In addition, the client may want to use third-party S3 as a storage place for copies, and then the mechanism comes to the rescue walg-backup capable of efficiently, optimized and secure database backup to the cloud. Here we also followed this path.

For work walg-backup configuration required .walg.json and a number of commands that can subsequently be wrapped in the simplest bash scriptbut let's start in order.

.walg.json should contain the following settings:

WALE_S3_PREFIX – prefix of names of archives in the repository;
AWS_ACCESS_KEY_ID – S3 access key;
AWS_ENDPOINT – S3 endpoint;
AWS_S3_FORCE_PATH_STYLE – enable path style addressing when connecting to an S3 compatible service;
AWS_SECRET_ACCESS_KEY – S3 secret key;
PGDATA – path to the database data directory;
PGHOST – host connection parameter;
WALG_UPLOAD_CONCURRENCY – number of parallel threads for downloading from the network;
WALG_DOWNLOAD_CONCURRENCY – number of parallel threads for downloading;
WALG_UPLOAD_CONCURRENCY – number of parallel threads for downloading;
WALG_UPLOAD_DISK_CONCURRENCY – number of parallel threads for uploading to disk;
WALG_DELTA_MAX_STEPS – determines how many differential backups can be between full backups;
WALG_COMPRESSION_METHOD – backup compression method;
WALG_LIBSODIUM_KEY – encryption key for archived copies of 32 bytes in size (it can be generated by executing the command openssl rand -hex 32);
WALG_LIBSODIUM_KEY_TRANSFORM – The transformation that will be applied to WALG_LIBSODIUM_KEY to obtain the required 32-byte key.

Example

{

“WALE_S3_PREFIX”: “s3://postgres-wal”,

“AWS_ACCESS_KEY_ID”: “default:s3-user”,

“AWS_ENDPOINT”: “https://storage.yandexcloud.net“,

“AWS_S3_FORCE_PATH_STYLE”: “true”,

“AWS_SECRET_ACCESS_KEY”: “

“PGDATA”: “/var/lib/pgsql/14/data/”,

“PGHOST”: “/var/run/postgresql/.s.PGSQL.5432”,

“WALG_UPLOAD_CONCURRENCY”: “2”,

“WALG_DOWNLOAD_CONCURRENCY”: “2”,

“WALG_UPLOAD_DISK_CONCURRENCY”: “2”,

“WALG_DELTA_MAX_STEPS”: “7”,

“WALG_COMPRESSION_METHOD”: “brotli”,

“WALG_LIBSODIUM_KEY”: “” class=”formula inline”>{YOUR_LIBSODIUM_KEY}”,

“WALG_LIBSODIUM_KEY_TRANSFORM”: “hex”

}

After creating the file .walg.jsonits further placement in /var/lib/pgsql and correctly setting up all necessary authentication keys for S3launching internal control commands wal-g will start the process of collecting a backup, encrypting it and then sending it to S3. But there is an important point: actions to collect backups must be carried out from the leader node PostgreSQL cluster for data consistency in collected backups:

su - postgres -c "/usr/local/bin/wal-g backup-push /var/lib/pgsql/14/data/ >> ~/walg_backup.log

Chapter 5: The Path Traveled

Coming to the end of this series articles, I want to announce the results.

Creating infrastructure from scratch is a very large and painstaking task for every engineer. It is impossible to take into account all the risk factors and all the difficulties that may arise during the stages of infrastructure growth in the future. You also need to understand and be prepared for the challenges of changing the current functionality of the infrastructure. Requests from the design bureau may come regularly, and it is not for nothing that the definition of cybersecurity includes the word “practice”: