blob: a3099a87adf960fa2825f41de9f7129292d661d8 (
plain)
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
|
\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();
|
