CYBERTEC Logo

View permissions and row-level security in PostgreSQL

06.2022 / Category: / Tags: | |
PostgreSQL view permissions confusing security experts
© 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:

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.

5 2 votes
Article Rating
Subscribe
Notify of
guest
8 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Dean Kayton
Dean Kayton
1 year ago

How are you able to get both? i.e. I don't want ordinary users to access underlying tables (of 'internal' schema). They only get access to 'api' schema, which houses all the views that query internal tables. But I would like to apply policies at table level.

If I change settings of views, to have privileges of invoker, they then are unable to access internal tables.

I guess my only option is to define row based access at the views level instead of at the table level?

laurenz
laurenz
1 year ago
Reply to  Dean Kayton

These requirements conflict with each other.
Here is an idea: put the tables in a schema on which the API user has no USAGE permission. Give the API user permissions on those tables. The user still cannot directly access them because of the schema permissions. Now create security_invoker views in the API schema and give the API user permissions on those views. Mission accomplished!

Dean Kayton
Dean Kayton
1 year ago
Reply to  laurenz

Great idea. For some reason in my iterations I did end up granting usage to the internal table schema but at some point I will check if things work without it.

Dean Kayton
Dean Kayton
1 year ago

How are you able to get both? i.e. I don't want ordinary users to access underlying tables (of 'internal' schema). They only get access to 'api' schema, which houses all the views that query internal tables. But I would like to apply policies at table level.

If I change settings of views, to have privileges of invoker, they then are unable to access internal tables.

I guess my only option is to define row based access at the views level instead of at the table level?

laurenz
laurenz
1 year ago
Reply to  Dean Kayton

These requirements conflict with each other.
Here is an idea: put the tables in a schema on which the API user has no USAGE permission. Give the API user permissions on those tables. The user still cannot directly access them because of the schema permissions. Now create security_invoker views in the API schema and give the API user permissions on those views. Mission accomplished!

Dean Kayton
Dean Kayton
1 year ago
Reply to  laurenz

Great idea. For some reason in my iterations I did end up granting usage to the internal table schema but at some point I will check if things work without it.

Paul Macinic
Paul Macinic
10 months ago

Hi Lorenz, in the conclusion of your article you are stating that

You can use views as a tool to allow less privileged users partial access to privileged data.

As far as I understand by using security_invoker feature are you able to create a sensitive_data table and a subset_view that contains a subset of that data.

My questions is, are you able to restrict all privileges for a specific role to the sensitive_data table, but still grant select privilege to the subset_view?

I have tried this, but as soon as I restrict privileges to the underlying table(sensitive_data) for the role, the view cannot query the data.

laurenz
laurenz
10 months ago
Reply to  Paul Macinic

You cannot use a security_definer view to provide access to restricted data. That only works with views that don't have this property set (or have it set to off). Don't forget to set the security_barrier property on the view.

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    8
    0
    Would love your thoughts, please comment.x
    ()
    x
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram