Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitcaae416

Browse files
committed
Fix ordering in pg_dump of GRANTs
The order in which GRANTs are output is important as GRANTs which havebeen GRANT'd by individuals via WITH GRANT OPTION GRANTs have to comeafter the GRANT which included the WITH GRANT OPTION. This happensnaturally in the backend during normal operation as we only changeexisting ACLs in-place, only add new ACLs to the end, and when removingan ACL we remove any which depend on it also.Also, adjust the comments in acl.h to make this clear.Unfortunately, the updates to pg_dump to handle initial privilegesinvolved pulling apart ACLs and then combining them back together andcould end up putting them back together in an invalid order, leading todumps which wouldn't restore.Fix this by adjusting the queries used by pg_dump to ensure that theACLs are rebuilt in the same order in which they were originally.Back-patch to 9.6 where the changes for initial privileges were done.
1 parent407e660 commitcaae416

File tree

2 files changed

+47
-18
lines changed

2 files changed

+47
-18
lines changed

‎src/bin/pg_dump/dumputils.c

Lines changed: 36 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -729,21 +729,36 @@ buildACLQueries(PQExpBuffer acl_subquery, PQExpBuffer racl_subquery,
729729
* We always perform this delta on all ACLs and expect that by the time
730730
* these are run the initial privileges will be in place, even in a binary
731731
* upgrade situation (see below).
732+
*
733+
* Finally, the order in which privileges are in the ACL string (the order
734+
* they been GRANT'd in, which the backend maintains) must be preserved to
735+
* ensure that GRANTs WITH GRANT OPTION and subsequent GRANTs based on
736+
* those are dumped in the correct order.
732737
*/
733-
printfPQExpBuffer(acl_subquery,"(SELECT pg_catalog.array_agg(acl) FROM "
734-
"(SELECT pg_catalog.unnest(coalesce(%s,pg_catalog.acldefault(%s,%s))) AS acl "
735-
"EXCEPT "
736-
"SELECT pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault(%s,%s)))) as foo)",
738+
printfPQExpBuffer(acl_subquery,
739+
"(SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM "
740+
"(SELECT acl, row_n FROM "
741+
"pg_catalog.unnest(coalesce(%s,pg_catalog.acldefault(%s,%s))) "
742+
"WITH ORDINALITY AS perm(acl,row_n) "
743+
"WHERE NOT EXISTS ( "
744+
"SELECT 1 FROM "
745+
"pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault(%s,%s))) "
746+
"AS init(init_acl) WHERE acl = init_acl)) as foo)",
737747
acl_column,
738748
obj_kind,
739749
acl_owner,
740750
obj_kind,
741751
acl_owner);
742752

743-
printfPQExpBuffer(racl_subquery,"(SELECT pg_catalog.array_agg(acl) FROM "
744-
"(SELECT pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault(%s,%s))) AS acl "
745-
"EXCEPT "
746-
"SELECT pg_catalog.unnest(coalesce(%s,pg_catalog.acldefault(%s,%s)))) as foo)",
753+
printfPQExpBuffer(racl_subquery,
754+
"(SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM "
755+
"(SELECT acl, row_n FROM "
756+
"pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault(%s,%s))) "
757+
"WITH ORDINALITY AS initp(acl,row_n) "
758+
"WHERE NOT EXISTS ( "
759+
"SELECT 1 FROM "
760+
"pg_catalog.unnest(coalesce(%s,pg_catalog.acldefault(%s,%s))) "
761+
"AS permp(orig_acl) WHERE acl = orig_acl)) as foo)",
747762
obj_kind,
748763
acl_owner,
749764
acl_column,
@@ -768,19 +783,25 @@ buildACLQueries(PQExpBuffer acl_subquery, PQExpBuffer racl_subquery,
768783
{
769784
printfPQExpBuffer(init_acl_subquery,
770785
"CASE WHEN privtype = 'e' THEN "
771-
"(SELECT pg_catalog.array_agg(acl) FROM "
772-
"(SELECT pg_catalog.unnest(pip.initprivs) AS acl "
773-
"EXCEPT "
774-
"SELECT pg_catalog.unnest(pg_catalog.acldefault(%s,%s))) as foo) END",
786+
"(SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM "
787+
"(SELECT acl, row_n FROM pg_catalog.unnest(pip.initprivs) "
788+
"WITH ORDINALITY AS initp(acl,row_n) "
789+
"WHERE NOT EXISTS ( "
790+
"SELECT 1 FROM "
791+
"pg_catalog.unnest(pg_catalog.acldefault(%s,%s)) "
792+
"AS privm(orig_acl) WHERE acl = orig_acl)) as foo) END",
775793
obj_kind,
776794
acl_owner);
777795

778796
printfPQExpBuffer(init_racl_subquery,
779797
"CASE WHEN privtype = 'e' THEN "
780798
"(SELECT pg_catalog.array_agg(acl) FROM "
781-
"(SELECT pg_catalog.unnest(pg_catalog.acldefault(%s,%s)) AS acl "
782-
"EXCEPT "
783-
"SELECT pg_catalog.unnest(pip.initprivs)) as foo) END",
799+
"(SELECT acl, row_n FROM "
800+
"pg_catalog.unnest(pg_catalog.acldefault(%s,%s)) "
801+
"WITH ORDINALITY AS privp(acl,row_n) "
802+
"WHERE NOT EXISTS ( "
803+
"SELECT 1 FROM pg_catalog.unnest(pip.initprivs) "
804+
"AS initp(init_acl) WHERE acl = init_acl)) as foo) END",
784805
obj_kind,
785806
acl_owner);
786807
}

‎src/include/utils/acl.h

Lines changed: 11 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -12,9 +12,17 @@
1212
* NOTES
1313
* An ACL array is simply an array of AclItems, representing the union
1414
* of the privileges represented by the individual items. A zero-length
15-
* array represents "no privileges". There are no assumptions about the
16-
* ordering of the items, but we do expect that there are no two entries
17-
* in the array with the same grantor and grantee.
15+
* array represents "no privileges".
16+
*
17+
* The order of items in the array is important as client utilities (in
18+
* particular, pg_dump, though possibly other clients) expect to be able
19+
* to issue GRANTs in the ordering of the items in the array. The reason
20+
* this matters is that GRANTs WITH GRANT OPTION must be before any GRANTs
21+
* which depend on it. This happens naturally in the backend during
22+
* operations as we update ACLs in-place, new items are appended, and
23+
* existing entries are only removed if there's no dependency on them (no
24+
* GRANT can been based on it, or, if there was, those GRANTs are also
25+
* removed).
1826
*
1927
* For backward-compatibility purposes we have to allow null ACL entries
2028
* in system catalogs. A null ACL will be treated as meaning "default

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp