Re: 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(dot)klaver(at)aklaver(dot)com