PostgreSQL Security Best Practices: Complete Guide

Pulse - Elasticsearch Operations Done Right

On this page

Security Fundamentals Authentication Best Practices Authorization and Access Control Network Security Data Encryption Auditing and Logging Security Maintenance Application Security Compliance and Governance Security Checklist Security Tools and Extensions Common Security Mistakes Related Topics Further Reading

PostgreSQL Security Best Practices: Complete Guide

Securing a PostgreSQL database requires a multi-layered approach covering authentication, authorization, encryption, network security, and monitoring. This guide provides comprehensive best practices for hardening PostgreSQL deployments in production environments.

Security Fundamentals

Defense in Depth

Implement multiple layers of security controls:

  • Network security: Firewalls and network segmentation
  • Authentication: Strong identity verification
  • Authorization: Granular access controls
  • Encryption: Data protection in transit and at rest
  • Auditing: Comprehensive logging and monitoring
  • Patch management: Regular updates and security fixes

Principle of Least Privilege

Grant users only the minimum permissions necessary:

  • Avoid using superuser accounts for applications
  • Create role-specific database users
  • Implement row-level security where appropriate
  • Regularly review and audit permissions

Authentication Best Practices

1. Use Strong Password Authentication

Configure SCRAM-SHA-256 (not MD5):

# postgresql.conf
password_encryption = 'scram-sha-256'

Set strong password policies:

-- Create user with strong password requirements
CREATE ROLE app_user WITH
    LOGIN
    PASSWORD 'YourStrongPassword123!@#'
    CONNECTION LIMIT 50
    VALID UNTIL '2026-12-31';

-- Enforce password complexity (using extension)
CREATE EXTENSION IF NOT EXISTS passwordcheck;

2. Configure pg_hba.conf Securely

Example secure pg_hba.conf:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# Local connections
local   all             postgres                                peer

# IPv4 local connections
host    all             all             127.0.0.1/32            scram-sha-256

# IPv4 remote connections (require SSL)
hostssl all             all             10.0.0.0/8              scram-sha-256

# Reject unencrypted connections
hostnossl all           all             0.0.0.0/0               reject

# IPv6 local connections
host    all             all             ::1/128                 scram-sha-256

Key rules:

  • Use scram-sha-256 instead of md5 or trust
  • Never use trust authentication in production
  • Require SSL for remote connections with hostssl
  • Be specific with IP ranges (avoid 0.0.0.0/0)
  • Order matters - most restrictive rules first

3. Implement Certificate-Based Authentication

Configure SSL certificates:

# postgresql.conf
ssl = on
ssl_cert_file = '/path/to/server.crt'
ssl_key_file = '/path/to/server.key'
ssl_ca_file = '/path/to/ca.crt'
ssl_crl_file = '/path/to/crl.pem'

Require client certificates:

# pg_hba.conf
hostssl all all 0.0.0.0/0 cert clientcert=verify-full

4. Use External Authentication

LDAP Integration:

# pg_hba.conf
host all all 0.0.0.0/0 ldap ldapserver=ldap.example.com ldapprefix="uid=" ldapsuffix=",ou=users,dc=example,dc=com"

Kerberos/GSSAPI:

# pg_hba.conf
host all all 0.0.0.0/0 gss include_realm=0 krb_realm=EXAMPLE.COM

Authorization and Access Control

1. Role-Based Access Control (RBAC)

Create hierarchical roles:

-- Create base roles
CREATE ROLE readonly;
CREATE ROLE readwrite;
CREATE ROLE admin;

-- Grant permissions to roles
GRANT CONNECT ON DATABASE mydb TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

GRANT readonly TO readwrite;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO readwrite;

-- Create users and assign roles
CREATE ROLE app_user WITH LOGIN PASSWORD 'secure_password';
GRANT readwrite TO app_user;

Default privileges for future objects:

-- Ensure new tables inherit permissions
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;

2. Schema-Level Security

Separate schemas for isolation:

-- Create schemas for different applications
CREATE SCHEMA app1;
CREATE SCHEMA app2;

-- Revoke public access
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- Grant schema access to specific roles
GRANT USAGE ON SCHEMA app1 TO app1_user;
GRANT ALL ON ALL TABLES IN SCHEMA app1 TO app1_user;

