PostgreSQL Security: Best Practices and Tools

A data breach or unauthorized access can lead to severe consequences, including financial losses, reputational damage, and legal liabilities. With an increasing amount of sensitive information being stored in databases, protecting that data from unauthorized access, theft, or tampering is critical.

PostgreSQL is one of the most popular open source relational database management systems, and it’s known for its robust security features.

However, even with its built-in security mechanisms, mistakes can still happen: 82% of data breaches involve human error.

Misconfiguration, weak passwords, and inadequate access controls are all common pitfalls that can expose a database to potential risks.

It’s crucial to implement additional best practices and use suitable tools to ensure a comprehensive and effective PostgreSQL security posture.

This article will introduce you to some PostgreSQL security best practices you can implement for access and storage security.

Additionally, you’ll learn about specific tools you can use, such as pgcrypto, CipherStash, PGAudit, and ModSecurity, to enhance the security of your PostgreSQL environment.

Best Practices for PostgreSQL Security

Now that you know how crucial data security is, let's dive into some best practices that you can implement to safeguard the integrity and confidentiality of PostgreSQL databases.

Ensure Proper Access Control

Proper access control (who can access the database and what they can do with the data) is fundamental to the security of any database. Thankfully, PostgreSQL provides robust access control mechanisms, such as roles and privileges. By carefully defining roles and granting only necessary privileges to users, you can restrict unauthorized access to sensitive data.

For instance, with the help of PostgreSQL commands, you can create a specific role for your organization’s sales team, granting them precise privileges only to the tables they need:

1-- Create a new role
2CREATE ROLE sales_team;
3
4-- Allow users to login with the new role
5
6ALTER ROLE sales_team LOGIN;
7-- Grant SELECT privilege on the products and orders table to the sales_team role
8GRANT SELECT ON products TO sales_team;
9GRANT SELECT ON orders TO sales_team;

Additionally, you'll need to regularly review and audit roles and their privileges to ensure that only the necessary access is granted. This can help prevent unnecessary access to confidential data.

To check what privileges are granted to a specific role or user in PostgreSQL, you can use the following SQL command:

1-- Replace 'sales_team' with the actual name of the role or user you want to check
2SELECT    
3grantee,    
4privilege_type,    
5table_catalog,    
6table_schema,    
7table_name,    
8is_grantable 
9FROM    
10information_schema.role_table_grants
11WHERE    
12grantee = 'sales_team';

Make sure you remove unnecessary or outdated permissions from the database to minimize the attack surface, like this:

1-- Revoke SELECT privilege on an imaginary table named sensitive_data from a role REVOKE SELECT ON sensitive_data FROM temporary_contractors;

You can also enhance PostgreSQL security by restricting access to specific IP addresses or ranges. This prevents unauthorized access to the database from external or unexpected sources. To do so, you can modify the PostgreSQL server's pg_hba.conf configuration file. More information on how to restrict access to IP addresses or ranges is available in this official documentation.

Use Robust Password Management Features

For database administrators (DBAs), safeguarding user credentials within PostgreSQL databases is paramount.

Weak or compromised passwords can serve as a gateway for unauthorized access, potentially leading to data breaches and security vulnerabilities.

PostgreSQL provides robust password management features that, when properly utilized, can significantly enhance the overall security posture of your database environment.

Next, we’ll explore a few important PostgreSQL password management best practices:

Enforce Strong Password Policies

Implementing stringent password policies is your first line of defense. Encourage users to create complex passwords that combine uppercase and lowercase letters, numbers, and special characters, totaling more than twelve characters.

One secure way to set a password is by using the PSQL Tool or a GUI-based client tool such as pgAdmin, where the typed passwords are masked and unavailable to read in plaintext format.

You can achieve this via the command line using the PSQL Tool:

1SET password_encryption = 'scram-sha-256'; 
2
3\password

You are prompted to enter the password. Enter it, and the PSQL Tool passes the encrypted password in a SQL statement to the PostgreSQL server, keeping it secure.

Use Password Expiration and Rotation

Regularly refreshing user passwords reduces the window of opportunity for potential attackers. Make sure you implement password expiration and require users to change their passwords periodically:

