On 4/2/21 10:23 AM, Stephen Frost wrote:
> Greetings,
>
> * Joe Conway (mail@joeconway.com) wrote:
>> On 4/2/21 9:57 AM, Isaac Morland wrote:
>> >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=# 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:
>>
>> That is because while VIEWs are effectively SECURITY DEFINER for table
>> access, functions running as part of the view are still SECURITY INVOKER if
>> they were defined that way. And "current_user" is essentially just a special
>> grammatical interface to a SECURITY INVOKER function:
>
> Right- and what I was really getting at is that it'd sometimes be nice
> to have the view run as 'security invoker' for table access. In
> general, it seems like it'd be useful to be able to control each piece
> and define if it's to be security invoker or security definer. We're
> able to do that for functions, but not other parts of the system.
+1
Agreed -- I have opined similarly in the past
Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development