3. Row-Level Security (RLS)

Implement RLS for multi-tenant applications:

-- Enable RLS on table
ALTER TABLE customers ENABLE ROW LEVEL SECURITY;

-- Create policy for tenant isolation
CREATE POLICY tenant_isolation ON customers
    USING (tenant_id = current_setting('app.tenant_id')::INTEGER);

-- Create policy for admin access
CREATE POLICY admin_access ON customers
    TO admin_role
    USING (true);

For detailed RLS implementation, see PostgreSQL Row Level Security.

4. Function and Procedure Security

Use SECURITY DEFINER carefully:

-- Create function with restricted privileges
CREATE OR REPLACE FUNCTION secure_update_user(user_id INT, new_name TEXT)
RETURNS VOID
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
    -- Validate inputs
    IF user_id IS NULL OR new_name IS NULL THEN
        RAISE EXCEPTION 'Invalid parameters';
    END IF;

    -- Perform operation
    UPDATE users SET name = new_name WHERE id = user_id;
END;
$$;

-- Revoke direct table access
REVOKE ALL ON users FROM app_user;

-- Grant execute permission on function
GRANT EXECUTE ON FUNCTION secure_update_user TO app_user;

Network Security

1. SSL/TLS Configuration

Enable SSL with strong ciphers:

# postgresql.conf
ssl = on
ssl_min_protocol_version = 'TLSv1.2'
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'
ssl_prefer_server_ciphers = on

Generate SSL certificates:

# Generate private key
openssl genrsa -out server.key 2048
chmod 600 server.key

# Generate certificate signing request
openssl req -new -key server.key -out server.csr

# Generate self-signed certificate (for testing)
openssl x509 -req -in server.csr -signkey server.key -out server.crt -days 365

# For production, use certificates from a trusted CA

2. Firewall Configuration

Configure host-based firewall:

# UFW (Ubuntu/Debian)
sudo ufw allow from 10.0.0.0/8 to any port 5432
sudo ufw deny 5432

# firewalld (RHEL/CentOS)
sudo firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="10.0.0.0/8" port protocol="tcp" port="5432" accept'
sudo firewall-cmd --reload

3. Listen Address Configuration

Bind to specific interfaces:

# postgresql.conf
# Don't listen on all interfaces
# listen_addresses = '*'  # BAD

# Listen on specific IP
listen_addresses = '10.0.1.100'

# Or localhost only for local connections
listen_addresses = 'localhost'

Data Encryption

1. Encryption in Transit

Enforce SSL connections:

-- Require SSL for specific user
ALTER ROLE app_user SET ssl = on;

-- Check SSL connections
SELECT datname, usename, ssl, client_addr
FROM pg_stat_ssl
JOIN pg_stat_activity ON pg_stat_ssl.pid = pg_stat_activity.pid;

2. Encryption at Rest

Enable transparent data encryption (using third-party tools):

Using pgcrypto for column-level encryption:

-- Install pgcrypto extension
CREATE EXTENSION pgcrypto;

-- Encrypt sensitive data
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT,
    ssn BYTEA
);

-- Insert encrypted data
INSERT INTO users (email, ssn)
VALUES ('user@example.com',
        pgp_sym_encrypt('123-45-6789', 'encryption_key'));

-- Query encrypted data
SELECT email,
       pgp_sym_decrypt(ssn, 'encryption_key') as ssn
FROM users;

File system encryption:

# Use LUKS for Linux
sudo cryptsetup luksFormat /dev/sdb
sudo cryptsetup luksOpen /dev/sdb pgdata
sudo mkfs.ext4 /dev/mapper/pgdata
sudo mount /dev/mapper/pgdata /var/lib/postgresql

3. Backup Encryption

Encrypt backups:

# Using pg_dump with encryption
pg_dump mydb | gpg --encrypt --recipient backup@example.com > backup.sql.gpg

# Using pg_basebackup with encryption
pg_basebackup -D - -Ft | gpg --encrypt > backup.tar.gpg

Auditing and Logging

1. Configure Comprehensive Logging

Essential logging settings:

# postgresql.conf
logging_collector = on
log_destination = 'stderr'
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB

# What to log
log_connections = on
log_disconnections = on
log_duration = on
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_statement = 'ddl'
log_min_duration_statement = 1000  # Log queries longer than 1 second

