Waiting for PostgreSQL 15 – Add support for security invoker views.

On 22nd of March 2022, Dean Rasheed committed patch:

Add support for security invoker views. 
 
A security invoker view checks permissions for accessing its
underlying base relations using the privileges of the user of the
view, rather than the privileges of the view owner. Additionally, if
any of the base relations are tables with RLS enabled, the policies of
the user of the view are applied, rather than those of the view owner.
 
This allows views to be defined without giving away additional
privileges on the underlying base relations, and matches a similar
feature available in other database systems.
 
It also allows views to operate more naturally with RLS, without
affecting the assignments of policies to users.
 
Christoph Heiss, with some additional hacking by me. Reviewed by
Laurenz Albe and Wolfgang Walther.
 
Discussion: https://postgr.es/m/b66dd6d6-ad3e-c6f2-8b90-47be773da240%40cybertec.at

This is pretty cool thing.

When you have tables, you can have privileges on them that allows some user access, and some don't.

Now, when you have functions, you normally run these functions as user that called them. For example, if I'd run:

$ psql -U depesz
...
=$ select some_function();

then some_functions will call all of it's body, including all queries, as user depesz.

But, sometimes it makes sense to make function that bypasses security checks (because, for example, it does something that user shouldn't be able to do “by hand"). These are called “security definer" functions, because function marked as such, when called, is executed with privileges of user that created the function, and not the one that called it.

Views were always “security definer". The idea was that if you make view, you can fine tune privileges on the view, but privileges on tables don't matter (that much).

Now, we can change it.

Let's make a test:

$ psql -X -U depesz -c 'create user test'
CREATE ROLE
 
$ psql -X -U depesz -c 'create table source_data as select id from generate_series(1,10) id'
SELECT 10
 
$ psql -X -U depesz -c 'create view old_type_view as select * from source_data'
CREATE VIEW
 
$ psql -X -U depesz -c 'grant select on old_type_view to test'
GRANT
 
$ psql -X -U depesz -c 'create view new_type_view with (security_invoker = true ) as select * from source_data'
CREATE VIEW
 
$ psql -X -U depesz -c 'grant select on new_type_view to test'
GRANT

and now, with user test:

$ psql -X -U test -d depesz -c "select * from old_type_view"
 id 
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)
 
$ psql -X -U test -d depesz -c "select * from new_type_view"
ERROR:  permission denied for table source_data

Sweet. Old type view, which is effectively security_invoker = false allows selecting, even without privileges to underlying table, but new one, with security_invoker = true will fail.

Nice. Thanks to all involved, cool stuff.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.