Partitioned tables vs GRANT

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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.