Re: Partitioned table permission question

On Tue, Apr 20, 2021 at 9:00 PM Junfeng Yang <yjerome(at)vmware(dot)com> wrote:
> 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?

Permission model of partitioning is same as traditional table
inheritance, about which we write the following in the documentation
[1]:

"Inherited queries perform access permission checks on the parent
table only. Thus, for example, granting UPDATE permission on the
cities table implies permission to update rows in the capitals table
as well, when they are accessed through cities. This preserves the
appearance that the data is (also) in the parent table. But the
capitals table could not be updated directly without an additional
grant. In a similar way, the parent table's row security policies (see
Section 5.8) are applied to rows coming from child tables during an
inherited query. A child table's policies, if any, are applied only
when it is the table explicitly named in the query; and in that case,
any policies attached to its parent(s) are ignored."

--
Amit Langote
EDB: http://www.enterprisedb.com

[1] https://www.postgresql.org/docs/current/ddl-inherit.html