MIME-Version: 1.0
References: <585e996c-a5c6-4e61-acc4-d92b7a1458ea@vondra.me>
 <CAGECzQS02M6YPDXemo36tShO-ZYObjqnyTJyVttua1PGyN4xRw@mail.gmail.com>
 <CAFPkQKzALOTTBrhj2qDHwVxZQyjF5Xg_P9M=Tn_Dcm3vr=xdTA@mail.gmail.com>
 <DBOFQN54M5FX.1XWJE4LKMR8XH@jeltef.nl>
 <CA+TgmoY=NO7_L=UDuoUWj-icABF-7EP=UNUXCFBYpDNFoUZmbA@mail.gmail.com>
 <CA+TgmoYDdYA1paUKtfHfx-iDdCKrL05m2OwPHz7SQ03t49f2oQ@mail.gmail.com>
 <CAOBaU_YTJwo=jevDDKXRjwFUqON2VoWqz=Aw0FedyxbfYSiisw@mail.gmail.com>
 <CAGECzQS9JqWv+zJR-e-1JMH7GhCnLc4vD9H-uEui8E5Ba9Trpw@mail.gmail.com>
 <aLaysb-v12hPW22V@jrouhaud>
 <CA+TgmoawwAoRZH2Hm8w-RP1QOebK9LQ=NzeJWWAz+pYhSQPT0g@mail.gmail.com>
 <aLt9f7u_jUnMgGOe@jrouhaud>
 <CAGECzQR8gnJ92R2joimAfg6VX_VZO2Dy2n2gG-Ozr3zQ7evmSA@mail.gmail.com>
 <CA+TgmoY0zKz-mkXjkRUd-vNT4sp+=j5aJKd6er9WgOH9Q0Qriw@mail.gmail.com>
 <CAGECzQT8Rjo73xJfS-KSouoj09oUSOM6UWhvy0JXXO8+U0qwwQ@mail.gmail.com>
In-Reply-To: 
 <CAGECzQT8Rjo73xJfS-KSouoj09oUSOM6UWhvy0JXXO8+U0qwwQ@mail.gmail.com>
From: Robert Haas <robertmhaas@gmail.com>
Date: Thu, 11 Sep 2025 10:52:14 -0400
Message-ID: 
 <CA+TgmoZsz_SNiTcofk2_2m8x1uEKV9ce=69r7qWSxYQg0uwX5Q@mail.gmail.com>
Subject: Re: Extension security improvement: Add support for extensions with
 an owned schema
To: Jelte Fennema-Nio <me@jeltef.nl>
Cc: Julien Rouhaud <rjuju123@gmail.com>,
 Artem Gavrilov <artem.gavrilov@percona.com>,
	Tomas Vondra <tomas@vondra.me>,
 "David G. Johnston" <david.g.johnston@gmail.com>,
	Jeff Davis <pgsql@j-davis.com>,
 PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2BTgmoZsz_SNiTcofk2_2m8x1uEKV9ce%3D69r7qWSxYQg0uwX5Q%40mail.gmail.com>
Precedence: bulk

On Thu, Sep 11, 2025 at 9:29=E2=80=AFAM Jelte Fennema-Nio <me@jeltef.nl> wr=
ote:
> You recall incorrectly ;) It only does that when you do:
> DROP EXTENSION ... CASCADE
>
> Otherwise you get errors like this:
>
>  DROP EXTENSION test_ext_owned_schema;
>  ERROR:  cannot drop extension test_ext_owned_schema because other
> objects depend on it
>  DETAIL:  function test_owned_schema_defaults.new_owned() depends on
> schema test_owned_schema_defaults

OK. Perhaps that needs some associated tests?

To be honest, I'm kind of leaning at this point toward saying we
shouldn't impose any special restrictions here. If the DROP doesn't
cascade, then the worst thing that can happen is that you make it hard
for yourself to drop your own extension cleanly. I think letting the
superuser and the schema owner do things and other people not is too
weird -- it basically boils down to ignoring GRANT sometimes, and I
think users will find it confusing. If we were going to have
special_tinkering_mode=3Dtrue|false that affected everyone equally, that
would make sense to me, but it sounds like nobody else really likes
that, so it's probably just a bad idea.

> > but somebody
> > could equally well just install an unrelated extension in the same
> > schema and then drop the first extension and, whoops.
>
> To be clear, that could only happen when that unrelated extension does
> not have owned_schema=3Dtrue. Because creating such an extension
> requires the schema to not exist yet. (And even then as explained
> above the accidental drop only happens when the user uses CASCADE.)

Sure.

--=20
Robert Haas
EDB: http://www.enterprisedb.com