Fix: PostgreSQL Row Level Security Not Working — Policy Not Applied, All Rows Visible, or Permission Denied

The Problem

RLS is enabled but all rows are still visible:

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY view_own_docs ON documents
  FOR SELECT
  USING (user_id = current_user);

-- As user 'alice':
SELECT * FROM documents;  -- Returns ALL rows, not just Alice's

Or a policy blocks all access even for authorized users:

-- Returns 0 rows even when user_id matches
SELECT * FROM documents WHERE user_id = 'alice';

-- Or: ERROR: new row violates row-level security policy for table "documents"

Or Supabase’s auth.uid() doesn’t match the stored user ID:

CREATE POLICY "users can read own data" ON profiles
  FOR SELECT USING (auth.uid() = user_id);

-- Returns nothing despite matching user being authenticated

Why This Happens

PostgreSQL RLS has several non-obvious behaviors:

• Table owners and superusers bypass RLS by default — if you’re connecting as the table owner or a superuser, RLS policies don’t apply. This is why SELECT * returns all rows during testing when connected as the table creator.
• RLS must be explicitly enabled — ENABLE ROW LEVEL SECURITY turns on the mechanism, but by default a table with no policies denies all access. FORCE ROW LEVEL SECURITY extends RLS to the table owner as well.
• current_user vs session_user — current_user changes when you execute SET ROLE, but session_user always reflects the original connection user. Most RLS policies should use current_user.
• No matching policy = no access — if a row doesn’t match any applicable USING clause, it’s silently hidden (for SELECT) or blocked (for INSERT/UPDATE/DELETE). There’s no error — the row simply doesn’t exist as far as the query is concerned.
• Policies are additive for the same command — multiple SELECT policies are OR’d together. A user can see a row if it matches ANY policy that applies to them.

The deeper reason RLS is hard to debug is that PostgreSQL evaluates policies as additional WHERE clauses transparently injected into your query, with no error message when they filter rows out. From the application’s perspective, a SELECT * simply returns fewer rows than expected — there is no permission denied to grep for. The same is true for UPDATE: if no policy matches, the UPDATE runs successfully and affects zero rows, with no error. Combined with the table-owner bypass during development, this creates the canonical failure mode where everything appears to work in local testing and silently leaks (or silently denies) data in production under a different connection role.

The second non-obvious behavior is that RLS interacts with GRANT permissions multiplicatively rather than additively. GRANT SELECT ON documents TO app_user gives the role permission to issue the query at all; the RLS policy then filters which rows come back. Forgetting either step yields the same symptom — empty results — but the fixes are completely different. Always check pg_class.relrowsecurity, pg_class.relforcerowsecurity, the role’s GRANTs, and the matching pg_policies row when debugging “no rows returned but the data is definitely there.”

How Other Tools Handle This

Row-level filtering is a problem every multi-tenant system faces; comparing PostgreSQL’s RLS with adjacent solutions clarifies the trade-offs.

PostgreSQL RLS vs MySQL views. MySQL has no native row-level security feature comparable to PostgreSQL RLS. The canonical workaround is to create a view per tenant or per role: CREATE SQL SECURITY DEFINER VIEW user_documents AS SELECT * FROM documents WHERE user_id = CURRENT_USER(); and revoke direct table access. This works but does not extend to INSERT/UPDATE/DELETE cleanly — you need INSTEAD OF triggers on the view, which break on bulk operations. MySQL 8.0’s invisible columns and stored functions can approximate parts of RLS, but every application query must route through the views, and ORMs that introspect the schema often expose the underlying tables anyway. Migrating from MySQL views to PostgreSQL RLS usually simplifies application code by 30-50% because the filter is invisible to the query layer.

PostgreSQL RLS vs SQL Server RLS. SQL Server introduced Row-Level Security in 2016 with predicates implemented as inline table-valued functions and applied via SECURITY POLICY objects. The mental model is identical to PostgreSQL — predicates run as part of query optimization — but SQL Server’s FILTER PREDICATE (analogous to USING) and BLOCK PREDICATE (analogous to WITH CHECK) are syntactically separate, and you must ENABLE the policy explicitly. SQL Server enforces predicates against the table owner without needing FORCE, which removes one common PostgreSQL pitfall. Performance is similar — both query planners can use indexes on the filter columns if the predicate is a simple equality.

