Skip to content
Tutorials Center
My learning

Topics

Lesson 75 of 90 ยท Security and Administration

Row-Level Security

Use PostgreSQL row-level security to filter rows per user or tenant with policies, USING and WITH CHECK, current_setting, and avoid the common bypass pitfalls.

Normal GRANT privileges work at the table and column level: a role can read a table or it can't. Often that is too coarse. In a multi-tenant application every customer's invoices live in the same invoices table, and customer A must never see customer B's rows, even if a developer forgets a WHERE tenant_id = ... somewhere. Row-level security (RLS) moves that rule into the database. You attach policies to a table, and PostgreSQL silently adds the policy condition to every query that touches it.

This lesson explains how policies work, how to build tenant isolation with a session setting, and where RLS quietly does not protect you. PostgreSQL's policy syntax is server-only, so the PostgreSQL code is shown read-only. The live examples use SQLite views and plain queries as honest stand-ins that behave the same way for the filtering idea.

The basic idea

RLS is off by default. You switch it on per table, and then you describe which rows each role may see or change:

SQL
CREATE TABLE invoices (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id  integer NOT NULL,
  customer   text    NOT NULL,
  amount     numeric(10,2) NOT NULL
);

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.tenant_id')::integer);

Once ENABLE ROW LEVEL SECURITY has run, the table is deny by default for everyone who is subject to RLS: with no policy, a query returns zero rows and writes fail. The policy above opens exactly the rows whose tenant_id matches a session setting called app.tenant_id. The application sets that value after it connects, and every later SELECT, UPDATE and DELETE on invoices behaves as if it had AND tenant_id = <that value> written in.

How row-level security works

Enable ALTER TABLE ... ENABLE ROW LEVEL SECURITY.
Policies Each policy is a rule about which rows are allowed.
Rewrite The planner adds the rule to every query.

Without any policy, an RLS-protected table returns nothing.

1 / 2

A live stand-in: the filter

SQLite has no policies, but the effect of a USING clause is the same as a view that adds the filter. In this example a one-row table session_ctx plays the part of current_setting('app.tenant_id'), and the view my_invoices plays the part of the table seen through the policy. Watch the same query return different rows when only the "session" changes:

SQL A view that behaves like a USING policy

Press Run (or Ctrl + Enter) to see the result here.

Tenant 1 sees two rows, tenant 2 sees two different rows, and an unknown tenant sees none. That last case is the important design point: a missing or wrong setting should produce no data, never all data.

USING and WITH CHECK

A policy has up to two expressions and they do different jobs:

  • USING is a filter on rows that already exist. It applies to SELECT, and to the "which rows can I find" part of UPDATE and DELETE.
  • WITH CHECK is a test on rows that are about to be written. It applies to INSERT and to the "new version" of an UPDATE. A row that fails it is rejected with an error: new row violates row-level security policy for table "invoices".

A policy can also be limited to a command (FOR SELECT, FOR INSERT, FOR UPDATE, FOR DELETE, or the default FOR ALL) and to roles (TO app_user). Rules for which expression is allowed: SELECT and DELETE policies only take USING, INSERT policies only take WITH CHECK, and UPDATE and ALL policies can take both. If an UPDATE or ALL policy has only USING, PostgreSQL reuses it as the WITH CHECK too.

SQL
CREATE POLICY tenant_read ON invoices FOR SELECT
  USING (tenant_id = current_setting('app.tenant_id')::integer);

CREATE POLICY tenant_write ON invoices FOR INSERT
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::integer);

CREATE POLICY tenant_change ON invoices FOR UPDATE
  USING      (tenant_id = current_setting('app.tenant_id')::integer)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::integer);

The WITH CHECK on the update policy prevents a classic leak: a tenant moving one of its own rows into another tenant's account by changing tenant_id. The USING clause lets it find the row; WITH CHECK refuses the new value.

You can't make an INSERT fail in a live run without ending the script, so here is the same test written as a query. It lists candidate rows and whether they would pass a check for tenant 1:

SQL Which new rows would WITH CHECK allow?

Press Run (or Ctrl + Enter) to see the result here.

The third row is rejected, too. A comparison with NULL is never true, and RLS treats "not true" as "fail". In PostgreSQL the NOT NULL constraint would stop that row anyway; the point is that a policy only lets a row through when its expression is true.

Multi-tenant isolation with current_setting

The usual recipe for a shared-schema SaaS database has four steps:

  1. Every tenant-owned table has a tenant_id column (see multi-tenant design for the schema trade-offs).
  2. The application connects as one low-privilege role, for example app_user, which does not own the tables.
  3. At the start of each request, inside a transaction, the application tells PostgreSQL which tenant it is serving.
  4. Policies compare tenant_id to that value.
