Re: BUG #18242: pg_dump with non-superuser from pg14 to pg15 fails on ALTER FUNCTION - Mailing list pgsql-bugs

From Aksel Allas
Subject Re: BUG #18242: pg_dump with non-superuser from pg14 to pg15 fails on ALTER FUNCTION
Date
Msg-id CABnLe_DK6awc1RWVPHbSC4hihQ_d1QDTQgMNvQrmT3e-bC9zug@mail.gmail.com
Whole thread Raw
In response to BUG #18242: pg_dump with non-superuser from pg14 to pg15 fails on ALTER FUNCTION  (PG Bug reporting form <noreply@postgresql.org>)
List pgsql-bugs
We get the following error:

psql:schema.psql:81: ERROR:  permission denied for schema public
on the command `ALTER FUNCTION "public"."get_current_user"() OWNER TO "application_user";`

Question is how to handle schema dump (without manually changing dump) when we don't have access to superuser e.g. in cloud environments

If I add `GRANT CREATE ON SCHEMA public TO application_user` in source database, then it appears at the end of the dump and after ALTER FUNCTION.

On Tue, Dec 12, 2023 at 1:27 PM PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:

Bug reference:      18242
Logged by:          Aksel Allas
Email address:      aksel.allas@pactum.com
PostgreSQL version: 15.5
Operating system:   Debian/GNU Linux 12
Description:       

Please see
https://github.com/AkselAllas/pg_dump-alter-function-problem-with-non-superuser
for reproduction steps in dockerized environment.

The gist is that:

When making a dump from PostgreSQL 14 and using it in PostgreSQL 15 with a
user that has no superuser access (e.g. cloud environments), then we run
into the following line from
https://www.postgresql.org/docs/15/sql-alterfunction.html

> To alter the owner, you must also be a direct or indirect member of the
new owning role, and that role must have CREATE privilege on the function's
schema.

pgsql-bugs by date:

Previous
From: PG Bug reporting form
Date:
Subject: BUG #18242: pg_dump with non-superuser from pg14 to pg15 fails on ALTER FUNCTION
Next
From: David Rowley
Date:
Subject: Re: BUG #18240: Undefined behaviour in cash_mul_flt8() and friends