Row-Level Security

Table of Contents

• When to Use This Reference
• Syntax / Mechanics
  • Mental Model — Five Rules
  • Decision Matrix
  • Enabling and Disabling RLS
  • CREATE POLICY Grammar
  • PERMISSIVE vs RESTRICTIVE
  • USING vs WITH CHECK
  • FOR Command Variants
  • TO Role Targeting
  • Multi-Policy Combination
  • Default-Deny Policy
  • BYPASSRLS and Owner Bypass
  • FORCE ROW LEVEL SECURITY
  • row_security GUC and pg_dump
  • Views and RLS
  • Replication and RLS
  • ALTER POLICY and DROP POLICY
  • Inspecting Policies
  • Per-Version Timeline
• Examples / Recipes
• Gotchas / Anti-patterns
• See Also
• Sources

When to Use This Reference

Reach for this file when you are:

• Building multi-tenant tables where every query must be scoped to the caller's tenant.
• Adding row-visibility filters that survive ad-hoc SQL access — not just application-layer checks.
• Mixing PERMISSIVE (additive) and RESTRICTIVE (subtractive) policies and need the combining rules.
• Writing an INSERT / UPDATE policy and deciding whether you want USING, WITH CHECK, or both.
• Investigating why a query returns fewer rows than expected — RLS silently drops invisible rows.
• Granting BYPASSRLS for a backup / monitoring / replication role, or wondering if a maintenance role inherits the exemption.
• Switching a view's RLS behavior from owner-context to caller-context via security_invoker (PG15+).
• Running pg_dump and seeing it fail with row security policy is enabled — row_security = off is not a bypass.
• Working with logical replication into a target with RLS enabled (PG15+ subscription owner rules).

[!NOTE] RLS has been remarkably stable across five PG majors PG14, PG16, and PG17 had zero RLS-related release-note items. PG15 had one narrow item — logical replication runs as the subscription owner and row-level security policies are not checked, so only superusers, BYPASSRLS roles, and table owners can replicate into RLS-protected tables.¹ PG18 added a --no-policies flag to pg_dump / pg_dumpall / pg_restore for stripping RLS from dumps². The core mechanics (policies, USING, WITH CHECK, PERMISSIVE/RESTRICTIVE, FORCE ROW LEVEL SECURITY, BYPASSRLS) are byte-identical across PG14 through PG18. If a tutorial claims a recent PG version "improved" RLS performance, the planner may have improved but the surface did not — verify against release notes directly.

This file is the SQL surface for RLS policies. Role attributes (including BYPASSRLS mechanics) live in 46-roles-privileges.md. View interaction details (security_invoker PG15+, security_barrier) live in 05-views.md. Authentication is 48-authentication-pg-hba.md. Audit logging is 51-pgaudit.md.

Syntax / Mechanics

Mental Model — Five Rules

1. RLS is per-table opt-in. A table has no policies by default; any role with table-level SELECT / INSERT / UPDATE / DELETE privilege gets every row.³ Policies only apply once you run ALTER TABLE ... ENABLE ROW LEVEL SECURITY. Without enabling, CREATE POLICY rows sit in pg_policy and do nothing — silently.

2. PERMISSIVE policies are OR-combined; RESTRICTIVE policies are AND-combined. Default is PERMISSIVE. Verbatim docs rule: "All permissive policies which are applicable to a given query will be combined together using the Boolean 'OR' operator ... All restrictive policies which are applicable to a given query will be combined together using the Boolean 'AND' operator."⁴ Practical consequence: you cannot deny access by adding more permissive policies — only by switching to restrictive or revoking grants.

3. USING controls visibility; WITH CHECK controls what writes are allowed. USING is checked against rows being read or matched (SELECT, UPDATE-WHERE, DELETE-WHERE). WITH CHECK is checked against rows after a write (INSERT new row, UPDATE post-modification row). Verbatim: "Existing table rows are checked against the expression specified in USING, while new rows that would be created via INSERT or UPDATE are checked against the expression specified in WITH CHECK."⁵ Rule of thumb: a violated USING silently drops the row from view; a violated WITH CHECK raises an error.

4. Table owners bypass their own table's RLS by default. Superusers and BYPASSRLS roles always bypass. Verbatim docs rule: "Superusers and roles with the BYPASSRLS attribute always bypass the row security system when accessing a table. Table owners normally bypass row security as well, though a table owner can choose to be subject to row security with ALTER TABLE ... FORCE ROW LEVEL SECURITY."³ If your application role owns the table, your policies never fire against it.

