Shipsafershipsafer.ai
Web Security

PostgreSQL Row-Level Security: Implementing Multi-Tenant Data Isolation

Implement PostgreSQL Row-Level Security with RLS policies, app_user patterns, Supabase RLS, testing isolation correctness, and understanding performance impact.

March 9, 20267 min readShipSafer Team

PostgreSQL Row-Level Security: Implementing Multi-Tenant Data Isolation

Row-Level Security (RLS) is one of PostgreSQL's most powerful security features — and one of the most underused. Without it, application-level code is your only barrier between a bug and a full data leak across tenants. RLS moves that enforcement into the database itself, making it impossible to bypass through application code mistakes.

What is Row-Level Security?

RLS lets you define policies that control which rows a database user can see or modify. These policies are enforced transparently — the database automatically adds WHERE clauses to every query based on the current user context.

-- Without RLS: everyone sees everything
SELECT * FROM orders;  -- Returns all 10 million rows

-- With RLS: each user sees only their rows
SET app.current_user_id = '123';
SELECT * FROM orders;  -- Returns only orders where user_id = '123'

The enforcement happens inside PostgreSQL, not in your application code. Even if a developer forgets a WHERE clause, RLS prevents the data leak.

Enabling RLS

-- Enable RLS on a table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- RLS is now active but no policies exist = no rows visible
-- (except to the table owner and superusers)

Important: enabling RLS without policies results in no rows being visible to non-owner users. Add policies before enabling RLS on production tables, or wrap the change in a transaction with the policies.

The app.current_user_id Pattern

The most common pattern is to pass the current user ID as a PostgreSQL session variable, then reference it in policies:

-- Set the variable at the start of each request
SET app.current_user_id = '550e8400-e29b-41d4-a716-446655440000';

-- Or use a role-specific setting
SET LOCAL app.current_user_id = '550e8400-e29b-41d4-a716-446655440000';
-- SET LOCAL is transaction-scoped — safer for connection pool environments

Define a helper function:

CREATE OR REPLACE FUNCTION current_app_user_id() RETURNS uuid AS $$
  SELECT NULLIF(current_setting('app.current_user_id', TRUE), '')::uuid;
$$ LANGUAGE sql STABLE;

Creating Policies

-- Policy: users can only see their own orders
CREATE POLICY orders_user_isolation ON orders
  FOR ALL
  TO app_user   -- Only applies to this database role
  USING (user_id = current_app_user_id());

-- Separate read and write policies for finer control
CREATE POLICY orders_select ON orders
  FOR SELECT
  USING (user_id = current_app_user_id());

CREATE POLICY orders_insert ON orders
  FOR INSERT
  WITH CHECK (user_id = current_app_user_id());

CREATE POLICY orders_update ON orders
  FOR UPDATE
  USING (user_id = current_app_user_id())
  WITH CHECK (user_id = current_app_user_id());

CREATE POLICY orders_delete ON orders
  FOR DELETE
  USING (user_id = current_app_user_id());

The USING clause applies to read operations (what rows are visible). The WITH CHECK clause applies to write operations (what rows can be written). Always set both for update operations.

Multi-Tenant Setup with app_user Role

Create a dedicated database role for application connections that has limited privileges:

-- Create application role (no superuser, no createdb)
CREATE ROLE app_user NOLOGIN;
CREATE ROLE app_connection LOGIN PASSWORD 'secure_password';
GRANT app_user TO app_connection;

-- Grant table access to the role, not superuser
GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON users TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;

-- Sequences
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

In your application, connect as app_connection (not a superuser). Superusers bypass RLS by default.

Application Integration (Node.js Example)

With a connection pool, use SET LOCAL in a transaction to scope the user ID correctly:

import { Pool } from 'pg';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

async function withUserContext(userId, callback) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query('SET LOCAL app.current_user_id = $1', [userId]);
    const result = await callback(client);
    await client.query('COMMIT');
    return result;
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}

// Usage — RLS automatically filters to userId's rows
const orders = await withUserContext(currentUser.id, async (client) => {
  const result = await client.query('SELECT * FROM orders');
  return result.rows;  // Only contains the current user's orders
});

Admin Bypass

For admin operations, you can temporarily bypass RLS:

