Skip to content

Commit f5fb6e9

Browse files
funbringerfunbringer
committed
improve regression test 'pathman_permissions'
1 parent a92624d commit f5fb6e9

File tree

2 files changed

+156
-49
lines changed

2 files changed

+156
-49
lines changed

‎expected/pathman_permissions.out

Lines changed: 96 additions & 27 deletions
Original file line numberDiff line numberDiff line change
@@ -1,61 +1,130 @@
11
\set VERBOSITY terse
22
CREATE EXTENSION pg_pathman;
3+
CREATE SCHEMA permissions;
34
CREATE ROLE user1 LOGIN;
45
CREATE ROLE user2 LOGIN;
6+
GRANT USAGE, CREATE ON SCHEMA permissions TO user1;
7+
GRANT USAGE, CREATE ON SCHEMA permissions TO user2;
8+
ALTER DEFAULT PRIVILEGES FOR ROLE user1
9+
IN SCHEMA permissions
10+
GRANT SELECT, INSERT ON TABLES
11+
TO user2;
12+
/* Switch to #1 */
513
SET ROLE user1;
6-
CREATE TABLE user1_table(id serial, a int);
7-
INSERT INTO user1_table SELECT g, g FROM generate_series(1, 20) as g;
8-
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO public;
14+
CREATE TABLE permissions.user1_table(id serial, a int);
15+
INSERT INTO permissions.user1_table SELECT g, g FROM generate_series(1, 20) as g;
16+
/* Should fail */
917
SET ROLE user2;
10-
/* Not owner and not superuser cannot create partitions */
11-
SELECT create_range_partitions('user1_table', 'id', 1, 10, 2);
12-
ERROR: permission denied for relation user1_table
18+
SELECT create_range_partitions('permissions.user1_table', 'id', 1, 10, 2);
19+
NOTICE: sequence "user1_table_seq" does not exist, skipping
20+
ERROR: only the owner or superuser can change partitioning configuration of table "user1_table"
21+
/* Should be ok */
1322
SET ROLE user1;
14-
/* Owner can */
15-
SELECT create_range_partitions('user1_table', 'id', 1, 10, 2);
23+
SELECT create_range_partitions('permissions.user1_table', 'id', 1, 10, 2);
1624
NOTICE: sequence "user1_table_seq" does not exist, skipping
1725
create_range_partitions
1826
-------------------------
1927
2
2028
(1 row)
2129

