when is RLS policy applied

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)

Responses

Browse pgsql-general by date

  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