Re: Extension security improvement: Add support for extensions with an owned schema - Mailing list pgsql-hackers

On Mon, 28 Jul 2025 at 00:03, Sadeq Dousti <msdousti@gmail.com> wrote:
> (a) The patch affects DROP EXTENSION in that it drops the schema as well, if it's owned by the extension. This needs
tobe mentioned in the documentation. In addition, an extra confirmation (e.g., "This will drop schema nnnn as well, do
youwish to continue?") when dropping the extension might be desired, as the extension schema could contain user data
(e.g.,pg_cron keeps the jobs and their execution details). 

This is already covered by docs for DROP EXTENSION:
"Dropping an extension causes its member objects <snip> to be dropped as well.

An extra confirmation or requiring of CASCADE seems unnecessary. The
schema itself will not contain user objects (that's entirely the point
of this change). It indeed might contain tables from the extension,
that contain user data, but that's no difference from extension
creating tables in other schemas. The only thing that will
additionally get removed for owned_schema extensions is the empty
schema that contained all of the other objects, that would normally be
removed.

> (b) From the patch description:
> > Writing the sql migration scripts that are run by CREATE EXTENSION
> > and ALTER EXTENSION UPDATE are security minefields for extension authors.
>
> While "ALTER EXTENSION UPDATE" is mentioned as a minefield, the patch does not fix it (only ALTER EXTENSION ... SET
SCHEMAis affected AFAICS). A possible remedy could be that, before the update, the extension makes sure no (sensitive?)
object(e.g., UDF/Operator) created by a non-superuser exists in its schema. 

The whole security issue comes from the fact that the schema that an
extension gets installed in might be owned by a low-privileged user.
By having the schema be created by (and thus be owned by) the
extension creator, this whole problem goes away.

> (c) Does it make sense to add the "owned_schema" option to the CREATE EXTENSION command? Something like:
>
> CREATE EXTENSION xyz
> WITH owned_schema=true

I don't think that's a good idea. Since the migration script behaviour
can change significantly, I don't think it's safe to allow people to
specify it in CREATE EXTENSION. Especially because then people would
likely also be able to set it to false.

> An alternative could be to have it by default true (security by default), and if the DBA doesn't want it for whatever
reason,they have to explicitly set it to false during CREATE EXTENSION. 

I think changing the default would probably be good. Extension authors
can then explicitly opt-in to the less secure option if they require
that. I didn't want to do that for the initial change, as this seems
probably more contentious than adding a new option.

> (d) As David (Wheeler) mentioned in the thread, an extension control file can have the "requires" field, in which an
extensionX installation depends on other extensions Y & Z to be installed. I was thinking if X calls a function from Y
duringinstallation, and Y does not have owned_schema, the search_path confusion attack can be transitively applied. It
couldmake sense that X refuses to install, unless both Y & Z (= all required extensions) are marked as
owned_schema=true.Although in favor of backwards compatibility, this can be overridable by an option in CREATE
EXTENSION,such as "WITH transitive_owned_schema=false". 

I think it's fine to depend on extensions without owned_schema as an
owned_schema extension. It's not as if it's impossible to write safe
extension scripts now, it's just quite hard. An extension author can
choose to use owned_schema, to make their own life easier when writing
those scripts, but they can still depend on a perfectly safe extension
that did not use owned_schema but correctly hardened its extension
migration scripts.