From: | Ted Toth <txtoth(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | when is RLS policy applied |
Date: | 2020-07-24 19:34:48 |
Message-ID: | CAFPpqQEs4qDXybQGaydQNYorHhzAW8KBK_7uSqDLNp2rm7a7zw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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)
From | Date | Subject | |
---|---|---|---|
Next Message | Christophe Pettus | 2020-07-24 19:35:35 | Re: Row estimates for empty tables |
Previous Message | Pavel Stehule | 2020-07-24 19:14:04 | Re: Row estimates for empty tables |