View permissions and row-level security in PostgreSQL

The details of how view permissions are checked have long puzzled me. PostgreSQL v15 introduces “security invoker” views, which change how permissions are checked. The new security invoker views make it possible to use row-level security effectively. I'll use this opportunity to explain how view permissions work and how you can benefit from this feature.

Why are view permissions checked in a special way?

Fundamentally, a view is an SQL statement with a name and an owner. When PostgreSQL processes an SQL statement, it replaces views with their definition. The replacement happens during the query rewrite step, before the optimizer calculates the best execution plan. PostgreSQL implements views as query rewrite rules.

But apart from being a shorthand for a subquery, a view can also serve as a security tool. A user can create a view on tables on which she has the SELECT privilege and grant SELECT on that view to another user. The other user can then access that view to see only part of the underlying data, even though he has no privileges on the underlying tables. Note, however, that using a view in this way is only safe if you set the view option security_barrier on the view. The PostgreSQL documentation contains a detailed description of the security problems thus avoided.

Details on how PostgreSQL checks view permissions

To allow using views as described in the previous paragraph, PostgreSQL checks view permissions in a special fashion.

It uses the owner of the view to check access to all relations referenced in the view definition. Here, “relation” is PostgreSQL jargon for anything stored in the system catalog pg_class: (partitioned) tables, views, (partitioned) indexes, sequences, composite types, materialized views and foreign tables. Typically, the view owner has all the required permissions. Otherwise, she would not have been allowed to create the view in the first place.

Note that the above does not extend to other objects. For example, the permissions on functions called in a view are checked as the user that accesses the view (the invoker).

Also, while PostgreSQL uses the view owner to check permissions, the invoker is the current_user during the execution of the query. Consequently, PostgreSQL executes all functions that are not defined with SECURITY DEFINER in the security context of the view invoker. In other words, ownership of a view results in a different behavior than ownership of a SECURITY DEFINER function.

Views and row-level security

Row-level security (RLS) determines which rows are visible to a user. A user can only see rows that satisfy the conditions imposed by row-level security policies. Since PostgreSQL uses the view owner to check permissions on the underlying tables, it makes sense that it also uses the view owner to check the row-level security policies on these tables.

While using the view owner to check both permissions and RLS policies makes sense, it wreaks havoc with a valid use case. Specifically, it would be nice to be able to query a view and see only those data from the underlying tables that you (as the invoking user) can see by virtue of the policies.

An example of view permissions

The following example is a showcase for the above:

joe can see data from rls even though he has no privileges on the table, because the permissions of the view owner laurenz apply. joe sees both rows, because PostgreSQL checks row-level security for the table owner laurenz, who is exempt from row-level security. But note that the function user (and hence whoami) returns the user that ran the query, not the view owner! The same would apply to the call to user in the policy definition, if the policy were used. While the view owner determines which row-level security policy applies, PostgreSQL evaluates the condition in USING as the view invoker.

View permissions in security_invoker views

PostgreSQL v15 introduces the view option security_invoker that changes how it checks permissions. If the option is set to on on a view, PostgreSQL checks all permissions as the invoking user. Essentially, the following code:

behaves in every way the same as

Let's see how our original example behaves with a security_invoker view:

Now the row-level security policy for joe is used, and we get only a single result row.

Use cases for security_invoker views

The main use case (and the one that inspired the feature) is to be able to use views and still check row-level security policies on the underlying tables as the invoker. But other use cases for views are also better served with views with security_invoker = on. For example, a view that provides “code reuse” for a frequently used subquery. security_invoker = on is the appropriate setting for most views that do not serve a security purpose.

Conclusion

You can use views as a tool to allow less privileged users partial access to privileged data. To facilitate that, PostgreSQL normally checks permissions on the underlying tables as the view owner. The security_invoker view option instead checks the permissions of the user accessing the view. This makes views and row-level security interact nicely and is the appropriate setting in most use cases.

If you want some advice on how to best use views, read my article about view dependencies, which also tells you how to keep track of nested views.

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on LinkedIn, on Twitter, or on Facebook.