Advanced20 min read

Security

Secure PostgreSQL with roles, RLS, encryption, and network hardening.

User and Role Management

Roles can login (users) or group roles inheriting privileges. GRANT least privilege on schemas, tables, sequences. PUBLIC default privileges should be restricted.

Revoke CREATE on schema public from PUBLIC in PostgreSQL 15+ hardened defaults.

CREATE ROLE app_user LOGIN PASSWORD 'secret';
GRANT CONNECT ON DATABASE myapp TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

Permissions

Column-level GRANT limits exposure. SECURITY DEFINER functions elevate privileges narrowly. Audit with pg_catalog and pgAudit extension for compliance logging.

Separate migration role with DDL from application DML role.

Never use superuser for applications
Rotate passwords and use SCRAM-SHA-256 authentication
Integrate LDAP or cert auth for enterprise identity

Row-Level Security

RLS policies filter rows per role using USING and WITH CHECK expressions. Enable on table with ALTER TABLE ... ENABLE ROW LEVEL SECURITY.

Essential for multi-tenant SaaS where shared tables hold all tenants—policy compares tenant_id to current_setting.

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON documents
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

Encryption

SSL/TLS encrypts connections—require sslmode=verify-full in production. pgcrypto extension encrypts columns at application-selected granularity.

Full disk encryption at volume level protects data at rest on disk theft; TLS protects in transit.

hostssl all all 0.0.0.0/0 scram-sha-256

Network and Hardening

Bind to private interfaces; restrict pg_hba.conf to application subnets. Disable unnecessary extensions and superuser remote access.

Keep PostgreSQL patched. Run vulnerability scans. Include SQL injection prevention in application layer as primary defense.