From 5fbaab61e6530b04a87a2330de3831c6df342e65 Mon Sep 17 00:00:00 2001 From: Marko Kreen Date: Thu, 29 Mar 2012 18:14:37 +0300 Subject: [PATCH] Test SQL/MED with SECURITY DEFINER --- test/expected/plproxy_sqlmed.out | 65 +++++++++++++++++++++++++++++++- test/sql/plproxy_sqlmed.sql | 46 ++++++++++++++++++++++ 2 files changed, 110 insertions(+), 1 deletion(-) diff --git a/test/expected/plproxy_sqlmed.out b/test/expected/plproxy_sqlmed.out index f1a8ce2..a0a66e4 100644 --- a/test/expected/plproxy_sqlmed.out +++ b/test/expected/plproxy_sqlmed.out @@ -10,12 +10,16 @@ create or replace function sqlmed_test1() returns setof text as $$ run on 0; select 'plproxy: user=' || current_user || ' dbname=' || current_database(); $$ language plproxy; +drop user if exists test_user_alice; drop user if exists test_user_bob; +drop user if exists test_user_charlie; +create user test_user_alice password 'supersecret'; create user test_user_bob password 'secret'; +create user test_user_charlie password 'megasecret'; -- no user mapping set session authorization test_user_bob; select * from sqlmed_test1(); -ERROR: user mapping not found for "test_user_bob" +ERROR: permission denied for foreign server sqlmedcluster reset session authorization; -- add a public user mapping create user mapping for public server sqlmedcluster @@ -35,6 +39,65 @@ select * from sqlmed_test1(); plproxy: user=test_user_bob dbname=test_part3 (1 row) +reset session authorization; +-- test security definer +create user mapping for test_user_alice server sqlmedcluster; +create user mapping for test_user_charlie server sqlmedcluster; +grant usage on foreign server sqlmedcluster to test_user_alice; +grant usage on foreign server sqlmedcluster to test_user_charlie; +create or replace function sqlmed_test_alice() returns setof text as $$ + cluster 'sqlmedcluster'; + run on 0; + select 'plproxy: user=' || current_user || ' dbname=' || current_database(); +$$ language plproxy security definer; +alter function sqlmed_test_alice() owner to test_user_alice; +create or replace function sqlmed_test_charlie() returns setof text as $$ + cluster 'sqlmedcluster'; + run on 0; + select 'plproxy: user=' || current_user || ' dbname=' || current_database(); +$$ language plproxy security definer; +alter function sqlmed_test_charlie() owner to test_user_charlie; +-- call as alice +set session authorization test_user_alice; +select * from sqlmed_test_alice(); + sqlmed_test_alice +------------------------------------------------- + plproxy: user=test_user_alice dbname=test_part3 +(1 row) + +select * from sqlmed_test_charlie(); + sqlmed_test_charlie +--------------------------------------------------- + plproxy: user=test_user_charlie dbname=test_part3 +(1 row) + +reset session authorization; +-- call as charlie +set session authorization test_user_charlie; +select * from sqlmed_test_alice(); + sqlmed_test_alice +------------------------------------------------- + plproxy: user=test_user_alice dbname=test_part3 +(1 row) + +select * from sqlmed_test_charlie(); + sqlmed_test_charlie +--------------------------------------------------- + plproxy: user=test_user_charlie dbname=test_part3 +(1 row) + +reset session authorization; +-- test refresh too +alter user mapping for test_user_charlie + server sqlmedcluster + options (add user 'test_user_alice'); +set session authorization test_user_bob; +select * from sqlmed_test_charlie(); + sqlmed_test_charlie +------------------------------------------------- + plproxy: user=test_user_alice dbname=test_part3 +(1 row) + reset session authorization; -- cluster definition validation -- partition numbers must be consecutive diff --git a/test/sql/plproxy_sqlmed.sql b/test/sql/plproxy_sqlmed.sql index c553edf..a3099a8 100644 --- a/test/sql/plproxy_sqlmed.sql +++ b/test/sql/plproxy_sqlmed.sql @@ -14,8 +14,12 @@ create or replace function sqlmed_test1() returns setof text as $$ select 'plproxy: user=' || current_user || ' dbname=' || current_database(); $$ language plproxy; +drop user if exists test_user_alice; drop user if exists test_user_bob; +drop user if exists test_user_charlie; +create user test_user_alice password 'supersecret'; create user test_user_bob password 'secret'; +create user test_user_charlie password 'megasecret'; -- no user mapping set session authorization test_user_bob; @@ -38,6 +42,48 @@ set session authorization test_user_bob; select * from sqlmed_test1(); reset session authorization; +-- test security definer + +create user mapping for test_user_alice server sqlmedcluster; +create user mapping for test_user_charlie server sqlmedcluster; +grant usage on foreign server sqlmedcluster to test_user_alice; +grant usage on foreign server sqlmedcluster to test_user_charlie; + +create or replace function sqlmed_test_alice() returns setof text as $$ + cluster 'sqlmedcluster'; + run on 0; + select 'plproxy: user=' || current_user || ' dbname=' || current_database(); +$$ language plproxy security definer; +alter function sqlmed_test_alice() owner to test_user_alice; + +create or replace function sqlmed_test_charlie() returns setof text as $$ + cluster 'sqlmedcluster'; + run on 0; + select 'plproxy: user=' || current_user || ' dbname=' || current_database(); +$$ language plproxy security definer; +alter function sqlmed_test_charlie() owner to test_user_charlie; + +-- call as alice +set session authorization test_user_alice; +select * from sqlmed_test_alice(); +select * from sqlmed_test_charlie(); +reset session authorization; + +-- call as charlie +set session authorization test_user_charlie; +select * from sqlmed_test_alice(); +select * from sqlmed_test_charlie(); +reset session authorization; + +-- test refresh too +alter user mapping for test_user_charlie + server sqlmedcluster + options (add user 'test_user_alice'); +set session authorization test_user_bob; +select * from sqlmed_test_charlie(); +reset session authorization; + + -- cluster definition validation -- partition numbers must be consecutive -- 2.39.5