Next.js + Postgres security — direct-to-database architecture

Going Next.js + Postgres without Supabase means you run auth, authorization, migrations, and connection pooling yourself. The tradeoff is clean: you never hit Supabase's RLS misconfiguration mode, but every API endpoint becomes the trust boundary and you own every layer of it. The three failures we see most: raw query interpolation (Prisma's $queryRawUnsafe, Drizzle's sql.raw, or template-string concatenation with `pg`) reintroducing SQL injection after you thought the ORM killed it. Connection-pool exhaustion when serverless functions spin up and each opens a fresh pg connection — a single traffic spike can saturate max_connections in seconds unless you front it with PgBouncer or Supabase's Pooler. And BOLA/IDOR on every `/api/[id]` endpoint, because without Supabase RLS there is no row-level guard; every query must include `where id = $1 and user_id = $2` where `$2` comes from the server-side session. Least-privilege database roles are the under-used defense. The application user should have no DROP, no TRUNCATE, no role-creation rights — so even a successful SQL injection can do less damage than total database compromise.

What breaks on this stack

Raw-query SQL injection

Prisma's $queryRawUnsafe with interpolation, Drizzle's sql.raw, and node-postgres template-string concatenation all reintroduce injection. Always use parameterized bindings: $1, $2 placeholders with the values passed separately.

Read the guide →

Connection-pool exhaustion from serverless

Every Vercel Function cold-start opens a fresh pg connection. At 100 concurrent requests you have 100 connections; default max_connections is often 100. Add a pooler (PgBouncer in transaction mode, Supabase Pooler, or RDS Proxy) between your functions and the database.

Missing TLS to the database

Connections over the public internet without TLS are plaintext. Add `?sslmode=require` (or `?sslmode=verify-full` with CA verification) to every connection string. Check that your pg driver actually enforces it.

BOLA on /api/[id] routes

Without RLS, ownership checks live in every query. `select * from orders where id = $1` lets attackers change the id and read anyone's order. Always add `and user_id = $2` and bind $2 server-side from the session.

Read the guide →

Over-privileged database user

The app should not be able to DROP tables. Create a dedicated application role with only INSERT/UPDATE/DELETE/SELECT on needed tables, no DDL. Keep the superuser credentials out of application env vars entirely.

Migrations running with application credentials

If your app user can run migrations, a compromise can rewrite the schema. Run migrations with a separate deploy-time credential; revoke DDL from the app role.

Pre-ship checklist

  • All queries use $1, $2 parameterized bindings — no string interpolation
  • ORM raw-query escapes ($queryRawUnsafe, sql.raw) audited and justified
  • sslmode=require (or verify-full) on every connection string
  • Serverless functions front-ended by PgBouncer or Supabase Pooler
  • Connection pool size capped per function instance
  • Every /api/[id] or /api/[slug] route verifies resource ownership
  • Dedicated application-role user without DDL privileges
  • Migrations run with a separate deploy-time credential
  • Database audit-log table records every write with user attribution
  • Row-count guards on bulk operations (no unbounded DELETE)
  • Prepared-statement-cache hit rate monitored (low = injection risk)
  • Backups encrypted at rest and tested with restore drills

Starter config

# .env.production — TLS + pooler connection strings
DATABASE_URL=postgresql://app_user:***@pooler.example.com:6543/appdb?sslmode=require
DIRECT_URL=postgresql://deploy_user:***@db.example.com:5432/appdb?sslmode=verify-full

-- Postgres — least-privilege role
create role app_user login password '***';
grant connect on database appdb to app_user;
grant usage on schema public to app_user;
grant select, insert, update, delete on all tables in schema public to app_user;
grant usage, select on all sequences in schema public to app_user;
revoke create on schema public from app_user;
alter default privileges in schema public grant select, insert, update, delete on tables to app_user;

// lib/db.ts — parameterized bindings only
import { Pool } from "pg";
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
export async function getOrder(id: string, userId: string) {
  const { rows } = await pool.query(
    "select * from orders where id = $1 and user_id = $2",
    [id, userId]
  );
  return rows[0];
}