In PostgreSQL, which roles bypass a table's row-level security policies once it is enabled, and what does FORCE ROW LEVEL SECURITY change?
answer
- three exemptions, one removable
- a role attribute, not a grant
- ownership through membership counts
- FORCE binds only the owner
- row_security errors, never bypasses
basics
~20 sSuperusers 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 sThe 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 linesSELECT 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
Remember the three exemptions: superuser, BYPASSRLS, and the table owner. FORCE removes only the owner's.
Walk through the server's decision order and explain why membership in the owning role counts as ownership while BYPASSRLS is never inherited.
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.
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