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:
-- 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).
-- 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:
-- 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:
-- 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:
-- 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:
-- 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:
sudo ufw allow from 192.168.1.0/24 to any port 5432
Example for iptables:
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:
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
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:
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! :)