-- Option 1: Use SET LOCAL with a special value your policies recognize
CREATE POLICY orders_admin ON orders
  FOR ALL
  TO app_user
  USING (
    current_app_user_id() = user_id
    OR current_setting('app.is_admin', TRUE) = 'true'
  );

-- Option 2: Use a separate admin role that bypasses RLS
CREATE ROLE admin_user BYPASSRLS NOLOGIN;

The BYPASSRLS option is the cleanest approach for admin users. Keep admin connections separate from application connections.

Supabase RLS Integration

Supabase builds on PostgreSQL's RLS using the auth.uid() function from the GoTrue auth service:

-- Enable RLS
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- Allow users to read their own posts
CREATE POLICY "users can read own posts"
  ON posts FOR SELECT
  USING (auth.uid() = user_id);

-- Allow users to create posts for themselves
CREATE POLICY "users can create posts"
  ON posts FOR INSERT
  WITH CHECK (auth.uid() = user_id);

-- Allow public read of published posts
CREATE POLICY "public can read published posts"
  ON posts FOR SELECT
  USING (published = true);

Multiple policies for the same operation are combined with OR logic. A row is visible if any matching policy permits it.

Testing RLS Isolation

Always test that RLS actually blocks cross-tenant access:

-- Test as a specific user
SET ROLE app_connection;
SET app.current_user_id = 'user-a-uuid';

-- This should only return user A's rows
SELECT count(*) FROM orders;

-- Attempt to access user B's data — should return 0 rows, not an error
SELECT * FROM orders WHERE user_id = 'user-b-uuid';

-- Reset
RESET ROLE;
RESET app.current_user_id;

Write automated integration tests:

describe('RLS isolation', () => {
  it('prevents cross-tenant data access', async () => {
    const userAOrders = await withUserContext(USER_A_ID, async (client) => {
      const { rows } = await client.query('SELECT * FROM orders');
      return rows;
    });

    // All returned orders belong to User A
    expect(userAOrders.every(o => o.user_id === USER_A_ID)).toBe(true);

    // User B's orders are not visible
    const userBOrderCount = await withUserContext(USER_A_ID, async (client) => {
      const { rows } = await client.query(
        'SELECT count(*) FROM orders WHERE user_id = $1',
        [USER_B_ID]
      );
      return parseInt(rows[0].count);
    });

    expect(userBOrderCount).toBe(0);
  });
});

Performance Impact

RLS policies add predicate conditions to every query. The performance impact depends on:

  1. Index coverage: If the policy column (user_id) is indexed, the impact is minimal
  2. Policy complexity: Simple equality checks are fast; joins or subqueries in policies are slower
  3. Policy evaluation: Each row is checked against policies during sequential scans

Index your tenant isolation column:

CREATE INDEX idx_orders_user_id ON orders (user_id);
CREATE INDEX idx_posts_user_id ON posts (user_id);

Use EXPLAIN ANALYZE to verify that RLS policies are using indexes:

SET app.current_user_id = 'test-uuid';
EXPLAIN ANALYZE SELECT * FROM orders;

-- Look for: "Index Scan using idx_orders_user_id"
-- Avoid: "Seq Scan" with a filter on user_id

Common Mistakes

Not setting WITH CHECK on UPDATE: Without WITH CHECK, users can update rows to have a different user_id, effectively transferring ownership.

Forgetting SET LOCAL scope: Using SET instead of SET LOCAL means the user ID persists after the transaction ends on a pooled connection, potentially leaking context.

Superuser bypass: Connecting as a superuser silently bypasses all RLS policies. Verify your connection string uses the app_connection role.

Missing policies for new operations: Adding a new table operation (TRUNCATE, for example) without a corresponding policy can leave a gap.

Summary

Row-Level Security provides defense-in-depth for multi-tenant data isolation:

  • Enable RLS on all tenant-scoped tables
  • Use SET LOCAL app.current_user_id within transactions for safe context passing
  • Create separate app_user role with BYPASSRLS only for admin roles
  • Index all tenant isolation columns
  • Test cross-tenant access in integration tests — verify 0 rows returned, not errors
  • Use EXPLAIN ANALYZE to confirm policies use indexes, not sequential scans

Check Your Security Score — Free

See exactly how your domain scores on DMARC, TLS, HTTP headers, and 25+ other automated security checks in under 60 seconds.