Re: [HACKERS] Partitioned tables vs GRANT
On Fri, Apr 7, 2017 at 8:41 PM, Tom Lane wrote: > Keith Fiske writes: > > On Fri, Apr 7, 2017 at 2:46 PM, Tom Lane wrote: > >> Joe Conway writes: > >>> Apparently INSERT and SELECT on the parent partitioned table skip > normal > >>> acl checks on the partitions. Is that intended behavior? > > >> Yes, this matches normal inheritance behavior. > > > Should that really be normal partitioning behavior though? > > Yes, it should. Consider the alternatives: > > 1. Owner must remember to run around and grant permissions on all child > tables along with the parent. > I'm not following. That's what Joe is saying is happening now. The child tables are not getting the parent privileges so this is what the owner must remember to do every time they add a new child if they want to role to be able to interact directly with the children. They can select, insert, etc with the parent, but any direct interaction with the child is denied. I know you're all trying to make the planner work so queries work efficiently from the parent, but they'll never be as good as being able to hit the child tables directly if they know where the data they want is. Why even leave the child tables visible at all they can't be interacted with the same as the parent? I thought that was supposed to be one of the advantages to doing partitioning this way vs how Oracle & MySQL do it. > 2. The system silently(?) doesn't show you some rows that are supposed > to be visible when scanning the parent table. > > If you want RLS, use RLS; this is not that, and is not a good substitute. > Agreed. It appears the rows are visible if the role has select privileges on the parent. But they cannot select directly from children. Not sure what this has to do with RLS. > > (We've been around on this topic before, btw. See the archives.) > > regards, tom lane >
Re: [HACKERS] Partitioned tables vs GRANT
Keith Fiske writes: > On Fri, Apr 7, 2017 at 2:46 PM, Tom Lane wrote: >> Joe Conway writes: >>> Apparently INSERT and SELECT on the parent partitioned table skip normal >>> acl checks on the partitions. Is that intended behavior? >> Yes, this matches normal inheritance behavior. > Should that really be normal partitioning behavior though? Yes, it should. Consider the alternatives: 1. Owner must remember to run around and grant permissions on all child tables along with the parent. 2. The system silently(?) doesn't show you some rows that are supposed to be visible when scanning the parent table. If you want RLS, use RLS; this is not that, and is not a good substitute. (We've been around on this topic before, btw. See the archives.) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Partitioned tables vs GRANT
On Fri, Apr 7, 2017 at 2:46 PM, Tom Lane wrote: > Joe Conway writes: > > Apparently INSERT and SELECT on the parent partitioned table skip normal > > acl checks on the partitions. Is that intended behavior? > > Yes, this matches normal inheritance behavior. > > regards, tom lane > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > Should that really be normal partitioning behavior though? Pretty sure people would expect child tables to have consistent permissions in a partition set and I'd think setting them on the parent should be what they expect the children to have. Keith
Re: [HACKERS] Partitioned tables vs GRANT
Joe Conway writes: > Apparently INSERT and SELECT on the parent partitioned table skip normal > acl checks on the partitions. Is that intended behavior? Yes, this matches normal inheritance behavior. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Partitioned tables vs GRANT
On Fri, Apr 7, 2017 at 2:05 PM, Joe Conway 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