PostgreSQL RLS vs Oracle VPD (Virtual Private Database). Oracle’s VPD predates PostgreSQL RLS by over a decade and is the most mature implementation. VPD uses PL/SQL policy functions that return a WHERE clause string, attached to tables via DBMS_RLS.ADD_POLICY. This is more flexible than PostgreSQL’s expression-based USING clause (VPD policies can run arbitrary procedural logic) but harder to reason about because the predicate is computed at query-rewrite time rather than declared statically. Oracle also supports policies that fire only on specific columns and policies attached to synonyms. The trade-off is verbosity: a VPD policy is dozens of lines of PL/SQL versus a one-line PostgreSQL CREATE POLICY.

PostgreSQL RLS vs application-layer auth. Many teams enforce tenant isolation entirely in the application — every query adds WHERE tenant_id = ? in the ORM. This works fine until someone writes a raw query, runs an ad-hoc migration, or uses a new SQL feature the ORM does not wrap. RLS shifts enforcement to the database, where a forgotten WHERE clause cannot leak data. The trade-off is debugging difficulty (policies are invisible to grep) and connection-pool complexity (you must set the session variable on every connection checkout). Most mature multi-tenant SaaS applications eventually move at least the strictest data (PII, payment information, audit logs) to RLS even if the bulk of queries rely on application filters.

Multi-tenant patterns. Shared-schema-with-RLS is one of three multi-tenancy models, alongside schema-per-tenant (one PostgreSQL schema per customer) and database-per-tenant (one database per customer). RLS scales best for small-to-medium per-tenant data sizes and high tenant counts (thousands+). Schema-per-tenant scales better for per-tenant data customization and easier backup/restore of individual tenants. Database-per-tenant scales best for very large per-tenant data sizes (>500GB) and the strictest compliance requirements (separate backup retention, separate encryption keys). Supabase, Hasura, and Postgraphile lean on RLS heavily; Salesforce uses a custom shared-schema model that resembles RLS conceptually; Stripe uses database-per-tenant for the largest accounts.

In Production: Incident Lens

The most common RLS production incident is the connection-pool context leak. Your application sets SET app.current_user_id = 'alice' at the start of a request, but uses SET instead of SET LOCAL. The connection returns to the pool with that setting still active, the next request from a different user reuses the same connection, and queries run with Alice’s user ID against Bob’s session. Detection is hard because the symptom is “user sees wrong data sometimes” — never a SQL error. Always SET LOCAL inside a transaction wrapping the entire request, and consider pg_reset_session() on connection check-in for extra safety. Add an integration test that performs two requests with different users on the same connection and asserts the data is correctly scoped.

The second pattern is the silent migration data loss when adding FORCE ROW LEVEL SECURITY. A migration adds FORCE to apply RLS even to the table owner — which is exactly the role your background workers use. The workers’ queries start returning empty results, but worker jobs do not fail; they just process zero rows and complete successfully. Hours later the queue is backed up and you discover the workers have been running but not doing anything. Stage FORCE changes behind a feature flag and run a smoke test that asserts row counts after the migration before declaring it successful.

The third recurring incident is index regression after adding RLS. A query that ran in 5ms suddenly takes 500ms because the planner can no longer use the index on a column the RLS predicate filters. The cause is usually a type mismatch — the policy compares user_id (UUID) against current_setting('app.user_id', TRUE) (TEXT), forcing a per-row cast that prevents index lookup. Always cast the setting to the column type explicitly (::UUID), and run EXPLAIN ANALYZE on critical queries after every RLS policy change to verify the index is still chosen.

Fix 1: Test Policies as a Non-Owner Role

Always test RLS by switching to a non-owner role:

-- Check who owns the table
SELECT tableowner FROM pg_tables WHERE tablename = 'documents';
-- Returns: your_admin_user  ← owner bypasses RLS

-- WRONG — testing as table owner
-- The owner bypasses RLS, so policies appear to do nothing
SET ROLE your_admin_user;
SELECT * FROM documents;  -- Returns all rows (owner bypass)

-- CORRECT — test as a regular user
SET ROLE alice;
SELECT * FROM documents;  -- RLS applies correctly

-- Or force RLS even for the table owner
ALTER TABLE documents FORCE ROW LEVEL SECURITY;
-- Now even the owner is subject to policies
-- Useful for testing and for application-level connections

-- Check if RLS is enabled
SELECT relname, relrowsecurity, relforcerowsecurity
FROM pg_class
WHERE relname = 'documents';
--  relname   | relrowsecurity | relforcerowsecurity
-- -----------+----------------+---------------------
--  documents | t              | f

Verify which policies exist:

SELECT schemaname, tablename, policyname, permissive, roles, cmd, qual, with_check
FROM pg_policies
WHERE tablename = 'documents';

Fix 2: Write Correct Policy Expressions

