From: Bossart, Nathan <bossartn@amazon.com>
To: Haribabu Kommi <kommi.haribabu@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly
Date: Fri, 3 May 2019 17:22:47 +0000
Message-ID: <4D066CB2-B177-40B4-8510-9318B9A184CE@amazon.com> (raw)
In-Reply-To: <CAJrrPGcsS_=bTuKvPOg6TEgkrc9PaEJVCFmhm=z_7VKpLnjxOg@mail.gmail.com>
References: <15788-4e18847520ebcc75@postgresql.org>
<9C9271CF-9A7A-45AE-B034-1246A93788C9@amazon.com>
<CAJrrPGcsS_=bTuKvPOg6TEgkrc9PaEJVCFmhm=z_7VKpLnjxOg@mail.gmail.com>
On 5/1/19, 9:10 PM, "Haribabu Kommi" <kommi.haribabu@gmail.com> wrote:
> On Thu, May 2, 2019 at 9:28 AM Bossart, Nathan <bossartn@amazon.com> wrote:
>> It looks like the bug reporting form destroyed the patch. I've
>> attached it again here.
>
> Thanks, for the patch. Just to make sure that we didn't miss, a similar problem
> can occur in v10 and v9.6 pg_dumpall. In v11, the database acl's dumping
> mechanism is moved from pg_dumpall to pg_dump.
>
> a similar change in pg_dumpall "dumpCreateDB" function works for 10 and 9.6.
Thanks for taking a look. I've attached all the versions of the patch
needed to back-patch this down to 9.6, where this query was
introduced.
Nathan
Attachments:
[application/octet-stream] v2-0001-Fix-ordering-of-GRANTs-for-databases-in-pg_dump-master-v11.patch (2.0K, ../4D066CB2-B177-40B4-8510-9318B9A184CE@amazon.com/2-v2-0001-Fix-ordering-of-GRANTs-for-databases-in-pg_dump-master-v11.patch)
download | inline diff:
From e27c83fea44bcbf2b9b6133351c301c0acf761fb Mon Sep 17 00:00:00 2001
From: Nathan Bossart <bossartn@amazon.com>
Date: Wed, 1 May 2019 23:04:04 +0000
Subject: [PATCH v2 1/1] Fix ordering of GRANTs for databases in pg_dump.
---
src/bin/pg_dump/pg_dump.c | 22 ++++++++++++++++------
1 file changed, 16 insertions(+), 6 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.cindex 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, "
--
2.16.5
[application/octet-stream] v2-0001-Fix-ordering-of-GRANTs-for-databases-in-pg_dump-v9.6.patch (2.1K, ../4D066CB2-B177-40B4-8510-9318B9A184CE@amazon.com/3-v2-0001-Fix-ordering-of-GRANTs-for-databases-in-pg_dump-v9.6.patch)
download | inline diff:
From 22426607acddee119ce3dacd3fa1b456ed88d4fa Mon Sep 17 00:00:00 2001
From: Nathan Bossart <bossartn@amazon.com>
Date: Thu, 2 May 2019 20:37:45 +0000
Subject: [PATCH v2 1/1] Fix ordering of GRANTs for databases in pg_dump.
---
src/bin/pg_dump/pg_dumpall.c | 20 ++++++++++++++++----
1 file changed, 16 insertions(+), 4 deletions(-)
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.cindex 66292354d7..edcc753d95 100644--- a/src/bin/pg_dump/pg_dumpall.c+++ b/src/bin/pg_dump/pg_dumpall.c@@ -1334,11 +1334,23 @@ dumpCreateDB(PGconn *conn)
"pg_encoding_to_char(d.encoding), "
"datcollate, datctype, datfrozenxid, datminmxid, "
"datistemplate, "
- "(SELECT pg_catalog.array_agg(acl) FROM (SELECT pg_catalog.unnest(coalesce(datacl,pg_catalog.acldefault('d',datdba))) AS acl "- "EXCEPT SELECT pg_catalog.unnest(pg_catalog.acldefault('d',datdba))) as foo)"+ "(SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM "+ "(SELECT acl, row_n FROM "+ "pg_catalog.unnest(coalesce(datacl,pg_catalog.acldefault('d',datdba))) "+ "WITH ORDINALITY AS perm(acl,row_n) "+ "WHERE NOT EXISTS ( "+ "SELECT 1 FROM "+ "pg_catalog.unnest(pg_catalog.acldefault('d',datdba)) "+ "AS init(init_acl) WHERE acl = init_acl)) as datacls) "
"AS datacl, "
- "(SELECT pg_catalog.array_agg(acl) FROM (SELECT pg_catalog.unnest(pg_catalog.acldefault('d',datdba)) AS acl "- "EXCEPT SELECT pg_catalog.unnest(coalesce(datacl,pg_catalog.acldefault('d',datdba)))) as foo)"+ "(SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM "+ "(SELECT acl, row_n FROM "+ "pg_catalog.unnest(pg_catalog.acldefault('d',datdba)) "+ "WITH ORDINALITY AS initp(acl,row_n) "+ "WHERE NOT EXISTS ( "+ "SELECT 1 FROM "+ "pg_catalog.unnest(coalesce(datacl,pg_catalog.acldefault('d',datdba))) "+ "AS permp(orig_acl) WHERE acl = orig_acl)) as rdatacls) "
"AS rdatacl, "
"datconnlimit, "
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
--
2.16.5
[application/octet-stream] v2-0001-Fix-ordering-of-GRANTs-for-databases-in-pg_dump-v10.patch (2.2K, ../4D066CB2-B177-40B4-8510-9318B9A184CE@amazon.com/4-v2-0001-Fix-ordering-of-GRANTs-for-databases-in-pg_dump-v10.patch)
download | inline diff:
From a9e7df76dac949ab68103dd4d3d63ac4dcce2cc3 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <bossartn@amazon.com>
Date: Thu, 2 May 2019 20:37:45 +0000
Subject: [PATCH v2 1/1] Fix ordering of GRANTs for databases in pg_dump.
---
src/bin/pg_dump/pg_dumpall.c | 22 ++++++++++++++++------
1 file changed, 16 insertions(+), 6 deletions(-)
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.cindex 050d6b6cab..b445fdc9fc 100644--- a/src/bin/pg_dump/pg_dumpall.c+++ b/src/bin/pg_dump/pg_dumpall.c@@ -1359,13 +1359,23 @@ dumpCreateDB(PGconn *conn)
"pg_encoding_to_char(d.encoding), "
"datcollate, datctype, datfrozenxid, datminmxid, "
"datistemplate, "
- "(SELECT pg_catalog.array_agg(acl ORDER BY acl::text COLLATE \"C\") FROM ( "- " SELECT pg_catalog.unnest(coalesce(datacl,pg_catalog.acldefault('d',datdba))) AS acl "- " EXCEPT SELECT pg_catalog.unnest(pg_catalog.acldefault('d',datdba))) as datacls)"+ "(SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM "+ "(SELECT acl, row_n FROM "+ "pg_catalog.unnest(coalesce(datacl,pg_catalog.acldefault('d',datdba))) "+ "WITH ORDINALITY AS perm(acl,row_n) "+ "WHERE NOT EXISTS ( "+ "SELECT 1 FROM "+ "pg_catalog.unnest(pg_catalog.acldefault('d',datdba)) "+ "AS init(init_acl) WHERE acl = init_acl)) as datacls) "
"AS datacl, "
- "(SELECT pg_catalog.array_agg(acl ORDER BY acl::text COLLATE \"C\") FROM ( "- " SELECT pg_catalog.unnest(pg_catalog.acldefault('d',datdba)) AS acl "- " EXCEPT SELECT pg_catalog.unnest(coalesce(datacl,pg_catalog.acldefault('d',datdba)))) as rdatacls)"+ "(SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM "+ "(SELECT acl, row_n FROM "+ "pg_catalog.unnest(pg_catalog.acldefault('d',datdba)) "+ "WITH ORDINALITY AS initp(acl,row_n) "+ "WHERE NOT EXISTS ( "+ "SELECT 1 FROM "+ "pg_catalog.unnest(coalesce(datacl,pg_catalog.acldefault('d',datdba))) "+ "AS permp(orig_acl) WHERE acl = orig_acl)) as rdatacls) "
"AS rdatacl, "
"datconnlimit, "
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
--
2.16.5
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: bossartn@amazon.com, kommi.haribabu@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly
In-Reply-To: <4D066CB2-B177-40B4-8510-9318B9A184CE@amazon.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox