Movatterモバイル変換


[0]ホーム

URL:


Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

Sign up
Appearance settings

Commitd124952

Browse files
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 parent984f2f4 commitd124952

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 iIN1..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 iIN1..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, CREATEON SCHEMA permissions TO user1;
1010
GRANT USAGE, CREATEON SCHEMA permissions TO user2;
1111

12-
ALTER DEFAULT PRIVILEGES FOR ROLE user1
13-
IN SCHEMA permissions
14-
GRANTSELECT, INSERTON TABLES
15-
TO user2;
1612

1713
/* Switch to #1*/
1814
SET ROLE user1;
1915
CREATETABLEpermissions.user1_table(idserial, aint);
2016
INSERT INTOpermissions.user1_tableSELECT g, gFROM 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+
GRANTSELECTONpermissions.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 INTOpermissions.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 INSERTONpermissions.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 INTOpermissions.user1_table (id, a)VALUES (35,0) RETURNING*;
54-
SELECT relaclFROM pg_classWHEREoid='permissions.user1_table_4'::regclass;
68+
SELECT relname, relaclFROM pg_class
69+
WHEREoid= ANY (SELECT partitionFROM pathman_partition_list
70+
WHERE parent='permissions.user1_table'::REGCLASS
71+
ORDER BY range_max::intDESC
72+
LIMIT3)
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)

[8]ページ先頭

©2009-2025 Movatter.jp