Re: pg_dumpall and owner of the extension

My main point is, if object in database (table, function etc) should not be
owned by bootstrap superuser, then avoid it: owner postgres

On Wed, 24 Jan 2024 at 16:13, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:

>
> I don't really see what you are expecting to gain from your design.
> Instead of temporarily elevating the schema owner's privileges, why
> don't you create the extension as superuser right away?
>
It's quite common that initialization needs more privilege than usage
(for example, as root user #yum install postgresql-server)

> (As an aside: you need a superuser to turn sumebody into a superuser.
> Don't tell me that your application is connecting as superuser...)
>
No,no. vice versa, seems my problem is that i try push out everything from
superuser

> So while I think that the current behavior is less than ideal, I don't
> see it as a massive problem that needs to be fixed. What is it that
> stops working for you after the restore?

Currently nothing, but i have tens of pg instances waiting for
export-import. And design is same:
schema owner is extension owner and extension is intalled into this
application schema.
Why I use it, is because if i have lot of different apps (schemas) with
different extensions then overview is better.
"Default way": public schema and owner postgres for all extension is a big
mess.

If this design is against concept of extensions or postgres i will change
it in future. Would be nice, if you can point some doc.
But pg_dumpall can't change ownership without any notification i think.
CREATE EXTENSION doc should be change like:
"The user who runs CREATE EXTENSION becomes the owner of the extension
until first export-import" ;)
br
Kaido