From d85adbeae4f83ce0355c8f626f7c8b0aa961735f Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sat, 29 Mar 2025 20:31:50 -0400 Subject: [PATCH v10 5/6] Add a test case showing undesirable RLS behavior in SQL functions. In the historical implementation of SQL functions, once we have built a set of plans for a SQL function we'll continue to use them during subsequent function invocations in the same query. This isn't ideal, and this somewhat-contrived test case shows one reason why not: we don't notice changes in RLS-relevant state. I'm putting this as a separate patch in the series so that the change in behavior will be apparent. Author: Alexander Pyhalov Reviewed-by: Tom Lane Discussion: https://postgr.es/m/8216639.NyiUUSuA9g@aivenlaptop --- src/test/regress/expected/rowsecurity.out | 59 +++++++++++++++++++++++ src/test/regress/sql/rowsecurity.sql | 44 +++++++++++++++++ 2 files changed, 103 insertions(+) diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out index 87929191d06..8f2c8319172 100644 --- a/src/test/regress/expected/rowsecurity.out +++ b/src/test/regress/expected/rowsecurity.out @@ -4695,6 +4695,65 @@ RESET ROLE; DROP FUNCTION rls_f(); DROP VIEW rls_v; DROP TABLE rls_t; +-- Check that RLS changes invalidate SQL function plans +create table rls_t (c text); +create table test_t (c text); +insert into rls_t values ('a'), ('b'), ('c'), ('d'); +insert into test_t values ('a'), ('b'); +alter table rls_t enable row level security; +grant select on rls_t to regress_rls_alice; +grant select on test_t to regress_rls_alice; +create policy p1 on rls_t for select to regress_rls_alice + using (c = current_setting('rls_test.blah')); +-- Function changes row_security setting and so invalidates plan +create function rls_f(text) returns text +begin atomic + select set_config('rls_test.blah', $1, true) || set_config('row_security', 'false', true) || string_agg(c, ',' order by c) from rls_t; +end; +set plan_cache_mode to force_custom_plan; +-- Table owner bypasses RLS +select rls_f(c) from test_t order by rls_f; + rls_f +------------- + aoffa,b,c,d + boffa,b,c,d +(2 rows) + +-- For other users, changes in row_security setting +-- should lead to RLS error during query rewrite +set role regress_rls_alice; +select rls_f(c) from test_t order by rls_f; + rls_f +------- + boffa + +(2 rows) + +reset role; +set plan_cache_mode to force_generic_plan; +-- Table owner bypasses RLS, although cached plan will be invalidated +select rls_f(c) from test_t order by rls_f; + rls_f +------------- + aoffa,b,c,d + boffa,b,c,d +(2 rows) + +-- For other users, changes in row_security setting +-- should lead to plan invalidation and RLS error during query rewrite +set role regress_rls_alice; +select rls_f(c) from test_t order by rls_f; + rls_f +------- + boffa + +(2 rows) + +reset role; +reset plan_cache_mode; +reset rls_test.blah; +drop function rls_f(text); +drop table rls_t, test_t; -- -- Clean up objects -- diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql index f61dbbf9581..9da967a9ef2 100644 --- a/src/test/regress/sql/rowsecurity.sql +++ b/src/test/regress/sql/rowsecurity.sql @@ -2307,6 +2307,50 @@ DROP FUNCTION rls_f(); DROP VIEW rls_v; DROP TABLE rls_t; +-- Check that RLS changes invalidate SQL function plans +create table rls_t (c text); +create table test_t (c text); +insert into rls_t values ('a'), ('b'), ('c'), ('d'); +insert into test_t values ('a'), ('b'); +alter table rls_t enable row level security; +grant select on rls_t to regress_rls_alice; +grant select on test_t to regress_rls_alice; +create policy p1 on rls_t for select to regress_rls_alice + using (c = current_setting('rls_test.blah')); + +-- Function changes row_security setting and so invalidates plan +create function rls_f(text) returns text +begin atomic + select set_config('rls_test.blah', $1, true) || set_config('row_security', 'false', true) || string_agg(c, ',' order by c) from rls_t; +end; + +set plan_cache_mode to force_custom_plan; + +-- Table owner bypasses RLS +select rls_f(c) from test_t order by rls_f; + +-- For other users, changes in row_security setting +-- should lead to RLS error during query rewrite +set role regress_rls_alice; +select rls_f(c) from test_t order by rls_f; +reset role; + +set plan_cache_mode to force_generic_plan; + +-- Table owner bypasses RLS, although cached plan will be invalidated +select rls_f(c) from test_t order by rls_f; + +-- For other users, changes in row_security setting +-- should lead to plan invalidation and RLS error during query rewrite +set role regress_rls_alice; +select rls_f(c) from test_t order by rls_f; +reset role; + +reset plan_cache_mode; +reset rls_test.blah; +drop function rls_f(text); +drop table rls_t, test_t; + -- -- Clean up objects -- -- 2.43.5