How to secure a PostgreSQL database in production

Akande Simpa Lead Security Engineer & Cofounder, Simpa Labs CEH (Certified Ethical Hacker)

Role-based access control

Stop connecting your application to PostgreSQL as the postgres superuser. This is the database equivalent of running your web server as root. If your application is compromised through SQL injection or a server-side vulnerability, the attacker inherits superuser privileges — they can read every table, drop databases, create new users, and install extensions.

Create dedicated roles with minimal privileges:

-- Application role: only the permissions it needs
CREATE ROLE app_user WITH LOGIN PASSWORD 'strong-random-password';
GRANT CONNECT ON DATABASE fintech_db TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO app_user;
-- No DELETE, no DROP, no CREATE TABLE

-- Migration role: used only during deployments
CREATE ROLE migration_user WITH LOGIN PASSWORD 'different-strong-password';
GRANT ALL ON SCHEMA public TO migration_user;

-- Read-only role: for dashboards and reporting
CREATE ROLE readonly_user WITH LOGIN PASSWORD 'another-strong-password';
GRANT CONNECT ON DATABASE fintech_db TO readonly_user;
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

Your application uses app_user, your deployment pipeline uses migration_user, and your analytics dashboard uses readonly_user. If any one is compromised, the blast radius is limited to its specific permissions. Store each credential separately in your secrets manager.

SSL/TLS connections

By default, PostgreSQL connections are unencrypted. Database credentials and query results travel in plaintext across the network. On shared hosting, within VPCs with multiple tenants, or between cloud regions, this is a data exfiltration risk.

Enable SSL in postgresql.conf:

# postgresql.conf
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'

Then enforce it in pg_hba.conf by using hostssl instead of host for all remote connections. On the client side, set sslmode=verify-full in your connection string to verify the server's certificate. Managed databases (AWS RDS, Google Cloud SQL, Supabase) provide SSL certificates by default — but you still need to configure your application to require them.

pg_hba.conf: the access control file

pg_hba.conf controls who can connect, from where, to which database, and how they authenticate. A misconfigured pg_hba.conf is one of the most critical PostgreSQL vulnerabilities:

# DANGEROUS — trust all local connections (no password required)
local   all   all   trust

# DANGEROUS — allow any IP to connect
host    all   all   0.0.0.0/0   md5

# CORRECT — specific IPs, SSL required, scram-sha-256 authentication
hostssl fintech_db  app_user      10.0.1.0/24   scram-sha-256
hostssl fintech_db  readonly_user 10.0.2.0/24   scram-sha-256
hostssl fintech_db  migration_user 10.0.0.5/32  scram-sha-256

Use scram-sha-256 instead of md5 for password authentication — MD5 hashing is cryptographically weak. Restrict IP ranges to your application servers only. Never expose PostgreSQL on 0.0.0.0 or allow connections from the public internet.

Row-level security (RLS)

Row-level security adds a second layer of access control inside the database itself. Even if your application has a bug that queries the wrong user's data, RLS prevents the database from returning it:

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

-- Policy: users can only see their own transactions
CREATE POLICY user_transactions ON transactions
  FOR SELECT
  USING (user_id = current_setting('app.current_user_id')::uuid);

-- Set the user ID in the session before queries
SET app.current_user_id = 'user-uuid-here';

RLS is particularly valuable for fintech multi-tenant architectures where multiple organisations share the same database. It prevents BOLA-style data leakage at the database layer, complementing your application-level authorization checks. If your application's object-level checks fail, RLS is the safety net.

Connection pooling security (PgBouncer)

PgBouncer sits between your application and PostgreSQL, managing connection pools for performance. But it introduces its own security considerations:

• Authentication passthrough — configure PgBouncer to pass authentication to PostgreSQL rather than maintaining its own user list. Use auth_type = scram-sha-256 and auth_query to verify credentials against the database.
• TLS between PgBouncer and PostgreSQL — if PgBouncer runs on a separate host, encrypt the connection. Set server_tls_sslmode = verify-full in the PgBouncer config.
• Admin console access — PgBouncer's admin console allows viewing and modifying pool settings. Bind it to localhost only and require authentication. Never expose it on a network interface.
• Connection limits — set max_client_conn and default_pool_size to prevent connection exhaustion attacks that would deny service to your application.

Backup encryption

Database backups contain everything: user data, credentials, transaction history. An unencrypted backup stored on S3, a shared drive, or a developer's laptop is a full data breach waiting to happen.

Encrypt backups at rest using pg_dump piped to GPG or a cloud-native encryption solution:

# Encrypted backup
pg_dump fintech_db | gpg --cipher-algo AES256 --symmetric \
  --output /backups/fintech_db_$(date +%Y%m%d).sql.gpg

# Verify the backup can be restored
gpg --decrypt /backups/fintech_db_20260601.sql.gpg | psql fintech_db_test

For managed databases, enable server-side encryption on your storage (S3 SSE, GCS encryption). Store backup encryption keys separately from the backups — a backup and its decryption key in the same location provides zero protection. This is a PCI DSS requirement for any fintech handling cardholder data.

Audit logging

PostgreSQL's default logging captures errors but not data access patterns. For fintech compliance (CBN requirements, NDPA/NDPR), you need audit trails that record who accessed what data and when.

Enable the pgAudit extension for granular audit logging:

# postgresql.conf
shared_preload_libraries = 'pgaudit'
pgaudit.log = 'read, write, ddl'
pgaudit.log_parameter = on

This logs all SELECT, INSERT, UPDATE, DELETE operations, and schema changes with the actual parameter values. Ship these logs to a centralised logging system (ELK, Loki, CloudWatch) where they can't be tampered with by someone who compromises the database server.

Extension security

PostgreSQL extensions run with the privileges of the installing user — often the superuser. A malicious or vulnerable extension has full access to your data. Only install extensions from trusted sources. Audit installed extensions with SELECT * FROM pg_extension; and remove anything unused.

Be particularly careful with extensions that provide external access (postgres_fdw, dblink) or filesystem access (adminpack). In a fintech context, limit extensions to what your application explicitly requires: uuid-ossp, pgcrypto, pgAudit, and your application-specific needs. Restrict CREATE EXTENSION privileges to the superuser role only.

Related reading

Blog: Cloud security checklist · Where to store API keys · Zero trust architecture

Guides: PCI DSS compliance · CBN compliance · ISO 27001 for fintech

Services: Penetration testing · Secure architecture review · Vulnerability assessment