From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | bossartn(at)amazon(dot)com |
Subject: | BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly |
Date: | 2019-05-01 23:18:16 |
Message-ID: | 15788-4e18847520ebcc75@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 15788
Logged by: Nathan Bossart
Email address: bossartn(at)amazon(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Bossart, Nathan | 2019-05-01 23:28:03 | Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly |
Previous Message | Anthony SKORSKI | 2019-05-01 17:30:00 | Re: BUG #15741: ERROR: failed to build any 3-way joins |