Topic: PostgreSQL

PostgreSQL RLS is not an automatic WHERE clause: four boundaries for multi-tenant data

Use PostgreSQL Row-Level Security for a shared table by separating GRANTs from policies, existing rows from proposed rows, and caller identity from view or function ownership.

A shared table is convenient. Every workspace uses the same documents table, and queries, migrations, and observability stay in one place. But one API that omits WHERE workspace_id = ... can break the isolation model.

PostgreSQL Row-Level Security, or RLS, lets the database decide which rows a role may read or change. It is a useful second line of defence for a multi-tenant system. It does not authenticate an HTTP request, and it does not replace GRANT. Treating RLS as a feature that silently adds a WHERE clause is how boundary bugs appear.

This article uses one shared documents table with id, workspace_id, title, and body. Each member may work only with documents in the current workspace.

First boundary: GRANT governs the table, policies govern rows

Once RLS is enabled, the database answers two separate questions:

1. Does this database role have SELECT, INSERT, UPDATE, or DELETE privilege?
2. If it does, which rows may it operate on?

GRANT answers the first question. A policy answers the second. Both must allow the operation. A role with a policy but without GRANT SELECT still cannot read. A role with a grant but no applicable policy gets the default-deny behaviour on an RLS-enabled table. That is the behaviour defined in the PostgreSQL RLS documentation.

A migration can start like this:

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

GRANT SELECT, INSERT, UPDATE, DELETE ON documents TO app_member;

Do not give a role broader table privileges than the application needs. RLS is not a patch for loose table permissions. Keeping the two layers separate makes a migration much easier to review.

Second boundary: where the trusted workspace identity comes from

A policy needs to know the current workspace. The workspace_id in a request body, a query parameter, or browser storage is not a trustworthy answer. A user can alter each of them.

A safer flow verifies the login and workspace membership in the backend, then sets a workspace context inside the same database transaction. This example uses a custom setting. The application must still pass a backend-verified value through a parameterized query.

BEGIN;

SELECT set_config('app.workspace_id', $1, true);
-- The backend supplies $1 only after verifying workspace membership.

SELECT id, title
FROM documents;

COMMIT;

The final true makes the setting transaction-local. It expires when the transaction ends. This matters with transaction pooling because the next request may borrow the same PostgreSQL connection. A setting made once at service startup does not belong to the next request automatically.

The trust boundary matters more than the setting function. Browsers must not receive a database connection that can issue arbitrary SQL against this table. The backend must finish authorization before it sets the context. current_setting() and set_config() transport an already-verified decision into a policy. They do not authenticate anyone.

Third boundary: USING sees the old row, WITH CHECK sees the proposed row

This distinction causes many RLS mistakes.

• USING filters rows that already exist. It controls which rows a SELECT can see and which existing rows an UPDATE or DELETE can target.
• WITH CHECK validates the proposed row after an INSERT or UPDATE. A false or NULL result rejects the write.

Putting the current workspace behind one function keeps the policies readable:

CREATE FUNCTION app.current_workspace_id()
RETURNS uuid
LANGUAGE sql
STABLE
AS $$
  SELECT NULLIF(current_setting('app.workspace_id', true), '')::uuid;
$$;

Then write a policy for each operation:

CREATE POLICY documents_read ON documents
  FOR SELECT TO app_member
  USING (workspace_id = app.current_workspace_id());

CREATE POLICY documents_add ON documents
  FOR INSERT TO app_member
  WITH CHECK (workspace_id = app.current_workspace_id());

CREATE POLICY documents_edit ON documents
  FOR UPDATE TO app_member
  USING (workspace_id = app.current_workspace_id())
  WITH CHECK (workspace_id = app.current_workspace_id());

CREATE POLICY documents_remove ON documents
  FOR DELETE TO app_member
  USING (workspace_id = app.current_workspace_id());

documents_edit needs both expressions. If a member may edit a document in workspace A, USING makes it an eligible target. WITH CHECK stops the member from changing its workspace_id to B. Leaving out either condition opens a different hole: one in selecting the old row or one in accepting the new row.

The CREATE POLICY documentation defines the full behaviour. It also explains why INSERT ... RETURNING and ON CONFLICT DO UPDATE belong in the same test suite as ordinary CRUD.

More policies are not automatically safer

PostgreSQL combines permissive policies with OR. If a table already has a current-workspace rule and a new permissive SELECT policy allows is_public = true, a row matching either rule becomes visible. That may be correct, but it expands the visible set. It does not add another restriction.

Restrictive policies combine with AND, but they still need at least one permissive policy to allow access. When reviewing a change, list every policy for the command, then ask whether any permissive rule opens rows unexpectedly. Reading only the newest migration is not enough.

Owners, views, and functions can change the effective identity

RLS does not constrain superusers or roles with BYPASSRLS. A table owner usually bypasses RLS for its own table too, unless the table uses ALTER TABLE documents FORCE ROW LEVEL SECURITY. Do not treat a successful owner query as evidence that application isolation works.

Views and SECURITY DEFINER functions need the same level of review. A normal view usually evaluates access to its base tables with the view owner’s privileges and policies. If access should follow the caller, make security_invoker explicit:

CREATE VIEW workspace_documents
WITH (security_invoker = true)
AS
SELECT id, workspace_id, title
FROM documents;

A SECURITY DEFINER function executes with its owner’s privileges. Use one only when elevated privilege is required. Give it a trusted, least-privilege owner, set a safe search_path, and grant EXECUTE only to the roles that need it. The view documentation and the function security documentation explain these boundaries.

Two more exceptions are easy to miss. TRUNCATE is not subject to row policies, so table privileges must still limit it. Constraint and referential-integrity checks can also bypass row security to preserve data integrity. If an error can reveal whether a row exists, treat that as observable information.

Test allows and denials, not whether a policy exists

A useful test has two workspaces and a non-owner application role. For every operation, it should verify both sides:

• SELECT, INSERT, UPDATE, and DELETE work in the member’s own workspace.
• Cross-workspace reads, edits, deletes, and attempts to move a row to another workspace are rejected.

Add RETURNING, upserts, views, functions, and transaction-pooling cases. That is much closer to the real access path than seeing a policy after a migration succeeds.

RLS can add a database-level protection layer to a shared schema. It does not choose a multi-tenant architecture for you. Isolation needs, backup and recovery, workload interference, and operational constraints still belong in the decision. This PostgreSQL community discussion is useful adoption context, not a replacement for official semantics or direct tests.

What I learned

• I will review GRANT and RLS policies separately. One controls access to the table, the other controls access to individual rows.
• I will distinguish the old row from the proposed row before choosing USING and WITH CHECK. That prevents an update from crossing a workspace boundary.
• I will treat a new permissive policy as a possible permission expansion and test it with a non-owner role.
• I will not assume a view or SECURITY DEFINER function uses the caller’s identity. Both need their own review.

External references

• PostgreSQL: Row Security Policies
• PostgreSQL: CREATE POLICY
• PostgreSQL: CREATE VIEW
• PostgreSQL: Writing SECURITY DEFINER Functions Safely
• Supabase: Row Level Security
• Reddit: Multi-tenant SaaS architecture discussion

Further learning

• Supabase: Advanced Row Level Security Policies covers policy composition and authorization design.
• Supabase: Row Level Security step by step walks through enabling RLS, writing policies, and verifying the result.