When upgrading from a PostgreSQL 11-based YugabyteDB release to a PostgreSQL 15-based YugabyteDB release, the upgrade includes a PostgreSQL major-version upgrade phase.
One issue you may encounter involves catalog tables that existed in the PG11-based release but no longer exist in the PG15-based release.
For example, the upgrade may encounter an error like:
ERROR: relation "pg_catalog.pg_pltemplate" does not exist
STATEMENT:
GRANT SELECT ON TABLE "pg_catalog"."pg_pltemplate" TO "appdba_role";
Why?
The source database contains an explicit grant on pg_catalog.pg_pltemplate, but that relation no longer exists in the PG15 catalog. When the upgrade process attempts to recreate the grant against the PG15-based target, it fails.
This scenario is related to YSQL ACL preflight check is incomplete for removed/renamed catalog objects. During a PG11-to-PG15 YSQL major upgrade, ACL changes can reference catalog objects that no longer exist in the target catalog, causing the upgrade to fail. The issue tracks expanding the preflight checks to catch additional ACL-bearing objects, including catalog relations, before the upgrade proceeds.
Two Catalog Relations to Check
- ●
pg_pltemplate - ●
yb_int_pg_stats_v11
Before starting the upgrade, run the following query on the PG11-based YugabyteDB universe. It identifies explicit privileges on the two catalog relations granted to non-owner, non-superuser roles and generates the corresponding REVOKE command. PUBLIC, relation owners, and superusers are intentionally excluded so the results focus on grants that may have been manually added to user-created roles.
SELECT
n.nspname AS schema_name,
c.relname AS relation_name,
r.rolname AS grantee,
a.privilege_type,
a.is_grantable,
format(
'REVOKE %s ON TABLE %I.%I FROM %I;',
a.privilege_type,
n.nspname,
c.relname,
r.rolname
) AS revoke_command
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n
ON n.oid = c.relnamespace
CROSS JOIN LATERAL pg_catalog.aclexplode(c.relacl) a
JOIN pg_catalog.pg_roles r
ON r.oid = a.grantee
WHERE n.nspname = 'pg_catalog'
AND c.relname IN (
'pg_pltemplate',
'yb_int_pg_stats_v11'
)
AND c.relacl IS NOT NULL
-- Ignore PUBLIC
AND a.grantee <> 0
-- Ignore the relation owner
AND a.grantee <> c.relowner
-- Ignore all superusers / administrative users
AND r.rolsuper = false
ORDER BY
c.relname,
r.rolname,
a.privilege_type;
If no rows are returned, there are no matching non-owner, non-superuser grants on these two catalog relations that require review.
If rows are returned, review each entry and its generated revoke_command. The query intentionally excludes PUBLIC, the relation owner, and all superuser roles so the results focus on privileges granted to ordinary user-created roles.
Example output:
.schema_name | relation_name | grantee | privilege_type | is_grantable | revoke_command
-------------+---------------+-------------+----------------+--------------+---------------------------------------------------------------
pg_catalog | pg_pltemplate | appdba_role | SELECT | f | REVOKE SELECT ON TABLE pg_catalog.pg_pltemplate FROM appdba_role;
Review each generated revoke_command before running it. If the privilege was manually granted to a user-created role, revoke it before beginning the upgrade.
For example:
REVOKE SELECT ON TABLE pg_catalog.pg_pltemplate FROM appdba_role;
PUBLIC, or other built-in administrative roles.
And, if applicable:
REVOKE SELECT
ON TABLE pg_catalog.yb_int_pg_stats_v11
FROM appdba_role;
Then rerun the check:
SELECT
n.nspname AS schema_name,
c.relname AS relation_name,
r.rolname AS grantee,
a.privilege_type,
a.is_grantable,
format(
'REVOKE %s ON TABLE %I.%I FROM %I;',
a.privilege_type,
n.nspname,
c.relname,
r.rolname
) AS revoke_command
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n
ON n.oid = c.relnamespace
CROSS JOIN LATERAL pg_catalog.aclexplode(c.relacl) a
JOIN pg_catalog.pg_roles r
ON r.oid = a.grantee
WHERE n.nspname = 'pg_catalog'
AND c.relname IN (
'pg_pltemplate',
'yb_int_pg_stats_v11'
)
AND c.relacl IS NOT NULL
-- Ignore PUBLIC
AND a.grantee <> 0
-- Ignore the relation owner
AND a.grantee <> c.relowner
-- Ignore all superusers / administrative users
AND r.rolsuper = false
ORDER BY
c.relname,
r.rolname,
a.privilege_type;
Ideally, the query should now return no rows.
Before a PG11-to-PG15 YugabyteDB upgrade, check for manually assigned privileges on catalog relations that do not exist in the PG15-based release. Removing stale grants ahead of time can prevent the major-upgrade phase from attempting to recreate privileges on objects that no longer exist.
Final Takeaway
PostgreSQL major-version upgrades involve more than just migrating application tables. The PostgreSQL system catalog changes too.
A simple pre-upgrade check for explicit grants on:
pg_catalog.pg_pltemplate
pg_catalog.yb_int_pg_stats_v11
can catch a problem before the upgrade window.
If grants exist, revoke them in the PG11-based YugabyteDB universe first, verify the ACLs are gone, and then proceed with the upgrade.
Have Fun!
There we are! 😀 YugabyteDB on the directory at our Sunnyvale, CA office.
Always cool seeing the YB name right in the heart of Silicon Valley!