skip to content

Row-Level Policies

PostgreSQL row-level security attaches USING and WITH CHECK policies to a table, OR-ing permissive and AND-ing restrictive ones. Interviewers probe owner bypass, FORCE and per-transaction tenant ids.

part ofPostgreSQLread it →
on this pageshow

explore

questions

5

In PostgreSQL, which roles bypass a table's row-level security policies once it is enabled, and what does FORCE ROW LEVEL SECURITY change?

level: middleimportance: must knowfreq 62%

answer

  1. three exemptions, one removable
  2. a role attribute, not a grant
  3. ownership through membership counts
  4. FORCE binds only the owner
  5. row_security errors, never bypasses

basics

~20 s

Superusers and roles with the BYPASSRLS attribute always bypass row security, and the table owner bypasses it by default. ALTER TABLE ... FORCE ROW LEVEL SECURITY subjects the owner to its policies; superusers and BYPASSRLS roles still bypass.

solid answer

~50 s

The server decides in this order. A superuser or a `BYPASSRLS` role gets no policies. The table owner gets no policies unless `relforcerowsecurity` is set. Everyone else gets the policies. "Owner" includes any role that inherits the owning role's privileges through membership, so an application role granted the owner role quietly bypasses RLS. `BYPASSRLS` is a role attribute (`pg_roles.rolbypassrls`) that only a superuser or a role already holding it can grant. It is checked on the current role itself, not inherited from roles it belongs to. `FORCE ROW LEVEL SECURITY` makes the owner subject to the policies, though referential-integrity checks still bypass them. Predefined roles such as `pg_read_all_data` do not bypass RLS. Finally, `row_security = off` never bypasses anything: for a role the policies apply to, it turns filtering into an error, which is why `pg_dump` sets it.

code

sql · 11 lines
sql
SELECT c.relname, c.relrowsecurity, c.relforcerowsecurity,
       pg_get_userbyid(c.relowner) AS owner
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relname IN ('projects', 'tasks');

SELECT rolname, rolsuper, rolbypassrls
FROM pg_roles
WHERE rolsuper OR rolbypassrls;

SELECT pg_has_role('app_rw', 'project_owner', 'USAGE') AS app_rw_acts_as_owner;

go deeper

for a junior

Remember the three exemptions: superuser, BYPASSRLS, and the table owner. FORCE removes only the owner's.

for a middle

Walk through the server's decision order and explain why membership in the owning role counts as ownership while BYPASSRLS is never inherited.

for a senior

Show how you would audit a live system: relforcerowsecurity, rolbypassrls, inherited ownership via pg_has_role, and what pg_dump does under row_security off.

for a principal

Decide who may hold BYPASSRLS or table ownership at all, and how that boundary is reviewed as roles and migrations change over time.

