pg_integrity_verifier

pg_integrity_verifier is a specialized tool designed for monitoring the integrity of PostgreSQL databases. The application is designed to assist database administrators or security specialists in monitoring the integrity of databases. It saves database state records in the specified directory, and in case of any changes in the monitored databases, it creates a snapshot in sn_{database}_{datetime}.diff format. Then pg_integrity_verifier executes the command specified in run_command in the main configuration file. run_command specifies a command, script, or other application intended for analyzing changes and making decisions. For example, if a change in the structure of any table is detected, a notification can be sent to the specialists in charge. How and where the notification will be sent shall be determined solely by the external application. In addition to notifications, you can perform actions that modify the structure of the DBMS or its configurations.

pg_integrity_verifier can be run by any Linux OS user with the authority to execute the file. It is important to note that the only permissible point to connect to databases for integrity control is the current host, i.e. pg_integrity_verifier must run on the same host as the monitored database, since the application requires access to local PostgreSQL configuration files postgresql.conf and pg_hba.conf to control their integrity. Integrity verification can be applied to different database entities: users, configurations, tables, functions, triggers, and so on.

Launching parameters

List of startup parameters for pg_integrity_verifier:

Launch example:

./pg_integrity_verifier -l log4rs.yaml -c pg_integrity_verifier.yaml

Main configuration file

The configuration of pg_integrity_verifier is set in -c parameter through the main configuration file in yaml format. This file allows you to set various parameters that define the interaction of the application with the observed databases.

File structure:

mode: "<operation mode: one_time or daemon>"
daemon_period: "<interval in seconds for daemon mode>"
databases:
  - db_url: "postgres://<username>:<password>@<DB address>:<port>/<DB name>"
    snapshot_directory: "<directory for DB snapshots>"
    observed_objs: [<list of DB objects to monitor>]
    run_command: "<command or script to execute upon detecting a change> -f DIFF_FILE"
  - ...

Where:

Example of the main configuration file:

mode: "daemon"
daemon_period: "5"
databases:
  - db_url: "postgres://postgres:12345@localhost:5432/db1"
    snapshot_directory: "json1"
    observed_objs: ['config_file', 'pg_hba', 'pg_settings', 'pg_user', 'pg_proc', 'pg_trigger', 'pg_roles']
    run_command: "tests/run_command.sh -f DIFF_FILE"
  - db_url: "postgres://postgres:54321@localhost:5432/db2"
    snapshot_directory: "json2"
    observed_objs: ['config_file', 'pg_hba', 'pg_settings', 'pg_user', 'pg_proc', 'pg_trigger', 'pg_roles']
    run_command: "tests/run_command.sh -f DIFF_FILE"

Configuration file for logging

Event logging and application messages are configured in the -l parameter and also through a yaml file.

File structure:

refresh_rate: "<configuration update interval in seconds> seconds"
appenders:
  <component name>:
    kind: <component type: console, file, rolling_file, udp, or tcp>
    encoder:
      pattern: "<output or log entry pattern>"
    path: "<log file path>" # Only for file and rolling_file component types
    policy: # Only for rolling_file component type
      trigger:
        kind: <condition type: size, time, or compound>
        limit: <file size> # Only for size condition type
        schedule: <cron format schedule with seconds extension> # Only for time condition type
      roller:
        kind: <rotation mechanism type: fixed_window, delete, or compound>
        base: <starting index> # Only for fixed_window rotation mechanism type
        count: <number of files> # Only for fixed_window rotation mechanism type
        pattern: "<rotation name pattern>"
    endpoint: "<address>:<port>" # Only for udp and tcp component types
    reconnect: # Only for tcp component type
      strategy: <reconnection strategy: immediate or fixed>
      delay_ms: <delay before reconnection attempt in milliseconds> # Only for fixed strategy
root:
  level: <minimum logging level: trace, debug, info, warn, error>
  appenders:
    - <component name 1>
    - <component name 2>
    ...

Where:

Example configuration file for logging:

refresh_rate: 5 seconds
appenders:
  stdout:
    kind: console
    encoder:
      pattern: "{d(%Y-%m-%d %H:%M:%S)} - {m}{n}"
  my_file_logger:
    kind: rolling_file
    path: "logs/pg_integrity.log"
    encoder:
      pattern: "{d(%Y-%m-%d %H:%M:%S)} - {m}{n}"
    policy:
      trigger:
        kind: size
        limit: 104857600
      roller:
        kind: fixed_window
        base: 1
        count: 10
        pattern: "logs/pg_integrity_{}.log"
root:
  level: info
  appenders:
    - stdout
    - my_file_logger

Example of use

To alert DBMS administrators, you can use scripts that will be called by the pg_integrity_verifier application when changes are recorded in the observed database. In the reviewed example, alert_example.sh accepts a file containing a list of changes through the -f argument and displays its contents. For the actual application, the contents of the incoming file must be interpreted, and based on the changes, an external system must be called to perform notification functions, such as sending an e-mail or messenger alert.