HOW TO SECURE POSTGRESQL - SECURITY GUIDE | VIBEEVAL

PostgreSQL Security Context

PostgreSQL is the most security-feature-rich open-source DB — RLS, granular roles, SSL, ACLs at the column and row level. The risks are rarely in Postgres itself; they’re in the configuration around it: who can connect (pg_hba.conf), what they can do once connected (roles and grants), and what the application sends in queries (parameterization). For managed Postgres on Supabase / Neon / Railway / RDS, the platform handles much of this — the surface that’s left is RLS, query parameterization, and connection-string hygiene.

Security Checklist

1. Enable Row Level Security where applicable

For tables with multi-tenant data: ALTER TABLE my_table ENABLE ROW LEVEL SECURITY; plus per-operation policies keyed on the session’s user identity (current_setting('request.jwt.claim.sub') for Supabase / PostgREST setups, or a session variable you set per request). RLS at the database is defense-in-depth even when your application also filters — see the Supabase guide for the full setup.

2. Configure roles with least privilege

Don’t use the postgres superuser for application connections. Create a role per service: CREATE ROLE app_readonly LOGIN PASSWORD '...'; then GRANT SELECT ON specific_tables TO app_readonly;. Grant write only to roles that need it. The principle: a compromised application credential should not be able to drop the database.

3. Require SSL/TLS connections

In postgresql.conf: ssl = on. In pg_hba.conf: use hostssl (TLS required) instead of host (TLS optional) for non-local connections. For client connection strings, append ?sslmode=require (or verify-full to also verify the server cert). Without TLS, every query and credential transits in plaintext.

4. Always use parameterized queries

-- WRONG — SQL injection
"SELECT * FROM users WHERE email = '" + email + "'"

-- RIGHT — parameterized
"SELECT * FROM users WHERE email = $1", [email]

Use your driver’s parameterized query API or a prepared statement. ORMs (Prisma, Sequelize, SQLAlchemy) parameterize by default. The bug ships when a developer (or an AI assistant) drops to raw SQL with template literals.

5. Configure pg_hba.conf carefully

pg_hba.conf controls which hosts can connect with which auth method. Default in fresh installations sometimes includes host all all 0.0.0.0/0 md5 — anyone can attempt auth from anywhere. Restrict to specific subnets / addresses, prefer scram-sha-256 over md5 for password auth.

6. Use strong passwords and rotate

For any role with LOGIN: generate via openssl rand -base64 32. Store in a secret manager, not in .env files committed anywhere. Rotate after any team-member departure or any credential exposure.

7. Enable audit logging with pgAudit

Install the pgaudit extension and configure logging for security-relevant events (DDL, role changes, GRANTs). For managed Postgres, check whether the platform exposes pgAudit (Supabase yes, RDS yes, Neon limited). Stream logs to a SIEM for retention beyond the platform default.

8. Enable encryption at rest

For self-hosted: configure full-disk encryption on the volume hosting the data directory. For managed Postgres, encryption at rest is on by default — verify in the platform’s data-handling docs and choose customer-managed keys if your compliance requires it.

9. Set connection limits

max_connections in postgresql.conf: don’t set higher than the hardware can handle. Per-role limits via ALTER ROLE app SET CONNECTION LIMIT 50; — prevents one misbehaving service from exhausting the pool. Use a connection pooler (PgBouncer / Supavisor / Neon’s built-in) in front of the DB.

10. Configure statement timeouts

statement_timeout = 30000 (30s) in postgresql.conf or per-role. Per-query timeouts in your application driver. Prevents an attacker from triggering an unindexed query that locks the table.

11. Audit installed extensions

SELECT * FROM pg_extension; — review the list. Some extensions (plpython3u, pg_stat_statements with track_activity_query_size very large) have security implications. Remove unused; for ones you keep, confirm they’re patched to the current version.

12. Configure automated, encrypted backups

For self-hosted: pg_basebackup + WAL archiving + encryption at rest on the backup destination. For managed Postgres: enable point-in-time recovery (PITR) and verify the retention window matches your compliance requirement. Test the restore once before you need it.

13. Restrict network access

For self-hosted: bind Postgres to localhost or a private network interface (listen_addresses = 'localhost' for app-on-same-host setups, or a private VPC IP). Firewall to allow only the application servers. For managed: use VPC peering / private networking instead of public endpoints.

14. Enable pg_stat_statements

shared_preload_libraries = 'pg_stat_statements' and CREATE EXTENSION pg_stat_statements;. Lets you see the top queries by total time, which surfaces both performance issues and attempts to scan large tables.

15. Secure replication connections

For physical / logical replication: use a dedicated replication role with TLS-required connections. Restrict pg_hba.conf to allow replication only from known replica IPs. Stream replication credentials are full-database-read access; treat them like superuser credentials.

16. Configure connection pooling securely

PgBouncer / Supavisor: in transaction pooling mode, prepared statements behave differently — confirm your driver supports the mode in use. Configure auth_type with scram-sha-256. Limit pool size to prevent the pooler from being a DoS amplifier.

Related Resources

Supabase Guide

Postgres-on-Supabase with RLS — the most common AI-generated stack.

Neon Guide

Serverless Postgres with branching.

Vibe Coding Security Risk Guide

Full risk catalogue, including the SQL-injection patterns AI tools reproduce.

Automate Your Security Checks

VibeEval scans applications using PostgreSQL backends — RLS coverage, SQL-injection probing, BOLA via direct queries.