Re: replacing role-level NOINHERIT with a grant-level option

On Sat, Jul 9, 2022 at 1:27 AM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Tue, Jul 5, 2022 at 8:04 AM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> > On Sun, Jul 3, 2022 at 1:17 PM Nathan Bossart <nathandbossart(at)gmail(dot)com>
> wrote:
> > > If by "bolder" you mean "mark [NO]INHERIT as
> deprecated-and-to-be-removed
> > > and begin emitting WARNINGs when it and WITH INHERIT DEFAULT are
> used," I
> > > think it's worth consideration. I suspect it will be hard to sell
> removing
> > > [NO]INHERIT in v16 because it would introduce a compatibility break
> without
> > > giving users much time to migrate. I could be wrong, though.
> >
> > It's a fair point. But, if our goal for v16 is to do something that
> > could lead to an eventual deprecation of [NO]INHERIT, I still think
> > removing WITH INHERIT DEFAULT from the patch set is probably a good
> > idea.
>
> So here is an updated patch with that change.
>
>
Thanks, Robert, I created a few objects with different privileges on v14.4
e.g

postgres=# \dp+ atest2

Access privileges

Schema | Name | Type | Access privileges |
Column privileges | Policies

--------+--------+-------+-----------------------------------------------+-------------------+----------

public | atest2 | table | regress_priv_user1=arwdDxt/regress_priv_user1+|
|

| | | regress_priv_user2=r/regress_priv_user1 +|
|

| | | regress_priv_user3=w/regress_priv_user1 +|
|

| | | regress_priv_user4=a/regress_priv_user1 +|
|

| | | regress_priv_user5=D/regress_priv_user1 |
|

(1 row)

and found that after pg_upgrade there is no change on privileges on
v16(w/patch)

One scenario where the syntax is created in pg_dumpall is wrong

postgres=# create user u1;

CREATE ROLE

postgres=# create group g1 with user u1;

CREATE ROLE

postgres=# grant g1 to u1 with admin option, inherit false;

GRANT ROLE

postgres=#

Perform pg_dumpall

This is the syntax coming

"

-- Role memberships

--

GRANT g1 TO u1 WITH ADMIN OPTION WITH INHERIT FALSE GRANTED BY edb;

"

If we run this syntax on psql, there is an error.

postgres=# GRANT g1 TO u1 WITH ADMIN OPTION WITH INHERIT FALSE GRANTED BY
edb;

ERROR: syntax error at or near "WITH"

regards,