From: | Junfeng Yang <yjerome(at)vmware(dot)com> |
---|---|
To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Cc: | Zijie Ma <zijiem(at)vmware(dot)com> |
Subject: | Partitioned table permission question |
Date: | 2021-04-20 07:24:40 |
Message-ID: | DM6PR05MB54032E8C84DE1E4D5A55A9DECC489@DM6PR05MB5403.namprd05.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi hackers,
As I played with the partitioned table with GRANT, I found two questions.
Let's see an example:
CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
) PARTITION BY RANGE (logdate);
CREATE TABLE measurement_y2006m02 PARTITION OF measurement
FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');
CREATE TABLE measurement_y2006m03 PARTITION OF measurement
FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');
CREATE USER a;
GRANT SELECT ON measurement TO a;
GRANT INSERT ON measurement TO a;
I created a partitioned table with two leaf tables and only grant SELECT, INSERT on the root table to user a.
The first question is:
As a user a, since I don't have permission to read the leaf tables, but select from the root will return the leafs data successfully.
postgres=# set role a;
postgres=> explain select * from measurement_y2006m02;
ERROR: permission denied for table measurement_y2006m02
postgres=> explain select * from measurement;
QUERY PLAN
---------------------------------------------------------------------------------------------
Append (cost=0.00..75.50 rows=3700 width=16)
-> Seq Scan on measurement_y2006m02 measurement_1 (cost=0.00..28.50 rows=1850 width=16)
-> Seq Scan on measurement_y2006m03 measurement_2 (cost=0.00..28.50 rows=1850 width=16)
(3 rows)
From the plan, we do scan on the leaf tables without ACL check. And the reason is in expand_single_inheritance_child,
we always set childrte->requiredPerms = 0; Seems like we always think the child has the same permission with the partitioned table.
For the second question:
As a user a, I'm not allowed to insert any data into leaf tables.
But insert on the partitioned table will make the data go into leaves.
postgres=> insert into measurement_y2006m02 values (1, '2006-02-01', 1, 1);
ERROR: permission denied for table measurement_y2006m02
postgres=> insert into measurement values (1, '2006-02-01', 1, 1);
INSERT 0 1
It makes me feel strange, we can grant different permission for partition tables, but as long as the user
has permission on the partitioned table, it can still see/modify the leaf tables which don't have permission.
Can anyone help me understand the behavior?
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2021-04-20 07:37:32 | Re: pg_amcheck option to install extension |
Previous Message | Michael Paquier | 2021-04-20 07:21:57 | Re: Table refer leak in logical replication |