Skip to main content
13.4k
Cloud
Self-host
Cloud
Self-host

Postgres Row-Level Security Footguns

Adela · Sep 5, 2025

Row-Level Security (RLS) in Postgres lets you push access control down into the database itself, so a query only ever returns the rows a given user is allowed to see. That sounds like the cleanest possible place to enforce tenant isolation, and in many ways it is. The catch is that RLS interacts with the query planner, with view semantics, with connection pooling, and with constraints in ways that are easy to get wrong. Some of those mistakes wreck performance. Others quietly hand data to the wrong person while everything looks fine in testing.

This post walks through the traps that bite people most often. They fall into three buckets: performance, security, and implementation. For each one, you get a short explanation of what goes wrong and a code sample showing the safer way to do it.

Performance Footguns

1. The LEAKPROOF Function Performance Killer

When an RLS policy calls a function that isn't marked LEAKPROOF, Postgres can't safely push it down with index scans. It has to apply the RLS filtering first and run the function afterward, which often means a full table scan even when you have a perfectly good index.

Here's the kind of policy that triggers it:

-- This will cause full table scans even with an index on title
CREATE POLICY user_documents ON documents
FOR SELECT
USING (owner_id = current_user_id() AND title ILIKE '%search%');

The way out is to mark your function LEAKPROOF so the planner trusts it, or to move the heavier logic out of the policy entirely:

-- Use LEAKPROOF functions or move complex logic out of policies
CREATE OR REPLACE FUNCTION safe_ilike(text, text)
RETURNS boolean
LANGUAGE sql
LEAKPROOF
AS $$ SELECT $1 ILIKE $2 $$;

CREATE POLICY user_documents ON documents
FOR SELECT
USING (owner_id = current_user_id() AND safe_ilike(title, '%search%'));

On a large table this is the difference between a query that finishes in milliseconds and one that runs for hours. Mark a function LEAKPROOF only when you're sure it can't leak the values it's passed, since that's exactly the guarantee the planner is relying on.

2. Complex Policy Performance Death

A policy with a subquery in it runs that subquery for every row the planner considers. Put a multi-table join inside the policy and the cost multiplies fast.

CREATE POLICY complex_access ON orders
USING (
  EXISTS (
    SELECT 1 FROM user_permissions up
    JOIN departments d ON up.dept_id = d.id
    WHERE up.user_id = current_user_id()
    AND d.region = orders.region
  )
);

You can pull the same logic into a STABLE LEAKPROOF function. Postgres can then cache the result within a query rather than re-evaluating the whole join row by row:

-- Move complexity to a LEAKPROOF function
CREATE OR REPLACE FUNCTION user_has_region_access(region_name text)
RETURNS boolean
LANGUAGE sql
LEAKPROOF
STABLE
AS $$
  SELECT EXISTS (
    SELECT 1 FROM user_permissions up
    JOIN departments d ON up.dept_id = d.id
    WHERE up.user_id = current_user_id()
    AND d.region = region_name
  );
$$;

CREATE POLICY simple_access ON orders
USING (user_has_region_access(region));

3. Missing Indexes on Policy Columns

Whatever columns your policy filters on are, in practice, part of every query against that table. If they aren't indexed, you're signing up for sequential scans on every read.

-- Always index columns used in policies
CREATE INDEX ON orders(tenant_id);
CREATE INDEX ON orders(owner_id);
CREATE INDEX ON orders(tenant_id, owner_id); -- composite for AND conditions

The composite index matters when a policy combines columns with AND, which is common for tenant-plus-owner isolation.

Security Footguns

4. The BYPASSRLS Superuser Trap

By default, superusers and the table owner are exempt from RLS. The danger isn't the exemption itself, it's that you'll do your testing as one of those roles and conclude that your policies work, when in reality they were never being applied.

-- Force RLS even for table owners
ALTER TABLE sensitive_data ENABLE ROW LEVEL SECURITY;
ALTER TABLE sensitive_data FORCE ROW LEVEL SECURITY;

-- Never grant BYPASSRLS to application roles
-- Test with dedicated non-superuser accounts

The reliable habit is to test as the actual application role, not as yourself:

-- Create proper test user
CREATE ROLE test_user;
GRANT app_user TO test_user;

-- Test as the actual application user
SET ROLE test_user;
SET app.tenant_id = 'test-tenant-id';
-- Run your tests here
RESET ROLE;

5. SECURITY DEFINER View Bypass

A view created by a privileged role runs with that role's privileges, which means it can read straight past the RLS policies you carefully set up on the underlying table.

-- Created by superuser - bypasses ALL RLS policies!
CREATE VIEW all_patient_data AS
SELECT * FROM patients;

On Postgres 15 and later you can flip a view to run as the calling user with security_invoker. On older versions you don't have that switch, so if you must use a SECURITY DEFINER function you should check that row security is actually active before returning anything:

