Thread: pg_upgrade fails with an error "object doesn't exist"
Hi,
I'm writing to share an observation regarding `pg_catalog` system objects and their privileges during `pg_upgrade`. It's known that `pg_catalog` system objects are not dumped, but their privileges are. However, if user-created objects are placed within `pg_catalog` and their privileges are altered, `pg_upgrade` can fail with an "object does not exist" error.
I've reproduced this behavior using the following steps:
**Old Cluster (PG18):**
```sql
postgres=# create user alice login;
postgres=# create table t1(a int);
CREATE TABLE
postgres=# alter table t1 set schema pg_catalog;
ALTER TABLE
postgres=# grant select on table pg_catalog.t1 to alice;
GRANT
postgres=# CREATE OR REPLACE FUNCTION pg_catalog.nont_ext_func() RETURNS char AS $$ BEGIN return 'v'; END; $$ LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# grant execute on function pg_catalog.non_ext_func to alice;
GRANT
```
**New Cluster (PG18):**
```bash
$ ./db/bin/pg_upgrade -b ../pg18/db/bin -B db/bin -d ../pg18/db/data -D db/data
pg_restore: creating ACL "pg_catalog.FUNCTION "non_ext_func"()"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 3430; 0 0 ACL FUNCTION "non_ext_func"() vaibhav
pg_restore: error: could not execute query: ERROR: function pg_catalog.non_ext_func() does not exist
Command was: GRANT ALL ON FUNCTION "pg_catalog"."non_ext_func"() TO "alice";
```
This issue arises because `pg_dump`/`pg_restore` or `pg_upgrade` attempts to dump privileges for user objects within `pg_catalog` without first dumping the objects themselves. I believe one of the following approaches should be considered to prevent such failures:
1. Restrict the creation of user objects within the `pg_catalog` schema.
2. If user object creation within `pg_catalog` is allowed, then the objects themselves should be dumped prior to their privileges.
This is my current understanding of the situation. I'd appreciate hearing your thoughts on this behavior and if there's an alternative perspective.
Thanks,
Vaibhav
Hi Laurenz,
Thanks for the response.
> I believe one of the following approaches should be considered to prevent
> such failures:
>
> 1. Restrict the creation of user objects within the `pg_catalog` schema.
That's already the case:
test=# CREATE TABLE pg_catalog.new ();
ERROR: permission denied to create "pg_catalog.new"
DETAIL: System catalog modifications are currently disallowed.
I'm able to create the object as shown in the below:
postgres=# CREATE OR REPLACE FUNCTION pg_catalog.nont_ext_func() RETURNS char AS $$ BEGIN return 'v'; END; $$ LANGUAGE plpgsql;
CREATE FUNCTION
Why can't we strictly restrict object creation in pg_catalog?
Thanks,
Vaibhav
> On 16 Jun 2025, at 10:59, Vaibhav Dalvi <vaibhav.dalvi@enterprisedb.com> wrote: > It's OFF. > postgres=# select version(); > version > ---------------------------------------------------------------------------------------------------------------- > PostgreSQL 18beta1 on aarch64-unknown-linux-gnu, compiled by gcc (Ubuntu 14.2.0-4ubuntu2~24.04) 14.2.0, 64-bit > (1 row) > postgres=# show allow_system_table_mods ; > allow_system_table_mods > ------------------------- > off > (1 row) > postgres=# CREATE FUNCTION pg_catalog.nont_ext_func() RETURNS char AS $$ BEGIN return 'v'; END; $$ LANGUAGE plpgsql; > CREATE FUNCTION I stand corrected, I misremembered the extent to which we prohibit creation in pg_catalog via that GUC. It still feels like a case of getting to keep both pieces when breaking it, but I wonder if we shouldn't make it harder to break? -- Daniel Gustafsson
Hi Tom,
Should we at least restrict dumping privileges for user objects inside pg_catalog to avoid pg_upgrade failure?
Regards,
Vaibhav
On Mon, Jun 16, 2025 at 7:11 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Daniel Gustafsson <daniel@yesql.se> writes:
> On 16 Jun 2025, at 09:29, Vaibhav Dalvi <vaibhav.dalvi@enterprisedb.com> wrote:
>> Why can't we strictly restrict object creation in pg_catalog?
> Do you have allow_system_table_mods set to ON by any chance? As Laurenz said,
> such creation is already restricted, but it can be circumvented by using said
> GUC (which is *not* intended for production usage).
I think that setting only applies to creating or modifying *tables*,
not functions. The point of it is to keep you from breaking the C
code's assumptions about the layout of system catalogs.
Having said that, I don't see a problem here. You're not going
to be able to create/modify functions in pg_catalog unless you
are superuser (or a superuser gave you permissions you shouldn't
have). There are already a near-infinite number of ways
for a superuser to break the system, so this one isn't making it
detectably worse. Furthermore, there are legitimate use-cases
for adding/changing functions there. (I recall that the old
"adminpack" extension used to do so, for example, and there are
probably others that still do.)
regards, tom lane
Vaibhav Dalvi <vaibhav.dalvi@enterprisedb.com> writes:
> Should we at least restrict dumping privileges for user objects inside
> pg_catalog to avoid pg_upgrade failure?
You haven't made a credible case for us to add any complexity in this
area. Anybody messing with pg_catalog is living very much in "if you
break it, you get to keep both pieces" territory. That extends not
just to whether the backend works at all, but whether auxiliary
functionality such as pg_dump works. So in particular I don't see
a reason why we should cater to manually-added pg_catalog functions
with non-default ACLs. There are enough moving parts in that area
already that I'm not eager to add more constraints to what pg_dump
needs to do.
regards, tom lane