© Laurenz Albe 2022
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:
\connect - laurenz -- joe has no SELECT privileges CREATE TABLE rls (rls_user text); INSERT INTO rls VALUES ('laurenz'), ('joe'); -- does not apply to the table owner! ALTER TABLE rls ENABLE ROW LEVEL SECURITY; -- everybody can see their own row CREATE POLICY u ON rls TO PUBLIC USING (rls_user = user); -- shows the current user -- everybody has the EXECUTE privilege CREATE FUNCTION whoami() RETURNS text RETURN user; CREATE VIEW v AS SELECT rls_user, whoami() FROM rls; GRANT SELECT ON v TO joe; \connect - joe TABLE v; rls_user │ whoami ══════════╪════════ laurenz │ joe joe │ joe (2 rows)
joe can see data from
rls even though he has no privileges on the table, because the permissions of the view owner
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
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:
CREATE VIEW v AS SELECT /* whatever */; GRANT SELECT ON v TO joe; \connect - joe SELECT * FROM v;
behaves in every way the same as
\connect - joe SELECT * FROM (SELECT /* whatever */) AS v;
Let’s see how our original example behaves with a
\connect - laurenz ALTER VIEW v SET (security_invoker = on); -- necessary with "security_invoker" GRANT SELECT ON rls TO joe; \connect - joe TABLE laurenz.v; rls_user │ whoami ══════════╪════════ joe │ joe (1 row)
Now the row-level security policy for
joe is used, and we get only a single result row.
Use cases for
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.
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.