Architecture¶

PostgreSQL uses a process-per-connection model supervised by a postmaster daemon. Shared memory structures (buffer pool, WAL buffer, lock tables) are accessible to all backend processes, while each backend handles query parsing, planning, and execution independently. This architecture provides strong isolation between client sessions and relies on the Write-Ahead Log (WAL) protocol for crash recovery.

Process Architecture¶

When PostgreSQL starts, the postmaster daemon initializes shared memory and spawns several background processes. For each incoming client connection, the postmaster forks a dedicated backend process.

graph TB
    POSTMASTER["postmaster<br/>(supervisor daemon)"]

    subgraph "Background Processes"
        BGWRITER["Background Writer<br/>(bgwriter)"]
        CHECKPT["Checkpointer"]
        WALWRITER["WAL Writer<br/>(walwriter)"]
        AUTOVAC["Autovacuum Launcher"]
        AUTOWORK["Autovacuum Workers<br/>(dynamic)"]
        STATS["Statistics Collector<br/>(or shared memory stats in PG 15+)"]
        LOGICAL["Logical Replication<br/>Worker (if configured)"]
    end

    subgraph "Client Backends"
        BE1["Backend Process<br/>(session 1)"]
        BE2["Backend Process<br/>(session 2)"]
        BEN["Backend Process<br/>(session N)"]
    end

    subgraph "Shared Memory"
        SHARED["shared_buffers<br/>WAL Buffer<br/>Lock Tables<br/>ProcArray / CLog"]
    end

    POSTMASTER --> BGWRITER
    POSTMASTER --> CHECKPT
    POSTMASTER --> WALWRITER
    POSTMASTER --> AUTOVAC
    AUTOVAC --> AUTOWORK
    POSTMASTER --> STATS
    POSTMASTER --> LOGICAL
    POSTMASTER --> BE1
    POSTMASTER --> BE2
    POSTMASTER --> BEN

    BE1 --- SHARED
    BE2 --- SHARED
    BEN --- SHARED
    BGWRITER --- SHARED
    CHECKPT --- SHARED
    WALWRITER --- SHARED

    style POSTMASTER fill:#e8f4f8,stroke:#2196f3,color:#000
    style SHARED fill:#fff3e0,stroke:#ff9800,color:#000
    style BGWRITER fill:#e8f5e9,stroke:#4caf50,color:#000

Background Processes¶

Process	Role
Background Writer (bgwriter)	Periodically writes dirty pages from `shared_buffers` to the OS page cache. Its goal is to reduce the I/O spike during checkpoints by spreading writes over time. Tuned via `bgwriter_delay`, `bgwriter_lru_maxpages`.
Checkpointer	Forces all dirty pages in `shared_buffers` to disk (via `fsync`) and records a checkpoint record in the WAL. This allows WAL segments older than the checkpoint to be recycled or archived. Triggered by `checkpoint_timeout` (default 5 min) or when the WAL size exceeds `max_wal_size`.
WAL Writer (walwriter)	Flushes WAL records from the in-memory WAL buffer to WAL segment files on disk. Runs on a cycle controlled by `wal_writer_delay` (default 200 ms). Ensures recent WAL records are durable even for transactions that have not yet committed.
Autovacuum Launcher	Monitors table statistics and spawns autovacuum worker processes for tables that exceeded their dead-tuple threshold. Essential for MVCC garbage collection.
Statistics Collector	Collects runtime statistics (table access counts, dead tuple counts, and others). In PostgreSQL 15+, replaced by an in-memory shared statistics area eliminating the separate collector process.

Shared Memory Structures¶

PostgreSQL allocates shared memory at startup. The most important regions are:

graph LR
    subgraph "Shared Memory"
        SB["shared_buffers<br/>(8 KiB page frames)"]
        WALB["WAL Buffer<br/>(circular)"]
        LT["Lock Tables"]
        PA["ProcArray<br/>(active txn XIDs)"]
        CL["CLOG / CommitLog<br/>(txn status bits)"]
        MT["MultiXact<br/>(shared row locks)"]
    end

    style SB fill:#e3f2fd,stroke:#2196f3,color:#000
    style WALB fill:#fce4ec,stroke:#e91e63,color:#000