5. row_security = off does not bypass RLS — it raises an error if filtering would happen. Setting the GUC to off is a safety check, not a permission. Verbatim docs: "This does not in itself bypass row security; what it does is throw an error if any query's results would get filtered by a policy."³ This is what pg_dump does by default to refuse silently-incomplete dumps.

Decision Matrix

Three smell signals that you reached for RLS but should not have:

• The policy expression calls a slow function on every row. RLS evaluates per-row. Use a CHECK constraint or trigger if the expression is value-derivable.
• You added BYPASSRLS to every service account. That's a sign your tenancy model lives in the application layer; RLS is providing no value. Pick one — drop RLS or remove the bypasses.
• You disabled RLS to make migrations work. Migrations should run as a role that owns the table (and bypasses by default) or has BYPASSRLS. Disabling RLS during DDL is operationally fragile.

Enabling and Disabling RLS

ALTER TABLE events ENABLE ROW LEVEL SECURITY;
ALTER TABLE events DISABLE ROW LEVEL SECURITY;

Verbatim docs rule: "These forms control the application of row security policies belonging to the table. If enabled and no policies exist for the table, then a default-deny policy is applied. Note that policies can exist for a table even if row-level security is disabled. In this case, the policies will not be applied and the policies will be ignored."⁷

Two operational consequences:

1. Enabling without creating a policy locks everyone out except the owner and BYPASSRLS roles. The default policy is deny-all.
2. Disabling does not delete policies. They remain in pg_policy and re-activate the moment you re-enable. DROP POLICY is the only way to remove them.

ENABLE/DISABLE and FORCE/NO FORCE are independent toggles. The four states form a small matrix:

CREATE POLICY Grammar

Verbatim full synopsis from PG16 docs:⁸