Policies use USING (filter for reads) and WITH CHECK (validate for writes):

-- Drop existing table if starting fresh
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    user_id TEXT NOT NULL,
    title TEXT NOT NULL,
    content TEXT,
    is_public BOOLEAN DEFAULT FALSE
);

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- SELECT policy: users see their own docs AND public docs
CREATE POLICY select_documents ON documents
  FOR SELECT
  USING (
    user_id = current_user    -- Own documents
    OR is_public = TRUE        -- OR public documents
  );

-- INSERT policy: users can only insert rows with their own user_id
CREATE POLICY insert_documents ON documents
  FOR INSERT
  WITH CHECK (user_id = current_user);  -- WITH CHECK for INSERT/UPDATE

-- UPDATE policy: users can only update their own docs
CREATE POLICY update_documents ON documents
  FOR UPDATE
  USING (user_id = current_user)         -- Which rows can be updated
  WITH CHECK (user_id = current_user);   -- What the updated row must look like

-- DELETE policy: users can only delete their own docs
CREATE POLICY delete_documents ON documents
  FOR DELETE
  USING (user_id = current_user);

-- All-in-one policy using ALL command
CREATE POLICY own_documents ON documents
  FOR ALL
  USING (user_id = current_user)
  WITH CHECK (user_id = current_user);

Policy with role-based access:

-- Grant different access to different roles
CREATE ROLE app_user;
CREATE ROLE app_admin;

-- Regular users: only their own rows
CREATE POLICY user_isolation ON documents
  FOR ALL
  TO app_user  -- Only applies to app_user role
  USING (user_id = current_user)
  WITH CHECK (user_id = current_user);

-- Admins: full access
CREATE POLICY admin_access ON documents
  FOR ALL
  TO app_admin  -- Only applies to app_admin role
  USING (TRUE)
  WITH CHECK (TRUE);

Fix 3: Use Application-Level User IDs with JWT

In web applications, you typically pass the authenticated user’s ID via a JWT claim rather than using PostgreSQL roles:

-- Store user context in a transaction-local setting
-- This is the pattern used by Supabase and many multi-tenant apps

-- Set the current user ID (called at the start of each transaction/request)
SET LOCAL app.current_user_id = '550e8400-e29b-41d4-a716-446655440000';

-- Access it in policies
CREATE POLICY user_isolation ON documents
  FOR ALL
  USING (user_id::TEXT = current_setting('app.current_user_id', TRUE))
  WITH CHECK (user_id::TEXT = current_setting('app.current_user_id', TRUE));

-- In your application code (Node.js / Prisma example):
await prisma.$executeRaw`SET LOCAL app.current_user_id = ${userId}`;
// Then run your queries — they respect the RLS policy

Supabase RLS patterns:

-- Supabase sets auth.uid() via a JWT claim
-- The auth.uid() function reads from the current JWT session

-- Profile table: users can read/update their own profile
CREATE POLICY "Users can view own profile" ON profiles
  FOR SELECT USING (auth.uid() = id);

CREATE POLICY "Users can update own profile" ON profiles
  FOR UPDATE USING (auth.uid() = id);

-- Posts: users can read all, write their own
CREATE POLICY "Posts are viewable by everyone" ON posts
  FOR SELECT USING (TRUE);

CREATE POLICY "Users can insert own posts" ON posts
  FOR INSERT WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update own posts" ON posts
  FOR UPDATE USING (auth.uid() = user_id);

-- Check that auth.uid() is actually returning a value
SELECT auth.uid();
-- Returns null if no JWT is set — policies using auth.uid() will filter everything out

Debug Supabase auth.uid() issues:

-- Check what auth.uid() returns in your current session
SELECT auth.uid(), auth.role(), auth.jwt();

-- If auth.uid() is null, the JWT isn't being passed to the database
-- In Supabase client, ensure you're using the authenticated client:
-- const { data } = await supabase.from('profiles').select('*')
-- NOT: await supabase.from('profiles').select('*')  (unauthenticated)

Fix 4: Handle the No-Policy Default Deny

When RLS is enabled and no policy matches, access is denied by default:

-- Enable RLS with no policies → access denied for all non-owners
ALTER TABLE sensitive_data ENABLE ROW LEVEL SECURITY;

-- Test: as a regular user
SET ROLE regular_user;
SELECT * FROM sensitive_data;  -- Returns 0 rows (not an error — silently denied)
INSERT INTO sensitive_data VALUES (1, 'test');  -- ERROR: new row violates policy

-- To allow all access (effectively disabling the filter):
CREATE POLICY allow_all ON sensitive_data USING (TRUE) WITH CHECK (TRUE);

