Your Database Already Has an Authorization System. You're Just Not Using It.
Every backend developer has written this line a thousand times:
SELECT * FROM orders WHERE user_id = $1
And this one:
DELETE FROM invoices WHERE id = \(2 AND user_id = \)1
And somewhere, deep in the codebase, someone forgot the AND user_id = $1 part. Maybe it was a new hire. Maybe it was you at 11pm before a deadline. The query works fine in testing because your test user happens to own the right data. It ships. And three months later you find out that any logged-in user could read every invoice in the system.
This is not a hypothetical. This is the #1 cause of data leaks in multi-user applications. You're relying on every single query, in every single endpoint, written by every single developer, to always remember to filter by the right user. That's not a security model. That's a prayer.
PostgreSQL has a built-in feature that makes this entire class of bug impossible. It's called Row-Level Security (RLS), and after reading this post, you'll wonder why you ever did it any other way.
Now Think About Who's Actually Writing Your Queries
AI is generating a growing share of our backend code. Copilot autocompletes queries. Claude writes data access layers. Cursor refactors endpoints. The code works, tests pass, it ships.
But here's the reality: AI-generated queries are often complex. Joins across five tables, nested CTEs, multi-branch subqueries. The kind of SQL where a missing WHERE clause doesn't jump out at you during review. And the volume keeps growing. Reviewing every generated query for authorization correctness is a losing game.
And it's not just code generation. More and more teams are connecting AI agents directly to their databases, letting them build and run queries at runtime based on user input. That trend is only going to accelerate. These queries don't exist in your codebase. You can't review them in a PR. They're constructed on the fly.
Authorization is the one domain where "almost always correct" isn't good enough. One missed filter in one query is a data leak.
RLS takes this off your plate entirely. The database enforces access rules regardless of what the query looks like or where it came from. Define the rules once, and every query gets scoped automatically. Whether it was written by a developer, generated by Copilot, or constructed by an agent at runtime. It's one of those rare things you can genuinely delegate and stop thinking about.
-- At the start of every agent turn
SELECT set_config('app.current_user_id', '<uuid-from-jwt>', true);
SET ROLE authenticated;
-- Now let the agent run whatever it generates
-- RLS makes it impossible to access unauthorized data
The agent operates inside a sandbox it can't escape, not because you told it nicely, but because the database won't let it. That's a fundamentally different security posture than hoping your prompt engineering holds up.
How It Actually Works
RLS has three building blocks: Roles, Grants, and Policies. They work as layers, each one narrowing what's possible.
Roles: Who Are You?
PostgreSQL doesn't separate "users" and "groups." It has one concept: the role. A role can log in (acts like a user), or it can be a container that other roles inherit from (acts like a group).
In practice, every RLS setup lands on three roles:
anon is the unauthenticated visitor. Almost no access. Exists so that requests without a valid token fail loudly instead of silently returning empty results.
authenticated is any logged-in user (or any agent acting on behalf of a logged-in user). Can read and write tables, but every operation passes through RLS policies first.
service_role bypasses RLS entirely. Reserved for migrations, background jobs, and admin scripts. Never exposed to end users. Never given to an agent.
CREATE ROLE anon NOLOGIN;
CREATE ROLE authenticated NOLOGIN;
CREATE ROLE service_role NOLOGIN BYPASSRLS;
CREATE ROLE app_api LOGIN PASSWORD '...' NOINHERIT;
GRANT anon, authenticated, service_role TO app_api;
That NOINHERIT is critical. Without it, app_api automatically inherits service_role's bypass power the moment it connects. With NOINHERIT, your app must explicitly SET ROLE authenticated per request. This is the gate that makes the whole model work.
Grants: What Can You Do?
Grants control which operations a role can perform on which objects. Can this role SELECT from this table at all? Can it INSERT?
GRANT USAGE ON SCHEMA app TO authenticated;
GRANT SELECT, INSERT, UPDATE, DELETE ON app.documents TO authenticated;
-- No grants to anon = anon can't touch the table, period
If a role doesn't have SELECT on a table, RLS never even comes into play. Grants are the outer wall. RLS is the room-by-room access control inside the building.
Policies: Which Rows Can You See?
This is the main event. A policy is a rule attached to a table that Postgres injects into every query as an invisible WHERE clause.
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE documents FORCE ROW LEVEL SECURITY;
CREATE POLICY users_own_documents ON documents
FOR SELECT TO authenticated
USING (owner_id = app.current_user_id());
Now when any authenticated role runs SELECT * FROM documents, Postgres silently rewrites it to SELECT * FROM documents WHERE owner_id = '<current-user>'. Every time. Whether the query came from your application, a raw SQL console, or an AI agent that decided to get creative.
The FORCE part matters. Without it, the table owner bypasses RLS by default. Add it, or your migration user can see everything.
USING vs WITH CHECK: The Two Guards
Every policy has up to two expressions that do different jobs.
USING filters which existing rows you can see or target. It's your read filter. Applied to SELECT, and to the "before" state of UPDATE and DELETE.
WITH CHECK validates new or modified rows after a write. It catches sneaky mutations.
Why do you need both? Without WITH CHECK, someone (or some agent) could do:
INSERT INTO documents (owner_id, title)
VALUES ('someone-elses-id', 'Gotcha');
USING wouldn't catch it because there's no existing row to filter. WITH CHECK rejects the write because the new row doesn't belong to the current user. For UPDATE, both work together: USING controls which rows you can touch, WITH CHECK ensures you can't mutate a row into something you shouldn't own.
Permissive vs Restrictive: Building Walls and Doors
Postgres has two flavors of policy, and they combine with different logic.
Permissive (the default) policies are OR together. If any one of them says yes, the row is visible. These are doors. Each one is a way in.
Restrictive policies are AND on top of the permissive result. They can only narrow access, never widen it. These are walls. They hold no matter how many doors you add.
This is where multi-tenancy gets elegant:
-- WALL: tenant isolation. Always enforced.
CREATE POLICY tenant_wall ON documents AS RESTRICTIVE
FOR ALL TO authenticated
USING (tenant_id = app.current_tenant_id())
WITH CHECK (tenant_id = app.current_tenant_id());
-- DOOR: see your own docs within your tenant
CREATE POLICY own_docs ON documents
FOR SELECT USING (owner_id = app.current_user_id());
-- DOOR: see public docs within your tenant
CREATE POLICY public_docs ON documents
FOR SELECT USING (is_public = true);
The effective filter: (own_docs OR public_docs) AND tenant_wall
You can add ten more permissive doors and the tenant wall still holds. A developer (or an agent) can never accidentally create a path that leaks data across tenants. The restrictive layer is a structural guarantee, not a convention.
One thing to watch: if you only have restrictive policies and zero permissive ones, nothing is visible. Restrictive narrows the permissive set. An empty set AND with anything is still empty. You always need at least one door.
The Plumbing
Policies reference app.current_user_id(), but how does Postgres know who the current user is? Through session variables that your application sets at the start of each request.
CREATE FUNCTION app.current_user_id() RETURNS uuid
LANGUAGE sql STABLE AS $$
SELECT nullif(current_setting('app.current_user_id', true), '')::uuid;
$$;
Your backend (or your agent runtime) sets context before running any queries:
BEGIN;
SELECT set_config('app.current_user_id', '<uuid-from-jwt>', true);
SET ROLE authenticated;
-- queries go here, RLS is active
COMMIT; -- settings revert, safe for connection pooling
The true in set_config means "transaction-local." When the transaction ends, the setting vanishes. No state leaks between requests, even with connection pooling. This is what makes it safe to share a single Postgres connection pool across thousands of users (or thousands of agent sessions).
Teams and Shared Access
Real apps aren't just "users own rows." You need teams, organizations, shared workspaces. The pattern is clean:
CREATE FUNCTION app.my_team_ids() RETURNS SETOF uuid
LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public AS $$
SELECT team_id FROM team_members
WHERE user_id = app.current_user_id();
$$;
CREATE POLICY team_access ON projects
FOR SELECT TO authenticated
USING (team_id IN (SELECT app.my_team_ids()));
SECURITY DEFINER means the function runs as the function owner, bypassing RLS on the team_members table itself. This avoids a circular dependency. Always set search_path explicitly on SECURITY DEFINER functions to block injection.
Want role-based permissions? Layer it. Viewers can read. Members can write. Admins can delete. Same table, different policies, each checking the user's team role.
Why RLS Matters More Now Than Ever
We're entering an era where the code accessing your database isn't fully written by humans anymore. Agents generate queries. Copilots autocomplete SQL. Low-code platforms abstract away the data layer. The surface area for "someone forgot the WHERE clause" is exploding.
The traditional approach of scattering authorization checks across application code was already fragile. In an agentic world, it's untenable. You can't review dynamically generated queries. You can't unit test every possible natural language input mapped to SQL. You can't guarantee that an LLM will always respect a system prompt that says "only access the current user's data."
But you can guarantee that the database won't return unauthorized rows. That's not a prompt. That's not a convention. That's a constraint enforced by the database engine on every query, from every source, every time.
RLS turns your database from a dumb store that trusts its callers into an active participant in your security model. It doesn't replace application logic. It catches everything your application logic misses. And when an AI agent is the one writing the application logic at runtime, that safety net isn't optional anymore.