CREATE POLICY name ON table_name
    [ AS { PERMISSIVE | RESTRICTIVE } ]
    [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
    [ TO { role_name | PUBLIC | CURRENT_ROLE | CURRENT_USER | SESSION_USER } [, ...] ]
    [ USING ( using_expression ) ]
    [ WITH CHECK ( check_expression ) ]

Verbatim docs description: "The CREATE POLICY command defines a new row-level security policy for a table. Note that row-level security must be enabled on the table (using ALTER TABLE ... ENABLE ROW LEVEL SECURITY) in order for created policies to be applied."⁵

Four rules to remember:

1. Policies are named. The name is unique per (table, command), not globally per database. Two policies named tenant_read on different tables are independent.
2. OR REPLACE is not supported on CREATE POLICY. You must DROP POLICY ... IF EXISTS then CREATE or use ALTER POLICY.
3. USING and WITH CHECK are independent expressions. Omit either where it does not apply (see below).
4. The policy expression cannot contain aggregates or window functions. Verbatim: "The conditional expression cannot contain any aggregate or window functions."⁹

PERMISSIVE vs RESTRICTIVE

PG10 added the distinction³; the file's PG16+ era uses both heavily.

PERMISSIVE (default): "All permissive policies which are applicable to a given query will be combined together using the Boolean 'OR' operator. By creating permissive policies, administrators can add to the set of records which can be accessed. Policies are permissive by default."⁴

RESTRICTIVE: "All restrictive policies which are applicable to a given query will be combined together using the Boolean 'AND' operator. By creating restrictive policies, administrators can reduce the set of records which can be accessed as all restrictive policies must be passed for each record."¹⁰

The critical combining rule, verbatim: "Note that there needs to be at least one permissive policy to grant access to records before restrictive policies can be usefully used to reduce that access. If only restrictive policies exist, then no records will be accessible. When a mix of permissive and restrictive policies are present, a record is only accessible if at least one of the permissive policies passes, in addition to all the restrictive policies."¹⁰

Mental model — a row is visible to the caller iff:

( P1_USING OR P2_USING OR ... )   [at least one permissive]
   AND
( R1_USING AND R2_USING AND ... ) [every restrictive]

If there are zero permissive policies, the OR-chain is empty (false) and no row is visible regardless of restrictive policies. Gotcha #2 expands on this.

USING vs WITH CHECK

The four-row asymmetric rule:

Verbatim docs:

• "A SELECT policy cannot have a WITH CHECK expression, as it only applies in cases where records are being retrieved from the relation."¹¹
• "An INSERT policy cannot have a USING expression, as it only applies in cases where records are being added to the relation."¹²
• "Since an UPDATE command involves pulling an existing record and replacing it with a new modified record, UPDATE policies accept both a USING expression and a WITH CHECK expression. The USING expression determines which records the UPDATE command will see to operate against, while the WITH CHECK expression defines which modified rows are allowed to be stored back into the relation."¹³
• "For a DELETE command, only rows that pass this policy will be seen by the DELETE command."¹⁴

Verbatim error/silent-drop distinction: "When a USING expression returns true for a given row then that row is visible to the user, while if false or null is returned then the row is not visible. Typically, no error occurs when a row is not visible ... When a WITH CHECK expression returns true for a row then that row is inserted or updated, while if false or null is returned then an error occurs."⁵

WITH CHECK is also evaluated against the post-UPDATE row, not the pre-UPDATE row: "the check_expression is evaluated against the proposed new contents of the row, not the original contents."⁹

FOR Command Variants

Five command keywords:

• FOR ALL — applies to every command. Verbatim: "If an ALL policy exists and more specific policies exist, then both the ALL policy and the more specific policy (or policies) will be applied. Additionally, ALL policies will be applied to both the selection side of a query and the modification side, using the USING expression for both cases if only a USING expression has been defined."¹⁵ Practically: an ALL policy with only USING makes that expression do double duty as the WITH CHECK for writes.
• FOR SELECT — USING only.
• FOR INSERT — WITH CHECK only.
• FOR UPDATE — both USING and WITH CHECK.
• FOR DELETE — USING only.

Two policies of different FOR types do not OR-combine in the way two FOR SELECT policies do — they apply to disjoint operations. A FOR SELECT policy and a FOR INSERT policy both being present means SELECT queries use the first, INSERT statements use the second, never both.

The exception is FOR ALL + a more-specific policy: both apply to the matched command, OR-combined as permissive.

TO Role Targeting

Verbatim docs default: "The default is PUBLIC, which will apply the policy to all roles."¹⁶

Policies can be scoped to one role, several roles (comma-separated), or PUBLIC. The role match uses direct + inherited membership: a role inherits a policy granted to any role it is a member of (under standard INHERIT). Useful patterns:

• Tenant-scoped policy TO PUBLIC (everyone is filtered) plus an override policy TO admins (admin role sees everything).
• Separate FOR SELECT TO reader and FOR ALL TO writer policies so reader cannot write.
• Audit-only role with policy TO auditor USING (true) to make the filtering explicit even though it returns all rows.

Multi-Policy Combination

Three operational rules pulling together the per-mode and PERMISSIVE/RESTRICTIVE rules:

1. Within one command type, all applicable permissive policies are OR-combined, and the result must AND with every applicable restrictive policy.
2. FOR ALL policies apply to every command AND are OR-combined with the command-specific permissive policies for that command.
3. A FOR UPDATE policy's USING matches existing rows; its WITH CHECK is also OR-combined separately for the post-UPDATE row. If a FOR ALL policy with only USING is in play, that USING becomes the implicit WITH CHECK for the update.

The decision is operational: write one permissive policy per tenant boundary (or per user-data-scope), then add RESTRICTIVE policies to enforce cross-cutting rules ("no row may have status='draft' if approval_level < 2").

Default-Deny Policy

If ROW LEVEL SECURITY is enabled and no policy applies to the current command + role, RLS denies access entirely. The verbatim rule: "If enabled and no policies exist for the table, then a default-deny policy is applied."⁷

Three operational situations where this surfaces:

• You enabled RLS before creating policies. All non-bypass non-owner queries return zero rows / fail to write until the first policy is created.
• Your policy was FOR SELECT only. INSERT/UPDATE/DELETE are denied by default — you need explicit policies for write operations.
• You dropped the last policy. From DROP POLICY docs verbatim: "Note that if the last policy is removed for a table and the table still has row-level security enabled via ALTER TABLE, then the default-deny policy will be used."¹⁷ Verify the last-policy state before dropping in production.

BYPASSRLS and Owner Bypass

Three classes of bypass:

Verbatim BYPASSRLS docs: "BYPASSRLS / NOBYPASSRLS — These clauses determine whether a role bypasses every row-level security (RLS) policy. NOBYPASSRLS is the default. Only superuser roles or roles with BYPASSRLS can specify BYPASSRLS."¹⁸

Three operational consequences:

1. BYPASSRLS is orthogonal to grants. A BYPASSRLS role still needs SELECT on the table to read it. The attribute only skips the policy filter — it does not grant access.
2. BYPASSRLS must be granted by a superuser or another BYPASSRLS role. Pre-PG16, a CREATEROLE-having role could grant BYPASSRLS; PG16 narrowed this — the granting role must have BYPASSRLS itself.¹⁹ See 46-roles-privileges.md gotcha #8.
3. Owner bypass is invisible. There's no error or warning when an owner reads through RLS — they just see every row. Tests that pass as the table owner may fail as a non-owner.

Verbatim docs on pg_dump + BYPASSRLS: "Note that pg_dump will set row_security to OFF by default, to ensure all contents of a table are dumped out. If the user running pg_dump does not have appropriate permissions, an error will be returned. However, superusers and the owner of the table being dumped always bypass RLS."¹⁸

FORCE ROW LEVEL SECURITY

ALTER TABLE events FORCE ROW LEVEL SECURITY;
ALTER TABLE events NO FORCE ROW LEVEL SECURITY;

Verbatim docs: "These forms control the application of row security policies belonging to the table when the user is the table owner. If enabled, row-level security policies will be applied when the user is the table owner. If disabled (the default) then row-level security will not be applied when the user is the table owner."⁷

When to reach for FORCE:

• The application user that runs DML is the table owner (common in single-role app deployments). Without FORCE, RLS does nothing.
• You want defense-in-depth and don't trust the "owner won't run risky SQL" assumption.

When not to reach for FORCE:

• Operational scripts running as the owner (REINDEX, pg_repack, partition rotation) — they'll be filtered by RLS, which is rarely what you want. Either separate the role or use BYPASSRLS for that role.

row_security GUC and pg_dump

Verbatim docs: "In some contexts it is important to be sure that row security is not being applied. For example, when taking a backup, it could be disastrous if row security silently caused some rows to be omitted from the backup. In such a situation, you can set the row_security configuration parameter to off. This does not in itself bypass row security; what it does is throw an error if any query's results would get filtered by a policy."³

The three values:

Two operational consequences:

1. pg_dump sets row_security = off automatically. If the dumping role can bypass RLS (superuser, table owner, BYPASSRLS), the dump completes. Otherwise the dump errors out. This is intentional: better to fail loudly than to silently dump a partial table.
2. PG18 added --no-policies to pg_dump, pg_dumpall, and pg_restore. Verbatim release-note: "Add option --no-policies to disable row level security policy processing in pg_dump, pg_dumpall, pg_restore."² Use this when migrating an RLS-protected schema to a target where RLS will be configured separately (e.g., during dev environment refresh).

Views and RLS

Two view security attributes interact with RLS. Verbatim docs on the security_barrier interaction with RLS: "When it is necessary for a view to provide row-level security, the security_barrier attribute should be applied to the view. This prevents maliciously-chosen functions and operators from being passed values from rows until after the view has done its work."²⁰

PG15+ added security_invoker. Verbatim docs: "If any of the underlying base relations has row-level security enabled, then by default, the row-level security policies of the view owner are applied, and access to any additional relations referred to by those policies is determined by the permissions of the view owner. However, if the view has security_invoker set to true, then the policies and permissions of the invoking user are used instead, as if the base relations had been referenced directly from the query using the view."⁶

Three-row decision table:

Note that views inherit policies from their base tables, not from themselves — you cannot CREATE POLICY on a view. The base table is what gets enabled, and the view either evaluates as the owner (default) or the caller (security_invoker = true).

PG15 cross-reference: 05-views.md covers the full grammar and rule mechanics.

Replication and RLS

Verbatim PG15 release note: "Allow logical replication to run as the owner of the subscription (Mark Dilger). Because row-level security policies are not checked, only superusers, roles with bypassrls, and table owners can replicate into tables with row-level security policies."¹

Three operational consequences for logical replication:

1. The subscription owner must be BYPASSRLS, the table owner, or superuser. Otherwise apply fails on RLS-protected target tables.
2. Policies on the source publisher are not replicated. Logical replication ships row changes, not DDL. If the target schema has RLS configured, configure it separately on the target.
3. For physical streaming replication, RLS is irrelevant on the standby in normal operation — the standby is read-only and replays bytes verbatim; queries on the standby still apply RLS as on the primary.

ALTER POLICY and DROP POLICY

Verbatim ALTER POLICY synopsis:²¹

ALTER POLICY name ON table_name RENAME TO new_name;

ALTER POLICY name ON table_name
    [ TO { role_name | PUBLIC | CURRENT_ROLE | CURRENT_USER | SESSION_USER } [, ...] ]
    [ USING ( using_expression ) ]
    [ WITH CHECK ( check_expression ) ];

Verbatim: "ALTER POLICY only allows the set of roles to which the policy applies and the USING and WITH CHECK expressions to be modified. To change other properties of a policy, such as the command to which it applies or whether it is permissive or restrictive, the policy must be dropped and recreated."²²

Practical consequence: changing FOR SELECT → FOR ALL or PERMISSIVE → RESTRICTIVE requires a DROP POLICY + CREATE POLICY sequence. Plan for the brief window where the policy doesn't exist — wrap in a transaction or accept that the default-deny may apply if the table has no other policies covering that command.

DROP POLICY synopsis: DROP POLICY [ IF EXISTS ] name ON table_name [ CASCADE | RESTRICT ];¹⁷

Inspecting Policies

The pg_policies view exposes all policies in a queryable form:

SELECT schemaname, tablename, policyname, permissive, roles, cmd, qual, with_check
FROM pg_policies
WHERE schemaname = 'public'
ORDER BY tablename, policyname;

The qual column is the USING expression text; with_check is the WITH CHECK expression text. The cmd column is SELECT / INSERT / UPDATE / DELETE / ALL.

pg_class.relrowsecurity is true if RLS is ENABLEd. pg_class.relforcerowsecurity is true if FORCE ROW LEVEL SECURITY is set. Useful audit:

SELECT n.nspname, c.relname, c.relrowsecurity AS rls_enabled, c.relforcerowsecurity AS rls_forced
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'p')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY rls_enabled DESC, n.nspname, c.relname;

