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@xxxxxxxxxxx
