Thread: BUG #18625: user-created extensions change ownership to "postgres" after upgrade

The following bug has been logged on the website:

Bug reference:      18625
Logged by:          Evgeni Golov
Email address:      evgeni@golov.de
PostgreSQL version: 16.4
Operating system:   CentOS Stream 9, Debian 12
Description:

Hi,

when upgrading 15 to 16 (but also observed the same on 12 to 13), an
extension that was previously created by a "normal" user is now owned by
"postgres":

psql (15.8 (Debian 15.8-0+deb12u1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384,
compression: off)
Type "help" for help.

foreman=> create extension cube;
CREATE EXTENSION
foreman=> select * from pg_extension;
  oid  | extname | extowner | extnamespace | extrelocatable | extversion |
extconfig | extcondition 
-------+---------+----------+--------------+----------------+------------+-----------+--------------
 13559 | plpgsql |       10 |           11 | f              | 1.0        |
        | 
 16386 | cube    |    16384 |         2200 | t              | 1.5        |
        | 

<upgrade to 16.4 via apt.postgresql.org>

foreman=> select * from pg_extension;
  oid  | extname | extowner | extnamespace | extrelocatable | extversion |
extconfig | extcondition 
-------+---------+----------+--------------+----------------+------------+-----------+--------------
 13562 | plpgsql |       10 |           11 | f              | 1.0        |
        | 
 16386 | cube    |       10 |         2200 | t              | 1.5        |
        | 
(2 rows)

Interestingly, the entry in pg_shdepend is still owned by the user.
 
foreman=> select * from pg_shdepend;
 dbid | classid | objid | objsubid | refclassid | refobjid | deptype 
------+---------+-------+----------+------------+----------+---------
    0 |    1262 | 16385 |        0 |       1260 |    16384 | o
(1 row)

I am reading https://postgrespro.com/list/thread-id/2333512 as "those two
should match".

PG Bug reporting form <noreply@postgresql.org> writes:
> when upgrading 15 to 16 (but also observed the same on 12 to 13), an
> extension that was previously created by a "normal" user is now owned by
> "postgres":

Yeah, this is a known shortcoming --- pg_dump doesn't make any effort
to preserve ownership of extensions.  Nobody's really been motivated
to do something about that.

            regards, tom lane

On Fri, Sep 20, 2024 at 10:16:32AM GMT, Tom Lane wrote:
> PG Bug reporting form <noreply@postgresql.org> writes:
> > when upgrading 15 to 16 (but also observed the same on 12 to 13), an
> > extension that was previously created by a "normal" user is now owned by
> > "postgres":
> 
> Yeah, this is a known shortcoming --- pg_dump doesn't make any effort
> to preserve ownership of extensions.  Nobody's really been motivated
> to do something about that.

:/

There is also no way for the user to update that ownership on their own,
after that happened, right?

foreman=> update pg_extension set extowner=16384 where extname='cube';
ERROR:  permission denied for table pg_extension

Background: my app uses "DROP OWNED BY CURRENT_USER CASCADE;" to clean
the DB (and then start fresh), which now (obviously) fails as the
extension is not owned and thus not dropped. But a type defined by the
extenion is still owned and is tried to be dropped, which leads to
errors.

I guess the same would also apply to any "ALTER EXTENSION … UPDATE" etc
calls by the user, which would now be denied.

Evgeni Golov <evgeni@golov.de> writes:
> On Fri, Sep 20, 2024 at 10:16:32AM GMT, Tom Lane wrote:
>> Yeah, this is a known shortcoming --- pg_dump doesn't make any effort
>> to preserve ownership of extensions.  Nobody's really been motivated
>> to do something about that.

> There is also no way for the user to update that ownership on their own,
> after that happened, right?
> foreman=> update pg_extension set extowner=16384 where extname='cube';
> ERROR:  permission denied for table pg_extension

You could do that as superuser, but it's not really enough because
there are pg_shdepend entries that ought to be added/updated.

The bigger picture here is that it's not just the pg_extension object
whose ownership is at stake.  There are going to be objects belonging
to the extension that probably --- but not certainly --- should have
the same owner as the extension.  And it's not too clear what should
happen to them.  That's why we've not implemented ALTER EXTENSION
OWNER: it's not 100% clear what it should do to the contained objects.

And that's a problem for pg_dump, because its standard strategy for
dealing with object ownership is to issue ALTER <object> OWNER
commands.  It does have the ability to use SET SESSION AUTHORIZATION
commands instead, but as the fine manual says,

  --use-set-session-authorization

    Output SQL-standard SET SESSION AUTHORIZATION commands instead of
    ALTER OWNER commands to determine object ownership. This makes the
    dump more standards-compatible, but depending on the history of
    the objects in the dump, might not restore properly. Also, a dump
    using SET SESSION AUTHORIZATION will certainly require superuser
    privileges to restore correctly, whereas ALTER OWNER requires
    lesser privileges.

(I'm not sure offhand what cases there are that "might not restore
properly".  But the point about requiring superuser is surely valid.)

So the only simple fix is for pg_dump to issue SET SESSION
AUTHORIZATION before the CREATE EXTENSION command.  But we have not
really wanted to do that because of the requires-superuser angle.
Maybe we should just bite the bullet and do it, though.

Another idea might be to use SET ROLE instead of SET SESSION
AUTHORIZATION, because the backend will let you do that if you've been
granted appropriate privileges; it's not a hard "must be superuser"
check.  I do not recall why pg_dump doesn't do that already, but
perhaps there's a reason beyond historical accident.  It'd require
some research before we could consider changing that.

            regards, tom lane

On Fri, Sep 20, 2024 at 12:18:00PM GMT, Tom Lane wrote:
> Evgeni Golov <evgeni@golov.de> writes:
> > On Fri, Sep 20, 2024 at 10:16:32AM GMT, Tom Lane wrote:
> >> Yeah, this is a known shortcoming --- pg_dump doesn't make any effort
> >> to preserve ownership of extensions.  Nobody's really been motivated
> >> to do something about that.
> 
> > There is also no way for the user to update that ownership on their own,
> > after that happened, right?
> > foreman=> update pg_extension set extowner=16384 where extname='cube';
> > ERROR:  permission denied for table pg_extension
> 
> You could do that as superuser, but it's not really enough because
> there are pg_shdepend entries that ought to be added/updated.

At least in my trivial "create/upgrade" example, all entries in
pg_shdepend have refobjid of the user (16384).

What happens if those (pg_shdepend.refobjid, pg_extension.extowner)
are/get out of sync?

> The bigger picture here is that it's not just the pg_extension object
> whose ownership is at stake.  There are going to be objects belonging
> to the extension that probably --- but not certainly --- should have
> the same owner as the extension.  And it's not too clear what should
> happen to them.  That's why we've not implemented ALTER EXTENSION
> OWNER: it's not 100% clear what it should do to the contained objects.

Is there a way to find/list those related objects?

In my case where I want to be able to drop things afterwards anyway,
updating them to be owned by the user seems reasonable?

> And that's a problem for pg_dump, because its standard strategy for
> dealing with object ownership is to issue ALTER <object> OWNER
> commands.  It does have the ability to use SET SESSION AUTHORIZATION
> commands instead, but as the fine manual says,
> 
>   --use-set-session-authorization
> 
>     Output SQL-standard SET SESSION AUTHORIZATION commands instead of
>     ALTER OWNER commands to determine object ownership. This makes the
>     dump more standards-compatible, but depending on the history of
>     the objects in the dump, might not restore properly. Also, a dump
>     using SET SESSION AUTHORIZATION will certainly require superuser
>     privileges to restore correctly, whereas ALTER OWNER requires
>     lesser privileges.
> 
> (I'm not sure offhand what cases there are that "might not restore
> properly".  But the point about requiring superuser is surely valid.)
> 
> So the only simple fix is for pg_dump to issue SET SESSION
> AUTHORIZATION before the CREATE EXTENSION command.  But we have not
> really wanted to do that because of the requires-superuser angle.
> Maybe we should just bite the bullet and do it, though.

At least in the specific case of pg_upgrade, that'd be OK as it happens
as superuser anyway, right?

> Another idea might be to use SET ROLE instead of SET SESSION
> AUTHORIZATION, because the backend will let you do that if you've been
> granted appropriate privileges; it's not a hard "must be superuser"
> check.  I do not recall why pg_dump doesn't do that already, but
> perhaps there's a reason beyond historical accident.  It'd require
> some research before we could consider changing that.

Thanks for the explanation and background!

Evgeni

Evgeni Golov <evgeni@golov.de> writes:
> On Fri, Sep 20, 2024 at 12:18:00PM GMT, Tom Lane wrote:
>> You could do that as superuser, but it's not really enough because
>> there are pg_shdepend entries that ought to be added/updated.

> What happens if those (pg_shdepend.refobjid, pg_extension.extowner)
> are/get out of sync?

Stuff like DROP OWNED BY will misbehave, because it won't detect that
that extension belongs to that role.

>> The bigger picture here is that it's not just the pg_extension object
>> whose ownership is at stake.  There are going to be objects belonging
>> to the extension that probably --- but not certainly --- should have
>> the same owner as the extension.

> Is there a way to find/list those related objects?

Sure, \dx+ in psql, or look in pg_depend for 'e' dependencies.

            regards, tom lane