1-- Set password expiration for a user
2ALTER USER user1 VALID UNTIL '2023-12-31';

Note: PostgreSQL does not update the date provided in the VALID UNTIL statement automatically.

Master the Art of PostgreSQL Logs

PostgreSQL logging offers insights into database activities, aiding troubleshooting and performance optimization. However, these logs can inadvertently expose sensitive data if not configured correctly, potentially becoming an Achilles' heel for PostgreSQL security.

For instance, incorrect log configurations like the following one can lead to sensitive data showing up in PostgreSQL logs:

1log_statement = 'all'

Setting log_statement to 'all' logs all SQL statements, including sensitive data, such as passwords or personal information, leading to the exposure of confidential data.

Mastering the art of PostgreSQL logging is crucial in maintaining a delicate equilibrium between transparency and safeguarding confidential information.

Following are a few essential practices for effective PostgreSQL logging while mitigating the risk of sensitive data exposure:

Configure Log Settings

Fine-tuning log settings is your initial line of defense. By customizing what gets logged and how it's presented, you can maximize insights while minimizing exposure:

1# Set log format and level in postgresql.conf
2
3log_statement = 'none'
4
5log_line_prefix = 'time=%t, pid=%p %q db=%d, usr=%u, client=%h , app=%a, line=%l '

In this code, log_statement is set to 'none', which means that no SQL statements are included in the logs. This is useful for preventing sensitive information, such as passwords or specific query details, from appearing in the logs. log_statement enhances PostgreSQL security by minimizing the exposure of potentially confidential data.

