pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #15788: 'pg_dump --create' orders database GRANTs incorrectly
10+ messages / 4 participants
[nested] [flat]
* BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly
@ 2019-05-01 23:18 PG Bug reporting form <noreply@postgresql.org>
2019-05-01 23:28 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Bossart, Nathan <bossartn@amazon.com>
0 siblings, 1 reply; 10+ messages in thread
From: PG Bug reporting form @ 2019-05-01 23:18 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: bossartn@amazon.com
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly
2019-05-01 23:18 BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly PG Bug reporting form <noreply@postgresql.org>
@ 2019-05-01 23:28 ` Bossart, Nathan <bossartn@amazon.com>
2019-05-02 04:08 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Haribabu Kommi <kommi.haribabu@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Bossart, Nathan @ 2019-05-01 23:28 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
It looks like the bug reporting form destroyed the patch. I've
attached it again here.
Nathan
Attachments:
[application/octet-stream] v1-0001-Fix-ordering-of-GRANTs-for-databases-in-pg_dump.patch (2.0K, ../../9C9271CF-9A7A-45AE-B034-1246A93788C9@amazon.com/2-v1-0001-Fix-ordering-of-GRANTs-for-databases-in-pg_dump.patch)
download | inline diff:
From 710647c541514ded39adbc8b100b53d0a0378407 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <bossartn@amazon.com>
Date: Wed, 1 May 2019 23:04:04 +0000
Subject: [PATCH v1 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.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, "
--
2.16.5
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly
2019-05-01 23:18 BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly PG Bug reporting form <noreply@postgresql.org>
2019-05-01 23:28 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Bossart, Nathan <bossartn@amazon.com>
@ 2019-05-02 04:08 ` Haribabu Kommi <kommi.haribabu@gmail.com>
2019-05-03 17:22 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Bossart, Nathan <bossartn@amazon.com>
0 siblings, 1 reply; 10+ messages in thread
From: Haribabu Kommi @ 2019-05-02 04:08 UTC (permalink / raw)
To: Bossart, Nathan <bossartn@amazon.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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.
Regards,
Haribabu Kommi
Fujitsu Australia
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly
2019-05-01 23:18 BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly PG Bug reporting form <noreply@postgresql.org>
2019-05-01 23:28 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Bossart, Nathan <bossartn@amazon.com>
2019-05-02 04:08 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Haribabu Kommi <kommi.haribabu@gmail.com>
@ 2019-05-03 17:22 ` Bossart, Nathan <bossartn@amazon.com>
2019-05-04 12:32 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 10+ messages in thread
From: Bossart, Nathan @ 2019-05-03 17:22 UTC (permalink / raw)
To: Haribabu Kommi <kommi.haribabu@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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.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, "
--
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.c
index 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.c
index 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly
2019-05-01 23:18 BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly PG Bug reporting form <noreply@postgresql.org>
2019-05-01 23:28 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Bossart, Nathan <bossartn@amazon.com>
2019-05-02 04:08 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Haribabu Kommi <kommi.haribabu@gmail.com>
2019-05-03 17:22 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Bossart, Nathan <bossartn@amazon.com>
@ 2019-05-04 12:32 ` Michael Paquier <michael@paquier.xyz>
2019-05-06 16:08 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Bossart, Nathan <bossartn@amazon.com>
0 siblings, 1 reply; 10+ messages in thread
From: Michael Paquier @ 2019-05-04 12:32 UTC (permalink / raw)
To: Bossart, Nathan <bossartn@amazon.com>; +Cc: Haribabu Kommi <kommi.haribabu@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Fri, May 03, 2019 at 05:22:47PM +0000, Bossart, Nathan wrote:
> 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.
This requires a careful lookup, and it may be too late for the next
round of minor releases. Could you add it to the next commit fest [1]
so as we don't forget about it?
[1]: https://commitfest.postgresql.org/23/
--
Michael
Attachments:
[application/pgp-signature] signature.asc (832B, ../../20190504123221.GA2127@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly
2019-05-01 23:18 BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly PG Bug reporting form <noreply@postgresql.org>
2019-05-01 23:28 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Bossart, Nathan <bossartn@amazon.com>
2019-05-02 04:08 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Haribabu Kommi <kommi.haribabu@gmail.com>
2019-05-03 17:22 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Bossart, Nathan <bossartn@amazon.com>
2019-05-04 12:32 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Michael Paquier <michael@paquier.xyz>
@ 2019-05-06 16:08 ` Bossart, Nathan <bossartn@amazon.com>
2019-05-20 08:27 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 10+ messages in thread
From: Bossart, Nathan @ 2019-05-06 16:08 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Haribabu Kommi <kommi.haribabu@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On 5/4/19, 5:33 AM, "Michael Paquier" <michael@paquier.xyz> wrote:
> On Fri, May 03, 2019 at 05:22:47PM +0000, Bossart, Nathan wrote:
>> 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.
>
> This requires a careful lookup, and it may be too late for the next
> round of minor releases. Could you add it to the next commit fest [1]
> so as we don't forget about it?
Added here: https://commitfest.postgresql.org/23/2111/
Nathan
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly
2019-05-01 23:18 BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly PG Bug reporting form <noreply@postgresql.org>
2019-05-01 23:28 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Bossart, Nathan <bossartn@amazon.com>
2019-05-02 04:08 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Haribabu Kommi <kommi.haribabu@gmail.com>
2019-05-03 17:22 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Bossart, Nathan <bossartn@amazon.com>
2019-05-04 12:32 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Michael Paquier <michael@paquier.xyz>
2019-05-06 16:08 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Bossart, Nathan <bossartn@amazon.com>
@ 2019-05-20 08:27 ` Michael Paquier <michael@paquier.xyz>
2019-05-20 22:37 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Bossart, Nathan <bossartn@amazon.com>
0 siblings, 1 reply; 10+ messages in thread
From: Michael Paquier @ 2019-05-20 08:27 UTC (permalink / raw)
To: Bossart, Nathan <bossartn@amazon.com>; +Cc: Haribabu Kommi <kommi.haribabu@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Mon, May 06, 2019 at 04:08:47PM +0000, Bossart, Nathan wrote:
> Added here: https://commitfest.postgresql.org/23/2111/
Thanks for adding the patch to the CF.
With your patch attached the difference in the dump is plain:
REVOKE CONNECT,TEMPORARY 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;
RESET SESSION AUTHORIZATION;
-GRANT TEMPORARY ON DATABASE mydb TO c_user WITH GRANT OPTION;
So what happens is that the GRANT command to a_user fails when
switching to the session context to c_user as this user does not have
yet the authorization to perform this command. If the GRANT
permissions assigned to c_user are moved prior its actual actions then
the restore is able to work. I have been looking at it, and wondered
first if we could have just used buildACLQueries(), until I noticed
that we don't support initial privileges on databases, so the patch
you have sent looks fine to me.
I had first a hard time parsing the subqueries added, so I have
tweaked your patch with more indentation, and a comment block with
more details about why we need to preserve the ACL ordering (you will
note that I don't have a lot of imagination here).
v12 beta1 is going to ship soon, so let's wait for the version to be
tagged before committing it.
--
Michael
Attachments:
[text/x-diff] database-acls-v3.patch (2.5K, ../../20190520082705.GA1921@paquier.xyz/2-database-acls-v3.patch)
download | inline diff:
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index a8f1ad7333..8931fc4014 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, "
[application/pgp-signature] signature.asc (832B, ../../20190520082705.GA1921@paquier.xyz/3-signature.asc)
download
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly
2019-05-01 23:18 BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly PG Bug reporting form <noreply@postgresql.org>
2019-05-01 23:28 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Bossart, Nathan <bossartn@amazon.com>
2019-05-02 04:08 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Haribabu Kommi <kommi.haribabu@gmail.com>
2019-05-03 17:22 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Bossart, Nathan <bossartn@amazon.com>
2019-05-04 12:32 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Michael Paquier <michael@paquier.xyz>
2019-05-06 16:08 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Bossart, Nathan <bossartn@amazon.com>
2019-05-20 08:27 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Michael Paquier <michael@paquier.xyz>
@ 2019-05-20 22:37 ` Bossart, Nathan <bossartn@amazon.com>
2019-05-22 06:26 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 10+ messages in thread
From: Bossart, Nathan @ 2019-05-20 22:37 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Haribabu Kommi <kommi.haribabu@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On 5/20/19, 1:27 AM, "Michael Paquier" <michael@paquier.xyz> wrote:
> I had first a hard time parsing the subqueries added, so I have
> tweaked your patch with more indentation, and a comment block with
> more details about why we need to preserve the ACL ordering (you will
> note that I don't have a lot of imagination here).
Thanks. Your version looks good to me.
> v12 beta1 is going to ship soon, so let's wait for the version to be
> tagged before committing it.
Sounds good.
Nathan
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly
2019-05-01 23:18 BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly PG Bug reporting form <noreply@postgresql.org>
2019-05-01 23:28 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Bossart, Nathan <bossartn@amazon.com>
2019-05-02 04:08 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Haribabu Kommi <kommi.haribabu@gmail.com>
2019-05-03 17:22 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Bossart, Nathan <bossartn@amazon.com>
2019-05-04 12:32 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Michael Paquier <michael@paquier.xyz>
2019-05-06 16:08 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Bossart, Nathan <bossartn@amazon.com>
2019-05-20 08:27 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Michael Paquier <michael@paquier.xyz>
2019-05-20 22:37 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Bossart, Nathan <bossartn@amazon.com>
@ 2019-05-22 06:26 ` Michael Paquier <michael@paquier.xyz>
2019-05-22 16:59 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Bossart, Nathan <bossartn@amazon.com>
0 siblings, 1 reply; 10+ messages in thread
From: Michael Paquier @ 2019-05-22 06:26 UTC (permalink / raw)
To: Bossart, Nathan <bossartn@amazon.com>; +Cc: Haribabu Kommi <kommi.haribabu@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Mon, May 20, 2019 at 10:37:50PM +0000, Bossart, Nathan wrote:
> On 5/20/19, 1:27 AM, "Michael Paquier" <michael@paquier.xyz> wrote:
>> I had first a hard time parsing the subqueries added, so I have
>> tweaked your patch with more indentation, and a comment block with
>> more details about why we need to preserve the ACL ordering (you will
>> note that I don't have a lot of imagination here).
>
> Thanks. Your version looks good to me.
Okay, I have done more tests, checks and tweaks on this stuff, and
pushed the fix for databases down to 9.6.
Now, I have noticed two separate issues while testing:
1) We need a similar fix for tablespaces and pg_dumpall
1-1) Take for example this set of commands, reusing your previous
example:
\! rm -rf /tmp/tbspc/
\! mkdir -p /tmp/tbspc/
CREATE ROLE a_user;
CREATE ROLE b_user WITH SUPERUSER;
CREATE ROLE c_user;
CREATE TABLESPACE poo LOCATION '/tmp/tbspc/';
SET SESSION AUTHORIZATION b_user;
REVOKE ALL ON TABLESPACE poo FROM public;
GRANT CREATE ON TABLESPACE poo TO c_user WITH GRANT OPTION;
SET SESSION AUTHORIZATION c_user;
GRANT CREATE ON TABLESPACE poo TO a_user;
1-2) And then run pg_dumpall -g (-t is fine but including roles ensures
a correct test), where you will notice the following GRANT ordering:
CREATE TABLESPACE poo OWNER ioltas LOCATION '/tmp/tbspc';
SET SESSION AUTHORIZATION c_user;
GRANT ALL ON TABLESPACE poo TO a_user; -- breaks here
RESET SESSION AUTHORIZATION;
GRANT ALL ON TABLESPACE poo TO c_user WITH GRANT OPTION;
The problem comes from dumpTablespaces() which still has a broken ACL
ordering logic. I'll start a new thread about that as this thread is
actually now fixed. It would be interesting to refactor all that
stuff, but I'd like to think that it would be much more interesting if
we could get pg_init_privs to work with databases and tablespace
instead, but that would not be back-patchable. So I'll focus on the
bug fix.
2) On HEAD, before the patch, I am able to trigger the following
failure in libpq when using for example pg_dumpall and a 9.5 backend:
pg_dump: column number -1 is out of range 0..36
I am not sure yet what's exactly causing that, but this is just a
heads-up.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (832B, ../../20190522062626.GC1486@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly
2019-05-01 23:18 BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly PG Bug reporting form <noreply@postgresql.org>
2019-05-01 23:28 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Bossart, Nathan <bossartn@amazon.com>
2019-05-02 04:08 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Haribabu Kommi <kommi.haribabu@gmail.com>
2019-05-03 17:22 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Bossart, Nathan <bossartn@amazon.com>
2019-05-04 12:32 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Michael Paquier <michael@paquier.xyz>
2019-05-06 16:08 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Bossart, Nathan <bossartn@amazon.com>
2019-05-20 08:27 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Michael Paquier <michael@paquier.xyz>
2019-05-20 22:37 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Bossart, Nathan <bossartn@amazon.com>
2019-05-22 06:26 ` Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly Michael Paquier <michael@paquier.xyz>
@ 2019-05-22 16:59 ` Bossart, Nathan <bossartn@amazon.com>
0 siblings, 0 replies; 10+ messages in thread
From: Bossart, Nathan @ 2019-05-22 16:59 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Haribabu Kommi <kommi.haribabu@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On 5/21/19, 11:26 PM, "Michael Paquier" <michael@paquier.xyz> wrote:
> On Mon, May 20, 2019 at 10:37:50PM +0000, Bossart, Nathan wrote:
>> On 5/20/19, 1:27 AM, "Michael Paquier" <michael@paquier.xyz> wrote:
>>> I had first a hard time parsing the subqueries added, so I have
>>> tweaked your patch with more indentation, and a comment block with
>>> more details about why we need to preserve the ACL ordering (you will
>>> note that I don't have a lot of imagination here).
>>
>> Thanks. Your version looks good to me.
>
> Okay, I have done more tests, checks and tweaks on this stuff, and
> pushed the fix for databases down to 9.6.
Thanks!
> The problem comes from dumpTablespaces() which still has a broken ACL
> ordering logic. I'll start a new thread about that as this thread is
> actually now fixed. It would be interesting to refactor all that
> stuff, but I'd like to think that it would be much more interesting if
> we could get pg_init_privs to work with databases and tablespace
> instead, but that would not be back-patchable. So I'll focus on the
> bug fix.
I'll take a look at this.
Nathan
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2019-05-22 16:59 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-05-01 23:18 BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly PG Bug reporting form <noreply@postgresql.org>
2019-05-01 23:28 ` Bossart, Nathan <bossartn@amazon.com>
2019-05-02 04:08 ` Haribabu Kommi <kommi.haribabu@gmail.com>
2019-05-03 17:22 ` Bossart, Nathan <bossartn@amazon.com>
2019-05-04 12:32 ` Michael Paquier <michael@paquier.xyz>
2019-05-06 16:08 ` Bossart, Nathan <bossartn@amazon.com>
2019-05-20 08:27 ` Michael Paquier <michael@paquier.xyz>
2019-05-20 22:37 ` Bossart, Nathan <bossartn@amazon.com>
2019-05-22 06:26 ` Michael Paquier <michael@paquier.xyz>
2019-05-22 16:59 ` Bossart, Nathan <bossartn@amazon.com>
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