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
partitionedtable.
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?
