pg_upgrade fails with an error "object doesn't exist"

On Fri, 2025-06-13 at 13:38 +0530, Vaibhav Dalvi wrote:

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.

It is not supported to create objects in pg_catalog. Don't do it.

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.

Yours,
Laurenz Albe

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 09:29, Vaibhav Dalvi <vaibhav.dalvi@enterprisedb.com> wrote:

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?

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).

--
Daniel Gustafsson

Hi Daniel,

Thanks for your response.

On Mon, Jun 16, 2025 at 1:27 PM Daniel Gustafsson <daniel@yesql.se> wrote:

On 16 Jun 2025, at 09:29, Vaibhav Dalvi <vaibhav.dalvi@enterprisedb.com>

wrote:

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?

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).

--
Daniel Gustafsson

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
postgres=# \df+ nont_ext_func

List of functions
Schema | Name | Result data type | Argument data types | Type
| Volatility | Parallel | Owner | Security | Lea
kproof? | Access privileges | Language | Internal name | Description
------------+---------------+------------------+---------------------+------+------------+----------+---------+----------+----
--------+-------------------+----------+---------------+-------------
pg_catalog | nont_ext_func | character | | func
| volatile | unsafe | vaibhav | invoker | no
| | plpgsql | |
(1 row)

Regards,
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

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

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:

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

On 17 Jun 2025, at 04:58, Tom Lane <tgl@sss.pgh.pa.us> wrote:

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.

Agreed. I we were to do anything I think a check in pg_upgrade would be more
appropriate than altering pg_dump (but I'm not sure it's worth spending the
cycles in every upgrade to test for this, the check phase is already quite
extensive).

--
Daniel Gustafsson