Per-Version Timeline

Examples / Recipes

Recipe 1 — Multi-tenant baseline with session GUC

The canonical multi-tenant RLS pattern: every row carries tenant_id; the app sets a session GUC at connection time; one policy filters every read and write.

CREATE TABLE events (
    id          bigserial PRIMARY KEY,
    tenant_id   int NOT NULL,
    payload     jsonb,
    created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX ON events (tenant_id, created_at DESC);

ALTER TABLE events ENABLE ROW LEVEL SECURITY;

-- Session GUC pattern — app sets this on connection checkout.
CREATE POLICY events_tenant_isolation
    ON events
    FOR ALL
    TO PUBLIC
    USING  (tenant_id = current_setting('app.tenant_id', true)::int)
    WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::int);

-- On each new connection, the app runs:
-- SET app.tenant_id = '42';

The true second argument to current_setting returns NULL instead of erroring if the GUC is unset — important because false OR NULL = NULL, which a USING clause treats as "row not visible." So an unset GUC silently denies all rows rather than crashing — much safer than the alternative.

If the table owner runs the app, add FORCE so the owner is subject to RLS:

ALTER TABLE events FORCE ROW LEVEL SECURITY;

Recipe 2 — Layered permissive policies (tenant + admin override)

Two permissive policies OR-combine. A regular caller sees only their tenant's rows; an admin role sees everything:

