Enforcing Authorization in the Data Layer
Push the check below the application, so a query written at 2am still cannot cross a boundary.
Application-level checks depend on every developer remembering, on every path, forever. That works until the fortieth query, the new hire, the urgent hotfix, or the raw SQL in a reporting job. Moving enforcement into the database converts a discipline problem into a mechanism.
Postgres row-level security
RLS attaches a policy to a table. Every query — including SELECT * written by hand in psql — is silently filtered by it. The database refuses to return rows the policy excludes.
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON invoices
USING (tenant_id = current_setting('app.tenant_id')::uuid);The three details that make it correct
set_config(..., true)— thetruemakes the setting transaction-local. Without it, a pooled connection carries one tenant's context into the next request. This is the mistake that turns RLS into a false sense of security.FORCE ROW LEVEL SECURITY— by default the table owner bypasses policies. If your application connects as the owner, RLS does nothing at all.- Separate
USINGandWITH CHECK—USINGfilters reads and the rows an update may touch;WITH CHECKvalidates rows being written. Without the latter, an update can move a row into another tenant.
Least-privilege database users
The application does not need DROP TABLE. Separate roles — a read-write app user, a read-only reporting user, a migration user used only by migrations — mean that SQL injection in a reporting query cannot write anything.
What this does and does not solve
RLS enforces rows. It does not shape columns in your API response, does not implement business rules, and does not replace your application checks — it backstops them. Defence in depth: the app checks, and the database refuses.
Example
-- Enable, and FORCE so the owner does not bypass it
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;
-- Reads and the rows an UPDATE may see
CREATE POLICY tenant_read ON invoices
FOR SELECT USING (tenant_id = current_setting('app.tenant_id', true)::uuid);
-- Writes: WITH CHECK validates the NEW row, stopping a row being moved
-- into another tenant
CREATE POLICY tenant_write ON invoices
FOR ALL
USING (tenant_id = current_setting('app.tenant_id', true)::uuid)
WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);
-- Now, in a transaction:
BEGIN;
SELECT set_config('app.tenant_id', 'acme-uuid', true); -- true = THIS tx only
SELECT * FROM invoices; -- only acme's rows, whatever the query says
COMMIT;When to use it
- A raw SQL reporting query written in a hurry cannot leak across tenants, because the database applies the policy regardless of the query text.
- A migration that forgot a tenant filter is caught in staging when the database returns zero rows instead of everyone's.
- SQL injection in a read-only reporting endpoint cannot write, because that endpoint connects as a role with no INSERT or UPDATE grants.
More examples
Wiring RLS to the application, correctly
The second test is the one worth copying: RLS silently does nothing when the app connects as the table owner, and only pg_class tells you.
// The setting must be transaction-local, or connection pooling leaks context.
export async function withTenantTx(tenantId, fn) {
if (!tenantId) throw new Error('withTenantTx requires a tenantId');
return db.transaction(async (tx) => {
// The third argument `true` = is_local = scoped to THIS transaction.
// With `false`, the value survives on the pooled connection and the NEXT
// request — for a different tenant — inherits it.
await tx.raw('SELECT set_config(?, ?, true)', ['app.tenant_id', tenantId]);
// Optional: also set the user, for per-user policies
await tx.raw('SELECT set_config(?, ?, true)', ['app.user_id', currentUser()]);
return fn(tx);
});
}
app.use(auth, (req, res, next) => {
req.tx = (fn) => withTenantTx(req.user.tenant, fn);
next();
});
app.get('/api/invoices', async (req, res) => {
// No tenant filter anywhere in this query — and it is still safe.
const rows = await req.tx((tx) => tx('invoices').orderBy('created_at', 'desc'));
res.json(rows.map(InvoiceSerializer.public));
});
// ── The test that proves it is actually on ────────────────────────────
it('RLS blocks cross-tenant reads even for a deliberately unscoped query', async () => {
const rows = await withTenantTx(acme.id, (tx) =>
tx.raw('SELECT * FROM invoices')); // no WHERE at all
expect(rows.rows.every((r) => r.tenant_id === acme.id)).toBe(true);
});
it('RLS is FORCED, so the app role does not bypass it', async () => {
const [{ relforcerowsecurity }] = (await db.raw(
`SELECT relforcerowsecurity FROM pg_class WHERE relname = 'invoices'`)).rows;
expect(relforcerowsecurity).toBe(true);
});
it('a row cannot be moved into another tenant', async () => {
await expect(withTenantTx(acme.id, (tx) =>
tx('invoices').where({ id: acmeInvoice.id })
.update({ tenant_id: globex.id }) // blocked by WITH CHECK
)).rejects.toThrow();
});Least-privilege database roles
Point 4 is easy to miss: RLS with FORCE still applies to the owner only if you force it, so having a separate owner role removes the question entirely.
-- Separate roles so a compromise in one path cannot do everything.
-- 1. Migrations only. Used by CI, never by the running application.
CREATE ROLE app_migrator LOGIN PASSWORD '...';
GRANT ALL ON SCHEMA public TO app_migrator;
-- 2. The application: data operations, no DDL.
CREATE ROLE app_rw LOGIN PASSWORD '...';
GRANT USAGE ON SCHEMA public TO app_rw;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_rw;
REVOKE CREATE ON SCHEMA public FROM app_rw; -- cannot create or drop tables
-- 3. Reporting and analytics: read only.
CREATE ROLE app_ro LOGIN PASSWORD '...';
GRANT USAGE ON SCHEMA public TO app_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_ro;
-- SQL injection HERE cannot write anything, at all.
-- 4. Neither app role owns the tables, so FORCE RLS actually applies.
ALTER TABLE invoices OWNER TO app_migrator;
-- 5. Deny by default on future tables too.
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_rw;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO app_ro;
-- Verify what a role can actually do:
SELECT grantee, privilege_type, table_name
FROM information_schema.role_table_grants
WHERE grantee IN ('app_rw','app_ro') ORDER BY grantee, table_name;
-- The reporting connection string is the one most likely to end up in a
-- notebook, a dashboard tool, or a data scientist's laptop. Making it
-- read-only costs nothing and bounds that entirely.Per-user policies, and the admin escape hatch
Tying the support policy to a grant row rather than a boolean flag means the escape hatch cannot be opened by setting a session variable alone.
-- Policies can express more than tenancy.
-- Users see only their own rows...
CREATE POLICY own_rows ON documents
FOR SELECT USING (owner_id = current_setting('app.user_id', true)::uuid);
-- ...plus anything explicitly shared with them
CREATE POLICY shared_rows ON documents
FOR SELECT USING (
EXISTS (
SELECT 1 FROM document_shares s
WHERE s.document_id = documents.id
AND s.user_id = current_setting('app.user_id', true)::uuid
AND (s.expires_at IS NULL OR s.expires_at > now())
)
);
-- Multiple SELECT policies are OR-ed: own rows OR shared rows.
-- Writes stay narrower than reads: you may edit only what you own.
CREATE POLICY own_writes ON documents
FOR UPDATE
USING (owner_id = current_setting('app.user_id', true)::uuid)
WITH CHECK (owner_id = current_setting('app.user_id', true)::uuid);
-- The support escape hatch — explicit, and it leaves a trace in the setting
CREATE POLICY support_read ON documents
FOR SELECT USING (
current_setting('app.support_grant_id', true) IS NOT NULL
AND EXISTS (
SELECT 1 FROM cross_tenant_grants g
WHERE g.id = current_setting('app.support_grant_id', true)::uuid
AND g.expires_at > now()
AND g.closed_at IS NULL
)
);
-- Support access requires an open, unexpired grant row. Nobody can browse by
-- setting a variable; the grant must exist, and creating it is audited.
-- Keep policies simple. A policy with three joins runs on EVERY query against
-- that table, and it will show up in your p99 before you notice it in review.
Discussion