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.

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 and Fix: PostgreSQL Relation Does Not Exist.