SQL
CREATE ROLE app_user LOGIN PASSWORD 'change-me';
GRANT SELECT, INSERT, UPDATE, DELETE ON invoices TO app_user;

-- Per request, inside a transaction:
BEGIN;
SELECT set_config('app.tenant_id', '2', true);   -- true = local to this transaction
SELECT * FROM invoices;                           -- only tenant 2's rows
COMMIT;

set_config(name, value, is_local) with is_local = true is the same as SET LOCAL: the value disappears at COMMIT or ROLLBACK. That matters with a connection pool. In transaction-pooling mode, the next request on the same server connection might belong to a different tenant, and a plain SET would leak the previous tenant's value. Always use a transaction-local setting and clear nothing by hand.

Reading a setting that was never set raises an error in the policy above. If you prefer "no rows" over an error, use the two-argument form, which returns NULL when the setting is missing:

SQL
CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = nullif(current_setting('app.tenant_id', true), '')::integer);

current_setting('app.tenant_id', true) returns NULL or an empty string when unset, nullif turns an empty string into NULL, and tenant_id = NULL is never true, so the query returns nothing. Custom setting names need a dot, such as app.tenant_id, so they can't collide with built-in parameters.

Permissive and restrictive policies

Policies are permissive by default: if a table has several, a row is allowed when any of them says yes (they are combined with OR). That is handy for rules like "tenants see their own rows, and support staff see everything":

SQL
CREATE POLICY support_sees_all ON invoices
  TO support_role
  USING (true);

A restrictive policy (AS RESTRICTIVE) is combined with AND and must pass in addition to the permissive ones. Use it for rules that should never be overridden, such as hiding archived rows from everyone:

SQL
CREATE POLICY hide_archived ON invoices AS RESTRICTIVE
  USING (NOT archived);

Note that a table with only restrictive policies and no permissive one still returns nothing, because at least one permissive policy must allow the row first.

Who is exempt, and other bypass pitfalls

RLS is not a magic shield. These cases catch people out, so check each one before you rely on it:

  • Table owners bypass RLS unless you run ALTER TABLE invoices FORCE ROW LEVEL SECURITY. If your application connects as the owner (common in small projects), your policies do nothing.
  • Superusers and roles with BYPASSRLS always bypass it, even with FORCE. Never run the application as a superuser.
  • Views run with the view owner's rights, so a view over an RLS table can show every row to the people who query the view. Create the view WITH (security_invoker = true) (PostgreSQL 15 and later) so the caller's policies apply.
  • Foreign key checks and unique constraints ignore RLS. An insert can be rejected because a row exists that you can't see, which leaks that the row exists.
  • SECURITY DEFINER functions run as their owner and can bypass policies; review them like you'd review root access.
  • pg_dump needs to see all rows. By default it turns row security off and fails if you aren't allowed to bypass it. Back up with a role that has BYPASSRLS.
  • Policy expressions cost something. The condition runs for every candidate row, so index tenant_id, and write (SELECT current_setting(...)) or a stable helper function so PostgreSQL evaluates the setting once instead of once per row.

Also, test as the real role. In psql, SET ROLE app_user; switches to it, and SELECT current_user, row_security_active('invoices'); confirms the policies really apply. Testing as the owner or a superuser proves nothing.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. You run ALTER TABLE invoices ENABLE ROW LEVEL SECURITY and create no policies. What does a normal application role see?

    RLS is deny by default. With no policy that allows a row, queries return nothing and writes fail.
  2. 2. Which policy expression stops a tenant from changing the tenant_id of its own row to another tenant's id?

    WITH CHECK is tested against the new version of the row, so a changed tenant_id that no longer matches is rejected.
  3. 3. Why use set_config('app.tenant_id', '2', true) instead of a plain SET behind a transaction-pooling pooler?

    A session-level SET survives on the pooled server connection and could be seen by a different tenant's next request. A transaction-local setting vanishes at COMMIT or ROLLBACK.
  4. 4. Your app connects as the role that owns the invoices table, and tenants can see each other's data. What is the most likely reason?

    Owners are exempt by default. Connect as a separate role that doesn't own the table, and consider FORCE ROW LEVEL SECURITY.

Summary

  • ENABLE ROW LEVEL SECURITY turns a table to deny by default; CREATE POLICY opens specific rows.
  • USING filters existing rows; WITH CHECK validates rows being written.
  • Multi-tenant isolation: a tenant_id column, a non-owner app role, a per-transaction set_config(..., true), and a policy that compares them.
  • Permissive policies are OR-ed, restrictive policies are AND-ed on top.
  • Owners, superusers, BYPASSRLS roles, definer-rights views and SECURITY DEFINER functions can all sidestep policies; foreign keys and unique constraints can leak existence.
  • Test with the real role and index the columns your policies use.

Next: Authentication, TLS and Encryption.