Row-Level Security (RLS): How It Works and How to Implement It

Row-Level Security (RLS) is one of PostgreSQL’s most powerful features — and one of the most underused. Instead of enforcing data access rules in your application code (where it’s easy to miss a WHERE clause), RLS bakes security directly into the database. Even if you write a bug in your app, the database won’t return rows the user shouldn’t see.

This post covers how RLS works, how policies are evaluated, and how to implement it for real-world use cases like multi-tenant SaaS apps.

What is Row-Level Security?

By default, any user with SELECT access to a table can read every row. RLS changes that: you define policies that describe which rows a particular database role (or user) is allowed to see or modify.

-- Without RLS: Anyone with SELECT on "orders" can see ALL orders
SELECT * FROM orders; -- Returns orders for ALL users

-- With RLS: A policy restricts rows to only the current user's orders
SELECT * FROM orders; -- Returns ONLY orders belonging to the current user

RLS is enforced at the database engine level — below application code, below ORM queries, below even a compromised API layer.

Enabling RLS

RLS must be explicitly enabled on a table. It’s off by default.

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

-- By default, once enabled, table owners bypass RLS.
-- To also restrict the table owner:
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

Once enabled, no rows are accessible until you create at least one permissive policy. A table with RLS enabled and no policies returns zero rows to non-superusers.

Creating Policies

Policies are the rules that define who can see or modify which rows.

-- Basic syntax
CREATE POLICY policy_name
  ON table_name
  [AS { PERMISSIVE | RESTRICTIVE }]
  [FOR { ALL | SELECT | INSERT | UPDATE | DELETE }]
  [TO role_name]
  USING (expression)        -- Applied to SELECT, UPDATE, DELETE
  [WITH CHECK (expression)] -- Applied to INSERT, UPDATE

A Simple User-Isolation Policy

-- Allow users to only see their own rows
CREATE POLICY user_isolation_policy
  ON orders
  FOR ALL
  TO authenticated_users
  USING (user_id = current_user::uuid);

This tells PostgreSQL: “When any authenticated_users role member queries orders, only return rows where user_id matches the current database user.”

How RLS is Evaluated

Understanding evaluation order is critical:

1. Query runs → PostgreSQL’s planner adds the policy expression as an implicit WHERE clause
2. PERMISSIVE policies are OR’d together — a row is visible if it passes any permissive policy
3. RESTRICTIVE policies are AND’d — a row must pass all restrictive policies
4. The final result: (permissive_1 OR permissive_2) AND restrictive_1 AND restrictive_2

-- Permissive: User can see their own rows OR public rows
CREATE POLICY see_own_rows ON posts
  AS PERMISSIVE FOR SELECT
  USING (author_id = current_setting('app.user_id')::uuid);

CREATE POLICY see_public_posts ON posts
  AS PERMISSIVE FOR SELECT
  USING (is_public = true);

-- Restrictive: User can ONLY see non-deleted rows (enforced on top)
CREATE POLICY no_deleted_rows ON posts
  AS RESTRICTIVE FOR SELECT
  USING (deleted_at IS NULL);

-- Result: User sees rows that are (their own OR public) AND not deleted

Multi-Tenant RLS with app.current_tenant_id

The most common real-world use case is a multi-tenant SaaS application. All tenants share one database, but each must only see their own data.

Step 1: Set Up the Schema

CREATE TABLE tenants (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES tenants(id),
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE tasks (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id UUID NOT NULL REFERENCES projects(id),
  tenant_id UUID NOT NULL REFERENCES tenants(id),
  title TEXT NOT NULL,
  completed BOOLEAN DEFAULT false
);

Step 2: Create a Dedicated App Role

Never connect to PostgreSQL as a superuser from your app. Use a limited role:

-- Create a role for your application
CREATE ROLE app_user NOLOGIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- Create a login user that inherits app_user
CREATE ROLE api_user LOGIN PASSWORD 'your-strong-password' IN ROLE app_user;

Step 3: Enable RLS and Create Policies

-- Enable RLS on all tenant tables
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;

-- Projects policy: only see your tenant's projects
CREATE POLICY tenant_isolation_projects
  ON projects
  FOR ALL
  TO app_user
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::uuid);

-- Tasks policy: only see your tenant's tasks
CREATE POLICY tenant_isolation_tasks
  ON tasks
  FOR ALL
  TO app_user
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::uuid);

The current_setting('app.current_tenant_id') reads a PostgreSQL session variable that your app sets at the start of each request.

Step 4: Set the Tenant in Your Application

Before executing any queries, set the session variable to the authenticated tenant’s ID:

// Node.js + node-postgres (pg) example
import { Pool } from "pg";

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

