Securing a PostgreSQL database is paramount for safeguarding sensitive information and ensuring system integrity. The Center for Internet Security (CIS) offers a set of benchmarks that serve as a roadmap for database administrators aiming to fortify their PostgreSQL instances. Below, we delve into the essential CIS recommendations and their implementation.
1. Installation and Patches
- 1.1 Ensure Packages Are Obtained from Authorized Repositories:
- 1.1.1 PostgreSQL Packages Installed (Manual): Verify that PostgreSQL packages are installed correctly.
# Check Installed PostgreSQL Packages for On Red Hat Based Systems rpm -qa | grep postgresql
- 1.1.2 Ensure Packages Are Obtained from PGDG: Confirm that packages are sourced from the PostgreSQL Global Development Group (PGDG) repository.
1.2 Ensure Systemd Service Files Are Enabled
- Activate and configure systemd service files to manage PostgreSQL services.
# Check directly postgresql service if you do not use service that runs postgresql systemctl status postgresql-13.service
1.3 Ensure Data Cluster Initialized Successfully
- 1.3.1 Check Initialization of the PGDATA: Ensure that the data directory (PGDATA) is initialized properly.
# Directory list command for On Red Hat Based Systems. sudo ls -l /pg_data/data/
2. Directory and File Permissions
- 2.1 Ensure the File Permissions Mask Is Correct: Set the correct file permissions mask to prevent unauthorized access.
# Check directly postgresql service if you do not use service that runs postgresql systemctl status postgresql-13.service
3. Logging and Auditing
- 3.1 PostgreSQL Logging: Understand the importance of logging for monitoring and auditing. An audit trail is crucial for any relational database system.
# Check directly postgresql service if you do not use service that runs postgresql systemctl status postgresql-13.service
4. User Access and Authorization
- 4.1 Ensure Sudo Is Configured Correctly: Review and configure sudo permissions to limit administrative access.
# Please do not forget to use postgres user pg_controldata /pg_data/data/ | grep "Data page checksum version"
5. Connection and Login
- 5.1 Do Not Specify Passwords in the Command Line: Avoid exposing passwords in shell history or process lists.
# Logical volume size command lvs
6. PostgreSQL Settings
- 6.1 Understanding Attack Vectors and Runtime Parameters: Gain a thorough understanding of potential attack vectors and how to configure runtime parameters.
# Check the "FSTYPE" column to see the file system type of the partition. lsblk -o NAME,FSTYPE,FSVER,LABEL,UUID,FSAVAIL,FSUSE%,MOUNTPOINT
7. Replication
- 7.1 Ensure a Replication-Only User Is Created: Create a dedicated replication user with limited permissions.
# Create a Replication User # Please do not forget to use postgres user pg_controldata /pg_data/data/ | grep "Data page checksum version"
8. Special Configuration Considerations
- 8.1 Ensure PostgreSQL Subdirectory Locations Are Outside the Data Cluster: Place subdirectories outside the main data directory to enhance security and manageability.
# List of installed extensions postgres=# dx
By adhering to these CIS benchmarks, organizations can significantly bolster the security and integrity of their PostgreSQL databases, effectively mitigating risks associated with unauthorized access and data breaches. The continuous updates to these benchmarks ensure that security practices remain relevant and effective, fostering a proactive approach to database security.
