PostgreSQL Security

PostgreSQL is one of the world's most advanced open-source relational database systems. Like any database, properly securing your PostgreSQL installation is crucial to protect your data from unauthorized access, corruption, or loss.

Introduction

Database security is a critical aspect of any application infrastructure. PostgreSQL offers robust security features that, when properly configured, provide strong protection for your data. This guide will walk you through the essential security concepts and practices for PostgreSQL databases.

Authentication: Controlling Access to Your Database

Authentication is the process of verifying the identity of clients attempting to connect to your database.

Client Authentication Configuration

PostgreSQL manages client authentication through the Host-Based Authentication (HBA) configuration file: pg_hba.conf. This file controls which hosts can connect to which databases, which users can connect, and what authentication methods they must use.

Here's a basic structure of entries in the pg_hba.conf file:

# TYPE  DATABASE  USER  ADDRESS      METHOD
local   all       all                md5
host    all       all   127.0.0.1/32 md5
host    all       all   ::1/128      md5

Let's examine each field:

TYPE: Connection type (local, host, hostssl, hostnossl)
DATABASE: Database name(s) this rule applies to
USER: Username(s) this rule applies to
ADDRESS: Client IP address/range (for host, hostssl, hostnossl types)
METHOD: Authentication method (md5, scram-sha-256, trust, reject, etc.)

Authentication Methods

PostgreSQL supports several authentication methods:

Trust: No password required (dangerous in production!)
Password-based:
- md5: MD5-hashed password authentication
- scram-sha-256: More secure password authentication (recommended)
- password: Plain-text passwords (avoid using!)
External Services:
- LDAP: Authenticate against an LDAP directory
- RADIUS: Remote Authentication Dial-In User Service
- PAM: Pluggable Authentication Modules
System-level:
- ident: Uses operating system user names
- peer: Similar to ident but for local connections
Other:
- cert: SSL certificate authentication
- gss: GSSAPI authentication

Example configuration with different methods:

# TYPE  DATABASE  USER      ADDRESS        METHOD
local   all       postgres                 peer
local   all       all                      scram-sha-256
host    all       all       10.0.0.0/24    scram-sha-256
host    replication replica  192.168.1.100/32  md5
hostssl prod      app_user  0.0.0.0/0      cert

Setting Up Password Policies

Although PostgreSQL doesn't have built-in password policy enforcement, you can implement policies through extensions or application logic. For example, using the passwordcheck extension:

sql
-- Load the passwordcheck extension
CREATE EXTENSION IF NOT EXISTS passwordcheck;

-- Now passwords will be checked against the policy

This extension checks that passwords:

Are at least 8 characters long
Contain both letters and numbers
Are not the same as the username

Authorization: Managing User Access

Once users are authenticated, authorization controls what they can do within the database.

Role-Based Access Control

PostgreSQL uses roles for authorization. A role can be a user (can log in) or a group (cannot log in).

sql
-- Create a login role (user)
CREATE ROLE app_user WITH LOGIN PASSWORD 'secure_password';

-- Create a group role
CREATE ROLE read_only;

-- Grant the read_only role to app_user
GRANT read_only TO app_user;

Privilege Management

PostgreSQL lets you control what actions roles can perform on database objects:

sql
-- Grant SELECT privilege on all tables in schema public to read_only role
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;

-- Grant INSERT and UPDATE on specific table to app_user
GRANT INSERT, UPDATE ON customer_data TO app_user;

-- Revoke DELETE privilege
REVOKE DELETE ON customer_data FROM app_user;

Row-Level Security (RLS)

PostgreSQL supports row-level security policies to restrict which rows users can see or modify:

sql
-- Enable row-level security on a table
ALTER TABLE customer_data ENABLE ROW LEVEL SECURITY;

-- Create a policy that users can only see their own organization's data
CREATE POLICY org_isolation ON customer_data
    USING (org_id = current_setting('app.current_org_id')::integer);

Example of setting the context variable before querying:

sql
-- Set the organization context for the current session
SET app.current_org_id = '42';

-- Now queries will only return rows where org_id = 42
SELECT * FROM customer_data;

Data Encryption

Protecting data both in transit and at rest is essential for comprehensive security.

SSL/TLS Configuration

To encrypt connections between clients and the server:

Generate or obtain SSL certificates
Configure postgresql.conf:

ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'root.crt'  # If using your own CA

Force SSL for specific connections in pg_hba.conf:

# TYPE  DATABASE  USER  ADDRESS      METHOD
hostssl all       all   0.0.0.0/0    scram-sha-256

Data-at-Rest Encryption

PostgreSQL doesn't include built-in encryption for data at rest. Consider these approaches:

File system-level encryption: Use encrypted file systems like LUKS (Linux), BitLocker (Windows), or FileVault (macOS).
Transparent Data Encryption (TDE): Use extensions like pgcrypto for column-level encryption:

sql
-- Install pgcrypto extension
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Create a table with an encrypted column
CREATE TABLE sensitive_data (
    id SERIAL PRIMARY KEY,
    name TEXT,
    ssn TEXT,
    credit_card_encrypted BYTEA
);

-- Insert encrypted data
INSERT INTO sensitive_data (name, ssn, credit_card_encrypted)
VALUES (
    'John Doe',
    '123-45-6789',
    pgp_sym_encrypt('4111-1111-1111-1111', 'encryption_key')
);