-- Postgres 15+: Use SECURITY INVOKER
CREATE VIEW patient_data
WITH (security_invoker = true)
AS SELECT * FROM patients;

-- Older versions: Use SECURITY DEFINER with explicit RLS
CREATE OR REPLACE FUNCTION get_patient_data()
RETURNS TABLE(id uuid, name text, doctor_id uuid)
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$
BEGIN
  -- Explicitly check RLS in SECURITY DEFINER functions
  IF NOT row_security_active('patients') THEN
    RAISE EXCEPTION 'Row security must be active';
  END IF;

  RETURN QUERY SELECT p.id, p.name, p.doctor_id FROM patients p;
END;
$$ LANGUAGE plpgsql;

6. Timing Side-Channel Attacks

This one is subtle. Even when RLS correctly hides rows from a user, the time a query takes can still leak whether those rows exist. In a multi-tenant medical database, for example, an attacker can time queries to infer whether patients with a particular condition exist in another tenant's data, without ever seeing a single row.

A few things make this practical:

  • RLS enforcement produces measurable timing differences
  • Those differences let an attacker infer cardinality they aren't supposed to know
  • It still works across the network latency you'd see in a cloud environment
-- Timing reveals if forbidden patients exist
SELECT COUNT(*) FROM patients
WHERE condition = 'rare_disease'
AND tenant_id = current_setting('app.tenant_id')::uuid;

There's no single clean fix, which is part of what makes it a footgun. The mitigations all trade something away:

  1. Use data-oblivious query patterns (performance cost)
  2. Add artificial delays to normalize timing
  3. Limit query complexity for untrusted users
  4. Monitor for suspicious timing-based query patterns

This isn't just theory. The attack has been demonstrated in academic research and shown to work in real cloud deployments.

7. CVE-2019-10130: Statistics Leakage

CVE-2019-10130 is a good reminder that the leak doesn't have to come from your policies. Postgres collects planner statistics by sampling column values, and for a while a user could craft operators that read those statistics, which could contain data sampled from rows RLS was supposed to hide.

A few specifics:

  • The planner samples column data to build its statistics
  • A user could craft operators to read statistics holding forbidden data
  • It affected Postgres 9.5 through 11 before the May 2019 patches

It was fixed in Postgres 9.5.17, 9.6.13, 10.8, and 11.3. The takeaway is the boring but correct one: keep Postgres patched, and remember that even internal machinery can become a side channel.

Implementation Footguns

8. Missing FORCE ROW LEVEL SECURITY

ENABLE ROW LEVEL SECURITY turns policies on for ordinary roles, but the table owner still sails right past them. If your application connects as the owner, you've enabled RLS that does nothing.

-- Table owner still sees everything!
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

Adding FORCE is what makes the policies apply to everyone, owner included:

-- Force RLS for everyone, including owners
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

9. USING vs WITH CHECK Confusion

USING decides which existing rows a user can see and touch for SELECT, UPDATE, and DELETE. WITH CHECK decides which new or modified rows are allowed in on INSERT and UPDATE. If you only write USING, users can insert rows they'd never be allowed to read back.

-- Users can INSERT data they can't see!
CREATE POLICY tenant_data ON orders
FOR ALL
USING (tenant_id = current_setting('app.tenant_id')::uuid);

For real tenant isolation you almost always want both clauses, with the same condition:

CREATE POLICY tenant_isolation ON orders
FOR ALL
USING (tenant_id = current_setting('app.tenant_id')::uuid)
WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

10. Connection Pooling Context Loss

Behind a pooler like PgBouncer, every application connection shares the same database role. That makes current_user worthless for tenant isolation, because it's the same value for everyone.

-- Useless with PgBouncer - all connections share same user
CREATE POLICY user_data ON orders
USING (owner_id = current_user);

The usual answer is to carry the tenant identity in a session variable that your application sets per transaction, and have the policy read from that instead:

-- Use application-controlled session variables
-- App sets per transaction:
SET app.user_id = 'user-uuid';
SET app.tenant_id = 'tenant-uuid';

-- Policy reads from session:
CREATE POLICY tenant_isolation ON orders
FOR ALL
USING (tenant_id = current_setting('app.tenant_id')::uuid)
WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

If you go this route, make sure clients can't set those variables themselves, or the whole scheme falls apart:

-- Prevent clients from setting app.* directly
REVOKE ALL ON SCHEMA pg_catalog FROM app_user;
-- Only allow trusted server code to set these variables

11. Foreign Key Failures Under RLS

Foreign key checks have to read the parent row. If RLS blocks SELECT on the parent table, an otherwise valid insert into the child table fails the FK check, even though the referenced row genuinely exists.

-- This INSERT fails even if customer exists
INSERT INTO orders (customer_id, tenant_id)
VALUES ('existing-customer-id', 'my-tenant');
-- ERROR: insert or update on table "orders" violates foreign key constraint

The parent table needs a SELECT policy that lets the check see the rows it's validating against:

