30.2. Integrity Checks#
30.2.1. Overview#
Postgres Pro Standard includes thepg_integrity_check utility that provides the following features for integrity checks:
On-demand checksum calculation and validation
Built-in checksum validation at the server startup
pg_integrity_check can control read-only files, additional files, and system catalog tables.
30.2.1.1. Read-Only Files#
Controlled read-only files include executable files, libraries, and other files that must never be modified. Checksums for read-only files are managed by the/opt/pgpro/std-17/share/security/system.conf configuration file.
Thesystem.conf file is included into thePostgres Pro Standard distribution. EachPostgres Pro Standard instance has a singlesystem.conf file. Each line insystem.conf corresponds to a single controlled object and includes two fields: the checksum, consisting of 40 hexadecimal digits, and a relative path to the controlled object. These fields are separated by three symbols: space, dash, space. Checksums are calculated and written into this file at the time ofPostgres Pro Standard installation. They control both contents and attributes of read-only files. For example, if access rights to the file have been modified, the checksum will change.
30.2.1.2. Additional Files#
Additional files are controlled files that can be modified by a database administrator. Checksums for additional files are managed by configuration files stored in theshare/security directory. Each cluster must have a separate configuration file for additional files. The naming convention is as follows: the path to the data directory of the cluster (PGDATA), with forward slashes replaced by underscores, followed by the.user.conf postfix. For example, for a cluster with/var/lib/pgpro/std-17/data data directory, the configuration file is called_var_lib_pgpro_std-17_data.user.conf.
Each line in the configuration file corresponds to a single controlled object and includes two fields: the checksum, consisting of 40 hexadecimal digits, and a relative path to the controlled object. The fields are separated by three symbols: space, dash, space.
Checksums control both contents and attributes of additional files. For example, if access rights to the file have been modified, the checksum will change.
To set up checksum validation for additional files, database administrator needs to do the following:
Create a configuration file for each cluster, following the naming convention specified above.
In the created configuration file, specify all the additional files to be controlled. As a checksum, any 40 hexadecimal digits can be entered, for example, zeros.
Run the following command to recalculate the checksums, specifying the path to the data directory of your cluster:
pg_integrity_check -u -o -D /var/lib/pgpro/std-17/data
Note
Alternatively, administrator can run the above command to generate a sample configuration file, and then edit this file as needed.
30.2.1.3. System Catalog Tables#
System catalog tables represent a controlled data selection related toPostgres Pro Standard instance. Checksums for system catalog tables are managed by respective configuration files for each database in a cluster. You can setup integrity checks of system catalog tables for several databases by providing separate configuration files with specific checksum values.
Each line in the configuration file corresponds to a single controlled object and includes two fields: the checksum, consisting of 40 hexadecimal digits, and a relative path to the controlled object. The fields are separated by three symbols: space, dash, space.
To set up checksum validation for data selection, database administrator needs to do the following for each database in a cluster:
Create a configuration file for the selected database. For example,
database-name-catalog.confIn the created configuration file, specify SQL queries that return the controlled data. As a checksum, any 40 hexadecimal digits can be entered, for example, zeros.
Run the following command to recalculate checksums, specifying connection parameters for your database:
pg_integrity_check -c -o -Cconfiguration-file-full-path -d postgres -h localhost -p 5432 -U postgres To check system catalog tables, runpg_integrity_check for each database subject to integrity checks.
Note
Alternatively, administrator can run the above command to generate a sample configuration file, and then edit this file as needed.
30.2.2. Checking Integrity at Server Startup#
Checksums for read-only files are always validated at thePostgres Pro Standard server startup. If the server start is blocked because of a checksum mismatch, administrator must troubleshoot and resolve the issue to restart the server.
System catalog tables and additional files are not validated at the server startup. You can validate them by runningpg_integrity_check manually once the server is started.
30.2.3. Scheduling Integrity Checks#
If you are using a Linux-based system, you can schedule recurrent integrity checks using thecron daemon. To schedule integrity checks, modify the/etc/crontab file by adding the lines that define the frequency ofpg_integrity_check utility launches. The/etc/crontab is a system file that contains all instructions for thecron daemon. To get a detailed description of thecrontab file format for your Linux-based operating system, run the command:
man 5 crontab
Examples
The following example illustrates integrity checks on a Rosa SX operating system:
# Data directory of the clusterPGDATA = /var/lib/pgpro/std-17/data# pg_integrity_check log fileLOG = /opt/pgpro/std-17/share/security/log# Validate read-only files every day at 00:055 0 * * * root /opt/pgpro/std-17/bin/pg_integrity_check -s >> $LOG# Run integrity checks at 14:15 on the first day of the month15 14 1 * * root /opt/pgpro/std-17/bin/pg_integrity_check -s >> $LOG# Run integrity checks at 22.00 on weekdays0 22 * * 1-5 root /opt/pgpro/std-17/bin/pg_integrity_check -s >> $LOG# Run integrity checks at 00:23, 2:23, 4:23 ..., every day23 0-23/2 * * * root /opt/pgpro/std-17/bin/pg_integrity_check -s >> $LOG# Run integrity checks at 4:05 each Sunday5 4 * * sun root /opt/pgpro/std-17/bin/pg_integrity_check -s >> $LOG