CREATE POLICY events_tenant_isolation
    ON events
    FOR ALL
    TO PUBLIC
    USING (tenant_id = current_setting('app.tenant_id', true)::int)
    WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::int);

CREATE POLICY events_admin_full_access
    ON events
    FOR ALL
    TO admin_role
    USING  (true)
    WITH CHECK (true);

When a member of admin_role runs SELECT * FROM events, the combined permissive USING is (tenant_id = X) OR true = true. The admin sees everything. A non-admin gets only the tenant filter.

The TO admin_role clause means policy 2 is only applicable to admin role members — non-admins don't even see it in the OR-combination.

Recipe 3 — Restrictive policy enforcing a cross-cutting rule

A RESTRICTIVE policy AND-combines with whatever permissive policies allowed the row through. Use it to enforce a global constraint that should never be bypassed:

-- Permissive: each user sees their own org's data.
CREATE POLICY org_isolation
    ON contracts
    FOR ALL
    USING  (org_id = current_setting('app.org_id', true)::int)
    WITH CHECK (org_id = current_setting('app.org_id', true)::int);

-- Restrictive: even within your org, draft contracts are hidden from non-managers.
CREATE POLICY hide_drafts_from_non_managers
    ON contracts
    AS RESTRICTIVE
    FOR SELECT
    USING (
        status <> 'draft'
        OR EXISTS (
            SELECT 1 FROM org_members
            WHERE user_id = current_setting('app.user_id', true)::int
              AND role = 'manager'
        )
    );

Drafts are gated AND-combined: the row must pass both the org-isolation permissive policy AND the draft-visibility restrictive policy. Adding more permissive policies later cannot weaken the restrictive policy — exactly what "restrictive" means.

Recipe 4 — Soft-delete with RLS

Hide soft-deleted rows from normal queries; allow an admin role to see them. The visibility rule is row-data-derivable:

