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
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
|
\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
|
