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)