ALTER TABLE accounts ADD COLUMN deleted_at timestamptz;
ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

CREATE POLICY accounts_hide_deleted
    ON accounts
    FOR SELECT
    TO PUBLIC
    USING (deleted_at IS NULL);

CREATE POLICY accounts_admin_sees_deleted
    ON accounts
    FOR SELECT
    TO admin_role
    USING (true);

-- Application performs UPDATE accounts SET deleted_at = now() WHERE id = $1;
-- (Need an UPDATE policy for that — separate.)
CREATE POLICY accounts_owner_can_update
    ON accounts
    FOR UPDATE
    TO PUBLIC
    USING  (owner_id = current_setting('app.user_id', true)::int)
    WITH CHECK (owner_id = current_setting('app.user_id', true)::int);

This approach survives ad-hoc SQL access through any tool — the policy is enforced regardless of query source.

Recipe 5 — Per-user table with current_user

If your tenancy boundary is the database user itself (rare but legitimate for internal tools):

CREATE TABLE personal_notes (
    id     bigserial PRIMARY KEY,
    owner  name NOT NULL DEFAULT current_user,
    note   text
);

ALTER TABLE personal_notes ENABLE ROW LEVEL SECURITY;

CREATE POLICY notes_owner_only
    ON personal_notes
    FOR ALL
    TO PUBLIC
    USING  (owner = current_user)
    WITH CHECK (owner = current_user);

current_user returns the effective role after SET ROLE, so this composes with role-based access patterns. session_user would return the originally-authenticated role.

Recipe 6 — INSERT-only policy with WITH CHECK

Audit-log pattern: anyone can insert, no one can update or delete, only auditors can read:

CREATE TABLE audit_log (
    id        bigserial PRIMARY KEY,
    actor     name NOT NULL DEFAULT current_user,
    event     jsonb NOT NULL,
    occurred  timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE audit_log ENABLE ROW LEVEL SECURITY;

-- Anyone can insert (WITH CHECK forces actor to be current_user — prevents impersonation).
CREATE POLICY audit_log_insert_anyone
    ON audit_log
    FOR INSERT
    TO PUBLIC
    WITH CHECK (actor = current_user);

-- Only auditors can read.
CREATE POLICY audit_log_read_auditor
    ON audit_log
    FOR SELECT
    TO auditor_role
    USING (true);

-- No UPDATE or DELETE policies at all — default-deny applies.

Note: the lack of UPDATE/DELETE policies means even the auditor cannot delete rows. That's intentional for audit logs. If you need cleanup, run as the table owner (default bypass) or BYPASSRLS via a separate retention job.

Recipe 7 — Force RLS on owner-app pattern

Common deployment: the application connects as app_user, which is also the table owner. Without FORCE, RLS doesn't apply to the app at all:

ALTER TABLE events OWNER TO app_user;
ALTER TABLE events ENABLE ROW LEVEL SECURITY;
ALTER TABLE events FORCE ROW LEVEL SECURITY;  -- <-- critical

Without FORCE, the policies are dormant for the owning role. Verify:

SELECT relname, relrowsecurity, relforcerowsecurity
FROM pg_class
WHERE relname = 'events';
-- expect: rls_enabled=t, rls_forced=t

Recipe 8 — Test policies via SET ROLE inside a transaction

Don't deploy RLS without testing what each role sees:

BEGIN;
SET LOCAL ROLE alice;
SELECT count(*) FROM events;   -- what does alice see?
RESET ROLE;
SET LOCAL ROLE bob;
SELECT count(*) FROM events;   -- what does bob see?
ROLLBACK;

SET LOCAL ensures the role change doesn't escape the transaction. BEGIN ... ROLLBACK ensures no test data leaks.

For policies depending on a session GUC:

BEGIN;
SET LOCAL app.tenant_id = '42';
SET LOCAL ROLE webapp;
SELECT count(*) FROM events;
ROLLBACK;

Recipe 9 — Bypass RLS for a backup or maintenance job

Don't grant SUPERUSER to backup roles. Use BYPASSRLS plus the minimum table grants:

CREATE ROLE backup_role WITH LOGIN BYPASSRLS PASSWORD '...';
GRANT pg_read_all_data TO backup_role;
-- backup_role can now read every row of every table for pg_dump.

pg_read_all_data (PG14+) gives implicit SELECT on all tables; BYPASSRLS ensures policies don't filter. The combination is the right pattern for unattended backup roles. Cross-ref 46-roles-privileges.md recipe 8.

Recipe 10 — Use row_security = off to detect filtered data

Set the session GUC to off to verify your query sees every row:

SET row_security = off;
SELECT count(*) FROM accounts;
-- If RLS would filter, this raises:
-- ERROR: query would be affected by row-level security policy for table "accounts"

The error signals that RLS would filter; switch the role or get BYPASSRLS to access. Don't catch the error and retry with on — that defeats the safety check.

Recipe 11 — Audit query for tables missing RLS

Find tables that should be RLS-protected but aren't. This is a policy-and-process question, but the catalog query helps:

SELECT n.nspname AS schema,
       c.relname AS table,
       c.relrowsecurity AS rls_enabled,
       c.relforcerowsecurity AS rls_forced,
       (SELECT count(*) FROM pg_policy p WHERE p.polrelid = c.oid) AS policy_count
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'p')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
ORDER BY rls_enabled, n.nspname, c.relname;

