Security¶

PostgreSQL provides a defence-in-depth security model encompassing host-based authentication (pg_hba.conf), role-based access control, row-level security policies, encryption in transit via SSL/TLS, and extensibility through audit and cryptographic extensions. The system is designed so that security is enforced at multiple layers: network, authentication, authorisation, and data.

pg_hba.conf -- Host-Based Authentication¶

The pg_hba.conf file controls which clients can connect, how they authenticate, and from which network addresses. It is the first gate in PostgreSQL's access control chain. Every connection attempt is evaluated against the rules in this file top-to-bottom; the first matching rule wins.

Record Format¶

TYPE  DATABASE  USER  ADDRESS       METHOD
host  all       all   10.0.0.0/8    scram-sha-256
host  appdb     app   192.168.1.0/24  cert
local all       all                 peer

Field	Values	Description
TYPE	`local`, `host`, `hostssl`, `hostnossl`, `hostgssenc`, `hostnogssenc`	Connection type. `local` = Unix socket. `hostssl` = TLS-only TCP.
DATABASE	`all`, db name, `sameuser`, `@file`	Target database.
USER	`all`, role name, `+groupname`, `@file`	Target role. `+groupname` matches all members of that role.
ADDRESS	CIDR range, hostname	Client IP range (TCP connections only).
METHOD	See below	Authentication method.

Authentication Methods¶

Method	Description
`trust`	No authentication. Anyone can connect. Never use in production.
`reject`	Reject the connection unconditionally. Used for deny rules.
`scram-sha-256`	SCRAM-SHA-256 challenge-response. Recommended for password auth.
`md5`	Legacy MD5-challenge. Pre-PG10 default. Superseded by `scram-sha-256`.
`password`	Cleartext password. Never use on non-TLS connections.
`peer`	OS user name must match PostgreSQL role name. Unix sockets only.
`cert`	Client must present a valid TLS certificate. CN must match role name.
`gss`	GSSAPI / Kerberos authentication.
`ldap`	LDAP bind authentication.
`radius`	RADIUS authentication.
`pam`	PAM-based authentication.

Example Production Configuration¶

# pg_hba.conf
# TYPE   DATABASE  USER       ADDRESS          METHOD

# Local admin via peer auth (OS user = postgres)
local    all       postgres                    peer

# Application connections via SCRAM-SHA-256
hostssl  appdb     app_user   10.0.0.0/8      scram-sha-256

# Replication connections with certificate auth
hostssl  replication replicator 192.168.1.0/24 cert

# Read-only connections from reporting subnet
hostssl  appdb     reporting  172.16.0.0/16   scram-sha-256

# Deny all other connections
host     all       all        0.0.0.0/0        reject

Reload After Changes

After modifying pg_hba.conf, reload the configuration: SELECT pg_reload_conf(); or pg_ctl reload. A full restart is not required.

SCRAM-SHA-256 Authentication¶

PostgreSQL 10+ supports SCRAM-SHA-256 (Salted Challenge Response Authentication Mechanism) as the recommended password authentication method. It provides:

Mutual authentication -- both client and server verify each other.
Salted hashing -- passwords are stored as salted SHA-256 hashes in pg_authid.rolpassword.
Replay protection -- each authentication exchange uses a unique nonce.
Channel binding (PG 11+) -- ties the SCRAM exchange to the TLS session, preventing man-in-the-middle attacks even if the server certificate is compromised.

Enabling SCRAM-SHA-256¶

# postgresql.conf
password_encryption = scram-sha-256

After changing password_encryption, existing passwords remain in their original format until the user's password is set again:

-- Reset a user's password to upgrade to SCRAM-SHA-256
ALTER ROLE app_user PASSWORD 'new_password';

Role System¶

PostgreSQL uses a unified role model where "users" and "groups" are both roles. A role with the LOGIN attribute can connect to the database. Roles can inherit privileges from other roles via membership.

Role Attributes¶

Attribute	Effect
`LOGIN`	Can authenticate and connect.
`SUPERUSER`	Bypasses all permission checks (except RLS if configured). Use sparingly.
`CREATEDB`	Can create databases.
`CREATEROLE`	Can create, alter, and drop other roles.
`REPLICATION`	Can initiate streaming replication connections.
`BYPASSRLS`	Bypasses row-level security policies.
`CONNECTION LIMIT`	Limits concurrent connections for this role.
`PASSWORD`	Sets the authentication password (hashed).
`VALID UNTIL`	Password expiration timestamp.

-- Create an application role with limited attributes
CREATE ROLE app_user LOGIN
    PASSWORD 'secure_password'
    VALID UNTIL '2027-01-01'
    CONNECTION LIMIT 20;

-- Create an administrative role
CREATE ROLE db_admin LOGIN CREATEDB CREATEROLE
    PASSWORD 'admin_password';

-- Grant role membership
GRANT db_admin TO senior_engineer;

Privilege Model¶

PostgreSQL privileges apply at multiple levels:

Object	Grantable Privileges
Database	`CREATE`, `CONNECT`, `TEMPORARY`, `ALL`
Schema	`CREATE`, `USAGE`, `ALL`
Table	`SELECT`, `INSERT`, `UPDATE`, `DELETE`, `TRUNCATE`, `REFERENCES`, `TRIGGER`, `ALL`
Sequence	`USAGE`, `SELECT`, `UPDATE`, `ALL`
Function	`EXECUTE`, `ALL`
Foreign Server	`USAGE`, `ALL`

-- Grant granular privileges
GRANT CONNECT ON DATABASE appdb TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE ON TABLE orders TO app_user;
GRANT USAGE, SELECT ON SEQUENCE orders_id_seq TO app_user;

-- Set default privileges for future tables in the schema
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE ON TABLES TO app_user;

Row-Level Security (RLS)¶

Row-Level Security allows database administrators to define policies that restrict which rows a given role can see or modify. RLS is a powerful mechanism for multi-tenant applications and regulatory compliance.

Enabling RLS¶

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

-- By default, the table owner bypasses RLS. Force the owner to comply:
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

Policy Types¶

Command	Policy Applies To	Example
`SELECT`	Readable rows	`USING (tenant_id = current_setting('app.tenant_id')::int)`
`INSERT`	Insertable rows	`WITH CHECK (tenant_id = current_setting('app.tenant_id')::int)`
`UPDATE`	Visible + modifiable rows	Both `USING` and `WITH CHECK` clauses
`DELETE`	Deletable rows	`USING (tenant_id = current_setting('app.tenant_id')::int)`
`ALL`	All commands	Combined policy

Example: Multi-Tenant Policy¶

-- Create a policy that isolates tenants
CREATE POLICY tenant_isolation ON orders
    USING (tenant_id = current_setting('app.current_tenant')::INTEGER)
    WITH CHECK (tenant_id = current_setting('app.current_tenant')::INTEGER);

-- Grant table access to the application role
GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO app_user;

RLS and Superusers

By default, superusers bypass RLS. Use FORCE ROW LEVEL SECURITY on the table if even superusers should be subject to the policy.

SSL / TLS Encryption¶

PostgreSQL supports TLS 1.2 and TLS 1.3 for encrypting client-server connections and replication channels.

Server Configuration¶

# postgresql.conf
ssl = on
ssl_cert_file = '/etc/postgresql/server.crt'
ssl_key_file  = '/etc/postgresql/server.key'
ssl_ca_file   = '/etc/postgresql/ca.crt'        # for client cert verification
ssl_min_protocol_version = 'TLSv1.2'
ssl_prefer_server_ciphers = on

Client Certificate Authentication¶

Combine TLS with the cert method in pg_hba.conf to require client certificates:

# Require a valid client certificate where CN matches the PostgreSQL role
hostssl  all  all  0.0.0.0/0  cert

Connection string for certificate-based connections:

postgresql://user@host:5432/db?sslmode=verify-full&sslcert=/path/client.crt&sslkey=/path/client.key&sslrootcert=/path/ca.crt

SSL Mode Reference¶

`sslmode`	Behaviour
`disable`	No encryption.
`allow`	Prefer non-TLS, fall back to TLS.
`prefer` (default)	Prefer TLS, fall back to non-TLS.
`require`	TLS required. No certificate verification.
`verify-ca`	TLS required. Server certificate must be signed by a trusted CA.
`verify-full`	TLS required. Server certificate verified and CN must match hostname.

pgAudit Extension¶

The pgaudit extension provides detailed session and object audit logging for PostgreSQL. It logs SQL statements (with parameters) to the standard PostgreSQL log, enabling compliance and forensic analysis.

Installation and Configuration¶

-- Enable the extension (requires shared_preload_libraries)
CREATE EXTENSION pgaudit;

# postgresql.conf
shared_preload_libraries = 'pgaudit'
pgaudit.log = 'all'
pgaudit.log_catalog = off
pgaudit.log_parameter = on
pgaudit.log_relation = on
pgaudit.log_statement_once = off

Log Levels¶

`pgaudit.log` Value	What Is Logged
`READ`	SELECT, COPY FROM
`WRITE`	INSERT, UPDATE, DELETE, TRUNCATE, COPY TO
`FUNCTION`	Function calls and DO blocks
`ROLE`	GRANT, REVOKE, CREATE/ALTER/DROP ROLE
`DDL`	CREATE, ALTER, DROP for non-role objects
`MISC`	SET, DISCARD, LOCK, CHECKPOINT
`ALL`	All of the above

Example Output¶

LOG:  AUDIT: SESSION,1,1,WRITE,INSERT,TABLE,public.orders,INSERT INTO orders (id, total) VALUES (42, 99.50);,<none>
LOG:  AUDIT: OBJECT,2,1,READ,SELECT,TABLE,public.customers,SELECT * FROM customers WHERE id = 42,<none>

Encryption¶

pgcrypto Extension¶

The pgcrypto extension provides cryptographic functions for encrypting individual columns or data values within the database:

CREATE EXTENSION pgcrypto;

-- Hash a password with bcrypt
SELECT crypt('user_password', gen_salt('bf'));

-- Encrypt a column value with AES-256
INSERT INTO secrets (id, data)
VALUES (1, pgp_sym_encrypt('sensitive data', 'encryption_key'));

-- Decrypt
SELECT pgp_sym_decrypt(data, 'encryption_key') FROM secrets WHERE id = 1;

Available functions:

Function	Purpose
`crypt(password, salt)`	Password hashing (bf/blowfish, sha256, sha512).
`gen_salt(type)`	Generate a salt for password hashing.
`pgp_sym_encrypt(data, key)`	Symmetric encryption using PGP.
`pgp_sym_decrypt(data, key)`	Symmetric decryption.
`pgp_pub_encrypt(data, pgp_key)`	Asymmetric (public key) encryption.
`pgp_pub_decrypt(data, private_key)`	Asymmetric decryption.
`digest(data, algorithm)`	Compute a hash (sha256, sha512, etc.).
`hmac(data, key, algorithm)`	Compute HMAC.

Transparent Data Encryption (TDE)¶

PostgreSQL does not include built-in TDE at the storage level. Options for full-disk or filesystem-level encryption include:

Linux: LUKS (Linux Unified Key Setup) -- block-level encryption for the data volume.
Filesystem: eCryptfs or fscrypt -- file-level encryption.
Cloud provider: AWS EBS encryption, GCP persistent disk encryption, Azure disk encryption.
Third-party extensions: CyberTec TDE (commercial extension providing column-level or tablespace-level encryption).

Security Best Practices¶

Production Security Checklist

Set password_encryption = scram-sha-256 and migrate all user passwords.
Configure pg_hba.conf with principle of least privilege: use reject as the final rule.
Use hostssl (not host) for all TCP connections to enforce TLS.
Set ssl_min_protocol_version = 'TLSv1.2' at minimum.
Use sslmode=verify-full in client connection strings.
Never use trust authentication in production.
Create dedicated roles for each application; avoid using postgres superuser for applications.
Enable Row-Level Security on tables containing tenant-specific or regulated data.
Install and configure pgaudit for compliance-sensitive environments.
Use pgcrypto for column-level encryption of PII or secrets.
Encrypt the data volume at the OS or cloud provider level (LUKS, EBS encryption).
Set log_connections = on and log_disconnections = on for connection auditing.
Regularly rotate passwords and review role memberships.