-- To deny all access explicitly (same as no policy):
CREATE POLICY deny_all ON sensitive_data USING (FALSE);

-- PERMISSIVE vs RESTRICTIVE policies:
-- Default: PERMISSIVE — policies are OR'd (any matching policy grants access)
-- RESTRICTIVE — AND'd with all permissive policies (must match ALL restrictive policies)

CREATE POLICY must_be_active ON accounts
  AS RESTRICTIVE  -- User must ALSO satisfy this policy
  FOR ALL
  USING (is_active = TRUE);

Fix 5: Grant Permissions Correctly Alongside RLS

RLS works on top of regular GRANT permissions — both must allow the operation:

-- Create application role
CREATE ROLE app_user LOGIN PASSWORD 'secure_password';

-- Grant table-level permissions (required in addition to RLS)
GRANT SELECT, INSERT, UPDATE, DELETE ON documents TO app_user;
GRANT USAGE ON SEQUENCE documents_id_seq TO app_user;  -- For INSERT with serial

-- RLS then further restricts which rows are accessible
CREATE POLICY own_docs ON documents
  FOR ALL
  TO app_user
  USING (user_id = current_user)
  WITH CHECK (user_id = current_user);

-- Full example for a multi-tenant setup:
-- 1. Enable RLS
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE documents FORCE ROW LEVEL SECURITY;  -- Apply even to table owner

-- 2. Create policy
CREATE POLICY tenant_isolation ON documents
  FOR ALL
  USING (tenant_id = current_setting('app.tenant_id')::INTEGER);

-- 3. Grant access (RLS filters rows, GRANT allows the operation)
GRANT SELECT, INSERT, UPDATE, DELETE ON documents TO app_user;

Fix 6: Debug RLS Policies

Use EXPLAIN and system functions to verify policy behavior:

-- See which policies are being applied to a query
EXPLAIN (VERBOSE, FORMAT TEXT)
SELECT * FROM documents WHERE id = 1;

-- The VERBOSE output shows the actual filter applied including RLS predicates
-- Look for lines like: Filter: (documents.user_id = (CURRENT_USER)::text)

-- Test policy expressions directly
SELECT current_user;
SELECT current_setting('app.current_user_id', TRUE);
SELECT auth.uid();  -- Supabase only

-- Temporarily disable RLS for debugging (as superuser)
ALTER TABLE documents DISABLE ROW LEVEL SECURITY;
-- Run queries to verify data is there...
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- Check effective policies for a specific role
SET ROLE app_user;
SET app.current_user_id = 'test-user-id';
EXPLAIN SELECT * FROM documents;
RESET ROLE;

-- List all policies with their expressions
SELECT
    policyname,
    cmd,
    roles,
    qual AS using_expr,
    with_check AS check_expr
FROM pg_policies
WHERE schemaname = 'public'
ORDER BY tablename, policyname;

Performance: index the RLS filter columns:

-- RLS policies add WHERE clauses to every query — index them
CREATE INDEX idx_documents_user_id ON documents (user_id);
CREATE INDEX idx_documents_tenant_id ON documents (tenant_id);

-- For Supabase (UUID user_id)
CREATE INDEX idx_documents_user_id ON documents (user_id);
-- auth.uid() returns UUID — ensure user_id column is UUID type, not TEXT
-- Comparing UUID to TEXT causes a type mismatch that disables the index

Still Not Working?

RLS policy is correct but EXPLAIN shows a full scan — if the RLS predicate uses a function call like current_setting() or auth.uid(), PostgreSQL may not be able to use an index because the value is evaluated per-row. Cast to the correct type and ensure the column type matches the function return type. For current_setting(), use ::UUID or ::INTEGER casts to match the column type.

Recursive RLS — policy references the same table — a policy that runs a subquery on the same table it’s protecting will bypass RLS for that subquery (to avoid infinite recursion). If you need cross-row visibility checks, use SECURITY DEFINER functions to safely encapsulate the check.

SET LOCAL vs SET — SET LOCAL only persists for the current transaction and automatically resets after COMMIT or ROLLBACK. SET persists for the entire session. For web applications with connection pooling, always use SET LOCAL at the start of each transaction to prevent user context from leaking between requests:

BEGIN;
SET LOCAL app.current_user_id = '...';
-- Your queries
COMMIT;  -- Setting is automatically reset

For related PostgreSQL issues, see Fix: PostgreSQL Permission Denied for Table, Fix: PostgreSQL Relation Does Not Exist, Fix: Supabase Not Working, and Fix: PostgreSQL Slow Query.