On Fri, 2 Apr 2021 at 09:30, Stephen Frost <sfr...@snowman.net> wrote:
> Greetings, > > * Isaac Morland (isaac.morl...@gmail.com) wrote: > > On Fri, 2 Apr 2021 at 01:44, Dan Lynch <pyramat...@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=>