Three rows are suspicious:

• rls_enabled = true with policy_count = 0 — table is locked out (default-deny applies).
• rls_enabled = false with policy_count > 0 — dormant policies will activate the moment someone enables RLS.
• Tables in your tenant-data schemas with rls_enabled = false — should probably have policies.

Recipe 12 — Inspect existing policies with pg_policies

SELECT schemaname, tablename, policyname, permissive,
       array_to_string(roles, ', ') AS roles,
       cmd,
       qual AS using_expr,
       with_check
FROM pg_policies
WHERE schemaname = 'public'
ORDER BY tablename, policyname;

The view is a read-only convenience over pg_policy joined with pg_class and pg_namespace. Use it to dump policy state for review, code review diffs, or migration scripts.

Recipe 13 — security_invoker view as caller-context aperture (PG15+)

A view over an RLS table normally evaluates RLS as the view owner. PG15+ security_invoker flips that:

CREATE VIEW my_events
    WITH (security_invoker = true, security_barrier = true)
    AS SELECT id, payload, created_at FROM events;

A caller alice querying my_events triggers RLS evaluation as alice, not as the view owner — exactly as if alice had run SELECT ... FROM events directly. Combined with security_barrier, leaky operators can't see pre-filter rows. Cross-ref 05-views.md.

Recipe 14 — Migrate a dump across environments using --no-policies (PG18+)

PG18+ allows stripping RLS from dump and restore. Useful for dev-environment refreshes where policies will be re-applied separately by migrations:

# PG18+
pg_dump --no-policies -Fc -d prod_db -f prod.dump
pg_restore --no-policies -d dev_db prod.dump

Pre-PG18 you must edit the dump SQL by hand or restore everything and DROP POLICY post-hoc. Cross-ref 83-backup-pg-dump.md.

Gotchas / Anti-patterns

1. ENABLE ROW LEVEL SECURITY without any policy = default-deny. All non-bypass non-owner queries return zero rows. Always create at least one policy before enabling.⁷

2. Only RESTRICTIVE policies = no rows visible. Verbatim: "If only restrictive policies exist, then no records will be accessible."¹⁰ You need at least one PERMISSIVE policy to grant access; restrictive policies AND-combine on top.

3. Table owner bypasses RLS by default. If your app role owns the table, your policies are dormant for that role. Add FORCE ROW LEVEL SECURITY to subject owners to policies.⁷

4. USING returning NULL silently drops the row. Verbatim: "if false or null is returned then the row is not visible."⁵ Use current_setting('app.x', true) (missing-OK form) and pair with IS NOT NULL checks if the GUC unset case should be visible to admins. Otherwise unset GUC = no rows visible.

5. WITH CHECK raises an error, USING doesn't. A USING violation silently filters; a WITH CHECK violation errors with new row violates row-level security policy. Asymmetry caught in USING vs WITH CHECK section.

6. FOR ALL policy with only USING makes that expression do double duty as WITH CHECK. Verbatim: "ALL policies will be applied to both the selection side of a query and the modification side, using the USING expression for both cases if only a USING expression has been defined."¹⁵ Often what you want for tenant isolation; surprising if you intended only to filter reads.

7. You cannot OR REPLACE a policy. CREATE POLICY is the only form. ALTER POLICY can change the role list, USING, and WITH CHECK but not the command or permissive/restrictive status — those require DROP POLICY + CREATE POLICY.²²

8. Policy expressions cannot contain aggregates or window functions. Verbatim docs.⁹ If your access rule needs a group-by, push it into a SECURITY DEFINER function (cross-ref 06-functions.md) and call from the policy.

