1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
|
\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();
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();
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();
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
alter server sqlmedcluster options (drop partition_2);
select * from sqlmed_test1();
-- invalid partition count
alter server sqlmedcluster options
(drop partition_3,
add partition_2 'dbname=test_part1 host=localhost');
select * from sqlmed_test1();
-- 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();
-- 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();
-- now drop the SQL/MED testcluster, and test fallback
drop server testcluster cascade;
-- back on testcluster again
select * from sqlmed_compat_test();
-- 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');
-- 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');
|
