Re: policies with security definer option for allowing inline optimization

On Fri, 2 Apr 2021 at 09:30, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> Greetings,
>
> * Isaac Morland (isaac(dot)morland(at)gmail(dot)com) wrote:
> > On Fri, 2 Apr 2021 at 01:44, Dan Lynch <pyramation(at)gmail(dot)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 $$;
CREATE FUNCTION
postgres=# select get_current_user_sd ();
get_current_user_sd
---------------------
postgres
(1 row)

postgres=# set role t1;
SET
postgres=> select get_current_user_sd ();
get_current_user_sd
---------------------
postgres
(1 row)

postgres=>