What is transaction ID wraparound in PostgreSQL?

PostgreSQL uses 32-bit transaction IDs. After approximately 2 billion transactions, IDs wrap around, potentially causing data loss. PostgreSQL includes autovacuum to prevent this, but misconfigured systems with heavy write loads can approach the limit. connection_audit() checks your current transaction age and warns if approaching danger levels.

How many connections should a PostgreSQL database have?

It depends on your hardware. A common rule of thumb is max_connections = (2 x CPU cores) + effective_io_concurrency. For most applications: 100-200 is plenty. If you need more, use a connection pooler like PgBouncer. Never set max_connections to thousands — each connection consumes ~10MB of RAM.

Why are idle connections a security risk?

Idle connections hold authenticated sessions that could be hijacked. They also indicate potential issues: leaked connection pools that never release connections, background workers that connected once and never disconnected, or monitoring tools that maintain permanent connections. Each idle connection consumes memory and counts against your max_connections limit.

How does SSL protect database connections?

SSL/TLS encrypts all data between your application and PostgreSQL, including authentication credentials and query results. Without SSL, anyone with network access (same subnet, compromised router, cloud VPC misconfiguration) can intercept credentials and data in plaintext. Enable SSL in postgresql.conf and require it via hostssl entries in pg_hba.conf.

PostgreSQL Connection Security Audit: Finding Hidden Risks

The Six Connection Checks

Most database security audits focus on authentication and access control. But connection-level configuration is equally important — and often overlooked. A database with perfect role-based access control is still vulnerable if SSL is disabled, timeouts are missing, or connections leak.

SELECT * FROM pgcomply.connection_audit();

This runs six checks in a single call and returns actionable results.

1. Connection Utilization

Are you running out of connections — or wasting them?

 check: connection_utilization
 status: WARN
 detail: 87 of 100 connections used (87%)
 recommendation: Consider increasing max_connections or using PgBouncer

Above 80% utilization means you are one traffic spike away from connection exhaustion. Below 10% on a production database means max_connections is set too high, wasting shared memory.

The fix depends on your situation:

-- Check who is using connections
SELECT usename, count(*) as connections, state
FROM pg_stat_activity
GROUP BY usename, state
ORDER BY connections DESC;

Often the surprise is a monitoring tool or ORM connection pool consuming 50+ connections while your actual application uses 10.

2. Idle Connections

Connections that are open but doing nothing:

 check: idle_connections
 status: WARN
 detail: 12 connections idle > 1 hour
 recommendation: Set idle_in_transaction_session_timeout, review connection pooling

Find the idle culprits:

SELECT * FROM pgcomply.idle_users('30 minutes');

 pid   | usename     | state              | idle_for    | client_addr    | application
-------+-------------+--------------------+-------------+----------------+------------
 14523 | analytics   | idle               | 03:24:15    | 10.0.1.45      | Metabase
 14891 | web_api     | idle in transaction| 01:45:33    | 10.0.1.10      | Rails
 15002 | backup_svc  | idle               | 12:30:00    | 10.0.1.80      | pg_dump

The "idle in transaction" state is especially dangerous — it holds locks and prevents autovacuum from cleaning up dead tuples. Set a timeout:

ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
SELECT pg_reload_conf();

3. SSL Status

 check: ssl_enforced
 status: FAIL
 detail: ssl = off — credentials travel in plaintext
 recommendation: Enable ssl = on in postgresql.conf

This is the single most common critical finding in database security audits. Without SSL, every query, every password, every piece of data crosses the network unencrypted.

-- Verify SSL status
SHOW ssl;

-- Check which connections use SSL
SELECT usename, ssl, client_addr
FROM pg_stat_ssl
JOIN pg_stat_activity USING (pid);

4. Statement Timeout

Runaway queries without timeouts can hold locks for hours, exhaust connections, and crash the database:

 check: statement_timeout
 status: WARN
 detail: statement_timeout = 0 (disabled)
 recommendation: Set per-role timeouts

Never set a global timeout too aggressively — instead, set per-role:

-- Analytics: 30 seconds (they can retry)
ALTER ROLE analyst SET statement_timeout = '30s';

-- Web API: 5 seconds (user is waiting)
ALTER ROLE web_api SET statement_timeout = '5s';

-- Migration: no timeout (needs to complete)
ALTER ROLE migration SET statement_timeout = '0';

5. Transaction Wraparound Risk

PostgreSQL uses 32-bit transaction IDs. When 2 billion transactions are consumed, the database must shut down for emergency cleanup. This is rare but catastrophic.

 check: transaction_wraparound
 status: PASS
 detail: Database age = 145,203,847 (safe, limit ~2.1 billion)

If this shows WARN or FAIL, you have an autovacuum problem. Check:

SELECT datname, age(datfrozenxid) AS xid_age,
  round(age(datfrozenxid)::numeric / 2147483647 * 100, 2) AS pct_to_wraparound
FROM pg_database
ORDER BY xid_age DESC;

6. Table Bloat

Dead tuples from UPDATE/DELETE operations waste storage and degrade query performance:

 check: table_bloat
 status: WARN
 detail: 3 tables with > 50% dead tuples

This usually means autovacuum is not keeping up. Check:

SELECT schemaname, relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

Real-Time Session Monitoring

For continuous visibility:

-- Current sessions with details
SELECT * FROM pgcomply.session_tracking();

-- Log snapshot to audit trail
SELECT pgcomply.log_sessions();

Schedule session logging every 15 minutes for a historical record:

SELECT pgcomply.schedule_jobs(install := true);
-- Creates: session logging every 15 minutes

This historical data is valuable for incident investigations: "Who was connected during the breach window?"

A Complete Connection Hardening Script

-- 1. Enable SSL
ALTER SYSTEM SET ssl = on;

-- 2. Set idle timeouts
ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';

-- 3. Set per-role statement timeouts
ALTER ROLE analyst SET statement_timeout = '30s';
ALTER ROLE web_api SET statement_timeout = '5s';

-- 4. Verify all changes
SELECT pg_reload_conf();
SELECT * FROM pgcomply.connection_audit();

Summary

Connection security is the first line of defense. pgcomply.connection_audit() reveals six categories of risk in a single call. The most impactful fixes — enabling SSL, setting statement timeouts, and killing idle connections — take minutes to implement but prevent the most common database security incidents.