Common Postgres Row-Level-Security footguns

Postgres's Row-Level Security (RLS) is a powerful feature for implementing fine-grained access control, but it's riddled with subtle traps that can destroy performance or completely bypass security. This comprehensive guide covers all the major footguns with practical fixes and real-world examples.

Performance Footguns

1. The LEAKPROOF Function Performance Killer

The footgun: Non-LEAKPROOF functions in RLS policies prevent index usage, causing catastrophic performance degradation.

Why it happens: Postgres must apply RLS filtering first, then non-LEAKPROOF functions, preventing the query planner from using indexes.

Example problem:

-- 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 fix:

-- 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%'));

Performance impact: Queries can go from milliseconds to hours on large tables.

2. Complex Policy Performance Death

The footgun: Complex RLS policies with subqueries execute for every row, multiplying query cost exponentially.

Bad example:

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
  )
);

Better approach:

-- 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

The footgun: Forgetting to index columns used in RLS policies forces sequential scans.

Essential indexes:

-- 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

Security Footguns

4. The BYPASSRLS Superuser Trap

The footgun: Superusers and table owners bypass RLS by default, creating false security confidence during testing.

Why it's dangerous: Testing with superuser accounts makes RLS appear to work when it's actually being ignored.

The fix:

-- 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

Testing pattern:

-- 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

The footgun: Views are SECURITY DEFINER by default, running with creator's privileges and bypassing RLS.

Dangerous example:

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

Secure approaches:

-- 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

The footgun: Query execution time leaks information about restricted data, allowing sophisticated attacks.

Attack scenario: In a multi-tenant medical database, an attacker measures query times to determine if patients with specific conditions exist in other tenants' data.

Technical details:

• RLS policy enforcement creates measurable timing differences
• Attackers can infer secret cardinality information
• Works even across network latency in cloud environments

Example vulnerable query:

-- Timing reveals if forbidden patients exist
SELECT COUNT(*) FROM patients
WHERE condition = 'rare_disease'
AND tenant_id = current_setting('app.tenant_id')::uuid;

Mitigation strategies:

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

Research note: This attack has been demonstrated in academic research and works in real-world cloud environments.

7. CVE-2019-10130: Statistics Leakage

The footgun: CVE-2019-10130. Postgres's query planner statistics could leak sampled data from RLS-protected rows.

Technical details:

• Query planner collects statistics by sampling column data
• Users could craft operators to read statistics containing forbidden data
• Affected Postgres 9.5-11 before May 2019 patches

Status: Fixed in Postgres 9.5.17, 9.6.13, 10.8, 11.3

Lesson: Keep Postgres updated and remember that even internal mechanisms can leak data.

Implementation Footguns

8. Missing FORCE ROW LEVEL SECURITY

The footgun: Enabling RLS without FORCE allows table owners to bypass policies.

Problem:

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

Solution:

-- 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

The footgun: USING filters existing rows for SELECT/UPDATE/DELETE, but WITH CHECK validates new/modified rows for INSERT/UPDATE.

Dangerous example:

-- 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);

Correct approach:

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

The footgun: With connection pooling, current_user is a shared database role, useless for tenant isolation.

Problem:

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

Solution:

-- 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);

Security hardening:

-- 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

The footgun: INSERT into child tables fails FK checks because RLS blocks SELECT on parent rows.

Example failure:

-- 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

Solution:

-- 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

The footgun: Global unique constraints reveal data existence across tenants.

Problem:

-- 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

Solution:

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

13. Silent Failures

The footgun: RLS failures are silent - operations fail without errors or warnings.

Example: An UPDATE that should modify 100 rows silently affects 0 rows due to RLS policy.

Debugging approach:

-- 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

The footgun: RLS filters rows, not columns. Sensitive columns remain visible in allowed rows.

Problem:

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

Solutions:

-- 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

The footgun: Data copied to materialized views or exported by jobs isn't automatically protected by source table policies.

Problems:

-- 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';

Solutions:

-- 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

The footgun: Multiple permissive policies are OR-ed together; one broad policy can override stricter ones.

Problem:

-- 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!

Solutions:

-- 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

1. RLS is not a security silver bullet - it can be bypassed through multiple vectors
2. Performance impact is severe - always index policy columns and keep policies simple
3. Testing methodology is critical - never test with superuser accounts
4. Silent failures make debugging painful - policies fail without warnings
5. Context management is crucial - use secure session variables, not current_user
6. Defense in depth - combine RLS with column privileges, secure views, and application-level controls

RLS is powerful when implemented correctly, but requires careful attention to these footguns to avoid catastrophic security and performance failures.