Thread: when is RLS policy applied

On 7/24/20 12:34 PM, Ted Toth wrote:
> I'm trying to understand when RLS select policy is applied so I created 
> the follow to test but I don't understand why the query filter order is 
> different for the 2 queries can anyone explain?

The way I see it is:

1) First case. The service column is the one the RLS is being applied 
against so the server needs to run through the service values to see if 
they test True or not(not knowing they will all return true) and then 
apply the like filter against the rows that the user is allowed to see.

2) Second case. The server filters out the service_type that do not 
apply to get a shortened list of rows that it then applies the USING 
function against the service values to test whether they are True or not.

> 
> CREATE USER bob NOSUPERUSER;
> CREATE TABLE t_service (service_type text, service text);
> INSERT INTO t_service VALUES
>      ('open_source', 'PostgreSQL consulting'),
>      ('open_source', 'PostgreSQL training'),
>      ('open_source', 'PostgreSQL 24x7 support'),
>      ('closed_source', 'Oracle tuning'),
>      ('closed_source', 'Oracle license management'),
>      ('closed_source', 'IBM DB2 training');
> GRANT ALL ON SCHEMA PUBLIC TO bob;
> GRANT ALL ON TABLE t_service TO bob;
> CREATE FUNCTION debug_me(text) RETURNS boolean AS
> $$
> BEGIN
>      RAISE NOTICE 'called as session_user=%, current_user=% for "%" ',
>          session_user, current_user, $1;
>      RETURN true;
> END;
> $$ LANGUAGE 'plpgsql';
> GRANT ALL ON FUNCTION debug_me TO bob;
> ALTER TABLE t_service ENABLE ROW LEVEL SECURITY;
> CREATE POLICY bob_pol ON t_service
>      FOR SELECT
>      TO bob
>      USING (debug_me(service));
> SET ROLE bob;
> explain analyze select * from t_service where service like 'Oracle%';
> NOTICE:  called as session_user=postgres, current_user=bob for 
> "PostgreSQL consulting"
> NOTICE:  called as session_user=postgres, current_user=bob for 
> "PostgreSQL training"
> NOTICE:  called as session_user=postgres, current_user=bob for 
> "PostgreSQL 24x7 support"
> NOTICE:  called as session_user=postgres, current_user=bob for "Oracle 
> tuning"
> NOTICE:  called as session_user=postgres, current_user=bob for "Oracle 
> license management"
> NOTICE:  called as session_user=postgres, current_user=bob for "IBM DB2 
> training"
>                                                QUERY PLAN
> ------------------------------------------------------------------------------------------------------
>   Seq Scan on t_service  (cost=0.00..241.00 rows=1 width=64) (actual 
> time=0.294..0.391 rows=2 loops=1)
>     Filter: (debug_me(service) AND (service ~~ 'Oracle%'::text))
>     Rows Removed by Filter: 4
>   Planning time: 0.112 ms
>   Execution time: 0.430 ms
> (5 rows)
> 
> explain analyze select * from t_service where 
> t_service.service_type='open_source';
> NOTICE:  called as session_user=postgres, current_user=bob for 
> "PostgreSQL consulting"
> NOTICE:  called as session_user=postgres, current_user=bob for 
> "PostgreSQL training"
> NOTICE:  called as session_user=postgres, current_user=bob for 
> "PostgreSQL 24x7 support"
>                                                QUERY PLAN
> ------------------------------------------------------------------------------------------------------
>   Seq Scan on t_service  (cost=0.00..241.00 rows=1 width=64) (actual 
> time=0.159..0.302 rows=3 loops=1)
>     Filter: ((service_type = 'open_source'::text) AND debug_me(service))
>     Rows Removed by Filter: 3
>   Planning time: 0.129 ms
>   Execution time: 0.348 ms
> (5 rows)


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Ted Toth <txtoth@gmail.com> writes:
> I'm trying to understand when RLS select policy is applied so I created the
> follow to test but I don't understand why the query filter order is
> different for the 2 queries can anyone explain?

The core reason why not is that the ~~ operator isn't considered
leakproof.  Plain text equality is leakproof, so it's safe to evaluate
ahead of the RLS filter --- and we'd rather do so because the plpgsql
function is assumed to be much more expensive than a built-in operator.

