Re: policies with security definer option for allowing inline optimization - Mailing list pgsql-hackers

From Isaac Morland
Subject Re: policies with security definer option for allowing inline optimization
Date
Msg-id CAMsGm5fX1mk_F3XpYz08S0wARkpSR8NKuGOfYL8chq35eJt+Ew@mail.gmail.com
Whole thread Raw
In response to Re: policies with security definer option for allowing inline optimization  (Stephen Frost <sfrost@snowman.net>)
Responses Re: policies with security definer option for allowing inline optimization
List pgsql-hackers
On Fri, 2 Apr 2021 at 09:30, Stephen Frost <sfrost@snowman.net> wrote:
Greetings,

* 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 $$;
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=> 

pgsql-hackers by date:

Previous
From: Alvaro Herrera
Date:
Subject: Re: libpq debug log
Next
From: Isaac Morland
Date:
Subject: Re: policies with security definer option for allowing inline optimization