H.4. pg_sec_check#
H.4. pg_sec_check #
pg_sec_check — security audit tool for Tantor SE
H.4.1. Introduction #
pg_sec_check (Postgres Security Check) — is a
utility designed to audit the security of PostgreSQL database
configurations. It automates the process of verifying various
aspects of security—from server settings to database-specific
parameters—providing detailed reports on identified issues and
recommendations for remediation.
H.4.2. Quick Start #
Configure PostgreSQL
In the
postgresql.conffile, it should be set:unix_socket_directories = '/tmp'
Note
It is not necessary to specify
/tmpspecifically, you can use any directory for Unix sockets.Run the utility
./pg_sec_check \ --host /tmp \ --port 5432 \ --user postgres \ --database postgres \ --config ./config.json \ --format Full \ --output audit_result
Launch Parameters:
--host /tmp— the path to the Unix-socket directory (for example,/tmp/.s.PGSQL.5432)--port 5432— PostgreSQL port--user postgres— database user--database postgres— the name of the database--config ./config.json— the path to the configuration file--format Full— output format (generates HTML + JSON reports)--output audit_result— prefix for output files
Execution result
After the launch, the following files will be created:
audit_result.json— report in JSON formataudit_result_web/index.html— web interface with the verification results
Enabling logs and backtrace (for debugging)
The utility uses standard Rust environment variables.
Logging levels:
export RUST_LOG=info # normal log level export RUST_LOG=debug # detailed logs export RUST_LOG=trace # maximum details
Enabling Backtrace
Useful in case of crashes or errors:
export RUST_BACKTRACE=1
H.4.3. Key Features #
Flexible check system: define custom checks via configuration files.
Modular architecture: supports various types of executors (SQL, shell scripts) and validators (Lua scripts).
Versioned checks: checks can be tied to specific PostgreSQL versions (min/max).
Localization: multilingual support (currently Russian and English).
Report formats: generates reports in HTML and JSON formats.
Parallel execution: supports multithreading to speed up scanning.
Integrity protection: in release builds, verifies integrity of its components using checksums.
H.4.4. Checks description #
| Section | Check Name | Description / Purpose |
|---|---|---|
| Replication | Replication Users | Ensure that a dedicated user is created and used for streaming replication |
| Replication | Replication Command Log | Ensure that logging of replication commands is configured |
| Replication | WAL Archiving Config | Ensure that WAL archiving is configured and functioning |
| Replication | Primary Connection Info | Ensure that streaming replication parameters are configured correctly |
| PostgreSQL Settings | FIPS Mode | Ensure that OpenSSL cryptography is used in compliance with FIPS 140-2 |
| PostgreSQL Settings | Postmaster Parameters |
Ensure that Postmaster runtime parameters
are configured correctly
|
| PostgreSQL Settings | SIGHUP Parameters |
Ensure that SIGHUP runtime parameters are
configured correctly
|
| PostgreSQL Settings | USER Parameters |
Ensure that USER runtime parameters are
configured correctly
|
| PostgreSQL Settings | Cryptographic Extensions | Ensure that a cryptographic extension is installed |
| PostgreSQL Settings | SUPERUSER Parameters |
Ensure that SUPERUSER runtime parameters
are configured correctly
|
| PostgreSQL Settings | Backend Runtime Parameters | Ensure that backend runtime parameters are configured correctly |
| PostgreSQL Settings | Anonymization Extension | Ensure that an extension for data anonymization is installed |
| PostgreSQL Settings | TLS Configuration | Ensure that TLS is enabled and configured correctly |
| Logging and Audit | Log Destination | Ensure that log destinations are configured correctly |
| Logging and Audit | Logging Collector Enabled | Ensure that the logging collector is enabled |
| Logging and Audit | Log File Directory | Ensure that the log file directory is configured correctly |
| Logging and Audit | Log File Name | Ensure that the log file name pattern is configured correctly |
| Logging and Audit | Log File Mode | Ensure that log file permissions are configured correctly |
| Logging and Audit | log_truncate_on_rotation Config |
Ensure that log_truncate_on_rotation is
enabled
|
| Logging and Audit | Syslog Facility | Ensure that a correct syslog facility is selected |
| Logging and Audit | Max Log Retention | Ensure that the maximum log retention period is configured correctly |
| Logging and Audit | Max Log File Size | Ensure that the maximum log file size is configured correctly |
| Logging and Audit | Syslog Sequence | Ensure that syslog messages are not suppressed |
| Logging and Audit | Syslog Message Size | Ensure that syslog messages are not lost due to size limits |
| Logging and Audit | PostgreSQL Syslog Program Name | Ensure that the program name for PostgreSQL syslog messages is correct |
| Logging and Audit | Valid Server Log Messages | Ensure that valid messages are written to the server log |
| Logging and Audit | SQL Queries Generating Errors | Ensure that SQL queries generating errors are logged correctly |
| Logging and Audit | debug_print_parse Config |
Ensure that debug_print_parse is disabled
|
| Logging and Audit | debug_print_rewritten Config |
Ensure that debug_print_rewritten is
disabled
|
| Logging and Audit | debug_pretty_print Config |
Ensure that debug_pretty_print is enabled
|
| Logging and Audit | debug_print_plan Config |
Ensure that debug_print_plan is disabled
|
| Logging and Audit | log_connections Config |
Ensure that log_connections is enabled
|
| Logging and Audit | log_disconnections Config |
Ensure that log_disconnections is enabled
|
| Logging and Audit | log_hostname Config |
Ensure that log_hostname is configured
correctly
|
| Logging and Audit | log_error_verbosity Config |
Ensure that log_error_verbosity is
configured correctly
|
| Logging and Audit | log_line_prefix Config |
Ensure that log_line_prefix is configured
correctly
|
| Logging and Audit | log_statement Config |
Ensure that log_statement is configured
correctly
|
| Logging and Audit | log_timezone Config |
Ensure that log_timezone is configured
correctly
|
| Logging and Audit | PostgreSQL Audit Extension | Ensure that the PostgreSQL audit extension (pgAudit) is enabled |
| Installation and Updates | Checksums Enabled | Ensure that data checksums are enabled in the cluster (requires initdb with –data-checksums) |
| Installation and Updates | WAL and Temp Files on Separate Partitions | Ensure that WAL and temp files are placed on separate partitions from PGDATA for performance and reliability |
| Installation and Updates | PGDATA Initialization Check | Ensure that PGDATA has been successfully initialized |
| Installation and Updates | systemd Service Enabled | Ensure that the systemd service for PostgreSQL is enabled |
| Special Configuration Aspects | wal-g |
Ensure that the wal-g backup and restore
tool is installed and configured
|
| Special Configuration Aspects | Other Configuration Parameters | Ensure that other configuration parameters are correct |
| Directory and File Permissions | Correct Permission Mask | Verify that umask is set to a secure default value (e.g., 077 or 027) |
| Directory and File Permissions | pg_hba.conf Permissions | Ensure that pg_hba.conf is not readable or writable by unauthorized users |
| Directory and File Permissions | PGDATA Permissions | Ensure that the PGDATA directory has secure ownership and access mode (typically 0700) |
| Directory and File Permissions | Unix Socket Permissions | Verify that the PostgreSQL Unix socket has correct ownership and access permissions |
| Access and User Authorization | set_user Check | Ensure that the set_user extension is installed |
| Access and User Authorization | Predefined Roles Check | Verify predefined roles |
| Access and User Authorization | Protect public Schema | Ensure that the public schema is secured |
| Access and User Authorization | RLS Configuration | Ensure that Row-Level Security (RLS) is configured correctly |
| Access and User Authorization | Excessive Admin Privileges | Ensure that excessive administrative privileges are revoked |
| Access and User Authorization | Excessive Function Privileges | Ensure that excessive function privileges are revoked |
| Access and User Authorization | Excessive DML Privileges | Ensure that excessive DML privileges are revoked |
| Connections and Login | Unix Socket Login Check |
Verify that local Unix socket connections do not use
trust authentication in pg_hba.conf
|
| Connections and Login | Host Login Check | Ensure that TCP/IP host entries in pg_hba.conf do not use insecure authentication methods (trust, password, ident, md5) |
| Connections and Login | Authorized IP Ranges | Ensure that authorized IP ranges are not overly broad |
| Connections and Login | Specific Databases and Users |
Verify that specific database and user entries are used
instead of the generic all
|
| Connections and Login | Password Complexity |
Ensure that the passwordcheck extension is
active in shared_preload_libraries to enforce password
complexity
|
| Connections and Login | Password Encryption |
Ensure that the password_encryption
parameter is configured correctly
|
| Connections and Login | Authentication Timeout and Delay | Verify that authentication timeout and delay parameters are configured to protect against brute-force attacks |
| Connections and Login | Remote Superuser Connections | Ensure that superusers are not allowed to connect remotely |
| Connections and Login | SSL Usage | Ensure that SSL/TLS is enabled on the server and required for host connections via pg_hba.conf |
H.4.5. Architecture #
Core: handles CLI parsing, config reading, execution flow, executor/validator coordination, and report generation.
Config file (config.json): defines the structure of the report, sections, and checks, including script paths.
Executors:
.shor.sqlscripts that gather data from the system or database.Validators: lua scripts that analyze executor results and produce check status, description, and recommendations.
Localization mechanism: enables multi-language output in reports.
Three core module types interact for running checks:
Executors: gather raw data (SQL queries or shell commands).
Validators: lua-based logic for interpreting gathered data.
Configuration: maps executors/validators to each check, including metadata like PostgreSQL version applicability.
This modular structure provides a high degree of customization and allows users to add their own environment-specific checks without having to modify the main program code.
H.4.6. Configuration file (config.json) #
The checks are configured via a JSON file. Example, simple_config.json:
{
"header": "Server and Database Information Report",
"report_name": "Report name",
"description": "Some description",
"section" : {
"system" : {
"header": "Example bash test",
"description": "Common info about current host",
"items": [
{
"name": "hello world check",
"description" : "Compare hello world results",
"executors": [
{
"script": "tests/common/script/simple_script.sh",
"min_ver": "none",
"max_ver": "none"
}
],
"validators": [
{
"script": "tests/common/lua/simple_bash_validate.lua",
"min_ver": "none",
"max_ver": "none"
}
]
}
]
},
"sql" : {
"header": "Example sql test",
"description": "Test connection into database",
"items": [
{
"name" : "select pg_version",
"description" : "select pg_version",
"executors": [
{
"script": "tests/common/script/simple_sql.sql",
"min_ver": "none",
"max_ver": "none"
}
],
"validators": [
{
"script": "tests/common/lua/simple_sql_validate.lua",
"min_ver": "none",
"max_ver": "none"
}
]
}
]
}
}
}
H.4.6.1. Global fields #
header (String): report header.report_name (String): report name.description (String): general description.section (Object): object containing definitions of various sections of the report. Each section represents a logical group of checks. The keys of this object (for example, “system”, “sql”) are group identifiers that can be used to selectively run checks using the--groupscommand line argument.
H.4.6.2. section structure #
header (String):section header.description (String): section description.items (Array): array of objects, each of which defines a separate check.
H.4.6.3. items structure #
name (String): unique name displayed in the report.description (String): purpose of the check.executors (Array): array of executor objects. Several performers can be identified for a single check, however, it should be taken into account how their results will be processed by the validator. Usually, one contractor is used for verification.validators (Array): array of validator objects. Similarly to executors, there may be several validators. Most often, a single validator is used.
H.4.6.4. executors fields #
script (String): path to the executor’s script file. Can be a.shfile for shell scripts or a.sqlfile for SQL queries.min_ver (String): minimum version of PostgreSQL for which this check is applicable (for example, “10.0.0”). If “none” is specified, there is no limit on the minimum version. The check will not be run for PostgreSQL versions below the specified one.max_ver (String): maximum version of PostgreSQL for which this check is applicable. If “none” is specified, there is no limit on the maximum version. The check will not run for the above versions of PostgreSQL.
H.4.6.5. validators fields #
script (String): path to the validator’s Lua script file.min_ver (String)/max_ver (String): same as for executors.
This schema ensures precise version targeting for each check, ideal for mixed-version environments.
H.4.7. Check Mechanism #
Checks consist of two phases: executor runs → validator analyzes output.
H.4.7.1. Executors #
The executors are designed to collect the data necessary for security analysis.
Shell scripts (
.sh): executes system commands. Useful for inspecting PostgreSQL configs, access rights, env vars.When running the shell script, the program sets the following environment variables that can be used inside the script:
PG_HOST="database host"PG_PORT="database port"PG_USER:="username for connecting to the database"PG_DB="database name"PGDATA="path to the PostgreSQL data directory"PG_VERSION="version of the PostgreSQL server to connect to"PG_HBA_FILE="path to the pg_hba.conf file"
SQL scripts (
.sql): used to make queries to the PostgreSQL database in order to obtain information about settings, database objects, user rights, etc.Currently, only one SELECT query is supported for SQL files. The result of this request is passed to the validator. This limitation should be taken into account when designing SQL checks; complex checks may require either a more complex SELECT query or splitting into several separate checks.
H.4.7.2. Validators #
Validators analyze the data collected by the performers and give a verdict on the status of the parameter being checked. All validation logic is implemented in the Lua language.
Global data available in Lua validators:
For validators that process results of sh-scripts:
result.stdout (String):standard output (stdout) of the executed shell script.
result.stderr (String):standard error (stderr) of the executed shell script.
result.return_code (Number):exit code of the executed shell script.Example Lua script for shell script validation
simple_bash_validate.lua:-- GLOBAL DATA -- result.stdout the output of stdout from the executed bash script -- result.stderr the output of stderr from the executed bash script -- result.return_code the return code from the executed bash script function validate_result() local success = true local status_str = "Success" local short_desc = "The check was successful" local full_desc = "All parameters meet the requirements" local recommend = "No action is required" if not result.stdout == "hello world" then status_str = "Warning" short_desc = "Validation error" full_desc = "The X parameter does not match the Y format" recommend = "Fix the X parameter" end -- Important! The return value should look exactly like this. -- That is, the structure (table) should not change. -- { -- status: one of the values is (Warning, Info, Error, Success, Failed) -- short_description: String -- full_description: String -- recommendation: String -- } local result_table = { status = status_str, short_description = short_desc, full_description = full_desc, recommendation = recommend } return result_table endFor validators that process results of SQL scripts:
The Lua script receives a global table
sql_results. This table is an array where each element is a table corresponding to a row of the SQL query result. The field names in these nested tables match the column names in theSELECTquery.Example Lua script for SQL validation
simple_sql_validate.lua:-- GLOBAL DATA -- sql_results a table with the result of SQL execution function validate_result() local success = true local status_str = "Success" local short_desc = "Error: SQL data is not received or incorrect" local full_desc = "All parameters meet the requirements" local recommend = "No action is required" -- Checking for the presence of the first line if sql_results and sql_results[1] then -- Getting the first row (this is a table) local first_row = sql_results[1] -- an example for a table of the form: -- pg_version ------------------------ -- pg version data bla bla bla if first_row and type(first_row) == 'table' and first_row.pg_version then short_desc = first_row.pg_version status_str = "Success" full_desc = "Version PostgreSQL: " .. short_desc else short_desc = "Error: The 'pg_version' field was not found in the SQL result" status_str = "Failed" end else -- sql_results is empty status_str = "Failed" end -- Important! The return value should look exactly like this. -- That is, the structure (table) should not change. -- { -- status: one of the values is (Warning, Info, Error, Success, Failed) -- short_description: String -- full_description: String -- recommendation: String -- } local result_table = { status = status_str, short_description = short_desc, full_description = full_desc, recommendation = recommend } return result_table end
Structure of the return value for Lua validators:
Each Lua validator script must define a
validate_result() function, which returns a
table with the following fixed structure:
{
status: "Warning" | "Info" | "Error" | "Success" | "Failed", -- Must be one of these values
short_description: "Brief description of the result", -- String
full_description: "Detailed explanation of the result", -- String
recommendation: "Recommended remediation actions" -- String
}
Adhering to this structure is critical, as the program core expects results in exactly this format to generate the report correctly. Deviations will result in errors during validation result processing.
H.4.7.3. locale_msg function #
Validator Lua scripts can use the locale_msg(key) function to retrieve localized strings.
See the Localization section for more details.
H.4.8. Localization #
The program supports localization of messages displayed in reports, allowing the output to be adapted for users who speak different languages.
H.4.8.1. locale_msg mechanism #
Validator Lua scripts use the locale_msg(key)
function to retrieve translated strings, where
key is a string identifier for the message.
For example, calling
locale_msg("first") returns the
string corresponding to the "first"
key in the language specified via the
--locale command-line argument. If
localization files for the selected language are not found, or
the key is missing, the fallback language is English.
H.4.8.2. Localization file format (.loc) #
Localization files are plain text files with a
.loc extension (e.g.,
en.loc, ru.loc). Each line
in the file represents a message and follows this format:
id,"message_key"
Examples:
localizations/ru.loc# ru.loc # --- validate_anon_extension.lua --- 1,"<p>Проверка предзагрузки расширений анонимизации (<code>anon</code> или <code>pg_anonymize</code>) через параметр <code>session_preload_libraries</code>.</p>" 2,"<p>Анализ <code>session_preload_libraries</code> для расширений анонимизации не был успешно завершен или расширения не найдены.</p>" 3,"<ul><li>Проверьте SQL-запрос и конфигурацию PostgreSQL (параметр <code>session_preload_libraries</code>).</li></ul>" ... 1674,"использование SSL"
localizations/en.loc# en.loc # --- validate_anon_extension.lua --- 1,"<p>Checking preload of anonymization extensions (<code>anon</code> or <code>pg_anonymize</code>) via the <code>session_preload_libraries</code> parameter.</p>" 2,"<p>Analysis of <code>session_preload_libraries</code> for anonymization extensions was not successfully completed or extensions were not found.</p>" 3,"<ul><li>Check the SQL query and PostgreSQL configuration (parameter <code>session_preload_libraries</code>).</li></ul>" ... 1674,"Ssl usage"
If the program is launched with the
--locale ru argument, calling
locale_msg("first") will return the
string "first" in russian. The lookup
mechanism is based on matching the string key passed to
locale_msg with the second field in the
localization file. The purpose of the numeric ID (e.g.,
1) is currently not fully specified, but the
string key is considered the primary identifier.
Maintaining consistency and completeness of localization files for all supported languages is essential when adding new checks or updating existing messages. If a key is missing in the selected language’s localization file, the default (English) version of the message will be displayed.
H.4.8.3. Supported Languages #
English (
en)Russian (
ru)
H.4.9. Report Formats #
The program can generate check results in multiple output formats.
The desired format is specified using the
--format (or -f)
command-line argument.
H.4.9.1. Available formats: #
Html: generates a report as an HTML page. This format is suitable for visual analysis, as it supports interactive elements, styling, and is easily readable in a web browser.
Json: generates the report in JSON format. This is a plain-text, machine-readable format commonly used for data exchange between systems. JSON reports are well-suited for automated processing, integration with monitoring or SIEM systems, or further analysis via scripts and other tools.
Full: when this option is selected (and it is the default), the program generates reports in all supported formats simultaneously (i.e., both HTML and JSON). This allows users to obtain both a human-readable version for immediate inspection and a machine-readable version for automation—without needing to re-run the utility. Output filenames are based on the value of the
--outputargument, with appropriate.htmland.jsonextensions.
The choice of report format depends on the user’s goals: use HTML for quick review or management presentation, and JSON for automation or system integration.
H.4.10. Usage #
H.4.10.1. Command-Line Arguments #
The program is controlled via command-line arguments. The following table describes all available options:
| Argument | Type | Default | Description |
|---|---|---|---|
--format
|
String
| Full | Output report format. Options: Full, Html, Json |
--config
|
String
| config.json | Path to the configuration file |
--output
|
String
| output | Base name for report output files (output_web/ and output.json) |
--port
|
u16
| 5432 | Port to connect to the PostgreSQL database |
--host
|
String
| /tmp | Host to connect to the database (IP address, hostname, or Unix socket path) |
--user
|
String
| dev | Username for database connection |
--database
|
String
| postgres | Name of the target database |
--threads
|
u16
| CPU core count | Number of threads for task execution |
--debug
|
bool
| false | Enable debug mode (verbose output) |
--pgclients
|
u16
| 10 | PostgreSQL connection pool size |
--locale
|
String
| ru | Locale code for report output |
--groups
|
Vec<String>
| None | Comma-separated list of check groups to run (e.g., “system,sql”) |
--unsecure
|
bool
| false | Run without verifying checksums of bundled files |
The
--groupsoption allows selective execution of specific check groups, defined as keys in thesectionobject of the configuration file. This is useful for targeted testing or staged audits.The
--unsecureoption is intended for special use cases. In release builds, the program by default verifies the checksums of all its components—including bundled executor and validator scripts. This ensures tool integrity and protects against accidental or malicious modifications. If any of these files have been altered (e.g., during development or customization of standard checks), the program will refuse to run and report a checksum mismatch error. The--unsecureflag bypasses this verification, allowing the tool to run with modified files.
Warning
Using the
--unsecure flag disables a critical internal
security mechanism. Use with caution. It is not recommended to
use this flag in production or trusted environments unless you
fully understand the risks, as it may allow execution of
modified (potentially harmful) scripts.
Additional arguments:
RUST_LOG: Environment variable that controls logging verbosity.BACKTRACE: Environment variable that enables stack traces on panic or crash.
H.4.11. Development and Customization #
The flexibility of Postgres Security Check is enabled by the ability to create custom checks and add new localization languages.
H.4.11.1. Creating Custom Checks #
The process of creating a new check involves the following steps:
Define the goal of the check.
Clearly specify what security or configuration aspect should be validated.
Write an executor script.
For shell scripts (
.sh): Create a script that collects the required data. Be aware of the available environment variables (PG_HOST,PG_PORT,PG_USER,PG_DB,PGDATA,PG_VERSION,PG_HBA_FILE).For SQL scripts (
.sql): Write a singleSELECTquery (only one per file) that extracts the necessary information from the database.
Write a Lua validator script (
.lua).Develop a Lua script that analyzes the executor output (available via the global
resulttable for shell orsql_resultsfor SQL). The script must define avalidate_result()function that returns a table with the standard structure. Use thelocale_msg("key")function for all translatable strings and add the corresponding keys and translations to the*.locfiles.Register the new check in
config.json.In the appropriate section, add a new item to the
itemsarray. Specify thenameanddescriptionof the check. In theexecutorsandvalidatorsarrays, provide paths to the scripts. If needed, setmin_verandmax_verto restrict the check to specific PostgreSQL versions.
Debugging tips:
Test executor scripts independently to ensure they collect data correctly.
Validate the Lua logic by providing typical and edge-case input. Step-by-step debugging or printing intermediate values in Lua can help.
The ability to build custom checks is a major strength of this tool, allowing it to be tailored to the unique security and infrastructure needs of any organization.
H.4.11.2. Adding New Localization Languages #
The localization system is based on plain-text files, which simplifies the process of adding support for additional languages.
To add a new localization:
Create a new
.locfile. For example, for French, create a file namedfr.loc. The file name must match the language code that will be passed using the--localeargument.Copy existing keys. Use
en.locas a base and copy all key-value pairs.Translate values. Replace the text values with appropriate translations. For example, if
en.loccontains:10,"connection failed", thenfr.locmight contain:10,"échec de la connexion"Place the file. Make sure the program can locate the new localization file. These files should be placed in the dedicated
localizationsdirectory.
Maintaining completeness and consistency of localization files (ensuring all keys exist in all language files) is essential for a high-quality user experience across different languages.