A YSQL major-version upgrade is different from a normal YugabyteDB software upgrade. Moving from a PostgreSQL 11-based YugabyteDB release, such as the 2024.2 series, to a PostgreSQL 15-based release, such as 2025.1 or later, includes a PostgreSQL major catalog upgrade.
That raises an important upgrade-readiness question:
- What happens to custom privileges that have been manually granted or revoked on PostgreSQL/YugabyteDB system objects?
Most of the time, the upgrade machinery takes care of ACLs automatically. But system objects themselves can change between PostgreSQL major versions. A function can gain parameters, an object can disappear, or the new version can intentionally have different default privileges.
That means a good pre-upgrade practice is to capture your custom system-object ACL changes before starting the upgrade so you know exactly what security customizations existed and can verify them afterward.
PostgreSQL and YSQL store object privileges in ACLs. An ACL records which roles have privileges such as
SELECT or EXECUTE, who granted them, and whether the privilege includes the grant option.
A Real Example: pg_stat_statements_reset
Consider an application or monitoring role that was explicitly allowed to reset statement statistics:
CREATE ROLE app_monitor_role;
GRANT EXECUTE
ON FUNCTION pg_catalog.pg_stat_statements_reset()
TO app_monitor_role;
You might also have explicitly removed the default PUBLIC access:
REVOKE ALL
ON FUNCTION pg_catalog.pg_stat_statements_reset()
FROM PUBLIC;
On PostgreSQL 11, pg_stat_statements_reset is a zero-argument function:
pg_stat_statements_reset()
PostgreSQL 15 changed the interface to:
pg_stat_statements_reset(
userid oid,
dbid oid,
queryid bigint
)
with default values that allow callers to continue invoking it without specifying arguments.
That seemingly small signature change matters when ACL DDL contains the old function identity.
Why the Old ACL Statement Doesn’t Work
The following statement correctly identifies the PG11 function:
REVOKE ALL
ON FUNCTION pg_catalog.pg_stat_statements_reset()
FROM PUBLIC;
But on the PG15-based system, () explicitly means:
- the function having zero input arguments.
That function no longer exists.
The PG15 identity is instead:
pg_stat_statements_reset(userid oid, dbid oid, queryid bigint)
A schema dump produced by older tooling can therefore contain ACL DDL referencing an object identity that isn’t present in the PG15 catalog.
function() and function are not the same thing.Writing
function() specifically identifies a zero-argument function. Omitting the argument list can identify the function by name when that name is unique in the schema. This distinction becomes especially useful when a built-in function changes its signature across PostgreSQL major versions.
PostgreSQL’s GRANT syntax allows the argument list to be omitted. When a routine name is overloaded, however, you should use its exact identity arguments to avoid ambiguity.
The YSQL Major-Upgrade Twist
There is an additional safeguard in the YSQL binary-upgrade path for this particular function.
If custom ACL changes exist on the PG11 version of pg_stat_statements_reset(), a normal schema dump can contain statements such as:
REVOKE ALL
ON FUNCTION pg_catalog.pg_stat_statements_reset()
FROM PUBLIC;
GRANT EXECUTE
ON FUNCTION pg_catalog.pg_stat_statements_reset()
TO app_monitor_role;
However, testing with the PG15-side ysql_dump --binary-upgrade used by the major-upgrade workflow shows that ACL output for pg_stat_statements_reset is intentionally omitted. A test containing both the custom GRANT and REVOKE returned zero occurrences of pg_stat_statements_reset in the binary-upgrade dump.
That prevents the old zero-argument ACL statement from breaking the catalog upgrade.
But there is a consequence.
A custom EXECUTE grant assigned to a non-administrative role before the upgrade was no longer present after the PG11-to-PG15 upgrade, even though the role itself still existed.
GRANT EXECUTE on pg_stat_statements_reset() is lost after the upgrade because the function changes from a zero-argument signature to a three-argument signature with defaults. It also notes that tooling which references the old pg_stat_statements_reset() identity can fail after the upgrade because that exact function signature no longer exists.
When a system object changes across PostgreSQL major versions, upgrade tooling may need special handling to keep the upgrade itself safe. Capture custom privileges before the upgrade and verify them afterward rather than assuming every system-object ACL will be preserved.
Don’t Confuse a Normal Dump with the Major-Upgrade Dump
This is another useful distinction.
A normal schema dump from the PG11-based YugabyteDB release may show:
REVOKE ALL
ON FUNCTION pg_catalog.pg_stat_statements_reset()
FROM PUBLIC;
GRANT EXECUTE
ON FUNCTION pg_catalog.pg_stat_statements_reset()
TO app_monitor_role;
That does not necessarily mean those exact statements are what the YSQL major-upgrade process will restore. The binary-upgrade path has additional compatibility handling for major PostgreSQL version changes.
So when troubleshooting a YSQL major upgrade, don’t use an ordinary schema dump alone as proof of what the major-upgrade machinery will do.
The Better Approach: Capture ACL Differences Before the Upgrade
We could simply inspect every non-null ACL in pg_proc, pg_class, and the other catalogs.
But there’s a problem with that approach: a non-null ACL is not automatically a customer-created customization.
System objects can have explicit built-in ACLs too.
PostgreSQL gives us a better tool:
acldefault()
acldefault() returns the hard-coded default ACL for a particular object type and owner, while aclexplode() turns an ACL array into individual privilege rows.
That means we can compare:
- Current ACL
against:
- Default ACL
and report only the differences.
This is much safer than assuming every ACL entry is something we need to recreate.
The interesting question isn’t simply, “Does this system object have an ACL?” It is, “How does its current ACL differ from the normal default?” Those differences are the security customizations worth recording before the upgrade.
The Pre-Upgrade System ACL Catcher
Run the following on each important database in the PG11-based YugabyteDB universe before starting the major upgrade.
It checks system-schema functions, procedures, tables/views, sequences, types, domains, and schemas. It compares their current ACLs with acldefault() and produces candidate GRANT or REVOKE statements for the differences.
WITH objects AS (
-- Functions and procedures
SELECT
'pg_proc'::text AS catalog_class,
p.oid AS object_oid,
CASE
WHEN p.prokind = 'p' THEN 'PROCEDURE'
ELSE 'FUNCTION'
END AS object_type,
'f'::"char" AS acl_type,
n.nspname AS schema_name,
format(
'%I.%I(%s)',
n.nspname,
p.proname,
pg_catalog.pg_get_function_identity_arguments(p.oid)
) AS object_identity,
p.proowner AS owner_oid,
p.proacl AS acl
FROM pg_catalog.pg_proc p
JOIN pg_catalog.pg_namespace n
ON n.oid = p.pronamespace
WHERE n.nspname IN ('pg_catalog', 'information_schema')
AND p.proacl IS NOT NULL
UNION ALL
-- Tables, partitioned tables, views, materialized views,
-- foreign tables, and sequences
SELECT
'pg_class'::text,
c.oid,
CASE
WHEN c.relkind = 'S' THEN 'SEQUENCE'
ELSE 'TABLE'
END,
CASE
WHEN c.relkind = 'S' THEN 's'::"char"
ELSE 'r'::"char"
END,
n.nspname,
format('%I.%I', n.nspname, c.relname),
c.relowner,
c.relacl
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n
ON n.oid = c.relnamespace
WHERE n.nspname IN ('pg_catalog', 'information_schema')
AND c.relacl IS NOT NULL
AND c.relkind IN ('r', 'p', 'v', 'm', 'S', 'f')
UNION ALL
-- Types and domains
SELECT
'pg_type'::text,
t.oid,
'TYPE',
'T'::"char",
n.nspname,
format('%I.%I', n.nspname, t.typname),
t.typowner,
t.typacl
FROM pg_catalog.pg_type t
JOIN pg_catalog.pg_namespace n
ON n.oid = t.typnamespace
WHERE n.nspname IN ('pg_catalog', 'information_schema')
AND t.typacl IS NOT NULL
UNION ALL
-- The system schemas themselves
SELECT
'pg_namespace'::text,
n.oid,
'SCHEMA',
'n'::"char",
n.nspname,
format('%I', n.nspname),
n.nspowner,
n.nspacl
FROM pg_catalog.pg_namespace n
WHERE n.nspname IN ('pg_catalog', 'information_schema')
AND n.nspacl IS NOT NULL
),
current_acl AS (
SELECT
o.*,
a.grantee,
a.privilege_type,
a.is_grantable
FROM objects o
CROSS JOIN LATERAL
pg_catalog.aclexplode(o.acl) a
WHERE a.grantee <> o.owner_oid
),
default_acl AS (
SELECT
o.*,
a.grantee,
a.privilege_type,
a.is_grantable
FROM objects o
CROSS JOIN LATERAL
pg_catalog.aclexplode(
pg_catalog.acldefault(o.acl_type, o.owner_oid)
) a
WHERE a.grantee <> o.owner_oid
),
deltas AS (
-- Privilege exists now but is not part of the default ACL.
SELECT
c.catalog_class,
c.object_oid,
c.object_type,
c.schema_name,
c.object_identity,
c.grantee,
c.privilege_type,
c.is_grantable,
'GRANT'::text AS change_type
FROM current_acl c
WHERE NOT EXISTS (
SELECT 1
FROM default_acl d
WHERE d.catalog_class = c.catalog_class
AND d.object_oid = c.object_oid
AND d.grantee = c.grantee
AND d.privilege_type = c.privilege_type
)
UNION ALL
-- Privilege is part of the default ACL but has been removed.
SELECT
d.catalog_class,
d.object_oid,
d.object_type,
d.schema_name,
d.object_identity,
d.grantee,
d.privilege_type,
d.is_grantable,
'REVOKE'::text
FROM default_acl d
WHERE NOT EXISTS (
SELECT 1
FROM current_acl c
WHERE c.catalog_class = d.catalog_class
AND c.object_oid = d.object_oid
AND c.grantee = d.grantee
AND c.privilege_type = d.privilege_type
)
UNION ALL
-- Existing privilege gained WITH GRANT OPTION.
SELECT
c.catalog_class,
c.object_oid,
c.object_type,
c.schema_name,
c.object_identity,
c.grantee,
c.privilege_type,
c.is_grantable,
'GRANT OPTION'::text
FROM current_acl c
JOIN default_acl d
ON d.catalog_class = c.catalog_class
AND d.object_oid = c.object_oid
AND d.grantee = c.grantee
AND d.privilege_type = c.privilege_type
WHERE c.is_grantable
AND NOT d.is_grantable
UNION ALL
-- Existing default grant option has been removed.
SELECT
c.catalog_class,
c.object_oid,
c.object_type,
c.schema_name,
c.object_identity,
c.grantee,
c.privilege_type,
c.is_grantable,
'REVOKE GRANT OPTION'::text
FROM current_acl c
JOIN default_acl d
ON d.catalog_class = c.catalog_class
AND d.object_oid = c.object_oid
AND d.grantee = c.grantee
AND d.privilege_type = c.privilege_type
WHERE NOT c.is_grantable
AND d.is_grantable
),
resolved AS (
SELECT
d.*,
CASE
WHEN d.grantee = 0 THEN 'PUBLIC'
ELSE r.rolname
END AS grantee_name,
COALESCE(r.rolsuper, false) AS is_superuser,
CASE
WHEN d.grantee = 0 THEN false
ELSE COALESCE(r.rolname ~ '^(pg_|yb_)', false)
END AS looks_builtin_role
FROM deltas d
LEFT JOIN pg_catalog.pg_roles r
ON r.oid = d.grantee
)
SELECT
change_type,
schema_name,
object_type,
object_identity,
grantee_name,
privilege_type,
is_superuser,
looks_builtin_role,
CASE change_type
WHEN 'GRANT' THEN
format(
'GRANT %s ON %s %s TO %s%s;',
privilege_type,
object_type,
object_identity,
CASE
WHEN grantee_name = 'PUBLIC' THEN 'PUBLIC'
ELSE quote_ident(grantee_name)
END,
CASE
WHEN is_grantable THEN ' WITH GRANT OPTION'
ELSE ''
END
)
WHEN 'REVOKE' THEN
format(
'REVOKE %s ON %s %s FROM %s;',
privilege_type,
object_type,
object_identity,
CASE
WHEN grantee_name = 'PUBLIC' THEN 'PUBLIC'
ELSE quote_ident(grantee_name)
END
)
WHEN 'GRANT OPTION' THEN
format(
'GRANT %s ON %s %s TO %s WITH GRANT OPTION;',
privilege_type,
object_type,
object_identity,
quote_ident(grantee_name)
)
WHEN 'REVOKE GRANT OPTION' THEN
format(
'REVOKE GRANT OPTION FOR %s ON %s %s FROM %s;',
privilege_type,
object_type,
object_identity,
quote_ident(grantee_name)
)
END AS candidate_ddl
FROM resolved
-- Keep PUBLIC changes and ordinary custom roles.
-- Filter common PostgreSQL/YugabyteDB built-in roles to reduce noise.
WHERE grantee_name = 'PUBLIC'
OR (
is_superuser = false
AND looks_builtin_role = false
)
ORDER BY
schema_name,
object_type,
object_identity,
grantee_name,
privilege_type;
Why This Query Is Different
The key part is that it does not generate a REVOKE FROM PUBLIC simply because an object has an explicit ACL.
Instead, it asks:
Current ACL
vs.
acldefault()
and reports the delta.
PostgreSQL documents acldefault() as the function that constructs the normal default access privileges for an object type and aclexplode() as the function that breaks an ACL into individual grantee/privilege rows.
That gives us four meaningful cases:
| Change | Meaning | Candidate DDL |
|---|---|---|
| GRANT | A privilege exists now that is not part of the normal default ACL. | GRANT ... |
| REVOKE | A privilege normally present by default has been explicitly removed. | REVOKE ... |
| GRANT OPTION | A privilege was customized to include WITH GRANT OPTION. |
GRANT ... WITH GRANT OPTION |
| REVOKE GRANT OPTION | A default grant option has been removed while preserving the privilege itself. | REVOKE GRANT OPTION FOR ... |
What Should the Example Find?
For our PG11 example:
GRANT EXECUTE
ON FUNCTION pg_catalog.pg_stat_statements_reset()
TO app_monitor_role;
REVOKE ALL
ON FUNCTION pg_catalog.pg_stat_statements_reset()
FROM PUBLIC;
the interesting output would look conceptually like:
.change_type | object_identity | grantee_name | privilege_type
-------------+-----------------------------------------+------------------+---------------
GRANT | pg_catalog.pg_stat_statements_reset() | app_monitor_role | EXECUTE
REVOKE | pg_catalog.pg_stat_statements_reset() | PUBLIC | EXECUTE
and the query would preserve candidate DDL such as:
GRANT EXECUTE
ON FUNCTION pg_catalog.pg_stat_statements_reset()
TO app_monitor_role;
REVOKE EXECUTE
ON FUNCTION pg_catalog.pg_stat_statements_reset()
FROM PUBLIC;
Why Filter pg_ and yb_ Roles?
System catalogs can contain legitimate built-in grants to roles such as PostgreSQL monitoring roles or YugabyteDB administrative roles. Those can appear as differences from the generic acldefault() even though they were created as part of the product’s normal initialization.
The query therefore marks:
looks_builtin_role
and filters common pg_ and yb_ role names from its focused output.
That keeps the report centered on ordinary application and operational roles.
The built-in-role filter is intentionally a noise-reduction step. If you want to audit every ACL difference, remove the
looks_builtin_role = false predicate and review the additional PostgreSQL and YugabyteDB system-role entries as well.
Save the Results Before You Upgrade
The generated commands are an ACL inventory, so save the results along with the rest of your upgrade-readiness artifacts.
In ysqlsh, for example:
\o /tmp/pre_upgrade_system_acl_deltas.txt
Run the query, then return output to the terminal:
\o
You now have a record of the custom security state that existed immediately before the major upgrade.
After the Upgrade: Don’t Blindly Run the File
This part is important.
The candidate DDL contains the PG11 object identities.
A system object may have:
- A different signature
- A different default ACL
- A different name
- Been removed entirely
in PG15.
Therefore, treat the captured output as a desired-state inventory to review, not as a script to execute blindly.
PostgreSQL major releases can intentionally change system objects and their default privileges. After the upgrade, verify that each object still exists, confirm its new identity or signature, and determine whether the custom privilege is still required before executing the captured DDL.
Handling a Changed Function Signature
For pg_stat_statements_reset, first find the new function identity:
SELECT
n.nspname AS schema_name,
p.proname AS function_name,
pg_catalog.pg_get_function_identity_arguments(p.oid)
AS identity_arguments
FROM pg_catalog.pg_proc p
JOIN pg_catalog.pg_namespace n
ON n.oid = p.pronamespace
WHERE n.nspname = 'pg_catalog'
AND p.proname = 'pg_stat_statements_reset';
On a PG15-based release, you should see the three input arguments.
If there is only one function with that name in the schema, PostgreSQL allows the argument list to be omitted when referring to it in GRANT.
So instead of trying to replay the old:
GRANT EXECUTE
ON FUNCTION pg_catalog.pg_stat_statements_reset()
TO app_monitor_role;
use:
GRANT EXECUTE
ON FUNCTION pg_catalog.pg_stat_statements_reset
TO app_monitor_role;
Alternatively, use the exact PG15 identity:
GRANT EXECUTE
ON FUNCTION pg_catalog.pg_stat_statements_reset(
userid oid,
dbid oid,
queryid bigint
)
TO app_monitor_role;
If multiple overloaded functions share the same name, use the exact target-version identity arguments instead. Never remove a function signature merely to make an ACL command succeed without first checking for overloads.
Verify the ACL After the Upgrade
After reapplying the desired grant, verify it directly from the catalog:
SELECT
n.nspname AS schema_name,
p.proname AS function_name,
pg_catalog.pg_get_function_identity_arguments(p.oid)
AS identity_arguments,
p.proacl AS access_privileges
FROM pg_catalog.pg_proc p
JOIN pg_catalog.pg_namespace n
ON n.oid = p.pronamespace
WHERE n.nspname = 'pg_catalog'
AND p.proname = 'pg_stat_statements_reset';
You can also decode the ACL into individual privileges:
SELECT
CASE
WHEN a.grantee = 0 THEN 'PUBLIC'
ELSE r.rolname
END AS grantee,
a.privilege_type,
a.is_grantable
FROM pg_catalog.pg_proc p
JOIN pg_catalog.pg_namespace n
ON n.oid = p.pronamespace
CROSS JOIN LATERAL
pg_catalog.aclexplode(p.proacl) a
LEFT JOIN pg_catalog.pg_roles r
ON r.oid = a.grantee
WHERE n.nspname = 'pg_catalog'
AND p.proname = 'pg_stat_statements_reset'
ORDER BY grantee, privilege_type;
How This Complements Another Upgrade Check
This is closely related to another YugabyteDB Tip:
- Check Grants on Removed PG11 Catalog Tables Before a PG15 Upgrade
The tip Check Grants on Removed PG11 Catalog Tables Before a PG15 Upgrade covers the opposite side of the problem: old ACLs referencing catalog relations that no longer exist can interfere with an upgrade. This tip focuses on preserving intentional custom ACLs that you may still need after the PostgreSQL major-version transition.
Together, the two checks cover an important pair of risks:
| Upgrade Risk | Pre-Upgrade Action |
|---|---|
| Stale ACL references a system object that does not exist in PG15. | Identify and remove the obsolete manually added grant before upgrading. |
| Required custom ACL is not preserved because the system object changes during the major upgrade. | Capture the ACL delta before upgrading, then verify and reapply it against the PG15 object when needed. |
Final Takeaway
Most YSQL ACLs migrate normally, so the point is not to build a giant post-upgrade script that blindly recreates every system privilege.
Instead, capture the exceptions.
Before a PG11-to-PG15 YSQL major upgrade:
- ✔ Identify system-object ACLs that differ from their normal defaults.
- ✔ Capture custom
GRANTs to application and operational roles. - ✔ Capture intentional
REVOKEs such as changes toPUBLICaccess. - ✔ Filter owner, superuser, and common built-in-role noise when you want a focused report.
- ✔ Save the candidate DDL as part of your upgrade-readiness artifacts.
After the upgrade:
- ✔ Verify that the object still exists.
- ✔ Check whether its signature or identity changed.
- ✔ Check whether PG15’s default privilege is already what you want.
- ✔ Reapply only the custom ACLs that are still required.
- ✔ Verify the resulting ACL directly from the PG15 catalog.
The important lesson is that a major PostgreSQL upgrade changes more than application schemas. System catalogs, system functions, function identities, and their default security posture can change too. A small pre-upgrade ACL inventory gives you a record of your intentional customizations so those changes don’t become post-upgrade surprises.
Treat custom system ACLs as part of your upgrade configuration, just like GFlags, extensions, and application schema changes. The goal isn’t to reproduce PG11’s system catalog exactly — it’s to preserve the intentional security requirements that still make sense on PG15.
One important improvement here over simply listing every non-null ACL is the use of acldefault() + aclexplode(). That makes the query a true ACL-delta detector instead of labeling built-in owner/default permissions as custom changes.
Have Fun!
As I travel around the country, I always enjoy checking out the local malls. Unfortunately, as we all know, many of them aren’t what they used to be… lots of empty storefronts and closed shops.
But here in Pittsburgh, Ross Park Mall is still going strong after 40 years! They even put together this really cool wall showing the mall’s history through the decades.
I’ve spent plenty of time here over the years, and this is definitely one of the places I’m going to miss when I move to Dallas. 🛍️❤️
40 years and still thriving… not something you can say about many malls these days!