when is RLS policy applied - Mailing list pgsql-general

From Ted Toth
Subject when is RLS policy applied
Date
Msg-id CAFPpqQEs4qDXybQGaydQNYorHhzAW8KBK_7uSqDLNp2rm7a7zw@mail.gmail.com
Whole thread Raw
Responses Re: when is RLS policy applied
Re: when is RLS policy applied
List pgsql-general
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? 

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)

pgsql-general by date:

Previous
From: Pavel Stehule
Date:
Subject: Re: Row estimates for empty tables
Next
From: Christophe Pettus
Date:
Subject: Re: Row estimates for empty tables