BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly

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

Responses

Browse pgsql-bugs by date

  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