diff options
Diffstat (limited to 'src/bin/pg_dump/dumputils.c')
| -rw-r--r-- | src/bin/pg_dump/dumputils.c | 51 |
1 files changed, 36 insertions, 15 deletions
diff --git a/src/bin/pg_dump/dumputils.c b/src/bin/pg_dump/dumputils.c index dfc611848b0..e4c95feb63f 100644 --- a/src/bin/pg_dump/dumputils.c +++ b/src/bin/pg_dump/dumputils.c @@ -722,21 +722,36 @@ buildACLQueries(PQExpBuffer acl_subquery, PQExpBuffer racl_subquery, * We always perform this delta on all ACLs and expect that by the time * these are run the initial privileges will be in place, even in a binary * upgrade situation (see below). + * + * Finally, the order in which privileges are in the ACL string (the order + * they 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. */ - printfPQExpBuffer(acl_subquery, "(SELECT pg_catalog.array_agg(acl) FROM " - "(SELECT pg_catalog.unnest(coalesce(%s,pg_catalog.acldefault(%s,%s))) AS acl " - "EXCEPT " - "SELECT pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault(%s,%s)))) as foo)", + printfPQExpBuffer(acl_subquery, + "(SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM " + "(SELECT acl, row_n FROM " + "pg_catalog.unnest(coalesce(%s,pg_catalog.acldefault(%s,%s))) " + "WITH ORDINALITY AS perm(acl,row_n) " + "WHERE NOT EXISTS ( " + "SELECT 1 FROM " + "pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault(%s,%s))) " + "AS init(init_acl) WHERE acl = init_acl)) as foo)", acl_column, obj_kind, acl_owner, obj_kind, acl_owner); - printfPQExpBuffer(racl_subquery, "(SELECT pg_catalog.array_agg(acl) FROM " - "(SELECT pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault(%s,%s))) AS acl " - "EXCEPT " - "SELECT pg_catalog.unnest(coalesce(%s,pg_catalog.acldefault(%s,%s)))) as foo)", + printfPQExpBuffer(racl_subquery, + "(SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM " + "(SELECT acl, row_n FROM " + "pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault(%s,%s))) " + "WITH ORDINALITY AS initp(acl,row_n) " + "WHERE NOT EXISTS ( " + "SELECT 1 FROM " + "pg_catalog.unnest(coalesce(%s,pg_catalog.acldefault(%s,%s))) " + "AS permp(orig_acl) WHERE acl = orig_acl)) as foo)", obj_kind, acl_owner, acl_column, @@ -761,19 +776,25 @@ buildACLQueries(PQExpBuffer acl_subquery, PQExpBuffer racl_subquery, { printfPQExpBuffer(init_acl_subquery, "CASE WHEN privtype = 'e' THEN " - "(SELECT pg_catalog.array_agg(acl) FROM " - "(SELECT pg_catalog.unnest(pip.initprivs) AS acl " - "EXCEPT " - "SELECT pg_catalog.unnest(pg_catalog.acldefault(%s,%s))) as foo) END", + "(SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM " + "(SELECT acl, row_n FROM pg_catalog.unnest(pip.initprivs) " + "WITH ORDINALITY AS initp(acl,row_n) " + "WHERE NOT EXISTS ( " + "SELECT 1 FROM " + "pg_catalog.unnest(pg_catalog.acldefault(%s,%s)) " + "AS privm(orig_acl) WHERE acl = orig_acl)) as foo) END", obj_kind, acl_owner); printfPQExpBuffer(init_racl_subquery, "CASE WHEN privtype = 'e' THEN " "(SELECT pg_catalog.array_agg(acl) FROM " - "(SELECT pg_catalog.unnest(pg_catalog.acldefault(%s,%s)) AS acl " - "EXCEPT " - "SELECT pg_catalog.unnest(pip.initprivs)) as foo) END", + "(SELECT acl, row_n FROM " + "pg_catalog.unnest(pg_catalog.acldefault(%s,%s)) " + "WITH ORDINALITY AS privp(acl,row_n) " + "WHERE NOT EXISTS ( " + "SELECT 1 FROM pg_catalog.unnest(pip.initprivs) " + "AS initp(init_acl) WHERE acl = init_acl)) as foo) END", obj_kind, acl_owner); } |