30+
/* Should be able to see */
31+
SET ROLE user2;
32+
SELECT * FROM pathman_config;
33+
partrel | attname | parttype | range_interval
34+
-------------------------+---------+----------+----------------
35+
permissions.user1_table | id | 2 | 10
36+
(1 row)
37+
38+
SELECT * FROM pathman_config_params;
39+
partrel | enable_parent | auto | init_callback
40+
-------------------------+---------------+------+---------------
41+
permissions.user1_table | f | t | -
42+
(1 row)
43+
44+
/* Should fail */
2245
SET ROLE user2;
23-
/* Try to change partitioning parameters for user1_table */
24-
SELECT set_enable_parent('user1_table', true);
46+
SELECT set_enable_parent('permissions.user1_table', true);
47+
ERROR: only the owner or superuser can change partitioning configuration of table "user1_table"
48+
SELECT set_auto('permissions.user1_table', false);
2549
ERROR: only the owner or superuser can change partitioning configuration of table "user1_table"
26-
SELECT set_auto('user1_table', false);
50+
/* Should fail */
51+
SET ROLE user2;
52+
DELETE FROM pathman_config
53+
WHERE partrel = 'permissions.user1_table'::regclass;
2754
ERROR: only the owner or superuser can change partitioning configuration of table "user1_table"
28-
/*
29-
* Check that user is able to propagate partitions by inserting rows that
30-
* doesn't fit into range
31-
*/
32-
INSERT INTO user1_table (id, a) VALUES (25, 0);
33-
ERROR: permission denied for relation user1_table
55+
/* No rights to insert, should fail */
56+
SET ROLE user2;
57+
INSERT INTO permissions.user1_table (id, a) VALUES (35, 0);
58+
/* Have rights, should be ok (bgw connects as user1) */
59+
SET ROLE user1;
60+
GRANT INSERT ON permissions.user1_table TO user2;
61+
SET ROLE user2;
62+
INSERT INTO permissions.user1_table (id, a) VALUES (35, 0) RETURNING *;
63+
id | a
64+
----+---
65+
35 | 0
66+
(1 row)
67+
68+
SELECT relacl FROM pg_class WHERE oid = 'permissions.user1_table_4'::regclass;
69+
relacl
70+
--------------------------------------
71+
{user1=arwdDxt/user1,user2=ar/user1}
72+
(1 row)
73+
74+
/* Try to drop partition, should fail */
75+
SELECT drop_range_partition('permissions.user1_table_4');
76+
ERROR: must be owner of relation user1_table_4
77+
/* Disable automatic partition creation */
78+
SET ROLE user1;
79+
SELECT set_auto('permissions.user1_table', false);
80+
set_auto
81+
----------
82+
83+
(1 row)
84+
85+
/* Partition creation, should fail */
86+
SET ROLE user2;
87+
INSERT INTO permissions.user1_table (id, a) VALUES (55, 0) RETURNING *;
88+
ERROR: no suitable partition for key '55'
89+
/* Finally drop partitions */
3490
SET ROLE user1;
35-
SELECT drop_partitions('test1_table');
36-
ERROR: relation "test1_table" does not exist at character 24
91+
SELECT drop_partitions('permissions.user1_table');
92+
NOTICE: function permissions.user1_table_upd_trig_func() does not exist, skipping
93+
NOTICE: 10 rows copied from permissions.user1_table_1
94+
NOTICE: 10 rows copied from permissions.user1_table_2
95+
NOTICE: 0 rows copied from permissions.user1_table_3
96+
NOTICE: 2 rows copied from permissions.user1_table_4
97+
drop_partitions
98+
-----------------
99+
4
100+
(1 row)
101+
102+
/* Switch to #2 */
37103
SET ROLE user2;
38104
/* Test ddl event trigger */
39-
CREATE TABLE user2_table(id serial);
40-
SELECT create_hash_partitions('user2_table', 'id', 3);
105+
CREATE TABLE permissions.user2_table(id serial);
106+
SELECT create_hash_partitions('permissions.user2_table', 'id', 3);
41107
create_hash_partitions
42108
------------------------
43109
3
44110
(1 row)
45111

46-
INSERT INTO user2_table SELECT generate_series(1, 30);
47-
SELECT drop_partitions('user2_table');
48-
NOTICE: function public.user2_table_upd_trig_func() does not exist, skipping
49-
NOTICE: 9 rows copied from user2_table_0
50-
NOTICE: 11 rows copied from user2_table_1
51-
NOTICE: 10 rows copied from user2_table_2
112+
INSERT INTO permissions.user2_table SELECT generate_series(1, 30);
113+
SELECT drop_partitions('permissions.user2_table');
114+
NOTICE: function permissions.user2_table_upd_trig_func() does not exist, skipping
115+
NOTICE: 9 rows copied from permissions.user2_table_0
116+
NOTICE: 11 rows copied from permissions.user2_table_1
117+
NOTICE: 10 rows copied from permissions.user2_table_2
52118
drop_partitions
53119
-----------------
54120
3
55121
(1 row)
56122

123+
/* Finally reset user */
57124
RESET ROLE;
58125
DROP OWNED BY user1;
59126
DROP OWNED BY user2;
60127
DROP USER user1;
61128
DROP USER user2;
129+
DROP SCHEMA permissions CASCADE;
130+
DROP EXTENSION pg_pathman;

‎sql/pathman_permissions.sql

Lines changed: 60 additions & 22 deletions
Original file line numberDiff line numberDiff line change
@@ -1,53 +1,91 @@
11
\set VERBOSITY terse
22

33
CREATE EXTENSION pg_pathman;
4+
CREATE SCHEMA permissions;
5+
46
CREATE ROLE user1 LOGIN;
57
CREATE ROLE user2 LOGIN;
68

7-
SET ROLE user1;
9+
GRANT USAGE, CREATE ON SCHEMA permissions TO user1;
10+
GRANT USAGE, CREATE ON SCHEMA permissions TO user2;
811

9-
CREATE TABLE user1_table(id serial, a int);
10-
INSERT INTO user1_table SELECT g, g FROM generate_series(1, 20) as g;
12+
ALTER DEFAULT PRIVILEGES FOR ROLE user1
13+
IN SCHEMA permissions
14+
GRANT SELECT, INSERT ON TABLES
15+
TO user2;
1116

12-
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO public;
17+
/* Switch to #1 */
18+
SET ROLE user1;
19+
CREATE TABLE permissions.user1_table(id serial, a int);
20+
INSERT INTO permissions.user1_table SELECT g, g FROM generate_series(1, 20) as g;
1321

22+
/* Should fail */
1423
SET ROLE user2;
24+
SELECT create_range_partitions('permissions.user1_table', 'id', 1, 10, 2);
1525

16-
/* Not owner and not superuser cannot create partitions */
17-
SELECT create_range_partitions('user1_table', 'id', 1, 10, 2);
18-
26+
/* Should be ok */
1927
SET ROLE user1;
28+
SELECT create_range_partitions('permissions.user1_table', 'id', 1, 10, 2);
2029

21-
/* Owner can */
22-
SELECT create_range_partitions('user1_table', 'id', 1, 10, 2);
30+
/* Should be able to see */
31+
SET ROLE user2;
32+
SELECT * FROM pathman_config;
33+
SELECT * FROM pathman_config_params;
2334

35+
/* Should fail */
2436
SET ROLE user2;
37+
SELECT set_enable_parent('permissions.user1_table', true);
38+
SELECT set_auto('permissions.user1_table', false);
2539

26-
/* Try to change partitioning parameters for user1_table */
27-
SELECT set_enable_parent('user1_table', true);
28-
SELECT set_auto('user1_table', false);
40+
/* Should fail */
41+
SET ROLE user2;
42+
DELETE FROM pathman_config
43+
WHERE partrel = 'permissions.user1_table'::regclass;
2944

30-
/*
31-
* Check that user is able to propagate partitions by inserting rows that
32-
* doesn't fit into range
33-
*/
34-
INSERT INTO user1_table (id, a) VALUES (25, 0);
45+
/* No rights to insert, should fail */
46+
SET ROLE user2;
47+
INSERT INTO permissions.user1_table (id, a) VALUES (35, 0);
3548

49+
/* Have rights, should be ok (bgw connects as user1) */
3650
SET ROLE user1;
51+
GRANT INSERT ON permissions.user1_table TO user2;
52+
SET ROLE user2;
53+
INSERT INTO permissions.user1_table (id, a) VALUES (35, 0) RETURNING *;
54+
SELECT relacl FROM pg_class WHERE oid = 'permissions.user1_table_4'::regclass;
3755

38-
SELECT drop_partitions('test1_table');
56+
/* Try to drop partition, should fail */
57+
SELECT drop_range_partition('permissions.user1_table_4');
3958

59+
/* Disable automatic partition creation */
60+
SET ROLE user1;
61+
SELECT set_auto('permissions.user1_table', false);
62+
63+
/* Partition creation, should fail */
4064
SET ROLE user2;
65+
INSERT INTO permissions.user1_table (id, a) VALUES (55, 0) RETURNING *;
66+
67+
/* Finally drop partitions */
68+
SET ROLE user1;
69+
SELECT drop_partitions('permissions.user1_table');
70+
4171

72+
/* Switch to #2 */
73+
SET ROLE user2;
4274
/* Test ddl event trigger */
43-
CREATE TABLE user2_table(id serial);
44-
SELECT create_hash_partitions('user2_table', 'id', 3);
45-
INSERT INTO user2_table SELECT generate_series(1, 30);
46-
SELECT drop_partitions('user2_table');
75+
CREATE TABLE permissions.user2_table(id serial);
76+
SELECT create_hash_partitions('permissions.user2_table', 'id', 3);
77+
INSERT INTO permissions.user2_table SELECT generate_series(1, 30);
78+
SELECT drop_partitions('permissions.user2_table');
79+
4780

81+
/* Finally reset user */
4882
RESET ROLE;
4983

5084
DROP OWNED BY user1;
5185
DROP OWNED BY user2;
5286
DROP USER user1;
5387
DROP USER user2;
88+
89+
90+
DROP SCHEMA permissions CASCADE;
91+
DROP EXTENSION pg_pathman;

0 commit comments

Comments
 (0)