async function withTenant(tenantId, callback) {
  const client = await pool.connect();
  try {
    // Set the tenant context for this session
    await client.query(`SET LOCAL app.current_tenant_id = '${tenantId}'`);

    // Run the callback — all queries within are scoped to the tenant
    return await callback(client);
  } finally {
    client.release();
  }
}

// Express middleware
app.use(async (req, res, next) => {
  const tenantId = req.user?.tenantId; // From JWT or session
  if (!tenantId) return res.status(401).json({ error: "Unauthorized" });

  req.db = {
    query: (text, params) =>
      withTenant(tenantId, (client) => client.query(text, params)),
  };
  next();
});

// Route handler — no need to add WHERE tenant_id = ? manually!
app.get("/api/projects", async (req, res) => {
  const { rows } = await req.db.query(
    "SELECT * FROM projects ORDER BY created_at DESC",
  );
  res.json(rows); // Automatically scoped to the tenant
});

Step 5: Using SET LOCAL vs SET

SET LOCAL scopes the variable to the current transaction. SET scopes it to the session.

-- Scoped to transaction (recommended for connection pools)
BEGIN;
SET LOCAL app.current_tenant_id = 'uuid-here';
SELECT * FROM projects; -- Only tenant's projects
COMMIT;

-- Scoped to session (risky with pooled connections!)
SET app.current_tenant_id = 'uuid-here';

With connection pooling, always use SET LOCAL inside a transaction to prevent tenant context from leaking between requests.

Using RLS with Supabase

Supabase builds heavily on PostgreSQL RLS. Their auth.uid() function integrates with JWT-based authentication:

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

-- Users can only see their own posts
CREATE POLICY "Users can view own posts"
  ON posts FOR SELECT
  USING (auth.uid() = user_id);

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

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

-- Users can only delete their own posts
CREATE POLICY "Users can delete own posts"
  ON posts FOR DELETE
  USING (auth.uid() = user_id);

Supabase’s client library automatically passes the user’s JWT to PostgREST, which sets the correct context for RLS.

Testing RLS Policies

Always test that your policies work correctly by simulating non-privileged access:

-- Test as a regular user
SET ROLE app_user;
SET app.current_tenant_id = 'tenant-a-uuid';

-- Should return only Tenant A's projects
SELECT * FROM projects;

-- Try to access another tenant's data
SET app.current_tenant_id = 'tenant-b-uuid';
SELECT * FROM projects WHERE tenant_id = 'tenant-a-uuid'::uuid;
-- Returns 0 rows — RLS blocks it

-- Reset role
RESET ROLE;

You can also use EXPLAIN to verify that the policy predicate is being applied:

EXPLAIN SELECT * FROM projects;
-- Filter: (tenant_id = (current_setting('app.current_tenant_id'))::uuid)

Performance Considerations

RLS adds a predicate to every query, but the performance impact is minimal when indexes are set up correctly:

-- Ensure tenant_id is indexed on all RLS-protected tables
CREATE INDEX idx_projects_tenant_id ON projects(tenant_id);
CREATE INDEX idx_tasks_tenant_id ON tasks(tenant_id);

-- Composite indexes for common query patterns
CREATE INDEX idx_tasks_tenant_project ON tasks(tenant_id, project_id);

With proper indexes, PostgreSQL will use an index scan on tenant_id — making RLS queries as fast as if you’d written the WHERE clause yourself.

Common Pitfalls

1. Superusers bypass RLS

-- Superusers (postgres) bypass RLS by default
-- Use FORCE ROW LEVEL SECURITY if the table owner also needs to be restricted
ALTER TABLE projects FORCE ROW LEVEL SECURITY;

2. Forgetting WITH CHECK for INSERT/UPDATE

USING applies to reads (SELECT, UPDATE filter, DELETE filter). WITH CHECK applies to writes (INSERT, UPDATE new values).

-- ❌ This allows SELECT but doesn't prevent inserting rows for other tenants!
CREATE POLICY bad_policy ON projects
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

-- ✅ Restrict both reads and writes
CREATE POLICY good_policy ON projects
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::uuid);

3. Session variable not set

If current_setting('app.current_tenant_id') is called but the variable hasn’t been set, PostgreSQL throws an error. Use the missing_ok argument to return NULL instead:

-- Returns NULL instead of error if variable not set
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid)

Conclusion

Row-Level Security is the most robust way to enforce data access rules. Instead of relying on every query in your application to include the correct WHERE clause, you define the rules once at the database level and they’re enforced everywhere — without exception.

For multi-tenant SaaS applications in particular, RLS is the right tool: it’s battle-tested, performant, and moves the security guarantee to where it belongs — the database.

---

💬 Want to learn, build, and grow with a community of developers? Join the King Technologies Discord — where code meets community!