pg_upgrade fails with non-standard ACL

From: Anastasia Lubennikova <a(dot)lubennikova(at)postgrespro(dot)ru>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: pg_upgrade fails with non-standard ACL
Date: 2019-07-18 15:53:12
Message-ID: f85991ad-bbd4-ad57-fde4-e12f0661dbf0@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
pg_dump_ACL_test.sh application/x-shellscript 1.9 KB
pg_dump_ACL_fix_v0.patch text/x-patch 8.1 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andres Freund 2019-07-18 15:56:45 Re: buildfarm's typedefs list has gone completely nutso
Previous Message Daniel Gustafsson 2019-07-18 15:51:26 Re: Documentation fix for adding a column with a default value