Re: when is RLS policy applied

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