-- Parent table needs SELECT policy for FK checks
CREATE POLICY customer_fk_visibility ON customers
FOR SELECT
USING (tenant_id = current_setting('app.tenant_id')::uuid);

12. Unique Constraint Cross-Tenant Leakage

A global unique constraint enforces uniqueness across the entire table, which means a duplicate-key error tells the inserting user that the value already exists somewhere, even in a tenant they can't see.

-- This reveals that email exists in ANY tenant
CREATE UNIQUE INDEX users_email_unique ON users(email);
-- INSERT fails with "duplicate key" even for other tenants

Scope the uniqueness to the tenant and the leak goes away:

-- Scope uniqueness to tenant
CREATE UNIQUE INDEX users_email_per_tenant
ON users(tenant_id, lower(email));

13. Silent Failures

RLS doesn't shout when it blocks you. An UPDATE that you expect to touch 100 rows can quietly affect 0, with no error and no warning, because the policy filtered them all out. That makes debugging genuinely unpleasant.

When a query isn't doing what you expect, the quickest check is to take RLS out of the picture temporarily and see if the behavior changes:

-- Temporarily disable RLS to test
SET row_security = off;
-- Run your query to see if RLS is the issue
SET row_security = on;

-- Or check if policies are active
SELECT row_security_active('table_name');

14. Column-Level Security Gaps

RLS works on rows, not columns. So within a row the user is allowed to see, every column is fair game, including the ones you'd rather they didn't see.

-- Users can see SSN in their own records
SELECT * FROM users WHERE tenant_id = current_setting('app.tenant_id')::uuid;

If you need to protect specific columns, RLS won't do it for you. You can revoke column privileges, or expose a view that masks the sensitive fields:

-- Option 1: Column privileges
REVOKE SELECT (ssn, salary) ON users FROM app_user;

-- Option 2: Secure views
CREATE VIEW users_safe AS
SELECT id, name, email,
       CASE WHEN has_role('hr_role')
            THEN ssn
            ELSE 'XXX-XX-' || right(ssn, 4)
       END as ssn_masked
FROM users;

15. Materialized Views and Background Jobs

A policy protects the table it's attached to. The moment data leaves that table, into a materialized view, an export file, or a background job, it's no longer covered. A SELECT * into a materialized view happily copies every tenant's rows.

-- Materialized view bypasses RLS
CREATE MATERIALIZED VIEW order_summary AS
SELECT * FROM orders; -- Contains all tenants' data!

-- Background job exports everything
COPY (SELECT * FROM orders) TO '/tmp/backup.csv';

The fix is to do the filtering yourself wherever data crosses that boundary, either by aggregating away the row-level detail or by scoping the export:

-- Apply filtering in materialized views
CREATE MATERIALIZED VIEW tenant_order_summary AS
SELECT tenant_id, COUNT(*), SUM(amount)
FROM orders
GROUP BY tenant_id;

-- Use RLS-aware exports
COPY (
  SELECT * FROM orders
  WHERE tenant_id = 'specific-tenant'
) TO '/tmp/tenant_backup.csv';

16. Multiple Policy Confusion

Permissive policies are combined with OR. Add a second policy and you've widened access, not narrowed it. It's easy to write a broad admin policy that quietly grants everyone more than you meant to.

-- These policies are OR-ed - users get access if EITHER is true
CREATE POLICY user_own_data ON orders
USING (owner_id = current_user_id());

CREATE POLICY admin_all_data ON orders
USING (has_role('admin')); -- Oops, too broad!

You have two ways to keep control. A restrictive policy is AND-ed with the rest, so it can only tighten access. Or you can keep all the logic in a single policy where you can see the whole condition at once:

-- Option 1: Use restrictive policies (AND-ed)
CREATE POLICY tenant_restriction ON orders
AS RESTRICTIVE
USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- Option 2: Combine logic in single policy
CREATE POLICY combined_access ON orders
USING (
  tenant_id = current_setting('app.tenant_id')::uuid
  AND (
    owner_id = current_user_id()
    OR has_role('tenant_admin')
  )
);

Key Takeaways

If there's a thread running through all sixteen, it's that RLS does exactly one job, filtering rows, and assumes you've handled everything around it.

  1. RLS is not a security silver bullet: it can be bypassed through several different vectors, from views to materialized views to side channels.
  2. Performance impact is severe: index your policy columns and keep the policies themselves simple.
  3. Testing methodology is critical: test as the application role, never as a superuser.
  4. Silent failures make debugging painful: policies fail closed, without warnings, so a wrong result looks like a correct empty one.
  5. Context management matters: carry identity in secure session variables, not current_user, especially behind a pooler.
  6. Defense in depth: pair RLS with column privileges, secure views, and application-level checks rather than leaning on it alone.

Used carefully, RLS is a genuinely good way to enforce isolation close to the data. The footguns above are the price of that proximity, and once you know they're there, most of them are straightforward to design around.

Back to blog

Related articles

Explore the standard for database development

Self-host
Cloud