PostgreSQL Security Best Practices: A Developer's Guide

TL;DR
Secure your PostgreSQL database by configuring access control (UDS + TCP/IP), implementing proper user roles and row-level security, enabling SSL encryption, and using pgcrypto for sensitive data.

📰 Read the full article: Best Practices for Postgres Security

Your database contains your most valuable asset: data. Yet a shocking number of PostgreSQL instances remain vulnerable to attacks due to poor security practices.

This guide cuts through the complexity of PostgreSQL security, providing actionable best practices to lock down your database and protect what matters most—without sacrificing performance or accessibility.

PostgreSQL Access Control

To ensure only authorized processes and remote connections can access your PostgreSQL database—providing a foundational layer of security—you need to secure access via Unix Domain Sockets (UDS) and TCP/IP.

Unix Domain Sockets

Unix Domain Sockets (UDS) provide a file-based access control system, managing permissions between processes on the same host. They facilitate data exchange between processes, establishing a secure and efficient method for local inter-process communication.

PostgreSQL UDS configuration: allows precise control through parameters such as:

• unix_socket_directories: Defines the directories where Unix-domain sockets for client connections will be placed.

unix_socket_group: Sets the owning group of the Unix-domain sockets.

unix_socket_permissions: Specifies the access permissions for the Unix-domain sockets.

TCP/IP

TCP/IP protocol is essential for managing remote database connections. By default, PostgreSQL listens on port 5432 for incoming connections.

Firewall configuration is critical - only known and trusted IP addresses should be allowed to connect.

*Example firewall rule: *

sudo iptables -A INPUT -p tcp --dport 5432 -s trusted_ip_address -j ACCEPT
sudo iptables -A INPUT -p tcp --dport 5432 -j DROP

This setup allows connections to port 5432 from a trusted IP address while dropping requests from unknown sources.

PostgreSQL User Roles and Groups

Creating users

Creating users in PostgreSQL is fundamental for database access management. Users are distinct from operating system users, having global scope across a database cluster rather than being restricted to individual databases.

The primary SQL command for this operation is CREATE USER, which allows specifying the username and optionally, a password for the user.

The process is straightforward:

CREATE USER username WITH PASSWORD 'password';

This command creates a new database user with login capabilities.

Creating roles

Roles in PostgreSQL serve as a versatile mechanism for managing database permissions. They can represent individual users, groups of users, or a set of permissions.

CREATE ROLE role_name WITH LOGIN CREATEDB;

This example creates a role with the ability to log in and create databases. Roles streamline permission management through a centralized role-based access control system.

Row-level permission

PostgreSQL's Row-Level Security (RLS) feature adds a finer granularity of access control, allowing administrators to define policies that restrict access to specific rows within a table.

💡 _This is particularly useful in multi-tenant environments where users should only see their data. _

To enable RLS, you use the ALTER TABLE command:

ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;

Following this, you can define policies that specify the conditions under which rows are visible or modifiable by a role or user, using the CREATE POLICY command.

PostgreSQL Encryption

SSL encryption

SSL encryption safeguards data in transit, preventing man-in-the-middle attacks. While PostgreSQL doesn't natively implement SSL, it supports SSL connections to encrypt data between clients and servers.

Setting up SSL for PostgreSQL involves:

• Generating SSL certificates

• Configuring the PostgreSQL server for SSL

• Enforcing SSL connections for clients

Database encryption with pgcrypto

The pgcrypto extension adds encryption capabilities directly within PostgreSQL, allowing for the encryption of individual fields, entire tables, or data transactions. This is particularly useful for sensitive data that requires encryption at rest, such as healthcare records or personal identification information.

Basic example of using pgcrypto: To encrypt data without specifying a key explicitly, you can use the pgp_sym_encrypt function. Here's a simple example that encrypts a text string:

SELECT pgp_sym_encrypt('Sensitive data here', 'encryption_key_here') AS encrypted_data;

Decrypting the data can be done with the corresponding pgp_sym_decrypt function:

SELECT pgp_sym_decrypt(encrypted_data, 'encryption_key_here') AS original_data
FROM (SELECT pgp_sym_encrypt('Sensitive data here', 'encryption_key_here') AS encrypted_data) AS subquery;

Advanced Security with Timescale

Timescale enhances PostgreSQL by seamlessly integrating with it, inheriting its robust security framework while introducing advanced features.

Using pgcrypto with Timescale

After ensuring Timescale and PostgreSQL are properly installed, activate pgcrypto:

CREATE EXTENSION IF NOT EXISTS pgcrypto;

This allows the use of various cryptographic operations within your Timescale database environment. For example, to encrypt sensor readings in a hypertable, you could use the following approach:

-- Create a table for sensor readings
CREATE TABLE sensor_readings (
  time TIMESTAMPTZ NOT NULL,
  device_id BIGINT,
  reading BYTEA
);

-- Convert the table into a hypertable
SELECT create_hypertable('sensor_readings', 'time');

-- Insert an encrypted reading
INSERT INTO sensor_readings (time, device_id, reading)
VALUES (NOW(), 1, pgp_sym_encrypt('20.5', 'supersecretkey'));

To decrypt the data for use, you would use:

SELECT time, device_id, pgp_sym_decrypt(reading, 'supersecretkey') AS reading
FROM sensor_readings
WHERE device_id = 1;

Timescale Security Features
Timescale offers several advanced security features:

• Data encryption: Always encrypted, both in transit and at rest

• Networking security: Access strictly over TLS-encrypted connections

• Maintenance access protocol: Rigorous protocol with audit logging

• Privacy compliance: Compliant with regulations such as GDPR

• SOC 2 Type 2 compliance: Available for Enterprise Tier customers

• VPC peering: Isolated access without public internet exposure

Conclusion

We've explored essential best practices for securing PostgreSQL databases:

• Configuring Unix Domain Sockets and TCP/IP protocols

• Implementing role-based access control

• Enabling row-level security

• Utilizing SSL and pgcrypto for encryption

Timescale further extends PostgreSQL's security framework with advanced features that ensure comprehensive protection for time-series data.

With these security measures, you can create a robust security posture that protects your valuable data against unauthorized access and potential breaches.

If you want to maximize your time-series data with top-of-the-line security, try Timescale today. Or head to our Docs or Security page to learn more.

What security practices do you implement for your PostgreSQL databases? Share your experiences in the comments!