PostgreSQL Security Best Practices

Roles & Privileges

PostgreSQL uses a role-based access control system. A role can be a user (with login) or a group (without login). Follow the principle of least privilege — grant only the permissions each role actually needs.

-- Create roles with specific purposes
CREATE ROLE app_readonly;
CREATE ROLE app_readwrite;
CREATE ROLE app_admin;

-- Grant schema-level access
GRANT USAGE ON SCHEMA public TO app_readonly, app_readwrite;

-- Read-only role
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO app_readonly;

-- Read-write role inherits read + gets INSERT, UPDATE, DELETE
GRANT app_readonly TO app_readwrite;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT INSERT, UPDATE, DELETE ON TABLES TO app_readwrite;

-- Create login users that inherit role permissions
CREATE USER api_service WITH PASSWORD 'strong_password_here' IN ROLE app_readwrite;
CREATE USER analytics_user WITH PASSWORD 'another_password' IN ROLE app_readonly;

Row Level Security (RLS)

RLS lets you define per-row access policies directly in the database. Each query automatically filters rows based on the current user or session context — even if the application has bugs.

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

-- Policy: users can only see their own documents
CREATE POLICY documents_select ON documents
  FOR SELECT
  USING (owner_id = current_setting('app.current_user_id')::int);

-- Policy: users can only insert documents they own
CREATE POLICY documents_insert ON documents
  FOR INSERT
  WITH CHECK (owner_id = current_setting('app.current_user_id')::int);

-- Set the user context from your application
SET app.current_user_id = '42';
SELECT * FROM documents;  -- only shows documents owned by user 42

-- Admin policy: full access for admin role
CREATE POLICY admin_all ON documents
  FOR ALL
  TO app_admin
  USING (true)
  WITH CHECK (true);

pg_hba.conf & Network Security

# pg_hba.conf controls WHO can connect, FROM WHERE, and HOW
# TYPE    DATABASE  USER          ADDRESS           METHOD

# Local connections via Unix socket
local     all       postgres                        peer

# Application server (specific IP)
host      app_db    api_service   10.0.1.0/24       scram-sha-256

# Analytics (read-only subnet)
host      app_db    analytics_user 10.0.2.0/24      scram-sha-256

# Replication
host      replication replicator   10.0.3.0/24      scram-sha-256

# Reject everything else (implicit, but explicit is better)
host      all       all           0.0.0.0/0         reject

SSL/TLS Connections

-- postgresql.conf: require SSL
ssl = on
ssl_cert_file = '/etc/ssl/certs/server.crt'
ssl_key_file  = '/etc/ssl/private/server.key'
ssl_ca_file   = '/etc/ssl/certs/ca.crt'

-- pg_hba.conf: require SSL for remote connections
hostssl  app_db  api_service  10.0.1.0/24  scram-sha-256

-- Connection string with SSL
-- postgresql://api_service:pass@db.example.com:5432/app_db?sslmode=verify-full

-- Check SSL status from psql
SELECT ssl, version, cipher FROM pg_stat_ssl
WHERE pid = pg_backend_pid();

Audit Logging with pgAudit

-- Install and enable pgAudit extension
CREATE EXTENSION pgaudit;

-- postgresql.conf settings
shared_preload_libraries = 'pgaudit'
pgaudit.log = 'write, ddl'         -- log all writes and DDL
pgaudit.log_catalog = off          -- skip system catalog queries
pgaudit.log_relation = on          -- log table names

-- Per-role auditing (more granular)
ALTER ROLE api_service SET pgaudit.log = 'write';
ALTER ROLE app_admin SET pgaudit.log = 'all';

-- Audit log entries appear in PostgreSQL's standard log:
-- AUDIT: SESSION,1,1,WRITE,INSERT,TABLE,public.orders,
-- "INSERT INTO orders (user_id, total) VALUES (1, 99.99)"