Re: policies with security definer option for allowing inline optimization

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:

postgres=# \df+ current_user
List of functions
-[ RECORD 1 ]-------+------------------
Schema              | pg_catalog
Name                | current_user
Result data type    | name
Argument data types |
Type                | func
Volatility          | stable
Parallel            | safe
Owner               | postgres
Security            | invoker
Access privileges   |
Language            | internal
Source code         | current_user
Description         | current user name

Joe

-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development