diff options
| author | Michael Paquier <michael@paquier.xyz> | 2019-05-22 14:48:00 +0900 |
|---|---|---|
| committer | Michael Paquier <michael@paquier.xyz> | 2019-05-22 14:48:00 +0900 |
| commit | b8c6014a65ee0819e8fe4c7536f72da0df5d8e46 (patch) | |
| tree | b2ae1b425f772639891bfaa6eb4bdd4a35be64ac /src | |
| parent | 5eb4a51cb15166392ca3ede6ec58f8f1f820ef53 (diff) | |
| download | postgresql-b8c6014a65ee0819e8fe4c7536f72da0df5d8e46.tar.gz postgresql-b8c6014a65ee0819e8fe4c7536f72da0df5d8e46.zip | |
Fix ordering of GRANT commands in pg_dump for database creation
This uses a method similar to 68a7c24f, which guarantees that GRANT
commands using the WITH GRANT OPTION are dumped in a way so as cascading
dependencies are respected. As databases do not have support for
initial privileges via pg_init_privs, we need to repeat again the same
ACL reordering method.
ACL for databases have been moved from pg_dumpall to pg_dump in v11, so
this impacts pg_dump for v11 and above, and pg_dumpall for v9.6 and
v10.
Discussion: https://postgr.es/m/15788-4e18847520ebcc75@postgresql.org
Author: Nathan Bossart
Reviewed-by: Haribabu Kommi
Backpatch-through: 9.6
Diffstat (limited to 'src')
| -rw-r--r-- | src/bin/pg_dump/pg_dump.c | 35 |
1 files changed, 28 insertions, 7 deletions
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index a8f1ad73336..e8ce719a0a6 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -2664,20 +2664,41 @@ dumpDatabase(Archive *fout) pg_log_info("saving database definition"); - /* Fetch the database-level properties for this database */ + /* + * Fetch the database-level properties for this database. + * + * The order in which privileges are in the ACL string (the order they + * have been GRANT'd in, which the backend maintains) must be preserved to + * ensure that GRANTs WITH GRANT OPTION and subsequent GRANTs based on + * those are dumped in the correct order. Note that initial privileges + * (pg_init_privs) are not supported on databases, so this logic cannot + * make use of buildACLQueries(). + */ if (fout->remoteVersion >= 90600) { appendPQExpBuffer(dbQry, "SELECT tableoid, oid, datname, " "(%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, " |