-- Query encrypted data
SELECT 
    name,
    ssn,
    pgp_sym_decrypt(credit_card_encrypted, 'encryption_key') AS credit_card
FROM sensitive_data;

Network Security

Protecting network access to your PostgreSQL server is a critical layer of defense.

Restricting Network Access

Configure postgresql.conf for network listening:

listen_addresses = 'localhost'    # Listen only on localhost
# or
listen_addresses = '192.168.1.100'  # Listen on specific IP
# or
listen_addresses = '*'            # Listen on all interfaces (use with caution)

Set up firewall rules to restrict access to PostgreSQL port (default: 5432)

Example for UFW on Ubuntu:

bash
sudo ufw allow from 192.168.1.0/24 to any port 5432

Example for iptables:

bash
sudo iptables -A INPUT -p tcp -s 192.168.1.0/24 --dport 5432 -j ACCEPT
sudo iptables -A INPUT -p tcp --dport 5432 -j DROP

Auditing and Monitoring

Regular auditing and monitoring help detect suspicious activities and ensure compliance with security policies.

Configuring Logging

In postgresql.conf, enable logging for security-relevant events:

log_destination = 'csvlog'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

# What to log
log_connections = on
log_disconnections = on
log_duration = on
log_statement = 'ddl'        # Log data definition changes
log_min_error_statement = warning
log_line_prefix = '%m [%p] %q%u@%d '

Monitoring for Suspicious Activity

Set up monitoring to detect:

Failed login attempts
Unusual query patterns
Access to sensitive tables
Schema changes

Example query to check for failed login attempts:

sql
SELECT 
    to_timestamp(extract(epoch from log_time)::int) AS time,
    client_addr,
    substring(message from 'user "(.+?)"') AS username,
    message
FROM 
    postgresql_log
WHERE 
    message LIKE 'password authentication failed%'
ORDER BY 
    log_time DESC;

Best Practices and Security Checklist

Use this checklist to ensure your PostgreSQL deployment is secure:

Authentication
- Use strong authentication methods (scram-sha-256 preferred)
- Implement strict password policies
- Limit superuser access
- Regularly rotate passwords
Authorization
- Apply principle of least privilege
- Use role-based access control
- Implement row-level security for multi-tenant data
- Regularly audit role permissions
Encryption
- Enable SSL/TLS for all connections
- Use strong TLS protocols (TLSv1.2+ only)
- Implement encryption for sensitive columns
- Use encrypted backups
Network Security
- Restrict network access using pg_hba.conf
- Use firewalls to limit access to PostgreSQL port
- Consider using a VPN or SSH tunneling for remote access
- Keep PostgreSQL behind application servers, not exposed directly
Monitoring and Maintenance
- Enable comprehensive logging
- Set up alerts for suspicious activities
- Keep PostgreSQL updated with security patches
- Perform regular security audits
Additional Protection
- Use connection pooling to mitigate DoS attacks
- Implement rate limiting
- Consider using a Web Application Firewall for database-accessing applications

Common Security Issues and Fixes

Issue: Default or Weak Passwords

Fix: Enforce strong passwords and change defaults immediately

sql
ALTER ROLE postgres WITH PASSWORD 'complex_unique_password';

Issue: Too Permissive pg_hba.conf

Fix: Restrict access in pg_hba.conf

# Replace this line
host    all     all     0.0.0.0/0       md5

# With more restrictive lines
host    app_db  app_user  10.0.1.0/24   scram-sha-256
host    all     all       127.0.0.1/32  scram-sha-256

Issue: Exposed PostgreSQL Server on Internet

Fix: Use a jump server, VPN, or SSH tunnel for remote access

SSH tunnel example:

bash
ssh -L 5432:localhost:5432 user@db-server

Then connect to localhost:5432 from your local machine.

Summary

Implementing proper security measures for your PostgreSQL database is essential to protect your data. By following the principles of authentication, authorization, encryption, and regular monitoring, you can significantly reduce security risks. Remember that security is not a one-time setup but an ongoing process requiring regular updates, audits, and improvements.

Exercises

Set up a PostgreSQL database with password authentication and create roles with different permission levels.
Configure SSL/TLS on your PostgreSQL server and test secure connections.
Implement row-level security on a table to restrict data access based on user roles.
Write a script to monitor and alert on suspicious database activities.
Perform a security audit on an existing PostgreSQL installation and create a remediation plan.

Additional Resources

If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)

Introduction​

Authentication: Controlling Access to Your Database​

Client Authentication Configuration​

Authentication Methods​

Setting Up Password Policies​

Authorization: Managing User Access​

Role-Based Access Control​

Privilege Management​

Row-Level Security (RLS)​

Data Encryption​

SSL/TLS Configuration​

Data-at-Rest Encryption​

Network Security​

Restricting Network Access​

Auditing and Monitoring​

Configuring Logging​

Monitoring for Suspicious Activity​

Best Practices and Security Checklist​

Common Security Issues and Fixes​

Issue: Default or Weak Passwords​

Issue: Too Permissive pg_hba.conf​

Issue: Exposed PostgreSQL Server on Internet​

Summary​

Exercises​

Additional Resources​