BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly
От | PG Bug reporting form |
---|---|
Тема | BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly |
Дата | |
Msg-id | 15788-4e18847520ebcc75@postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly
("Bossart, Nathan" <bossartn@amazon.com>)
|
Список | pgsql-bugs |
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
В списке pgsql-bugs по дате отправления:
Предыдущее
От: Anthony SKORSKIДата:
Сообщение: Re: BUG #15741: ERROR: failed to build any 3-way joins
Следующее
От: "Bossart, Nathan"Дата:
Сообщение: Re: BUG #15788: 'pg_dump --create' orders database GRANTs incorrectly