(~~ isn't leakproof because it can throw errors that expose information
about the pattern argument.)

            regards, tom lane

On 7/24/20 2:12 PM, Ted Toth wrote:
> I've looked for information on leakproofness of operators but haven't 
> found anything can you direct me to a source of this information?

See here:

https://www.postgresql.org/docs/12/catalog-pg-proc.html

"proleakproof     bool           The function has no side effects. No information 
about the arguments is conveyed except via the return value. Any 
function that might throw an error depending on the values of its 
arguments is not leak-proof."

So as example:

select proname from pg_proc where proleakproof = 't';

select proname from pg_proc where proleakproof = 'f';

> 
> On Fri, Jul 24, 2020 at 3:40 PM Ted Toth <txtoth@gmail.com 
> <mailto:txtoth@gmail.com>> wrote:
> 
> 
>     On Fri, Jul 24, 2020 at 3:15 PM Tom Lane <tgl@sss.pgh.pa.us
>     <mailto:tgl@sss.pgh.pa.us>> wrote:
> 
>         Ted Toth <txtoth@gmail.com <mailto:txtoth@gmail.com>> writes:
>          > I'm trying to understand when RLS select policy is applied so
>         I created the
>          > follow to test but I don't understand why the query filter
>         order is
>          > different for the 2 queries can anyone explain?
> 
>         The core reason why not is that the ~~ operator isn't considered
>         leakproof.  Plain text equality is leakproof, so it's safe to
>         evaluate
>         ahead of the RLS filter --- and we'd rather do so because the
>         plpgsql
>         function is assumed to be much more expensive than a built-in
>         operator.
> 
>         (~~ isn't leakproof because it can throw errors that expose
>         information
>         about the pattern argument.)
> 
>                                  regards, tom lane
> 
> 
>     Thanks for the explanation.
> 
>     Ted
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Ted Toth <txtoth@gmail.com> writes:
> I've looked for information on leakproofness of operators but haven't found
> anything can you direct me to a source of this information?

Operators per se don't have a leakproofness attribute; the function
underlying the operator is what has that property.

            regards, tom lane

On 7/24/20 2:15 PM, Adrian Klaver wrote:
> On 7/24/20 2:12 PM, Ted Toth wrote:
>> I've looked for information on leakproofness of operators but haven't 
>> found anything can you direct me to a source of this information?
> 
> See here:
> 
> https://www.postgresql.org/docs/12/catalog-pg-proc.html
> 
> "proleakproof     bool           The function has no side effects. No 
> information about the arguments is conveyed except via the return value. 
> Any function that might throw an error depending on the values of its 
> arguments is not leak-proof."
> 
> So as example:
> 
> select proname from pg_proc where proleakproof = 't';
> 
> select proname from pg_proc where proleakproof = 'f';

To update this per Tom's answer:

select oprname, proname from pg_proc join pg_operator on pg_proc.oid = 
pg_operator.oprcode  where proleakproof = 't';

select oprname, proname from pg_proc join pg_operator on pg_proc.oid = 
pg_operator.oprcode  where proleakproof = 'f';

> 
>>
>> On Fri, Jul 24, 2020 at 3:40 PM Ted Toth <txtoth@gmail.com 
>> <mailto:txtoth@gmail.com>> wrote:
>>
>>
>>     On Fri, Jul 24, 2020 at 3:15 PM Tom Lane <tgl@sss.pgh.pa.us
>>     <mailto:tgl@sss.pgh.pa.us>> wrote:
>>
>>         Ted Toth <txtoth@gmail.com <mailto:txtoth@gmail.com>> writes:
>>          > I'm trying to understand when RLS select policy is applied so
>>         I created the
>>          > follow to test but I don't understand why the query filter
>>         order is
>>          > different for the 2 queries can anyone explain?
>>
>>         The core reason why not is that the ~~ operator isn't considered
>>         leakproof.  Plain text equality is leakproof, so it's safe to
>>         evaluate
>>         ahead of the RLS filter --- and we'd rather do so because the
>>         plpgsql
>>         function is assumed to be much more expensive than a built-in
>>         operator.
>>
>>         (~~ isn't leakproof because it can throw errors that expose
>>         information
>>         about the pattern argument.)
>>
>>                                  regards, tom lane
>>
>>
>>     Thanks for the explanation.
>>
>>     Ted
>>
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com