Row-Level Security Policies, FORCE RLS, Security Barrier Views, and Tenant Isolation

Learning outcomes

ServiceHub becomes multi-tenant: tenant A and tenant B share one table, but each application identity must see and modify only its own rows. Ordinary table GRANT cannot express “SELECT only rows whose tenant identity is yours.” PostgreSQL Row-Level Security (RLS) adds policy predicates to normal table access.

1. Create tenant identities and the shared table

DROP VIEW IF EXISTS app.ch20_open_ticket;DROP TABLE IF EXISTS app.ch20_ticket CASCADE;DROP ROLE IF EXISTS ch20_tenant_a;DROP ROLE IF EXISTS ch20_tenant_b;CREATE ROLE ch20_tenant_a LOGIN;CREATE ROLE ch20_tenant_b LOGIN;-- No passwords are set in this RLS lab; SET ROLE is used for deterministic tests.SET ROLE servicehub_owner;CREATE TABLE app.ch20_ticket (  ticket_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,  tenant_role name NOT NULL,  subject text NOT NULL,  status text NOT NULL CHECK (status IN ('open','closed','sealed')),  created_at timestamptz NOT NULL DEFAULT clock_timestamp());INSERT INTO app.ch20_ticket(tenant_role,subject,status) VALUES('ch20_tenant_a','Pump alarm','open'),('ch20_tenant_a','Billing correction','sealed'),('ch20_tenant_b','Valve inspection','open'),('ch20_tenant_b','Motor report','closed');RESET ROLE;GRANT USAGE ON SCHEMA app TO ch20_tenant_a, ch20_tenant_b;GRANT SELECT, INSERT, UPDATE, DELETEON app.ch20_ticketTO ch20_tenant_a, ch20_tenant_b;GRANT USAGE, SELECTON SEQUENCE app.ch20_ticket_ticket_id_seqTO ch20_tenant_a, ch20_tenant_b;

The tenant roles have LOGIN so Lesson 5 can discuss session_user as a direct-login identity. No password is assigned; all mandatory RLS tests use SET ROLE from an administrator session.

2. Enabling RLS without a policy is default deny

SET ROLE servicehub_owner;ALTER TABLE app.ch20_ticket ENABLE ROW LEVEL SECURITY;RESET ROLE;SET ROLE ch20_tenant_a;SELECT * FROM app.ch20_ticket;-- Expected: zero rows, because RLS is enabled and no policy permits rows.RESET ROLE;

RLS does not automatically infer tenancy from a column name. With row security enabled and no applicable policy, normal access is denied by a default-deny policy.

3. USING controls visible/targetable rows; WITH CHECK controls new row state

SET ROLE servicehub_owner;CREATE POLICY ch20_tenant_isolationON app.ch20_ticketAS PERMISSIVEFOR ALLTO ch20_tenant_a, ch20_tenant_bUSING (tenant_role = current_user)WITH CHECK (tenant_role = current_user);RESET ROLE;

For SELECT, USING is a visibility predicate. For UPDATE/DELETE, it also determines which existing rows can be targeted. WITH CHECK validates rows newly inserted or the new version produced by an update.

4. Tenant A positive/negative tests

SET ROLE ch20_tenant_a;SELECT ticket_id, tenant_role, subject, statusFROM app.ch20_ticketORDER BY ticket_id;INSERT INTO app.ch20_ticket(tenant_role,subject,status)VALUES ('ch20_tenant_a','A new tenant-A ticket','open');INSERT INTO app.ch20_ticket(tenant_role,subject,status)VALUES ('ch20_tenant_b','Cross-tenant injection','open');-- Expected: new row violates row-level security policy.RESET ROLE;

SET ROLE ch20_tenant_b;SELECT ticket_id, tenant_role, subject, statusFROM app.ch20_ticketORDER BY ticket_id;UPDATE app.ch20_ticketSET subject = 'stolen'WHERE tenant_role = 'ch20_tenant_a';-- Expected: UPDATE 0, because tenant-A rows are not visible/targetable.RESET ROLE;

