The RLS Trap That Passes Every Test: A Postgres View Runs as Its Definer Unless You Say Otherwise

Mostafa Dhouib·11 min read

The RLS Trap That Passes Every Test: A Postgres View Runs as Its Definer Unless You Say Otherwise

The short answer. A Postgres view does not run as the person querying it. It runs with the privileges of the role that defined it, and permission checks on the underlying tables, including row-level security policies, are evaluated against that role rather than the caller. The option that changes this, security_invoker, is off by default. So every view in your schema that predates the day somebody first thought about row-level security is in definer mode, which means it is not enforcing your policy. The reason nobody notices is that the test everybody writes, confirming a user can see their own data, returns the correct answer whether the policy is enforced or bypassed.

A team enables row-level security on a table holding records that belong to individual people. They write the policy, check it by hand, and it is correct.

Then somebody points an application, or a reporting tool, or an assistant, at a view over that table rather than at the table itself. Views are how a schema is made pleasant to query. They flatten joins, rename columns, and hide the parts nobody outside the data team should have to think about.

That view is where the policy stops applying, and nothing in the system says so.

A view carries an owner, and the owner is who the database thinks is asking

The mental model most people hold is that a view is a macro. You select from it, Postgres substitutes the stored query, and the result is as if you had typed the underlying select yourself.

That is right about the rewriting and wrong about the identity. When Postgres expands a view, it checks permissions on the view's underlying objects against the view's owner, not against the caller. This is what makes views useful as a privilege-granting mechanism in the first place. You can grant someone select on a view over a table they have no access to at all, and the view works, because the view owner's access is what gets consulted.

Row-level security rides on the same mechanism. Policies on the underlying table are evaluated with the view owner as the current role. Two things follow from that, and they fail differently.

If the view owner also owns the table, which is the overwhelmingly common case because the same migration created both, the policies are not applied at all. Table owners are exempt from row-level security by default. The view returns every row in the table, and whatever filtering the caller sees comes from the application's own where clause, which is precisely the thing row-level security was supposed to stop you depending on.

If the view owner is some other role that is subject to the policies, the policies are applied, but against the wrong identity. If the policy compares a column to the current role name, every caller now sees the view owner's rows. If the policy compares a column to a session variable, something stranger happens. The variable still resolves to the caller's value, because session settings belong to the session rather than to the role, so the view appears to work correctly. It is accidentally right, and it stays accidentally right until somebody rewrites the policy to reference the role.

That second case is worth sitting with, because it is the reason this bug survives review. A view can be in the wrong security mode and still produce exactly the right rows, for reasons unrelated to the mode being correct.

The migration problem: you do not have a few bad views, you have all of them

security_invoker is a per-view option, it is off unless set, and the option itself arrived only in a relatively recent major version of Postgres. Any view created before a team adopted it is in definer mode. There is no global switch, no database-level default, and no line in a configuration file that flips the behaviour for everything.

So this is not a bug you fix. It is a migration you run, view by view, and it has three awkward properties.

Flipping the mode can break the view. The whole point of definer semantics is that the caller may have no privileges at all on the underlying tables. Turn on security_invoker and the caller now needs select on every base table the view touches. Views built deliberately as privilege-granting surfaces will start throwing permission errors for exactly the users they were created to serve. You have to decide, per view, whether it was meant to grant access or meant to present data.

The change cascades. Views over views inherit the problem. A reporting view built on three convenience views built on two base tables has five places where the mode matters, and flipping the top one does nothing if the layer below it is still in definer mode and owned by the table owner.

Materialised views cannot be fixed this way at all. A materialised view does not support security_invoker, because reading one is not a read of the underlying table. It is a read of stored rows that were produced when somebody last refreshed it, with the refreshing role's privileges. Row-level security on the base table has no bearing on a select from the materialised view, ever. If one sits over protected data, the only controls available are grants and the choice of what goes into it. Treat it as a published extract rather than as a view, because that is what it is.

How to audit a schema for it

You want three lists, and the intersection of them is your work queue.

Which tables have row-level security on, and whether it is forced:

select n.nspname, c.relname, c.relrowsecurity, c.relforcerowsecurity
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where c.relkind = 'r'
  and n.nspname not in ('pg_catalog', 'information_schema')
order by 1, 2;

Which views are in definer mode. The reloptions column holds the per-view settings, and a view with no security_invoker entry is in definer mode:

select n.nspname, c.relname, c.relkind, pg_get_userbyid(c.relowner) as owner,
       coalesce(array_to_string(c.reloptions, ','), '(none)') as opts
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where c.relkind in ('v', 'm')
  and n.nspname not in ('pg_catalog', 'information_schema')
  and coalesce(array_to_string(c.reloptions, ','), '') not like '%security_invoker=true%'