For more information on PostgreSQL logging and the use of config tokens used in the `log_line_prefix, configuration, check out the official documentation.

Store Logs Securely

Another vital step when working with PostgreSQL logs is to make sure you choose a secure location with appropriate access control to store your log files. Regularly rotate and archive logs to prevent unauthorized access and maintain compliance:

1# Specify secure log location and rotation settings
2
3log_directory = '/var/log/postgresql/'
4
5log_filename = 'postgresql.log'
6
7log_rotation_age = 1d
8
9log_rotation_size = 0

In this code block, log_rotation_age = 1d specifies the maximum age of a log file before it's rotated. This means that a new log file is created to continue logging. The 1d value indicates a rotation interval of one day. log_rotation_size = 0 disables size-based log rotation. When log_rotation_size is set to 0, log rotation based on file size is effectively turned off. Log files are not rotated automatically once their size reaches a certain threshold. Instead, log files continue to grow without being split into smaller segments due to size limitations.

By strategically configuring log settings and ensuring secure log storage, you can optimize PostgreSQL logging for operations while fortifying data security.

Encrypt Sensitive/Confidential Data

PostgreSQL encryption involves transforming plain data into ciphertext, ensuring that even if unauthorized parties gain access to data, they can't decipher its contents without the necessary decryption key.

The PostgreSQL pgcrypto extension offers cryptographic functions for data encryption and decryption within PostgreSQL databases. It provides a range of algorithms and methods, including symmetric encryption for data-at-rest protection. While pgcrypto is convenient, it's considered to have poor security for critical use, in part due to its use of server-side encryption.

Challenges with pgcrypto and server-side encryption

pgcrypto performs PostgreSQL encryption and decryption on the server side, meaning that the encryption keys reside on the server. This presents a major risk: if the server is compromised, the keys could be exposed, allowing attackers to decrypt encrypted data.

For instance, in the following example, the pgcrypto extension encrypts and decrypts Social Security number (SSN) data using the pgp_sym_encrypt and pgp_sym_decrypt functions. However, the encryption key is used and managed on the server, which exposes the key to potential compromise if the server is breached:

1-- Create a table with encrypted column
2CREATE EXTENSION IF NOT EXISTS 
3pgcrypto;  
4CREATE TABLE 
5sensitive_data (   
6    id serial PRIMARY KEY,
7    name text,
8    ssn bytea -- Use 'bytea' for binary data);
9-- Insert data with server-side encryption
10INSERT INTO sensitive_data (name, ssn)
11VALUES ('Alice', pgp_sym_encrypt('123-45-6789', 'encryption_key'));  
12
13-- Select data from sensitive_data table to view the encrypted ssn value
14SELECT * from sensitive_data ;

Although the SSN information of the user Alice is encrypted in the table, if an unauthorized user were to gain access to the server-side key they could reveal the password in plaintext:

1-- Decrypt data (happens on the server)
2SELECT name, pgp_sym_decrypt(ssn, 'encryption_key') AS decrypted_ssn
3FROM sensitive_data;

While encryption as explained above does improve PostgreSQL security to certain extent, on the other hand, it means that to perform operations like searching or ordering on encrypted data, you would need to use decryption functions like pgp_sym_decrypt in your SQL queries. However, this can be computationally expensive and might result in slower query performance, especially if you have a large amount of data to decrypt and process.

CipherStash: A Better Alternative

To overcome pgcrypto's limitations, you can make use of tools like CipherStash that offer an alternative approach to encryption.

With CipherStash, encryption keys are managed separately from the server via ZeroKMS, significantly reducing the risk of key exposure in case of a breach. CipherStash also uses searchable encryption to run operations directly on encrypted data, avoiding the performance and security impact of decryption.

Regularly Backup Your PostgreSQL Database

Regularly back up your PostgreSQL database to ensure data availability and disaster recovery in case of data loss or corruption. Additionally, secure your backups to prevent unauthorized access to sensitive data. This can be achieved by limiting access to backup files only to authorized personnel. Set strict file permissions and ownership to ensure that only designated individuals can read or modify backup data:

1# Restrict file permissions to owner only
2
3chmod 600 backup_file.dump

In this example, the chmod command sets the file permissions for backup_file.dump to allow read and write access for the file owner and denies access to all other users.

For improved PostgreSQL security, the backups should be encrypted immediately. For example:

1export BACKUP_PASSWORD="your_password_here"
2pg_dump -d <dbname> -h localhost | openssl enc -aes-256-cbc -salt -pass env:BACKUP_PASSWORD -out dbdump.sql.encrypted

In addition, make sure you store encrypted backup files in a storage service that supports client-side encryption. This ensures that even if you store your backup in the cloud, the cloud provider cannot access your backup data without the decryption key (which resides outside the cloud provider's systems). 

Stay Up-to-Date with PostgreSQL Releases

Lastly, stay up-to-date with new PostgreSQL releases. Database updates often include security patches and bug fixes that protect against known vulnerabilities.

Neglecting updates can leave your database vulnerable to known exploits and security risks.

Enhancing PostgreSQL Security with Searchable Encryption

CipherStash is the ultimate in PostgreSQL security, adding searchable encryption and precision access control down to the row level to PostgreSQL databases.

Searchable encryption, also known as encryption-in-use, allows data to be securely searched and processed while it remains encrypted. With searchable encryption, data and search queries are both encrypted before they reach the server or database. This means that sensitive information can be stored and queried without revealing the data in its unencrypted form, enhancing privacy and security.

Searchable encryption overcomes the limitations of traditional PostgreSQL encryption, where data must be decrypted for use, degrading query performance and leaving data vulnerable to unauthorized access.

Searchable encryption also enables precision data access control by allowing fine-grained control over who can access specific data elements within an encrypted dataset.

PGAudit

PGAudit is an open-source tool extension that further improves PostgreSQL security with comprehensive auditing features that operate at both the database session level and for specific, identified groups of database objects. It allows administrators to track user actions and identify anomalies or unauthorized activities in real time. The audit information is available in the PostgreSQL server logs, and with the help of PGAudit Analyze, the audited information can be loaded into a database for analysis.

Conclusion

PostgreSQL is a robust and secure database management system, but it requires proper configuration to ensure data security. Neglecting PostgreSQL security can have severe consequences, including data breaches and financial losses. By following best practices, such as access control, strong passwords, and regular audits, you can significantly reduce the risk of data breaches.

Additionally, encryption is a vital aspect of securing data in PostgreSQL, and while pgcrypto is available, using a specialized tool like CipherStash can provide fully supported SQL using searchable encryption and key management capabilities. By harnessing the capabilities of tools like CipherStash, data security professionals can fortify their PostgreSQL environments against emerging threats.

Explore CipherStash today to enhance your PostgreSQL security with fast, searchable encryption and precision access control.