PostgreSQL - Why a privileged user can't access newly created partition

Question

Why a privileged user can't access newly created partition?

PostgreSQL version: 10.0

Suppose my PostgreSQL sever has a user called app with following permissions:

GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO app;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public to app;

Now this user app can do select, insert and update action for "existing" table/partition (for example: mytable_partition_old) just as my expectation, everything goes well so far.

However, a master account creates a new partition of a table after the GRANT command above by following:

CREATE TABLE IF NOT EXISTS "mytable_partition_new" PARTITION OF mytable FOR VALUES IN('some_value');

After mytable_partition_new is created, the user app got "permission denied for this relation" by executing INSERT INTO mytable_partition_new values (...) command.

I understand it can be resolved by issue GRANT SELECT, .... TO app again. My question is if there any better way to achieve it?

(we don't have a dedicated DBA and got stucked in this situation for a while..)

score 3 · Accepted Answer · 2020-03-05T15:11:15.167

3

The GRANTs you have shown, only granted the privileges for existing objects. To grant the privileges for "future" objects, you need to alter the default privileges:

alter default privileges in schema public
  for role master
  GRANT SELECT, INSERT, UPDATE ON TABLES TO app;

alter default privileges in schema public
  for role master
  GRANT USAGE ON SEQUENCES TO app;

The above will only affect future objects, so for the tables (or partitions) you have already created, you need to re-run you original GRANT statements once again.

edited Mar 05 '20 at 15:11

answered Mar 05 '20 at 14:47

Thank you! So if I run both `GRANT ... TO app` and the statements you provide **in the first time**, then I got both *existing* and *future* objects for `app`, am I correct? – MengCheng Wei Mar 05 '20 at 14:54
@LaurenzAlbe could you share what is the exactly command? (sorry for postgresql newbie – MengCheng Wei Mar 05 '20 at 15:08
@a_horse_with_no_name Yes, I don't want my account `app` has master role – MengCheng Wei Mar 05 '20 at 15:10
@LaurenzAlbe: thanks, added. I didn't see the master role in the question. – Mar 05 '20 at 15:11
what's the difference between your original answer (without `for role master`) and the new one (with `for role master`) @a_horse_with_no_name – MengCheng Wei Mar 05 '20 at 15:21
@MengChengWei: it applies all those grants for the master role, rather than for the current role – Mar 05 '20 at 15:22
Got it, so if I run those sql commands in master account, I can just omit `for role master`, correct? – MengCheng Wei Mar 05 '20 at 17:07

PostgreSQL - Why a privileged user can't access newly created partition

1 Answers1