BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      15788
Logged by:          Nathan Bossart
Email address:      bossartn@amazon.com
PostgreSQL version: 11.2
Operating system:   Linux
Description:

Hello,

Currently, 'pg_dump --create' will generate database GRANTs in the
wrong order, which can lead to WARNINGs or ERRORs when attempting to
restore its output.  Here is a simple way to reproduce the issue:

  1. As a superuser, run the following SQL commands.

        CREATE ROLE a_user;
        CREATE ROLE b_user WITH CREATEROLE CREATEDB;
        CREATE ROLE c_user;
        SET SESSION AUTHORIZATION b_user;
        CREATE DATABASE mydb;

        \c mydb

        SET SESSION AUTHORIZATION b_user;
        REVOKE ALL 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;

  2. Then, execute the following pg_dump and psql commands.

        pg_dump mydb -C -s -f dump.sql
        psql postgres -c "DROP DATABASE mydb;"
        psql postgres -q -c "\\set ON_ERROR_STOP" -f dump.sql

The last psql command will fail with the following ERROR:

        ERROR:  permission denied for database mydb

I think the underlying issue is that the pg_dump query is sorting the
ACLs, which may not be the natural ordering.  I was able to fix this
by making a very similar change to 68a7c24f in dumpDatabase().

diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index db8ca40a78..28e78756a8 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -2672,13 +2672,23 @@ dumpDatabase(Archive *fout)
                                                  "(%s datdba) AS dba, "

"pg_encoding_to_char(encoding) AS encoding, "
                                                  "datcollate, datctype,
datfrozenxid, datminmxid, "
-                                                 "(SELECT array_agg(acl
ORDER BY acl::text COLLATE \"C\") FROM ( "
-                                                 "  SELECT
unnest(coalesce(datacl,acldefault('d',datdba))) AS acl "
-                                                 "  EXCEPT SELECT
unnest(acldefault('d',datdba))) as datacls)"
+                                                 "(SELECT array_agg(acl
ORDER BY row_n) FROM "
+                                                 "(SELECT acl, row_n FROM
"
+
"unnest(coalesce(datacl,acldefault('d',datdba))) "
+                                                 "WITH ORDINALITY AS
perm(acl,row_n) "
+                                                 "WHERE NOT EXISTS ( "
+                                                 "SELECT 1 FROM "
+
"unnest(acldefault('d',datdba)) "
+                                                 "AS init(init_acl) WHERE
acl = init_acl)) as datacls)"
                                                  " AS datacl, "
-                                                 "(SELECT array_agg(acl
ORDER BY acl::text COLLATE \"C\") FROM ( "
-                                                 "  SELECT
unnest(acldefault('d',datdba)) AS acl "
-                                                 "  EXCEPT SELECT
unnest(coalesce(datacl,acldefault('d',datdba)))) as rdatacls)"
+                                                 "(SELECT array_agg(acl
ORDER BY row_n) FROM "
+                                                 "(SELECT acl, row_n FROM
"
+
"unnest(acldefault('d',datdba)) "
+                                                 "WITH ORDINALITY AS
initp(acl,row_n) "
+                                                 "WHERE NOT EXISTS ( "
+                                                 "SELECT 1 FROM "
+
"unnest(coalesce(datacl,acldefault('d',datdba))) "
+                                                 "AS permp(orig_acl) WHERE
acl = orig_acl)) as rdatacls)"
                                                  " AS rdatacl, "
                                                  "datistemplate,
datconnlimit, "
                                                  "(SELECT spcname FROM
pg_tablespace t WHERE t.oid = dattablespace) AS tablespace, "

Nathan