\set VERBOSITY terse
set client_min_messages = 'warning';
create server sqlmedcluster foreign data wrapper plproxy
    options (   partition_0 'dbname=test_part3 host=localhost',
                partition_1 'dbname=test_part2 host=localhost',
                partition_2 'dbname=test_part1 host=localhost',
                partition_3 'dbname=test_part0 host=localhost');
create or replace function sqlmed_test1() returns setof text as $$
    cluster 'sqlmedcluster';
    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:  permission denied for foreign server sqlmedcluster
reset session authorization;
-- add a public user mapping
create user mapping for public server sqlmedcluster
    options (   user        'test_user_bob',
                password    'secret1');
-- no access to foreign server
set session authorization test_user_bob;
select * from sqlmed_test1();
ERROR:  permission denied for foreign server sqlmedcluster
reset session authorization;
-- ok, access granted
grant usage on foreign server sqlmedcluster to test_user_bob;
set session authorization test_user_bob;
select * from sqlmed_test1();
                 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
alter server sqlmedcluster options (drop partition_2);
ERROR:  Pl/Proxy: partition numbers must start from 0 and be numbered consecutively
select * from sqlmed_test1();
                 sqlmed_test1                  
-----------------------------------------------
 plproxy: user=test_user_bob dbname=test_part3
(1 row)

-- invalid partition count
alter server sqlmedcluster options
    (drop partition_3,
     add  partition_2 'dbname=test_part1 host=localhost');
ERROR:  option "partition_2" provided more than once
select * from sqlmed_test1();
                 sqlmed_test1                  
-----------------------------------------------
 plproxy: user=test_user_bob dbname=test_part3
(1 row)

-- switching betweem SQL/MED and compat mode
create or replace function sqlmed_compat_test() returns setof text as $$
    cluster 'testcluster';
    run on 0;
    select 'plproxy: part=' || current_database();
$$ language plproxy;
-- testcluster
select * from sqlmed_compat_test();
    sqlmed_compat_test    
--------------------------
 plproxy: part=test_part0
(1 row)

-- override the test cluster with a SQL/MED definition
drop server if exists testcluster cascade;
create server testcluster foreign data wrapper plproxy
    options (partition_0 'dbname=regression host=localhost');
create user mapping for public server testcluster;
-- sqlmed testcluster
select * from sqlmed_compat_test();
    sqlmed_compat_test    
--------------------------
 plproxy: part=regression
(1 row)

-- now drop the SQL/MED testcluster, and test fallback
drop server testcluster cascade;
-- back on testcluster again
select * from sqlmed_compat_test();
    sqlmed_compat_test    
--------------------------
 plproxy: part=test_part0
(1 row)

-- test unordered creation
create server unordered1 foreign data wrapper plproxy
    options (   partition_0 'dbname=test_part0 host=localhost',
                partition_02 'dbname=test_part2 host=localhost',
                partition_001 'dbname=test_part1 host=localhost',
                partition_3 'dbname=test_part3 host=localhost');
-- test duplicate numbers
create server unordered2 foreign data wrapper plproxy
    options (   partition_1 'dbname=test_part1 host=localhost',
                partition_01 'dbname=test_part2 host=localhost');
ERROR:  Pl/Proxy: duplicate partition number: 1
-- test wrong numbers
create server unordered2 foreign data wrapper plproxy
    options (   partition_1 'dbname=test_part0 host=localhost',
                partition_2 'dbname=test_part2 host=localhost',
                partition_3 'dbname=test_part1 host=localhost',
                partition_4 'dbname=test_part3 host=localhost');
ERROR:  Pl/Proxy: partition numbers must start from 0 and be numbered consecutively