pg_restore: error: could not execute query: ERROR: permission denied for table hypertable

[akschu]

• PostgreSQL 11.13 upgrading to 13.4
• TimescaleDB version 2.3.1 on both 11.13 and 13.4
• Installation method: compiled from source.

When upgrading from postgres 11.13 to 13.4 using the following commands:

# su postgres -c "initdb -D /var/lib/pgsql/13.4/data --locale=en_US.UTF-8"
# cp postgresql.conf pg_hba.conf /var/lib/pgsql/13.4/data/
# su postgres -c "/usr/lib64/postgresql/13.4/bin/pg_upgrade --old-bindir=/usr/lib64/postgresql/11.13/bin/ --new-bindir=/usr/lib64/postgresql/13.4/bin/ --old-datadir=/var/lib/pgsql/11.13/data --new-datadir=/var/lib/pgsql/13.4/data --jobs=8"

The upgrade spits out:
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for system-defined composite types in user tables  ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for tables WITH OIDS                               ok
Checking for invalid "sql_identifier" user columns          ok
Creating dump of global objects                             ok
Creating dump of database schemas
                                                            ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok
Checking for new cluster tablespace directories             ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster                       ok
Freezing all rows in the new cluster                        ok
Deleting files from new pg_xact                             ok
Copying old pg_xact to new server                           ok
Setting oldest XID for new cluster                          ok
Setting next transaction ID and epoch for new cluster       ok
Deleting files from new pg_multixact/offsets                ok
Copying old pg_multixact/offsets to new server              ok
Deleting files from new pg_multixact/members                ok
Copying old pg_multixact/members to new server              ok
Setting next multixact ID and offset for new cluster        ok
Resetting WAL archives                                      ok
Setting frozenxid and minmxid counters in new cluster       ok
Restoring global objects in the new cluster                 ok
Restoring database schemas in the new cluster
  messagescores
*failure*

Consult the last few lines of "pg_upgrade_dump_77936060.log" for
the probable cause of the failure.
Failure, exiting

child process exited abnormally: status 256
Failure, exiting

In that Log I get:

pg_restore: creating ACL "DATABASE "messagescores""
pg_restore: creating ACL "SCHEMA "_timescaledb_cache""
pg_restore: creating ACL "SCHEMA "_timescaledb_catalog""
pg_restore: creating ACL "SCHEMA "_timescaledb_config""
pg_restore: creating ACL "SCHEMA "_timescaledb_internal""
pg_restore: creating ACL "SCHEMA "timescaledb_information""
pg_restore: creating ACL "_timescaledb_catalog.TABLE "chunk_constraint""
pg_restore: creating ACL "_timescaledb_catalog.TABLE "hypertable""
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 3562; 0 0 ACL TABLE "hypertable" postgres
pg_restore: error: could not execute query: ERROR:  permission denied for table hypertable
Command was: SELECT pg_catalog.binary_upgrade_set_record_init_privs(true);
REVOKE ALL ON TABLE "_timescaledb_catalog"."hypertable" FROM "postgres";
SET SESSION AUTHORIZATION "messagescores";
GRANT ALL ON TABLE "_timescaledb_catalog"."hypertable" TO "messagescores";
RESET SESSION AUTHORIZATION;
SET SESSION AUTHORIZATION "messagescores";
GRANT SELECT ON TABLE "_timescaledb_catalog"."hypertable" TO PUBLIC;
RESET SESSION AUTHORIZATION;
SELECT pg_catalog.binary_upgrade_set_record_init_privs(false);

I've been playing around with the permissions and can't quite seem to figure out why it won't upgrade. I did upgrade from a much older version of timescaledb, so something got screwed up.

[svenklemm]

It seems like you revoked permissions from postgres for the internal catalog tables, but pg_upgrade runs as postgres and fails because it lacks permissions.

[zuss777]

Hi all!
I want to migrate from Postgresql 11 + timescaledb 1.7.4 to Postgresql 13 + timescaledb 2.3.1. The migration scheme is as follows: 1st step upgrade timescaledb 1.7.4 to version 2.3.1 then 2nd step upgrade Postgresql from version 11 to version 13
The 1st step was successful, but on the 2nd I get the error:

$ /usr/pgsql-13/bin/pg_upgrade --link --retain --old-bindir=/usr/pgsql-11/bin/ --new-bindir=/usr/pgsql-13/bin/ --old-datadir=/var/lib/pgsql/11/data/ --new-datadir=/var/lib/pgsql/13/data/

Performing Consistency Checks

Checking cluster versions ok
Checking database user is the install user ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for system-defined composite types in user tables ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for tables WITH OIDS ok
Checking for invalid "sql_identifier" user columns ok
Creating dump of global objects ok
Creating dump of database schemas
ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions ok
Checking for new cluster tablespace directories ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade

Analyzing all rows in the new cluster ok
Freezing all rows in the new cluster ok
Deleting files from new pg_xact ok
Copying old pg_xact to new server ok
Setting oldest XID for new cluster ok
Setting next transaction ID and epoch for new cluster ok
Deleting files from new pg_multixact/offsets ok
Copying old pg_multixact/offsets to new server ok
Deleting files from new pg_multixact/members ok
Copying old pg_multixact/members to new server ok
Setting next multixact ID and offset for new cluster ok
Resetting WAL archives ok
Setting frozenxid and minmxid counters in new cluster ok
Restoring global objects in the new cluster ok
Restoring database schemas in the new cluster
lqadvcesar_dev
failure

Consult the last few lines of "pg_upgrade_dump_7116928.log" for
the probable cause of the failure.
Failure, exiting_

In pg_upgrade_dump_7116928.log :

:
pg_restore: creating ACL "SCHEMA "timescaledb_information""
pg_restore: creating ACL "_timescaledb_catalog.TABLE "chunk_constraint""
pg_restore: creating ACL "_timescaledb_catalog.TABLE "hypertable""
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 1408827; 0 0 ACL TABLE "hypertable" postgres
pg_restore: error: could not execute query: ERROR: permission denied for table hypertable
Command was: SELECT pg_catalog.binary_upgrade_set_record_init_privs(true);
REVOKE ALL ON TABLE "_timescaledb_catalog"."hypertable" FROM "postgres";
SET SESSION AUTHORIZATION "lqadv";
GRANT ALL ON TABLE "_timescaledb_catalog"."hypertable" TO "lqadv";
RESET SESSION AUTHORIZATION;
:

$ cat /etc/redhat-release
CentOS Stream release 8

Please tell me how to fix the problem