Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly

On Mon, May 06, 2019 at 04:08:47PM +0000, Bossart, Nathan wrote:
> Added here: https://commitfest.postgresql.org/23/2111/

Thanks for adding the patch to the CF.

With your patch attached the difference in the dump is plain:
REVOKE CONNECT,TEMPORARY ON DATABASE mydb FROM PUBLIC;
+GRANT TEMPORARY ON DATABASE mydb TO c_user WITH GRANT OPTION;
SET SESSION AUTHORIZATION c_user;
GRANT TEMPORARY ON DATABASE mydb TO a_user;
RESET SESSION AUTHORIZATION;
-GRANT TEMPORARY ON DATABASE mydb TO c_user WITH GRANT OPTION;

So what happens is that the GRANT command to a_user fails when
switching to the session context to c_user as this user does not have
yet the authorization to perform this command. If the GRANT
permissions assigned to c_user are moved prior its actual actions then
the restore is able to work. I have been looking at it, and wondered
first if we could have just used buildACLQueries(), until I noticed
that we don't support initial privileges on databases, so the patch
you have sent looks fine to me.

I had first a hard time parsing the subqueries added, so I have
tweaked your patch with more indentation, and a comment block with
more details about why we need to preserve the ACL ordering (you will
note that I don't have a lot of imagination here).

v12 beta1 is going to ship soon, so let's wait for the version to be
tagged before committing it.
--
Michael