9. Logical replication into an RLS table requires the subscription role to bypass. Verbatim PG15 release note: only superusers, BYPASSRLS roles, and table owners can replicate into RLS-protected tables.¹ A plain replication role gets apply errors. Cross-ref 74-logical-replication.md.

10. RLS policies cost CPU per row. A SELECT on a 10M-row table now evaluates the policy expression 10M times. If the expression calls a slow function or does a subquery without an index, this is an O(N) tax on every query. Profile with EXPLAIN (ANALYZE, BUFFERS).

11. The planner may not push policy filters down through joins. Joins on RLS-protected tables can prevent index use because the policy must apply before the join sees the row. Test plans before and after enabling RLS; cross-ref 56-explain.md.

12. pg_dump fails on RLS tables for non-bypass roles. Default row_security = off raises an error if filtering would happen. Solution: dump as a BYPASSRLS role, the owner, or superuser.¹⁸

13. row_security = on does not bypass RLS — it's the default, the one that applies policies. off errors on filtering. There is no "bypass via GUC" — bypass requires the role attribute or ownership.³

14. security_invoker is PG15+. Pre-PG15 views always evaluate RLS as the view owner. If you need caller-context RLS on PG14, you must restructure: either grant the underlying table to all callers and let them query directly, or accept that policies will evaluate as the view owner.⁶

15. Disabling RLS does not delete policies. They remain in pg_policy and re-activate on ENABLE. Always DROP POLICY if you want them gone permanently.⁷

16. Dropping the last policy with RLS still enabled triggers default-deny. Verbatim: "Note that if the last policy is removed for a table and the table still has row-level security enabled via ALTER TABLE, then the default-deny policy will be used."¹⁷ Combine DROP POLICY with ALTER TABLE ... DISABLE ROW LEVEL SECURITY in the same transaction if you intend to remove all enforcement.

17. A policy's WITH CHECK is evaluated against the post-UPDATE row, not the pre-UPDATE. Verbatim.⁹ An UPDATE that moves a row out of the caller's tenant must satisfy both the pre-update USING and the post-update WITH CHECK — usually meaning the caller can't move rows between tenants at all.

18. current_setting('app.x', true) returns NULL on missing; current_setting('app.x') raises an error. The two-arg form is almost always correct in RLS expressions — you do not want connection setup errors crashing every query.

19. SET ROLE doesn't re-apply per-role GUCs. If your tenant_id is configured via ALTER ROLE webapp SET app.tenant_id = '42', then SET ROLE webapp does not trigger that GUC to be set. Per-role GUCs only apply at session start. Cross-ref 46-roles-privileges.md gotcha #6.

20. pg_dump --no-policies is PG18+ only. Pre-PG18, you must edit the dump SQL post-hoc to strip CREATE POLICY statements. There's no flag.²

21. BYPASSRLS does not grant SUPERUSER powers. It bypasses RLS only. The role still needs SELECT/INSERT/UPDATE/DELETE grants on the table.¹⁸

22. BYPASSRLS grant requires BYPASSRLS on the grantor (PG16+). Previously, any CREATEROLE-having role could grant it; PG16 narrowed this.¹⁹ Cross-ref 46-roles-privileges.md gotcha #8.

23. An RLS policy that references another table needs SELECT on that table for the role evaluating the policy. In default views, that's the view owner. In security_invoker views or direct table access, that's the caller. A subquery in USING (...) will fail if the caller lacks SELECT on the referenced table.

See Also

• 05-views.md — security_invoker (PG15+), security_barrier, INSTEAD OF triggers, and the full view-side RLS interaction.
• 46-roles-privileges.md — BYPASSRLS role attribute, pg_read_all_data, predefined roles, grant mechanics, PG16 CREATEROLE narrowing.
• 48-authentication-pg-hba.md — which roles can connect from where; RLS only kicks in after auth succeeds.
• 51-pgaudit.md — audit log integration; commonly paired with RLS for compliance.
• 56-explain.md — reading plans with RLS filters; the EXPLAIN plan shows policy Filter clauses.
• 64-system-catalogs.md — pg_class.relrowsecurity, pg_class.relforcerowsecurity, pg_policy, pg_policies view.
• 74-logical-replication.md — subscription owner + RLS interaction; PG15 release-note quote.
• 83-backup-pg-dump.md — pg_dump and row_security behavior; PG18 --no-policies.

Sources

Additional release notes consulted for absence-of-changes confirmation:

• PostgreSQL 14.0 release notes — zero RLS-related items. https://www.postgresql.org/docs/release/14.0/
• PostgreSQL 17.0 release notes — zero RLS-related items. https://www.postgresql.org/docs/release/17.0/