Re: pg_dumpall and owner of the extension

Yes, i have a real problem. To keep things clear in database, my design is:
- every application has own schema
- every schema has owner who is not a user postgres
- only schema owner can do DDL's on schema

So if some application needs some extensions, then i give temporary
suppersuser privilege to schema owner,
if "create extension" needs it, extensions are installed "with schema"
And revoke suppersuser privilege after extension inatall.
I'm not sure, is it my design against postgres concept or not :(.

But in same time, i expect that pg_dumpall will not change ownership or at
least gives some error or something.

br
Kaido

On Tue, 23 Jan 2024 at 17:30, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:

> On Tue, 2024-01-23 at 16:31 +0200, kaido vaikla wrote:
> > pg_pumpall (13.4) changes owner of the extension.
> >
> > [Extension created by user A, dump restored by user B -> Extension
> objects belong to B]
> >
> > Manual says "The user who runs CREATE EXTENSION becomes the owner of the
> extension".
> > So pg_dumpall is not aware who ran "CREATE EXTENSION"?
>
> The user who runs CREATE EXTENSION is the user who restores the dump,
> hence the
> changed ownership of the extension objects.
>
> There is no ALTER EXTENSION ... OWNER TO to change the owner to the
> original user,
> which is probably what would be needed in this case.
>
> This is arguably a bug.
>
> Trawling the archives, I found this pertinent thread:
>
> https://www.postgresql.org/message-id/flat/4E1F1072.3010300%40enterprisedb.com
>
> I looked for the referenced discussion, and all I found was this:
>
> https://www.postgresql.org/message-id/flat/21857.1297101968%40sss.pgh.pa.us#e2697a586e2aecbe175d3ce1b7f70bf2
>
> That sounds to me like ALTER EXTENSION ... OWNER TO was originally planned,
> but never implemented:
>
> "BTW, on trying this I notice that pg_dump's default approach to
> ownership doesn't work because of the lack of an ALTER EXTENSION
> OWNER TO command. I'm going to go ahead and add extowner to the catalog
> anyway, because it's easy and I'm convinced we're going to want it
> later. But I don't feel like writing ALTER EXTENSION OWNER TO right
> now, so pg_dump will continue its current behavior of creating the
> extension as the user running the script."
>
> Obviously nobody has felt like implementing ALTER EXTENSION ... OWNER TO
> in the dozen years that followed that statement...
>
> This was probably not a real problem for anybody, because traditionally
> you needed a superuser to run CREATE EXTENSION, and functions are
> executable
> by everybody by default, so you normally don't notice a difference.
>
> Now that we have trusted extensions that don't require a superuser to
> create, things might have changed. It would be interesting to know if you
> have a real problem with the current behavior, which would be an argument
> in favor of fixing the omission.
>
> Yours,
> Laurenz Albe
>