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.