Region	Purpose
shared_buffers	Main buffer pool. Default 128 MiB. Production systems typically set this to 25% of system RAM. Pages are 8 KiB. Uses a clock-sweep algorithm for page eviction.
WAL Buffer	Circular buffer for WAL records before they are written to disk. Default 64 KiB (`wal_buffers`). Autotuned to 1/32 of `shared_buffers` if left at default.
Lock Tables	Shared lock manager tracking relation-level, page-level, tuple-level, and advisory locks held by all backends.
ProcArray	Array of all active backend processes and their current transaction IDs. Used to compute snapshot visibility (which XIDs are visible to a given transaction).
CLOG (Commit Log)	Stores the commit status of each transaction ID (in-progress, committed, aborted). Stored as bitmaps in shared memory and materialized to disk in `pg_xact/`.
MultiXact	Tracks sets of transaction IDs that hold locks on the same row. Used for `SELECT ... FOR SHARE` and similar operations.

WAL (Write-Ahead Log)¶

The WAL protocol is the foundation of PostgreSQL's crash recovery. The core rule: the system never writes dirty data pages to disk before it flushes their corresponding WAL records.

WAL Record Lifecycle¶

sequenceDiagram
    participant BE as Backend Process
    participant WB as WAL Buffer
    participant WF as WAL Segment Files<br/>(pg_wal/)
    participant CK as Checkpointer
    participant DF as Data Files<br/>(table extents)

    BE->>BE: Modify tuple in shared_buffers
    BE->>WB: XLogInsertRecord (WAL entry)
    Note over BE,WB: WAL record appended to WAL buffer
    BE->>WF: XLogFlush at COMMIT
    Note over BE,WF: Ensures durability before ack to client
    CK->>DF: Write all dirty pages to disk (fsync)
    CK->>WF: Write checkpoint record
    Note over CK,WF: Old WAL segments before checkpoint<br/>can be recycled or archived

WAL Configuration¶

Parameter	Default	Purpose
`wal_level`	`replica`	Controls amount of WAL data. `minimal` for crash recovery only. `replica` for streaming replication. `logical` for logical decoding.
`max_wal_size`	1 GiB	Maximum WAL size before a checkpoint is triggered.
`min_wal_size`	80 MiB	Minimum WAL size to keep for recycling.
`checkpoint_timeout`	5 min	Maximum time between automatic checkpoints.
`wal_buffers`	-1 (auto)	Size of WAL buffer in shared memory. Auto = 1/32 of `shared_buffers`.
`wal_compression`	`off`	Compress full-page images in WAL. `lz4` or `zstd` reduce WAL volume significantly.

Full-Page Writes (FPW)¶

After a checkpoint, the first modification to any data page triggers a full-page image (FPI) write in the WAL. This protects against torn page writes: if a partial 8 KiB write occurs, the complete page can be reconstructed from the FPI in the WAL.

MVCC and VACUUM¶

PostgreSQL implements Multi-Version Concurrency Control (MVCC) using tuple header fields, not undo logs. Each row version (tuple) has:

xmin -- XID of the transaction that put this tuple in the table.
xmax -- XID of the transaction that deleted or updated this tuple (0 if still valid).
infomask -- status bits (committed, aborted, locked, and others).

Visibility Rules¶

When a backend reads a table, it computes a snapshot of which transaction IDs are visible:

Tuples with xmin committed and xmax either 0 or not-yet-committed are visible.
Tuples with xmin not-yet-committed are invisible (still in-progress from another session).
Tuples with xmax committed are invisible (deleted or replaced by an update).

Dead Tuples and VACUUM¶

When rows are updated or deleted, the old tuple versions become dead tuples. They remain on disk until VACUUM reclaims the space.

VACUUM -- marks dead tuple space as available for reuse. Does not shrink the file or return space to the OS (unless the dead tuples are at the end of the file).
VACUUM FULL -- rewrites the entire table, compacting it and returning space to the OS. Requires an exclusive lock.
Autovacuum -- daemon that automatically runs VACUUM and ANALYZE based on thresholds defined by autovacuum_vacuum_scale_factor and autovacuum_vacuum_threshold. Enabled by default.

Bloat from Long-Running Transactions

Long-running transactions (including idle-in-transaction sessions) prevent autovacuum from reclaiming dead tuples that are newer than the oldest open transaction's snapshot. This causes table and index bloat.

Transaction ID Wraparound¶

PostgreSQL uses a 32-bit transaction ID counter. Without intervention, it wraps around after approximately 2 billion transactions. The result is data corruption. To prevent this:

Freeze operations mark old tuples as "always visible" by replacing their xmin with a special FrozenXID.
Autovacuum triggers freeze operations when autovacuum_freeze_max_age (default 200 million transactions) is approached.
The system forces an autovacuum to prevent wraparound even if autovacuum is otherwise disabled.

Query Processing Pipeline¶

Each backend processes queries through the following stages:

