Row-Level Security in PostgreSQL: Keep Tenant Data in the Database

Multi-tenant applications have one security rule that must never be optional: a user from one tenant must not be able to read or change another tenant’s data.

It is tempting to enforce that rule only in application code:

SELECT * FROM projects WHERE organization_id = $1;

That works until one query forgets the filter, an admin endpoint is reused, or a new service accesses the database directly. PostgreSQL Row-Level Security (RLS) moves the rule to the place where every query must pass: the database.

What RLS Does

RLS lets PostgreSQL decide which rows a role may read, insert, update, or delete. The application can still query projects, but PostgreSQL silently limits the result to rows allowed by the policy.

Think of it as a WHERE clause enforced by the database, rather than one developers must remember to write.

RLS is especially useful for:

shared-schema SaaS applications
customer-facing reporting APIs
internal tools with organization or team boundaries
direct database access from backend services

A Small Multi-Tenant Schema

Suppose every project belongs to an organization:

CREATE TABLE projects (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id uuid NOT NULL,
  name text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX projects_organization_id_idx ON projects (organization_id);

The index matters. RLS policies become part of normal query planning, so the columns used to scope rows should be indexed just as they would be for an explicit filter.

Enable RLS and Create a Policy

First, enable RLS on the table:

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

Once it is enabled, non-owner roles have no access to rows unless a policy allows it. Here is a policy that reads the active organization from a transaction-local setting:

CREATE POLICY organization_can_access_its_projects
ON projects
FOR ALL
TO app_user
USING (
  organization_id = current_setting('app.organization_id', true)::uuid
)
WITH CHECK (
  organization_id = current_setting('app.organization_id', true)::uuid
);

USING controls which existing rows are visible for SELECT, UPDATE, and DELETE. WITH CHECK controls which new row values are permitted for INSERT and UPDATE.

Using both is important. A policy with only USING may let a user see the right rows but still leave writes insufficiently constrained.

Set the Tenant Context Safely

Before running application queries, set the organization ID inside a transaction:

BEGIN;

SELECT set_config('app.organization_id', 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', true);

SELECT id, name FROM projects;

COMMIT;

The final true makes the setting local to the current transaction. This is critical when using a connection pool: the next request must not inherit a previous request’s tenant identity.

In application code, authenticate the user first, derive their organization from trusted server-side data, start a transaction, set the context, and then issue queries. Never let a client choose an organization ID and pass it straight into set_config.

Separate Policies Make Intent Clearer

FOR ALL is concise, but separate policies can communicate different permissions more clearly. For example, members may read projects while only managers can create them:

CREATE POLICY members_can_read_projects
ON projects
FOR SELECT
TO app_user
USING (
  organization_id = current_setting('app.organization_id', true)::uuid
);

CREATE POLICY managers_can_create_projects
ON projects
FOR INSERT
TO app_user
WITH CHECK (
  organization_id = current_setting('app.organization_id', true)::uuid
  AND current_setting('app.role', true) = 'manager'
);

Policies are permissive by default: policies that apply to the same command are combined with OR. PostgreSQL also supports restrictive policies, which are combined with AND, when you need an additional rule such as a soft-delete or compliance boundary.

The Most Common RLS Mistakes

Using the Table Owner in the App

Table owners, superusers, and roles with BYPASSRLS can bypass RLS. Your application should connect as a deliberately limited role, not as the schema owner.

If the table owner must also be constrained, force it explicitly:

ALTER TABLE projects FORCE ROW LEVEL SECURITY;

Use this with care: foreign-key checks and administrative jobs may need a separate trusted role or a different access path.

Forgetting `WITH CHECK`

An UPDATE can change organization_id unless the new value is checked. Define WITH CHECK for write policies, even when the client never exposes that column today.

Treating RLS as Authentication

RLS decides what an already-connected database role can access. It does not verify passwords, validate JWTs, or decide who the request is. Authentication and secure tenant-context assignment still belong in your backend or identity layer.

Relying on RLS as the Only Performance Filter

RLS protects data, but queries should still be well shaped. Keep explicit filters where they make the query clearer, index scope columns, and inspect EXPLAIN (ANALYZE, BUFFERS) for important endpoints.

Test Policies Like Any Other Security Boundary

Testing only as the migration role is misleading because that role may bypass RLS. Test with the same limited role used by the application:

SET ROLE app_user;
BEGIN;
SELECT set_config('app.organization_id', 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', true);

-- Returns only this organization's projects
SELECT * FROM projects;

-- Must fail: the row belongs to a different organization
INSERT INTO projects (organization_id, name)
VALUES ('b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'Cross-tenant project');

ROLLBACK;

Cover the unhappy paths too: a missing tenant context, cross-tenant updates, deletes, and endpoints that join multiple protected tables.

A Practical Rollout Checklist

Add the tenant or ownership column and an index for it.
Create a least-privileged application role.
Enable RLS and add policies for each operation.
Set trusted identity context with transaction-local configuration.
Test as the application role, including denied access.
Roll out table by table and monitor query plans and policy errors.

RLS does not replace careful application authorization. It gives that authorization a final, independent enforcement point. In a multi-tenant system, that extra layer turns a missed WHERE organization_id = ... from a data leak into an empty result.