# Authentication failures
log_error_verbosity = default

2. Implement pgAudit

Install and configure pgAudit:

-- Install extension
CREATE EXTENSION pgaudit;

-- Configure audit logging
ALTER SYSTEM SET pgaudit.log = 'all';
ALTER SYSTEM SET pgaudit.log_catalog = off;
ALTER SYSTEM SET pgaudit.log_parameter = on;
ALTER SYSTEM SET pgaudit.log_relation = on;
ALTER SYSTEM SET pgaudit.log_statement_once = off;

SELECT pg_reload_conf();

Audit specific operations:

-- Audit all DDL and DML for specific role
ALTER ROLE app_user SET pgaudit.log = 'write, ddl';

-- Audit specific tables
CREATE TABLE sensitive_data (
    id SERIAL PRIMARY KEY,
    data TEXT
);

COMMENT ON TABLE sensitive_data IS 'pgaudit.log=read,write';

3. Monitor Security Events

Query for failed authentication attempts:

# grep for authentication failures
grep "authentication failed" /var/log/postgresql/postgresql-*.log

# grep for authorization failures
grep "permission denied" /var/log/postgresql/postgresql-*.log

Create monitoring views:

-- Monitor active connections
CREATE VIEW security_monitor AS
SELECT
    datname,
    usename,
    application_name,
    client_addr,
    state,
    query_start,
    state_change,
    query
FROM pg_stat_activity
WHERE usename NOT IN ('postgres', 'replication')
ORDER BY query_start DESC;

-- Monitor database size
CREATE VIEW database_sizes AS
SELECT
    datname,
    pg_size_pretty(pg_database_size(datname)) as size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

Security Maintenance

1. Regular Updates

Keep PostgreSQL updated:

# Check current version
psql -c "SELECT version();"

# Update PostgreSQL (Debian/Ubuntu)
sudo apt update
sudo apt upgrade postgresql

# Update PostgreSQL (RHEL/CentOS)
sudo yum update postgresql-server

Subscribe to security announcements:

  • PostgreSQL security mailing list
  • CVE databases
  • OS security updates

2. Regular Security Audits

Audit user permissions:

-- List all users and roles
SELECT
    rolname,
    rolsuper,
    rolcreaterole,
    rolcreatedb,
    rolcanlogin,
    rolconnlimit,
    rolvaliduntil
FROM pg_roles
ORDER BY rolname;

-- List table permissions
SELECT
    grantee,
    table_schema,
    table_name,
    privilege_type
FROM information_schema.table_privileges
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY grantee, table_name;

-- Find tables without RLS
SELECT schemaname, tablename
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
AND tablename NOT IN (
    SELECT tablename FROM pg_policies
);

3. Backup Security

Secure backup procedures:

# Encrypted backup script
#!/bin/bash
BACKUP_DIR="/secure/backups"
DATE=$(date +%Y%m%d_%H%M%S)
ENCRYPTION_KEY="backup@example.com"

# Perform backup with encryption
pg_dump -Fc mydb | \
    gpg --encrypt --recipient $ENCRYPTION_KEY > \
    $BACKUP_DIR/mydb_$DATE.dump.gpg

# Set secure permissions
chmod 600 $BACKUP_DIR/mydb_$DATE.dump.gpg

# Verify backup
gpg --decrypt $BACKUP_DIR/mydb_$DATE.dump.gpg | \
    pg_restore --list > /dev/null

Application Security

1. Prevent SQL Injection

Use parameterized queries:

# Python example with psycopg2
import psycopg2

# BAD - Vulnerable to SQL injection
user_input = "admin' OR '1'='1"
cursor.execute(f"SELECT * FROM users WHERE username = '{user_input}'")

# GOOD - Parameterized query
cursor.execute("SELECT * FROM users WHERE username = %s", (user_input,))

// Node.js example with pg
const { Pool } = require('pg');
const pool = new Pool();

// BAD - Vulnerable to SQL injection
const userId = "1 OR 1=1";
pool.query(`SELECT * FROM users WHERE id = ${userId}`);

// GOOD - Parameterized query
pool.query('SELECT * FROM users WHERE id = $1', [userId]);

2. Connection Security

Use connection pooling securely:

# Python example
from psycopg2 import pool