Parser -- converts SQL text into a parse tree.
Analyzer/Semantic Analysis -- resolves table and column references, does type checking. Produces a query tree.
Rewriter -- applies rules (for example, views are expanded, RLS policies are attached).
Planner/Optimizer -- generates execution plans. Uses a cost-based optimizer that considers:
- Sequential scans vs. index scans vs. index-only scans.
- Join methods: nested loop, hash join, merge join.
- Join ordering via dynamic programming or GEQO (for many-table joins).
- Parallel query paths (parallel sequential scan, parallel hash join).
Executor -- runs the plan using a tuple-oriented pipeline (each node pulls tuples from its children).

Logical Replication¶

PostgreSQL supports two native replication mechanisms:

Streaming (Physical) Replication¶

Ships WAL byte streams to standby servers.
Standbys are byte-for-byte copies of the primary.
Supports synchronous and asynchronous modes.
Standbys can serve read-only queries (hot standby).

Logical Replication¶

Decodes WAL records into logical changes (INSERT, UPDATE, DELETE) using a logical decoding output plugin (for example, pgoutput).
Publications define which tables to replicate. Subscriptions consume them.
Uses replication slots to track the consumer's WAL position. The primary then retains WAL until all subscribers consume it.
Allows selective table replication and cross-version replication.

graph LR
    PUB["Primary<br/>(Publisher)"]
    WALD["Logical Decoder<br/>(pgoutput plugin)"]
    SLOT["Replication Slot<br/>(tracks LSN)"]
    SUB1["Subscriber 1"]
    SUB2["Subscriber 2"]

    PUB --> WALD --> SLOT
    SLOT -->|"pg_replication slot"| SUB1
    SLOT -->|"pg_replication slot"| SUB2

    style PUB fill:#e8f5e9,stroke:#4caf50,color:#000
    style SLOT fill:#fff3e0,stroke:#ff9800,color:#000

Replication Slot Dangers

If a subscriber is offline for an extended period, its replication slot prevents WAL recycling on the primary. Monitor pg_replication_slots and set max_slot_wal_keep_size to prevent disk exhaustion.

Extension API¶

PostgreSQL's extensibility model allows adding new types, functions, operators, index access methods, and procedural languages without modifying core code:

Extensions -- packaged via CREATE EXTENSION (for example, pgcrypto, PostGIS, pg_stat_statements).
Procedural Languages -- PL/pgSQL (built-in), PL/Python, PL/Perl, PL/v8 (JavaScript).
Custom Scan Providers -- allow extensions to replace or augment query execution plans (used by Citus for distributed queries).
Foreign Data Wrappers (FDW) -- access external data sources as local tables (for example, postgres_fdw, file_fdw).

Key Configuration Parameters¶

Parameter	Default	Purpose
`shared_buffers`	128 MiB	Buffer pool size. Set to ~25% of system RAM.
`work_mem`	4 MiB	Per-sort/hash memory before spilling to disk.
`maintenance_work_mem`	64 MiB	Memory for VACUUM, CREATE INDEX, ALTER TABLE.
`effective_cache_size`	4 GiB	Planner hint for total OS + PG cache. Set to ~75% of RAM.
`max_connections`	100	Maximum concurrent backend processes.
`wal_level`	`replica`	WAL detail level for replication support.
`max_wal_size`	1 GiB	Checkpoint trigger threshold.
`autovacuum`	ON	Enable automatic VACUUM and ANALYZE.

Sources¶

How It Works¶

Process model, shared memory, MVCC, WAL, query execution, and v18 async I/O.

Process Architecture¶

flowchart TB
    subgraph Postmaster["Postmaster (main process)"]
        PM["Process Manager\n(forks backends)"]
    end

    subgraph Backends["Backend Processes"]
        B1["Backend 1\n(client connection)"]
        B2["Backend 2"]
        BN["Backend N"]
    end

    subgraph Background["Background Processes"]
        WAL_W["WAL Writer"]
        Checkpointer["Checkpointer"]
        Autovac["Autovacuum"]
        BGWriter["Background Writer"]
        StatsCol["Stats Collector"]
    end

    subgraph SharedMem["Shared Memory"]
        SharedBuf["Shared Buffers\n(page cache)"]
        WAL_Buf["WAL Buffers"]
        CLOG["CLOG\n(transaction status)"]
    end

    PM --> Backends
    PM --> Background
    Backends --> SharedMem
    Background --> SharedMem

    style SharedMem fill:#1565c0,color:#fff

MVCC (Multi-Version Concurrency Control)¶

Each row has hidden xmin (created by) and xmax (deleted by) transaction IDs. Readers never block writers.

