From: Laurenz Albe <laurenz.albe@cybertec.at>
To: kaido vaikla <kaido.vaikla@gmail.com>
To: pgsql-admin@postgresql.org
Subject: Re: pg_dumpall and owner of the extension
Date: Tue, 23 Jan 2024 16:30:43 +0100
Message-ID: <17144af8b80466cf6c807d531de8410f1783ed90.camel@cybertec.at> (raw)
In-Reply-To: <CA+427g-YecPcvrHLJjoP92gDQy199qvvKCj_OvXhR3qmeu0zRQ@mail.gmail.com>
References: <CA+427g-YecPcvrHLJjoP92gDQy199qvvKCj_OvXhR3qmeu0zRQ@mail.gmail.com>
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#e2697a586e2aecbe175d3ce1...
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: laurenz.albe@cybertec.at, kaido.vaikla@gmail.com
Subject: Re: pg_dumpall and owner of the extension
In-Reply-To: <17144af8b80466cf6c807d531de8410f1783ed90.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox