Skip to content

Commit d124952

Browse files
funbringerfunbringer
committed
implement function copy_acl_privileges(), improved test 'pathman_permissions', execute create_or_replace_sequence() after the config row has been inserted into PATHMAN_CONFIG, allow partition creation to users with INSERT privilege
1 parent 984f2f4 commit d124952

File tree

5 files changed

+220
-38
lines changed

5 files changed

+220
-38
lines changed

‎expected/pathman_permissions.out

Lines changed: 39 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -5,18 +5,20 @@ CREATE ROLE user1 LOGIN;
55
CREATE ROLE user2 LOGIN;
66
GRANT USAGE, CREATE ON SCHEMA permissions TO user1;
77
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;
128
/* Switch to #1 */
139
SET ROLE user1;
1410
CREATE TABLE permissions.user1_table(id serial, a int);
1511
INSERT INTO permissions.user1_table SELECT g, g FROM generate_series(1, 20) as g;
16-
/* Should fail */
12+
/* Should fail (can't SELECT) */
13+
SET ROLE user2;
14+
SELECT create_range_partitions('permissions.user1_table', 'id', 1, 10, 2);
15+
ERROR: permission denied for relation user1_table
16+
/* Grant SELECT to user2 */
17+
SET ROLE user1;
18+
GRANT SELECT ON permissions.user1_table TO user2;
19+
/* Should fail (don't own parent) */
1720
SET ROLE user2;
1821
SELECT create_range_partitions('permissions.user1_table', 'id', 1, 10, 2);
19-
NOTICE: sequence "user1_table_seq" does not exist, skipping
2022
WARNING: only the owner or superuser can change partitioning configuration of table "user1_table"
2123
ERROR: new row violates row-level security policy for table "pathman_config"
2224
/* Should be ok */
@@ -58,21 +60,42 @@ WARNING: only the owner or superuser can change partitioning configuration of t
5860
/* No rights to insert, should fail */
5961
SET ROLE user2;
6062
INSERT INTO permissions.user1_table (id, a) VALUES (35, 0);
61-
/* Have rights, should be ok (bgw connects as user1) */
63+
ERROR: permission denied for relation user1_table
64+
/* No rights to create partitions (need INSERT privilege) */
65+
SET ROLE user2;
66+
SELECT prepend_range_partition('permissions.user1_table');
67+
ERROR: permission denied for parent relation "user1_table"
68+
/* Allow user2 to create partitions */
6269
SET ROLE user1;
6370
GRANT INSERT ON permissions.user1_table TO user2;
71+
/* Should be able to prepend a partition */
72+
SET ROLE user2;
73+
SELECT prepend_range_partition('permissions.user1_table');
74+
prepend_range_partition
75+
---------------------------
76+
permissions.user1_table_4
77+
(1 row)
78+
79+
/* Have rights, should be ok (parent's ACL is shared by new children) */
6480
SET ROLE user2;
6581
INSERT INTO permissions.user1_table (id, a) VALUES (35, 0) RETURNING *;
6682
id | a
6783
----+---
6884
35 | 0
6985
(1 row)
7086

71-
SELECT relacl FROM pg_class WHERE oid = 'permissions.user1_table_4'::regclass;
72-
relacl
73-
--------------------------------------
74-
{user1=arwdDxt/user1,user2=ar/user1}
75-
(1 row)
87+
SELECT relname, relacl FROM pg_class
88+
WHERE oid = ANY (SELECT partition FROM pathman_partition_list
89+
WHERE parent = 'permissions.user1_table'::REGCLASS
90+
ORDER BY range_max::int DESC
91+
LIMIT 3)
92+
ORDER BY relname; /* we also check ACL for "user1_table_2" */
93+
relname | relacl
94+
---------------+--------------------------------------
95+
user1_table_2 | {user1=arwdDxt/user1,user2=r/user1}
96+
user1_table_5 | {user1=arwdDxt/user1,user2=ar/user1}
97+
user1_table_6 | {user1=arwdDxt/user1,user2=ar/user1}
98+
(3 rows)
7699

77100
/* Try to drop partition, should fail */
78101
SELECT drop_range_partition('permissions.user1_table_4');
@@ -95,11 +118,12 @@ SELECT drop_partitions('permissions.user1_table');
95118
NOTICE: function permissions.user1_table_upd_trig_func() does not exist, skipping
96119
NOTICE: 10 rows copied from permissions.user1_table_1
97120
NOTICE: 10 rows copied from permissions.user1_table_2
98-
NOTICE: 0 rows copied from permissions.user1_table_3
99-
NOTICE: 2 rows copied from permissions.user1_table_4
121+
NOTICE: 0 rows copied from permissions.user1_table_4
122+
NOTICE: 0 rows copied from permissions.user1_table_5
123+
NOTICE: 1 rows copied from permissions.user1_table_6
100124
drop_partitions
101125
-----------------
102-
4
126+
5
103127
(1 row)
104128

105129
/* Switch to #2 */

‎range.sql

Lines changed: 16 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -138,14 +138,14 @@ BEGIN
138138
v_atttype::TEXT);
139139
END IF;
140140

141-
/* Create sequence for child partitions names */
142-
PERFORM @extschema@.create_or_replace_sequence(parent_relid)
143-
FROM @extschema@.get_plain_schema_and_relname(parent_relid);
144-
145141
/* Insert new entry to pathman config */
146142
INSERT INTO @extschema@.pathman_config (partrel, attname, parttype, range_interval)
147143
VALUES (parent_relid, attribute, 2, p_interval::TEXT);
148144

145+
/* Create sequence for child partitions names */
146+
PERFORM @extschema@.create_or_replace_sequence(parent_relid)
147+
FROM @extschema@.get_plain_schema_and_relname(parent_relid);
148+
149149
/* Create first partition */
150150
FOR i IN 1..p_count
151151
LOOP
@@ -253,14 +253,14 @@ BEGIN
253253
end_value);
254254
END IF;
255255

256-
/* Create sequence for child partitions names */
257-
PERFORM @extschema@.create_or_replace_sequence(parent_relid)
258-
FROM @extschema@.get_plain_schema_and_relname(parent_relid);
259-
260256
/* Insert new entry to pathman config */
261257
INSERT INTO @extschema@.pathman_config (partrel, attname, parttype, range_interval)
262258
VALUES (parent_relid, attribute, 2, p_interval::TEXT);
263259

260+
/* Create sequence for child partitions names */
261+
PERFORM @extschema@.create_or_replace_sequence(parent_relid)
262+
FROM @extschema@.get_plain_schema_and_relname(parent_relid);
263+
264264
/* create first partition */
265265
FOR i IN 1..p_count
266266
LOOP
@@ -327,14 +327,14 @@ BEGIN
327327
start_value,
328328
end_value);
329329

330-
/* Create sequence for child partitions names */
331-
PERFORM @extschema@.create_or_replace_sequence(parent_relid)
332-
FROM @extschema@.get_plain_schema_and_relname(parent_relid);
333-
334330
/* Insert new entry to pathman config */
335331
INSERT INTO @extschema@.pathman_config (partrel, attname, parttype, range_interval)
336332
VALUES (parent_relid, attribute, 2, p_interval::TEXT);
337333

334+
/* Create sequence for child partitions names */
335+
PERFORM @extschema@.create_or_replace_sequence(parent_relid)
336+
FROM @extschema@.get_plain_schema_and_relname(parent_relid);
337+
338338
WHILE start_value <= end_value
339339
LOOP
340340
PERFORM @extschema@.create_single_range_partition(
@@ -397,14 +397,14 @@ BEGIN
397397
start_value,
398398
end_value);
399399

400-
/* Create sequence for child partitions names */
401-
PERFORM @extschema@.create_or_replace_sequence(parent_relid)
402-
FROM @extschema@.get_plain_schema_and_relname(parent_relid);
403-
404400
/* Insert new entry to pathman config */
405401
INSERT INTO @extschema@.pathman_config (partrel, attname, parttype, range_interval)
406402
VALUES (parent_relid, attribute, 2, p_interval::TEXT);
407403

404+
/* Create sequence for child partitions names */
405+
PERFORM @extschema@.create_or_replace_sequence(parent_relid)
406+
FROM @extschema@.get_plain_schema_and_relname(parent_relid);
407+
408408
WHILE start_value <= end_value
409409
LOOP
410410
EXECUTE

‎sql/pathman_permissions.sql

Lines changed: 26 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -9,17 +9,21 @@ CREATE ROLE user2 LOGIN;
99
GRANT USAGE, CREATE ON SCHEMA permissions TO user1;
1010
GRANT USAGE, CREATE ON SCHEMA permissions TO user2;
1111

12-
ALTER DEFAULT PRIVILEGES FOR ROLE user1
13-
IN SCHEMA permissions
14-
GRANT SELECT, INSERT ON TABLES
15-
TO user2;
1612

1713
/* Switch to #1 */
1814
SET ROLE user1;
1915
CREATE TABLE permissions.user1_table(id serial, a int);
2016
INSERT INTO permissions.user1_table SELECT g, g FROM generate_series(1, 20) as g;
2117

22-
/* Should fail */
18+
/* Should fail (can't SELECT) */
19+
SET ROLE user2;
20+
SELECT create_range_partitions('permissions.user1_table', 'id', 1, 10, 2);
21+
22+
/* Grant SELECT to user2 */
23+
SET ROLE user1;
24+
GRANT SELECT ON permissions.user1_table TO user2;
25+
26+
/* Should fail (don't own parent) */
2327
SET ROLE user2;
2428
SELECT create_range_partitions('permissions.user1_table', 'id', 1, 10, 2);
2529

@@ -46,12 +50,27 @@ WHERE partrel = 'permissions.user1_table'::regclass;
4650
SET ROLE user2;
4751
INSERT INTO permissions.user1_table (id, a) VALUES (35, 0);
4852

49-
/* Have rights, should be ok (bgw connects as user1) */
53+
/* No rights to create partitions (need INSERT privilege) */
54+
SET ROLE user2;
55+
SELECT prepend_range_partition('permissions.user1_table');
56+
57+
/* Allow user2 to create partitions */
5058
SET ROLE user1;
5159
GRANT INSERT ON permissions.user1_table TO user2;
60+
61+
/* Should be able to prepend a partition */
62+
SET ROLE user2;
63+
SELECT prepend_range_partition('permissions.user1_table');
64+
65+
/* Have rights, should be ok (parent's ACL is shared by new children) */
5266
SET ROLE user2;
5367
INSERT INTO permissions.user1_table (id, a) VALUES (35, 0) RETURNING *;
54-
SELECT relacl FROM pg_class WHERE oid = 'permissions.user1_table_4'::regclass;
68+
SELECT relname, relacl FROM pg_class
69+
WHERE oid = ANY (SELECT partition FROM pathman_partition_list
70+
WHERE parent = 'permissions.user1_table'::REGCLASS
71+
ORDER BY range_max::int DESC
72+
LIMIT 3)
73+
ORDER BY relname; /* we also check ACL for "user1_table_2" */
5574

5675
/* Try to drop partition, should fail */
5776
SELECT drop_range_partition('permissions.user1_table_4');

0 commit comments

Comments
 (0)