sequenceDiagram
    participant TX1 as Transaction 1 (xid=100)
    participant Heap as Table Heap
    participant TX2 as Transaction 2 (xid=101)

    TX1->>Heap: UPDATE row → creates new version (xmin=100)
    Note over Heap: Old version: xmax=100<br/>New version: xmin=100, xmax=∞
    TX2->>Heap: SELECT → sees old version (100 not committed yet)
    TX1->>TX1: COMMIT
    TX2->>Heap: SELECT → now sees new version

Async I/O (v18)¶

flowchart LR
    subgraph Old["v17 (Synchronous I/O)"]
        Req1_O["Read page 1"] --> Wait1["⏳ Wait"] --> Req2_O["Read page 2"] --> Wait2["⏳ Wait"]
    end

    subgraph New["v18 (Async I/O)"]
        Req1_N["Read page 1"]
        Req2_N["Read page 2"]
        Req3_N["Read page 3"]
        Req1_N --> Batch["io_uring batch\n(all in parallel)"]
        Req2_N --> Batch
        Req3_N --> Batch
        Batch --> Done["All pages ready\n(2-3× faster)"]
    end

    style Old fill:#c62828,color:#fff
    style New fill:#2e7d32,color:#fff

Sources¶

Benchmarks¶

Scope

PostgreSQL performance metrics, pgbench results, scaling characteristics, and comparison baselines.

pgbench Results (Standard Benchmark)¶

TPC-B Like Workload¶

Hardware	Clients	TPS (read-write)	TPS (read-only)	Latency P99
4 vCPU, 16Gi, SSD	16	2,500-4,000	15,000-25,000	5-10ms
8 vCPU, 32Gi, NVMe	32	8,000-15,000	50,000-80,000	2-5ms
16 vCPU, 64Gi, NVMe	64	20,000-35,000	100,000-150,000	1-3ms
32 vCPU, 128Gi, NVMe	128	40,000-60,000	200,000-300,000	1-2ms

Index Performance¶

Operation	B-tree	Hash	GIN	GiST	BRIN
Point lookup	~0.1ms	~0.05ms	N/A	~0.5ms	~1ms
Range scan	~1ms	N/A	~5ms	~2ms	~0.5ms
Insert overhead	Low	Low	High	Medium	Very low
Storage per row	8-16 bytes	4-8 bytes	Variable	Variable	~1 byte

Connection Scaling¶

Connection Count	Without PgBouncer	With PgBouncer	Notes
50	100% baseline	100%	No difference
200	85-90%	98%	PgBouncer multiplexes
500	60-70%	95%	Memory pressure without pooler
1,000	30-40%	90%	Context switching kills perf
5,000	Fails (OOM)	85%	Must use connection pooling

WAL Write Performance¶

Storage Type	WAL Write Throughput	fsync Latency	Notes
HDD	50-100 MB/s	5-20ms	Not recommended for production
SATA SSD	200-500 MB/s	0.5-2ms	Permitted for small deployments
NVMe SSD	1-3 GB/s	0.05-0.2ms	Recommended for production

Replication Performance¶

Scenario	Replication Lag	Throughput	Notes
Async, same DC	< 10ms	Near line-rate	Default config
Async, cross-DC	10-100ms	Network limited	WAN bandwidth matters
Sync, same DC	< 1ms	70-80% of standalone	Commit must wait for replica
Sync, cross-DC	10-50ms	20-40% of standalone	Not recommended for write-heavy

Scaling Limits¶

Dimension	Soft Limit	Hard Limit	Notes
Database size	10TB (comfortable)	100TB+	Needs partitioning beyond 10TB
Table size	1TB	32TB	Partition large tables
Rows per table	1 billion	No hard limit	Performance degrades with bloat
Columns per table	250	1,600	TOAST for wide rows
Indexes per table	20	No limit	Each index adds write overhead
Concurrent connections	200-500	10,000+ (with pooler)	Use PgBouncer

Sourcing Status¶

Unsourced Performance Data

Do not plan capacity from these numbers. We estimated them from vendor documentation, community benchmarks, and engineering judgment. They do not represent controlled benchmarks with documented test conditions. Specific hardware configurations, software versions, and test methodologies were not recorded.

Use these figures as rough guidance only. For production capacity planning, run your own benchmarks against your specific workload and infrastructure.

Sources¶

Security¶

PostgreSQL provides a defense-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 enforces security at multiple layers: network, authentication, authorization, 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. The system evaluates every connection attempt 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. If even superusers must be subject to the policy, set FORCE ROW LEVEL SECURITY on the table.

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`	Behavior
`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. This enables 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, and others).
`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. Do not use the 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.