In an era where data breaches are both costly and damaging to reputations, implementing best practices and robust security measures is crucial. PostgreSQL has emerged as one of the most successful and highly adopted open-source databases, competing with the enterprise standards of Oracle and SQL Server. Its increasing popularity is evident from DBEngine rankings, Stackoverflow surveys, and continuous growth metrics across various platforms. As the adoption of PostgreSQL for critical applications grows, securing these environments is absolutely necessary. To address this need, HexaCluster is pleased to announce the launch of PGDSAT, a PostgreSQL Database Security Assessment Tool. PGDSAT is designed to enhance your PostgreSQL clusters’ security, covering approximately 80 security controls, including those recommended by the CIS compliance benchmarks.
Where to find this project : PGDSAT ?
This project is on GitHub : https://github.com/HexaCluster/pgdsat and we invite Contributors to add thoughts, patches, reviews and issues to support PostgreSQL users across the planet.
Acknowledgment to Contributors
Kudos to Gilles Darold, the CTO of HexaCluster, who got us excited with the idea of PGDSAT and made it to the first release of PGDSAT. A couple of widely popular contributions from Gilles include pgBadger, a popular PostgreSQL log analyzer and Ora2Pg, a free tool to migrate Oracle, MySQL and SQL Server databases to PostgreSQL.
We would also like to specially thank Julien Rouhaud, a PostgreSQL major contributor, who was the early reviewer of PGDSAT and provided patches. I was very happy to submit a couple of my patches and I am looking forward to contribute more to this project.
CIS compliance benchmark for PostgreSQL
The CIS (Center for Internet Security) compliance benchmark is a highly respected standard developed through global consensus among IT security experts. These benchmarks offer prescriptive guidance for establishing a security baseline for various technology platforms.
For PostgreSQL users, adhering to the CIS benchmarks is helpful as these benchmarks provide a set of practices designed to secure the database. They cover everything from correct configurations to privileges and encryption standards, helping prevent unauthorized access and data leaks. By aligning with these benchmarks, organizations can ensure that their PostgreSQL databases are configured to the industry’s highest security standards. Furthermore, CIS benchmarks are developed by a community of cybersecurity professionals, ensuring that they are continually updated with the latest security protocols. This makes them a trusted resource in a landscape where technological threats are constantly evolving, providing PostgreSQL users with peace of mind and a robust defense against potential security breaches.
Supported security checks on PostgreSQL cluster by PGDSAT
1 - Installation and Patches
1.1 - Ensure packages are obtained from authorized repositories
1.1.1 - PostgreSQL packages installed. (Manual)
1.1.2 - Ensure packages are obtained from PGDG
1.2 - Ensure systemd Service Files Are Enabled
1.3 - Ensure Data Cluster Initialized Successfully
1.3.1 - Check initialization of the PGDATA
1.3.2 - Check version in PGDATA
1.3.3 - Ensure Data Cluster have checksum enabled
1.3.4 - Ensure WALs and temporary files are not on the same partition as the PGDATA
1.3.5 - Ensure that the PGDATA partition is encrypted (Manual)
1.4 - Ensure PostgreSQL versions are up-to-date
1.5 - Ensure unused PostgreSQL extensions are removed (Manual)
1.6 - Ensure tablespace location is not inside the PGDATA
2 - Directory and File Permissions
2.1 - Ensure the file permissions mask is correct
2.2 - Check permissions of PGDATA
2.3 - List content of PGDATA to check unwanted files and symlinks (Manual)
2.4 - Check permissions of pg_hba.conf
2.5 - Check permissions on Unix Socket
3 - Logging And Auditing
3.1 - PostgreSQL Logging
3.1.1 - Logging Rationale
3.1.2 - Ensure the log destinations are set correctly
3.1.3 - Ensure the logging collector is enabled
3.1.4 - Ensure the log file destination directory is set correctly
3.1.5 - Ensure the filename pattern for log files is set correctly (Manual)
3.1.6 - Ensure the log file permissions are set correctly
3.1.7 - Ensure 'log_truncate_on_rotation' is enabled
3.1.8 - Ensure the maximum log file lifetime is set correctly (Manual)
3.1.9 - Ensure the maximum log file size is set correctly (Manual)
3.1.10 - Ensure the correct syslog facility is selected (Manual)
3.1.11 - Ensure syslog messages are not suppressed
3.1.12 - Ensure syslog messages are not lost due to size
3.1.13 - Ensure the program name for PostgreSQL syslog messages is correct (Manual)
3.1.14 - Ensure the correct messages are written to the server log
3.1.15 - Ensure the correct SQL statements generating errors are recorded
3.1.16 - Ensure 'debug_print_parse' is disabled
3.1.17 - Ensure 'debug_print_rewritten' is disabled
3.1.18 - Ensure 'debug_print_plan' is disabled
3.1.19 - Ensure 'debug_pretty_print' is enabled
3.1.20 - Ensure 'log_connections' is enabled
3.1.21 - Ensure 'log_disconnections' is enabled
3.1.22 - Ensure 'log_error_verbosity' is set correctly
3.1.23 - Ensure 'log_hostname' is set correctly
3.1.24 - Ensure 'log_line_prefix' is set correctly
3.1.25 - Ensure 'log_statement' is set correctly
3.1.26 - Ensure 'log_timezone' is set correctly
3.1.27 - Ensure that log_directory is outside the PGDATA
3.2 - Ensure the PostgreSQL Audit Extension (pgAudit) is enabled
4 - User Access and Authorization
4.1 - Ensure sudo is configured correctly (Manual)
4.2 - Ensure excessive administrative privileges are revoked
4.3 - Ensure excessive function privileges are revoked (Manual)
4.4 - Ensure excessive DML privileges are revoked (Manual)
4.5 - Ensure Row Level Security (RLS) is configured correctly (Manual)
4.6 - Ensure the set_user extension is installed (Manual)
4.7 - Make use of predefined roles (Manual)
4.8 - Ensuse the public schema is protected
5 - Connection and Login
5.1 - Ensure login via "local" UNIX Domain Socket is configured correctly
5.2 - Ensure login via "host" TCP/IP Socket is configured correctly
5.3 - Ensure Password Complexity is configured
5.4 - Ensure authentication timeout and delay are well configured
5.5 - Ensure SSL is used for client connection
5.6 - Ensure authorized Ip addresses ranges are not too large
5.7 - Ensure specific database and users are used
5.8 - Ensure superusers are not allowed to connect remotely
5.9 - Ensure that 'password_encryption' is correctly set
6 - PostgreSQL Settings
6.1 - Understanding attack vectors and runtime parameters
6.2 - Ensure 'backend' runtime parameters are configured correctly
6.3 - Ensure 'Postmaster' runtime parameters are configured correctly (Manual)
6.4 - Ensure 'SIGHUP' runtime parameters are configured correctly (Manual)
6.5 - Ensure 'Superuser' runtime parameters are configured correctly (Manual)
6.6 - Ensure 'User' runtime parameters are configured correctly (Manual)
6.7 - Ensure FIPS 140-2 OpenSSL cryptography is used
6.8 - Ensure TLS is enabled and configured correctly
6.9 - Ensure a cryptographic extension is installed
6.10 - Ensure a data anonymization extension is installed
7 - Replication
7.1 - Ensure a replication-only user is created and used for streaming replication
7.2 - Ensure logging of replication commands is configured
7.3 - Ensure base backups are configured and functional
7.4 - Ensure WAL archiving is configured and functional
7.5 - Ensure streaming replication parameters are configured correctly
8 - Special Configuration Considerations
8.1 - Ensure PostgreSQL subdirectory locations are outside the data cluster
8.2 - Ensure the backup and restore tool, 'pgBackRest', is installed and configured
8.3 - Ensure miscellaneous configuration settings are correct (Manual)Installation Instructions for PGDSAT
To successfully install and run PGDSAT, follow these step-by-step instructions. I have tested the installation on Ubuntu 22.04.4 LTS. With a slight modification to below commands, you may be able to install on other supported Linux platforms.
Prerequisites
This tool has to be installed in the database server where the PostgreSQL cluster is initialized. This is mandatory as there are several file system level checks.
Step 1 : Install the dependencies
$ sudo apt install unzip make
Step 2 : Download and Install PGDSAT
-- Modify following commands with appropriate release as necessary
curl -LO https://github.com/HexaCluster/pgdsat/archive/refs/tags/v1.1.zip
unzip v1.1.zip
cd pgdsat-1.1
perl Makefile.PL
make
sudo make install
Step 3 : Validate the release
$ pgdsat --version
pgdsat v1.1Generating a report on Security Checks
Once PGDSAT is installed successfully, we must generate the report using the OS user that owns the PostgreSQL’s data directory.
Set PATH to PostgreSQL binaries
export PATH=$PATH:/usr/lib/postgresql/16/bin
You may set the following environment variable to avoid manual input
export PGPASSWORD=secret
Generate the report
pgdsat -U postgres -h localhost -d postgres -p 5432 -D /var/lib/postgresql/16/main -f html -o report_ubuntu.htmlAnalyzing the report
Now that the report is successfully generated, it is the time to analyze the report and fix the failed security checks.
You may view the Sample Report here.
As seen in the below image, for each security control check, we either have green tick :white_check_mark: that the check is success or a :x: to state that the check is not a success and an empty box :white_medium_square: that states that a manual check has to be performed.
When you click on one of the failed check, you will be redirected to the detailed explanation of the failure.
If you need any support in fixing these failures, do not hesitate to contact HexaCluster at connect@hexacluster.ai
Conclusion
In the future releases, we are going to focus on improving the security controls offered by PGDSAT. In addition to that, we are going to also add the support for running this tool against databases on DBaaS platforms like AWS RDS/Aurora, Azure Database for PostgreSQL as well as Google Cloud SQL for PostgreSQL. We haven’t tested the same against other platforms like Yugabyte, Aiven or pgEdge and other PostgreSQL derived databases. However, you are happy test it yourself and let us know your thoughts.
Looking for detailed PostgreSQL Architectural Health Assessment and Security Audit by experts ? Need Database Migrations assistance ? Click here to contact HexaCluster for experts assistance to meet your unique business needs.
Subscribe to our Newsletters and Stay tuned for more interesting topics.