## The decision the server makes for each table For every table a query touches, PostgreSQL (`check_enable_rls` in the source) works through these steps: 1. If `pg_class.relrowsecurity` is false, row security does not apply at all. 2. If the current role is a **superuser** or has **`BYPASSRLS`**, the policies are skipped. 3. If the current role counts as the table's **owner** and `pg_class.relforcerowsecurity` is false, the policies are skipped. 4. Otherwise the policies apply. If the session has `row_security = off`, the query fails instead of being filtered. The "current role" is whoever runs the query, or the view owner when the table is reached through an ordinary view (the `security_invoker` option is the views leaf's subject). ## Superusers and BYPASSRLS - A **superuser** passes every permission check, so no `ENABLE` or `FORCE` setting affects one. - **`BYPASSRLS`** is a role attribute, like `LOGIN` or `CREATEDB`, and `NOBYPASSRLS` is the default. Only a superuser or a role that already has `BYPASSRLS` can grant it. `CREATEROLE` explicitly cannot. - The server reads the attribute from the current role's own `rolbypassrls` flag. Being a member of a `BYPASSRLS` role does not pass the bypass on. You would have to `SET ROLE` to that role. - The predefined role `pg_read_all_data` acts like `SELECT` on every table, but it does **not** bypass row security. The manual suggests also setting `BYPASSRLS` on the roles that hold it if full reads are intended. ## The owner, and who counts as the owner The ownership test checks role *privileges*, not just the role name. A role that inherits the owning role's privileges through membership counts as the owner. This is how a multi-tenant project-management app ends up with no isolation even though its policies look right: - `project_owner` owns `projects` and `tasks` and runs the migrations. - `app_rw`, the runtime role, was granted `project_owner` (with `INHERIT`) to save writing grants. - Without `FORCE`, `app_rw` passes step 3 and reads every tenant's rows. | Role | `ENABLE` only | `ENABLE` + `FORCE` | |---|---|---| | superuser | bypasses | bypasses | | role with `BYPASSRLS` | bypasses | bypasses | | owner, or a role inheriting the owner's privileges | bypasses | subject to policies | | other role with table grants | subject to policies | subject to policies | | member of `pg_read_all_data` | subject to policies | subject to policies | ## What FORCE changes, and what it does not `ALTER TABLE tasks FORCE ROW LEVEL SECURITY` sets `relforcerowsecurity`. `NO FORCE` clears it, and only the owner can change it. - It makes the owner, and any role that inherits the owner's privileges, subject to the policies. - It does nothing for superusers or `BYPASSRLS` roles. - Referential-integrity checks still bypass it. They run as the owner in a context that ignores `FORCE`, so a foreign key can still find a referenced row that a policy hides. That is a covert channel the manual warns about. - `FORCE` has no effect while the switch itself is off. ## row_security is a tripwire, not a key The `row_security` parameter is a user-settable boolean whose default is `on`. Turning it `off` does **not** let a role skip policies. Instead, any query that would have been filtered by a policy fails with `query would be affected by row-level security policy for table "tasks"`. When the role is the owner of a `FORCE` table, the error adds a hint to use `NO FORCE ROW LEVEL SECURITY`. The parameter has no effect on superusers or `BYPASSRLS` roles. `pg_dump` sets `row_security = off` by default so that it cannot silently produce a partial dump. A dump run by a role the policies apply to fails instead, and that includes the owner of a `FORCE` table. `--enable-row-security` dumps only the visible rows instead. ## Auditing who bypasses The audit queries below show, per table, the switch, the force flag and the owner. They then list the roles that bypass everywhere and check whether the runtime role inherits the owner's privileges. `pg_has_role(..., 'USAGE')` answers exactly that question: are the role's privileges available without `SET ROLE`? Run the audit after every role or ownership change, not just once. A single `GRANT project_owner TO app_rw` added for convenience, or an `ALTER TABLE ... OWNER TO` during a migration, silently moves a role from "subject to policies" to "bypasses them". Neither statement touches the policies.

  • With FORCE ROW LEVEL SECURITY set, why can a foreign-key check still find a row the owner's policies hide?
    Referential-integrity checks run as the table owner in a mode that ignores `FORCE`, and the manual states they always bypass row security so that integrity holds. The cost is a covert channel: an insert that succeeds or fails can reveal whether a hidden key exists. Surrogate keys with no outside meaning narrow it.
  • Does a member of pg_read_all_data see every tenant's rows in an RLS-protected table?
    No. `pg_read_all_data` acts like `SELECT` on every table and `USAGE` on every schema, but it does not bypass row-level security. If an analytics role really must read every tenant, the manual suggests setting `BYPASSRLS` on the roles that hold it. That is a deliberate decision you should record.
  • What happens when pg_dump runs as the owner of a table that has FORCE ROW LEVEL SECURITY?
    `pg_dump` sets `row_security = off`. Because `FORCE` makes the owner subject to the policies, reading that table fails with `query would be affected by row-level security policy`, plus a hint to use `NO FORCE`. Dump as a superuser or a `BYPASSRLS` role, or pass `--enable-row-security` to dump only the visible rows.

  • FORCE ROW LEVEL SECURITY also applies the policies to superusers
  • An app role that inherits the owner role is still filtered by the policies
  • Members of a BYPASSRLS role inherit the bypass automatically
  • pg_read_all_data lets a role read rows hidden by policies
  • Setting row_security = off lets a role skip the policies
open as a page

In PostgreSQL, a tasks policy compares org_id with current_setting('app.org_id', true)::uuid; why does set_config's is_local argument matter, and what can current_setting return?

level: seniorimportance: must knowfreq 55%

basics

~20 s

set_config('app.org_id', id, true) keeps the tenant only until the transaction ends, so it cannot leak to the next request on a reused connection. current_setting(name, true) returns NULL if never set, but an empty string after a local value expires.

open as a page

In PostgreSQL, what happens to an ordinary role's SELECT, UPDATE and INSERT on a table after ENABLE ROW LEVEL SECURITY but before any CREATE POLICY?

level: juniorimportance: should knowfreq 48%

basics

~20 s

Default deny applies: SELECT returns zero rows and UPDATE or DELETE touch zero rows without error, while INSERT fails with a row-level security error. Superusers, BYPASSRLS roles and, unless FORCE is set, the table owner are unaffected.

open as a page

In PostgreSQL, how are several row-level security policies on one table combined when some are PERMISSIVE and others are RESTRICTIVE?

level: middleimportance: should knowfreq 45%

basics

~20 s

Applicable PERMISSIVE policies are OR-ed, applicable RESTRICTIVE policies are AND-ed, and the two results are AND-ed. With no permissive policy for that command, access is denied, so restrictive policies can only narrow what a permissive one grants.

open as a page

In PostgreSQL, why can a query on a row-level-security table stop using an index, and how do LEAKPROOF functions and security_barrier views relate?

level: seniorimportance: nice to knowfreq 24%

basics

~20 s

Policy conditions run before any user condition using a non-LEAKPROOF function, so that condition cannot drive an index scan: jsonb @> loses its GIN index for a role subject to RLS, while leakproof uuid equality keeps its index.

open as a page