Thread: BUG #18242: pg_dump with non-superuser from pg14 to pg15 fails on ALTER FUNCTION

BUG #18242: pg_dump with non-superuser from pg14 to pg15 fails on ALTER FUNCTION

From
PG Bug reporting form
Date:
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.


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.

On Tue, 2023-12-12 at 10:43 +0000, PG Bug reporting form wrote:
> 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.

That is working as it should.

Yours,
Laurenz Albe



Thanks for the quick reply!

So it's not possible to fully use pg_dump without manually altering the output of pg_dump in managed postgreSQL cases?

Probably pg_dump not supporting managed postgresql makes sense.

On Tue, Dec 12, 2023 at 2:14 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Tue, 2023-12-12 at 10:43 +0000, PG Bug reporting form wrote:
> 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.

That is working as it should.

Yours,
Laurenz Albe


Aksel Allas <allasaksel@gmail.com> writes:
> So it's not possible to fully use pg_dump without manually altering the
> output of pg_dump in managed postgreSQL cases?

Nope, trying to dump/restore other people's objects as a non-superuser
isn't considered supported at the moment.  You're not the first to
complain about this, but no coherent proposal to improve it has been
made.  It's not very clear what the supported case ought to be
exactly; clearly a zero-privilege user can't succeed at this, but what
privileges should be expected?  Also it's not clear what problems
exist beyond the schema-privilege one, and it's not clear how we could
solve the problem(s) without creating other ones.  There's a lot of
history behind the way that pg_dump does things, so rearranging its
choices about how/when to change privileges is scary.

            regards, tom lane



"Wetmore, Matthew (CTR)" <Matthew.Wetmore@express-scripts.com> writes:
> What about a whole new user type:
> An 'Admin' account that isn't a super user, but just has dump/ elevated permissions /customizable.

[ shrug... ]  Maybe, but there's a mighty lot of devils hiding in
the details.  Exactly what special privileges would this user type
need?  How would we convince ourselves (and more to the point,
convince the cloud providers) that such a set of privileges is
safe to give out?  Poking holes in the privilege model is usually
a good way to create security hazards.

BTW, please keep the list cc'd.

            regards, tom lane



In my specific scenario it could be fixed such that e.g. pg_dump has a flag for non superuser usage. 

If the flag is enabled, the dump could check if CREATE privilege is given at the end of the dump file to the correct schema and instead give the privilege after connecting to the correct database. Then my case would work.

Best wishes!
Aksel


On Tue, Dec 12, 2023, 6:55 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Wetmore, Matthew (CTR)" <Matthew.Wetmore@express-scripts.com> writes:
> What about a whole new user type:
> An 'Admin' account that isn't a super user, but just has dump/ elevated permissions /customizable.

[ shrug... ]  Maybe, but there's a mighty lot of devils hiding in
the details.  Exactly what special privileges would this user type
need?  How would we convince ourselves (and more to the point,
convince the cloud providers) that such a set of privileges is
safe to give out?  Poking holes in the privilege model is usually
a good way to create security hazards.

BTW, please keep the list cc'd.

                        regards, tom lane