30.2. Integrity Checks #
30.2.1. Overview #
Postgres Pro Standard includes the pg_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.
The system.conf file is included into the Postgres Pro Standard distribution. Each Postgres Pro Standard instance has a single system.conf file. Each line in system.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 of Postgres 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 the share/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 to Postgres 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 -C configuration-file-full-path -d postgres -h localhost -p 5432 -U postgres
To check system catalog tables, run pg_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 the Postgres 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 running pg_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 the cron daemon. To schedule integrity checks, modify the /etc/crontab file by adding the lines that define the frequency of pg_integrity_check utility launches. The /etc/crontab is a system file that contains all instructions for the cron daemon. To get a detailed description of the crontab 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 cluster PGDATA = /var/lib/pgpro/std-17/data # pg_integrity_check log file LOG = /opt/pgpro/std-17/share/security/log # Validate read-only files every day at 00:05 5 0 * * * root /opt/pgpro/std-17/bin/pg_integrity_check -s >> $LOG # Run integrity checks at 14:15 on the first day of the month 15 14 1 * * root /opt/pgpro/std-17/bin/pg_integrity_check -s >> $LOG # Run integrity checks at 22.00 on weekdays 0 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 day 23 0-23/2 * * * root /opt/pgpro/std-17/bin/pg_integrity_check -s >> $LOG # Run integrity checks at 4:05 each Sunday 5 4 * * sun root /opt/pgpro/std-17/bin/pg_integrity_check -s >> $LOG