On Fri, Apr 7, 2017 at 2:05 PM, Joe Conway <m...@joeconway.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
