Subject: Re: replacing role-level NOINHERIT with a grant-level option
To: Robert Haas <robertmhaas@gmail.com>
Cc: Nathan Bossart <nathandbossart@gmail.com>, Joe Conway
 <mail@joeconway.com>, Tom Lane <tgl@sss.pgh.pa.us>,
 "Bossart, Nathan" <bossartn@amazon.com>, Stephen Frost <sfrost@snowman.net>,
 PostgreSQL-development <pgsql-hackers@postgresql.org>
References: <20220701025830.GA369935@nathanxps13>
 <12e2f0cb-08ff-c65f-d31e-76cea1af70ec@joeconway.com>
 <CA+TgmoZRO3tO_w+ROHW6Xe1fmQfzVyPuZ5OtbYX40w3YBQkMwA@mail.gmail.com>
 <442d7887-645d-0e31-e971-7a8dc8952d2f@joeconway.com>
 <CA+Tgmoaw_k2jooV214S=2bOBDPPLo7Nnq27-UGp6MNTfk11x5g@mail.gmail.com>
 <CA+TgmoYbjr6F6V3zVp0p4Jjd8jZf9TuCV1iHZ8w-fw9NGanW9w@mail.gmail.com>
 <20220701211225.GA418166@nathanxps13>
 <CA+TgmoaS-RuSxbzRek9SUn-9ZoN=dSw1qXutkvxQegSuZUwYDA@mail.gmail.com>
 <20220702221635.GB1044460@nathanxps13>
 <CA+TgmoZCE8r93EfdJxGoLYbf9qa0nai33Y6cP16N-DXb1G941w@mail.gmail.com>
 <20220703171742.GB1048244@nathanxps13>
 <CA+TgmoZ90mLF6qogXuoyQYVoO7UcRfr1Mdnp=O+W10gNx_iObA@mail.gmail.com>
 <CA+Tgmoa5Sf4PiWrfxA=sGzDKg0Ojo3dADw=wAHOhR9dggV=RmQ@mail.gmail.com>
 <CAC6VRoZLfROLLsCOVkOSmPRwRS5h4Wyw_ntwaQ1K9s_HQp9Y+w@mail.gmail.com>
 <CA+Tgmobep7Zi1MCKhhUa1HnJZahf+Zj3aP7OoPAQzcAvEv56cw@mail.gmail.com>
From: tushar <tushar.ahuja@enterprisedb.com>
Message-ID: <8210e573-c861-fe45-302b-56d7352aae48@enterprisedb.com>
Date: Thu, 14 Jul 2022 20:23:37 +0530
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.14.0
MIME-Version: 1.0
In-Reply-To: 
 <CA+Tgmobep7Zi1MCKhhUa1HnJZahf+Zj3aP7OoPAQzcAvEv56cw@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Archived-At: 
 <https://www.postgresql.org/message-id/8210e573-c861-fe45-302b-56d7352aae48%40enterprisedb.com>
Precedence: bulk

On 7/11/22 11:01 PM, Robert Haas wrote:
> Oops. Here is a rebased version of v3 which aims to fix this bug.
I found one issue where pg_upgrade is failing

PG v14.4 , create these below objects

create user u1 with superuser;
create user u3;
create group g2 with user  u1;

now try to perform pg_upgrade from v16(w/patch), it is failing with 
these messages

[edb@centos7tushar bin]$ tail -10 
dc2/pg_upgrade_output.d/20220714T195919.494/log/pg_upgrade_utility.log
--
--
CREATE ROLE "u3";
CREATE ROLE
ALTER ROLE "u3" WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN 
NOREPLICATION NOBYPASSRLS;
ALTER ROLE
GRANT "g2" TO "u1" WITH  GRANTED BY "edb";
psql:dc2/pg_upgrade_output.d/20220714T195919.494/dump/pg_upgrade_dump_globals.sql:47: 
ERROR:  syntax error at or near "BY"
LINE 1: GRANT "g2" TO "u1" WITH  GRANTED BY "edb";
                                          ^
This issue is not reproducible on PG v16 (without patch).

-- 
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company