* Isaac Morland (isaac.morland@gmail.com) wrote: > On Fri, 2 Apr 2021 at 01:44, Dan Lynch <pyramation@gmail.com> wrote: > > RLS policies quals/checks are optimized inline, and so I generally avoid > > writing a separate procedure so the optimizer can do it's thing. > > > > However, if you need a security definer to avoid recursive RLS if you're > > doing a more complex query say, on a join table, anyone wish there was a > > flag on the policy itself to specify that `WITH CHECK` or `USING` > > expression could be run via security definer? > > > > The main reason for this is to avoid writing a separate security definer > > function so you can benefit from the optimizer. > > > > Is this possible? Would this be worth a feature request to postgres core? > > If we're going to do this we should do the same for triggers as well.
... and views.
Views already run security definer, allowing them to be used for some of the same information-hiding purposes as RLS. But I just found something strange: current_user/_role returns the user's role, not the view owner's role:
postgres=# create table tt as select 5;
SELECT 1
postgres=# create view tv as select *, current_user from tt;
CREATE VIEW
postgres=# table tt;
?column?
----------
5
(1 row)
postgres=# table tv;
?column? | current_user
----------+--------------
5 | postgres
(1 row)
postgres=# set role to t1;
SET
postgres=> table tt;
ERROR: permission denied for table tt
postgres=> table tv;
ERROR: permission denied for view tv
postgres=> set role to postgres;
SET
postgres=# grant select on tv to public;
GRANT
postgres=# set role to t1;
SET
postgres=> table tt;
ERROR: permission denied for table tt
postgres=> table tv;
?column? | current_user
----------+--------------
5 | t1
(1 row)
postgres=>
Note that even though current_user is t1 "inside" the view, it is still able to see the contents of table tt. Shouldn't current_user/_role return the view owner in this situation? By contrast security definer functions work properly:
postgres=# create function get_current_user_sd () returns name security definer language sql as $$ select current_user $$;
