pg_upgrade from 9.6 fails if old cluster had non-standard ACL
on pg_catalog functions that have changed between versions,
for example pg_stop_backup(boolean).
Error:
pg_restore: creating ACL "pg_catalog.FUNCTION "pg_stop_backup"()"
pg_restore: creating ACL "pg_catalog.FUNCTION
"pg_stop_backup"("exclusive" boolean, OUT "lsn" "pg_lsn", OUT
"labelfile" "text", OUT "spcmapfile" "text")"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2169; 0 0 ACL FUNCTION
"pg_stop_backup"("exclusive" boolean, OUT "lsn" "pg_lsn", OUT
"labelfile" "text", OUT "spcmapfile" "text") anastasia
pg_restore: [archiver (db)] could not execute query: ERROR: function
pg_catalog.pg_stop_backup(boolean) does not exist
Command was: GRANT ALL ON FUNCTION
"pg_catalog"."pg_stop_backup"("exclusive" boolean, OUT "lsn" "pg_lsn",
OUT "labelfile" "text", OUT "spcmapfile" "text") TO "backup";
Steps to reproduce:
1) create a database with pg9.6
2) create a user and change grants on pg_stop_backup(boolean):
CREATE ROLE backup WITH LOGIN;
GRANT USAGE ON SCHEMA pg_catalog TO backup;
GRANT EXECUTE ON FUNCTION pg_stop_backup() TO backup;
GRANT EXECUTE ON FUNCTION pg_stop_backup(boolean) TO backup;
3) perform pg_upgrade to v10 (or any version above)
The problem exists since we added to pg_dump support of ACL changes of
pg_catalog functions in commit 23f34fa4b.
I think this is a bug since it unpredictably affects user experience, so
I propose to backpatch the fix.
Script to reproduce the problem and the patch to fix it (credit to
Arthur Zakirov) are attached.
Current patch contains a flag for pg_dump --change-old-names to enforce
correct behavior.
I wonder, if we can make it default behavior for pg_upgrade?
--
Anastasia Lubennikova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
