Thread: [HACKERS] Partitioned tables vs GRANT
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
On Fri, Apr 7, 2017 at 2:05 PM, Joe Conway <mail@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
Joe Conway <mail@joeconway.com> 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
On Fri, Apr 7, 2017 at 2:46 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Joe Conway <mail@joeconway.com> 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
Keith Fiske <keith@omniti.com> writes:
> On Fri, Apr 7, 2017 at 2:46 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Joe Conway <mail@joeconway.com> 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
On Fri, Apr 7, 2017 at 8:41 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Keith Fiske <keith@omniti.com> writes:
> On Fri, Apr 7, 2017 at 2:46 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Joe Conway <mail@joeconway.com> 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