connection_pool = pool.SimpleConnectionPool(
    1, 20,
    host="localhost",
    database="mydb",
    user="app_user",
    password="secure_password",
    sslmode="require",
    sslrootcert="/path/to/ca.crt"
)

3. Secure Configuration Management

Use environment variables for credentials:

# .env file (never commit to git)
PGHOST=localhost
PGDATABASE=mydb
PGUSER=app_user
PGPASSWORD=secure_password
PGSSLMODE=require

Use pgpass file:

# ~/.pgpass (chmod 0600)
hostname:port:database:username:password
localhost:5432:mydb:app_user:secure_password

Compliance and Governance

1. Regulatory Compliance

GDPR Compliance:

  • Implement data retention policies
  • Enable comprehensive audit trails
  • Support data export and deletion
  • Encrypt personal data
-- Data retention policy
CREATE TABLE gdpr_deletions (
    user_id INTEGER,
    deleted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Anonymize user data
CREATE OR REPLACE FUNCTION anonymize_user(user_id INT)
RETURNS VOID AS $$
BEGIN
    UPDATE users
    SET
        email = 'deleted_' || user_id || '@example.com',
        name = 'Deleted User',
        phone = NULL
    WHERE id = user_id;

    INSERT INTO gdpr_deletions (user_id) VALUES (user_id);
END;
$$ LANGUAGE plpgsql;

HIPAA Compliance:

  • Encrypt PHI data
  • Implement access controls
  • Enable comprehensive audit logging
  • Regular security assessments

2. Security Documentation

Maintain security documentation:

  • Security policies and procedures
  • Access control matrix
  • Incident response plan
  • Disaster recovery procedures
  • Security configuration baselines

Security Checklist

Installation and Configuration

  • Install latest stable PostgreSQL version
  • Configure strong password authentication (SCRAM-SHA-256)
  • Secure pg_hba.conf with restrictive rules
  • Enable SSL/TLS for all connections
  • Configure listen_addresses appropriately
  • Set up firewall rules
  • Disable unnecessary extensions
  • Configure secure logging

Access Control

  • Implement role-based access control
  • Remove or secure default postgres user
  • Enable row-level security where needed
  • Use SECURITY DEFINER functions carefully
  • Regular permission audits
  • Implement password policies
  • Set connection limits per role

Encryption

  • Enable SSL/TLS with strong ciphers
  • Implement encryption at rest
  • Encrypt backups
  • Use encrypted connections from applications
  • Consider column-level encryption for sensitive data

Monitoring and Auditing

  • Enable comprehensive logging
  • Install and configure pgAudit
  • Set up log monitoring and alerting
  • Monitor failed authentication attempts
  • Track privilege escalation attempts
  • Regular security log reviews

Maintenance

  • Regular security updates
  • Scheduled vulnerability scans
  • Periodic security audits
  • Backup testing and validation
  • Incident response planning
  • Security training for team

Security Tools and Extensions

pgAudit

Comprehensive audit logging for PostgreSQL.

passwordcheck

Enforces password strength policies.

pgcrypto

Cryptographic functions for data encryption.

pg_permissions

Analyzes and reports on database permissions.

pghero

Performance and security monitoring dashboard.

Common Security Mistakes

1. Using Trust Authentication

Don't:

# pg_hba.conf
local all all trust  # NEVER in production

Do:

# pg_hba.conf
local all all scram-sha-256

2. Overly Permissive pg_hba.conf

Don't:

host all all 0.0.0.0/0 md5

Do:

hostssl specific_db app_user 10.0.1.0/24 scram-sha-256

3. Using Superuser for Applications

Don't:

-- Using postgres user in application
connection_string = "postgresql://postgres:password@host/db"

Do:

-- Use dedicated application user with minimal privileges
CREATE ROLE app_user WITH LOGIN PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE mydb TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON specific_table TO app_user;

Further Reading

Pulse - Elasticsearch Operations Done Right

Pulse can solve your Elasticsearch issues

Subscribe to the Pulse Newsletter

Get early access to new Pulse features, insightful blogs & exclusive events , webinars, and workshops.

We use cookies to provide an optimized user experience and understand our traffic. To learn more, read our use of cookies; otherwise, please choose 'Accept Cookies' to continue using our website.