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 Fri, Jul 24, 2020 at 3:15 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

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

Thanks for the explanation.

Ted

I've looked for information on leakproofness of operators but haven't found
anything can you direct me to a source of this information?

On Fri, Jul 24, 2020 at 3:40 PM Ted Toth <txtoth@gmail.com> 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';

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