The zero-row update is an important RLS behavior: invisible rows act as though they are not available to the command. Applications should not disclose whether hidden rows exist through different error messages.

5. Restrictive policies compose with permissive policies

Multiple permissive policies are ORed; restrictive policies are ANDed with the permissive result. ServiceHub can allow tenant rows but additionally hide sealed tickets from normal tenant reads.

SET ROLE servicehub_owner;CREATE POLICY ch20_hide_sealedON app.ch20_ticketAS RESTRICTIVEFOR SELECTTO ch20_tenant_a, ch20_tenant_bUSING (status <> 'sealed');RESET ROLE;SET ROLE ch20_tenant_a;SELECT ticket_id, subject, statusFROM app.ch20_ticketORDER BY ticket_id;RESET ROLE;

Tenant A's sealed ticket now disappears even though the tenant-isolation policy permits it. At least one permissive policy still has to grant the row before restrictive policies can further narrow it.

6. Owners normally bypass RLS; FORCE changes that

SET ROLE servicehub_owner;SELECT current_user, count(*) AS owner_visible_rowsFROM app.ch20_ticket;RESET ROLE;

The table owner normally bypasses RLS and can see all rows. Superusers and roles with BYPASSRLS always bypass RLS.

SET ROLE servicehub_owner;ALTER TABLE app.ch20_ticket FORCE ROW LEVEL SECURITY;SELECT current_user, count(*) AS owner_visible_rowsFROM app.ch20_ticket;-- The owner has no matching tenant policy and therefore sees no tenant rows.RESET ROLE;

FORCE ROW LEVEL SECURITY is useful when owner-executed code should obey the same policy model. It does not override superuser or BYPASSRLS bypass.

7. Do not use a caller-controlled custom GUC as your only tenant identity

SET ROLE ch20_tenant_a;SET app.tenant_id = 'tenant-b';SELECT current_setting('app.tenant_id');RESET ROLE;

Ordinary custom configuration parameters are not inherently trusted identity assertions. If a policy simply compares tenant_id = current_setting('app.tenant_id') and the caller may freely set that value, the caller can impersonate another tenant. Bind policy identity to authenticated roles or to a carefully hardened security-definer mapping that the caller cannot forge.

8. Security-invoker + security-barrier view

SET ROLE servicehub_owner;CREATE OR REPLACE VIEW app.ch20_open_ticketWITH (  security_barrier = true,  security_invoker = true)ASSELECT ticket_id, tenant_role, subject, status, created_atFROM app.ch20_ticketWHERE status = 'open';RESET ROLE;GRANT SELECT ON app.ch20_open_ticketTO ch20_tenant_a, ch20_tenant_b;

SET ROLE ch20_tenant_b;SELECT *FROM app.ch20_open_ticketORDER BY ticket_id;RESET ROLE;

security_invoker=true makes underlying table permissions/RLS use the invoking role rather than the view owner. security_barrier=true limits unsafe predicate reordering across the view boundary; leakproof operators/functions may still be evaluated earlier because they promise not to leak argument information.

9. RLS is not the only authorization layer

RLS protects row access through PostgreSQL queries. It does not replace application entitlement checks, rate limits, workflow state rules, API object validation, secure logging, or protection from a compromised BYPASSRLS/superuser identity. TRUNCATE and some whole-table operations are not row-filtered by RLS.

10. Policy/catalog evidence

SELECT c.oid::regclass AS relation,       c.relrowsecurity,       c.relforcerowsecurityFROM pg_class AS cWHERE c.oid = 'app.ch20_ticket'::regclass;SELECT schemaname, tablename, policyname,       permissive, roles, cmd, qual, with_checkFROM pg_policiesWHERE schemaname = 'app'  AND tablename = 'ch20_ticket'ORDER BY policyname;

Authoritative references

Authentication, TLS, authorization, and policy behavior is security- and version-sensitive. The lesson uses these PostgreSQL 18 primary sources.

• PostgreSQL 18 — Row Security Policies
• PostgreSQL 18 — CREATE POLICY
• PostgreSQL 18 — Rules and Privileges / Security Barrier Views
• PostgreSQL 18 — CREATE VIEW