Dear hackers,
(including Jeff, because 8185bb53 is written by you)
While working on another thread, I found that pg_upgrade could not upgrade nodes
if a subscription is created by non-superuser and it refers the foreign server.
This happened because GRANT ON FOREIGN SERVER can be executed after the ALTER
SUBSCRIPTION OWNER TO command. Please see the attached reproducer.
The upgrade would fail while restoring objects to the new node:
```
$ pg_upgrade ...
...
Performing Upgrade
------------------
...
Restoring global objects in the new cluster ok
Restoring database schemas in the new cluster
postgres
*failure*
Consult the last few lines of
"new/pg_upgrade_output.d/20260722T214937.529/log/pg_upgrade_dump_5.log" for
the probable cause of the failure.
Failure, exiting
```
And you can see that the restoring can happen after the ALTER SUBSCRIPTION.
```
$ pg_restore --file -
new/pg_upgrade_output.d/20260722T214937.529/dump/pg_upgrade_dump_5.custom
...
CREATE SERVER "server" FOREIGN DATA WRAPPER "postgres_fdw" OPTIONS (
"dbname" 'postgres',
"port" '5432'
);
...
--
-- Name: sub; Type: SUBSCRIPTION; Schema: -; Owner: test
--
CREATE SUBSCRIPTION "sub" SERVER "server" PUBLICATION "pub" WITH (connect =
false, slot_name = 'sub', streaming = parallel);
ALTER SUBSCRIPTION sub OWNER TO test;
...
GRANT ALL ON FOREIGN SERVER "server" TO "test";
```
IIUC, it happens because in pg_restore command, altering the owner is done in
the
main pass but ACL commands is done afterward. Some special objects can be
restored after ACL commands, but subscription does not have such a treatment.
My primitive idea for fix is to introduce a new TOC entry to record the OWNER
command, in the pg_dump. Unlike the normal CREATE SUBSCRIPTION, this entry
can be handled as RESTORE_PASS_POST_ACL.
I'm locally working on the idea and will post tomorrow. But better ideas are
also welcome.
Best regards,
Hayato Kuroda
FUJITSU LIMITED
#!/bin/bash
OLD_DATA=old
OLD_LOG=log.old
OLD_PORT=5432
NEW_DATA=new
NEW_PORT=5433
PGUSER=postgres
BINDIR=/usr/local/pgsql/bin/
pg_ctl stop -D $OLD_DATA
rm -rf $OLD_DATA $OLD_LOG $NEW_DATA
initdb -D $OLD_DATA -U $PGUSER -c port=$OLD_PORT
pg_ctl start -D $OLD_DATA -l $OLD_LOG
(
echo "
CREATE EXTENSION postgres_fdw;
CREATE USER test ;
GRANT CREATE ON DATABASE postgres TO test;
GRANT pg_create_subscription TO test;
GRANT ALL ON FOREIGN DATA WRAPPER postgres_fdw TO test;
CREATE SERVER server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname
'postgres', port '5432');
CREATE USER MAPPING FOR PUBLIC SERVER server OPTIONS (user 'test', password
'test');
GRANT ALL ON FOREIGN SERVER server TO test ;
GRANT CREATE ON SCHEMA PUBLIC TO test ;
SET SESSION AUTHORIZATION test;
CREATE SUBSCRIPTION sub SERVER server PUBLICATION pub WITH
(streaming=parallel, copy_data=off, password_required=true, enabled=off,
connect=off);"
) | psql -U $PGUSER -p $OLD_PORT
pg_ctl stop -D $OLD_DATA
initdb -D $NEW_DATA -U $PGUSER -c port=$NEW_PORT
pg_upgrade -d $OLD_DATA -D $NEW_DATA -b $BINDIR -U $PGUSER -r
