Partitioned table permission question

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?

Responses

Browse pgsql-hackers by date

  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