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(at)gmail(dot)com
>> <mailto:txtoth(at)gmail(dot)com>> wrote:
>>
>>
>>     On Fri, Jul 24, 2020 at 3:15 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us
>>     <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>> wrote:
>>
>>         Ted Toth <txtoth(at)gmail(dot)com <mailto:txtoth(at)gmail(dot)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(dot)klaver(at)aklaver(dot)com