order by 1, 2;

Which of those views actually touch a protected table. Dependencies through the rewrite rules give you this:

select distinct v.relname as view_name, t.relname as base_table
from pg_depend d
join pg_rewrite r on r.oid = d.objid
join pg_class v on v.oid = r.ev_class
join pg_class t on t.oid = d.refobjid
where d.classid = 'pg_rewrite'::regclass
  and d.refclassid = 'pg_class'::regclass
  and v.relkind in ('v', 'm')
  and t.relrowsecurity
  and v.oid <> t.oid
order by 1, 2;

Run the third query and you have the list that matters: views that read protected tables and are not running as their caller. Add the owner column from the second query and you can tell which of those are the full-bypass case and which are the wrong-identity case.

Then put the same three queries into continuous integration as an assertion rather than as a report. A view created next quarter by somebody who has never read any of this will be in definer mode, because that is the default, and a default is not something you fix once.

Why your test passes either way

This is the part that makes the whole thing dangerous rather than merely annoying.

The test a team writes for a permission boundary is almost always a positive test. Authenticate as a user, query for that user's records, assert the records come back and look right. It is the obvious test, it reads like the requirement, and it is what a product owner would ask for.

That test passes when the policy is enforced. It also passes when the policy is bypassed, because the application is still sending a where clause scoped to the current user, and the rows that come back are the same rows. The two configurations are indistinguishable from the result set. There is no error, no warning, no change in the query plan that a reviewer would notice, and no difference in latency worth remarking on.

The discriminating tests are the ones nobody writes, and there are three of them.

Query with no filter. Select from the view as a non-privileged user with no where clause at all, and assert the row count equals that user's own rows. In definer mode you get the whole table. This single test catches the bypass case outright.

Query for somebody else explicitly. Select from the view with a where clause naming another user's identifier, and assert zero rows. This catches the case where the application's filter is doing all the work.

Assert the configuration, not just the behaviour. The three audit queries above, as test assertions: this table has row-level security forced, this view has security_invoker set. Behaviour tests tell you the system is correct today. Configuration assertions tell you it will still be correct after the next view is added.

The general shape of the rule is worth stating plainly, because it reaches well beyond views. A permission control can only be verified by a test that fails when the control is removed. If removing the control does not turn the test red, the test is measuring something else.

The honest limit

Everything above concerns Postgres specifically, and the exact mechanism, a per-view option that defaults off, is a Postgres design choice. Other engines route around row-level filtering in their own ways and these audit queries will not transfer.

The queries find views that are in definer mode over protected tables. They do not tell you whether that is wrong, because some views are deliberately privilege-granting and converting them would break the thing they exist for. That judgement is per view and a person has to make it.

And views are one path of several. Functions declared with definer semantics, triggers, and extension code that runs with elevated privileges all produce the same class of failure, where the policy is written correctly and evaluated against somebody other than the caller. The view case is the one I keep finding first, because views are the most common thing a team adds for convenience after the security model was settled.

FAQ

Why does my Postgres view ignore row-level security? Because a view executes with its definer's privileges unless security_invoker is set on it, and that option is off by default. Policies on the underlying table are evaluated against the view's owner rather than the caller. If the view owner also owns the table, which is usual, the owner is exempt from row-level security and the view returns every row.

How do I find which views are affected? Query pg_class.reloptions for views lacking security_invoker=true, query pg_class.relrowsecurity for tables with policies enabled, and join through pg_depend and pg_rewrite to find which of those views read a protected table. The intersection is the list. Add the owner column to separate the full-bypass case from the wrong-identity case.

Will turning on security_invoker break anything? It can. Definer semantics are what allow a caller with no access to the base tables to select from a view. Once the view runs as its caller, that caller needs select on every table the view touches. Views built deliberately to grant narrow access will start failing for the users they were created for, so decide per view whether it presents data or grants access.

Can I set security_invoker for the whole database? No. It is a per-view option with no database-level default and no configuration-file equivalent, so every view has to be altered individually and every new view starts in definer mode. That is why the audit belongs in continuous integration as a failing assertion rather than as a one-off cleanup task.

Does this apply to materialised views? Worse, and differently. A materialised view does not support security_invoker at all, because reading it reads stored rows rather than the base table, so no policy on the base table is consulted. The rows were produced by whoever refreshed it, with their privileges. Control access with grants and with what you choose to put in it, and treat it as a published extract.

Why did our permission tests not catch this? Because they test that the right user sees their own data, and that passes whether the policy is enforced or bypassed, since the application is still sending a user-scoped filter. You need a test that selects from the view with no filter and asserts the row count equals the user's own rows. A control is only verified by a test that goes red when the control is removed.