Re: Partitioned tables vs GRANT

On Fri, Apr 7, 2017 at 2:05 PM, Joe Conway <mail(at)joeconway(dot)com> wrote:

> Apparently INSERT and SELECT on the parent partitioned table skip normal
> acl checks on the partitions. Is that intended behavior?
>
> 8<---------------------------
> test=# create user part_test;
> CREATE ROLE
> test=#
> test=# create table t1 (id int) partition by range ((id % 4));
> CREATE TABLE
> test=# create table t1_0 partition of t1 for values from (0) to (1);
> CREATE TABLE
> test=# create table t1_1 partition of t1 for values from (1) to (2);
> CREATE TABLE
> test=# create table t1_2 partition of t1 for values from (2) to (3);
> CREATE TABLE
> test=# create table t1_3 partition of t1 for values from (3) to (4);
> CREATE TABLE
> test=# grant all on TABLE t1 to part_test;
> GRANT
> test=# set session authorization part_test ;
> SET
> test=> select current_user;
> current_user
> --------------
> part_test
> (1 row)
>
> test=> insert into t1 values(0),(1),(2),(3);
> INSERT 0 4
> test=> insert into t1_0 values(0);
> ERROR: permission denied for relation t1_0
> test=> insert into t1_1 values(1);
> ERROR: permission denied for relation t1_1
> test=> insert into t1_2 values(2);
> ERROR: permission denied for relation t1_2
> test=> insert into t1_3 values(3);
> ERROR: permission denied for relation t1_3
> test=> select * from t1;
> id
> ----
> 0
> 1
> 2
> 3
> (4 rows)
>
> test=> select * from t1_0;
> ERROR: permission denied for relation t1_0
> test=> select * from t1_1;
> ERROR: permission denied for relation t1_1
> test=> select * from t1_2;
> ERROR: permission denied for relation t1_2
> test=> select * from t1_3;
> ERROR: permission denied for relation t1_3
> test=> reset session authorization;
> RESET
> test=# drop table if exists t1;
> DROP TABLE
> 8<---------------------------
>
> Joe
>
> --
> Crunchy Data - http://crunchydata.com
> PostgreSQL Support for Secure Enterprises
> Consulting, Training, & Open Source Development
>
>
I encountered that as well testing for native in pg_partman. I had to
include the code for non-native that propagates ownership/privileges from
the parent to the child.
Another question to ask is that if you change privileges on the parent,
does that automatically change them for all children as well? I encountered
this being a rather expensive operation using plpgsql methods to fix it
when the child count grows high. That's why I have resetting all child
table privileges as a separate, manual function and changes only apply to
new partitions automatically. Hopefully internally there's a more efficient
way.

Keith