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