On Mon, 28 Jul 2025 at 00:03, Sadeq Dousti <msdou...@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 to be mentioned in the documentation. > In addition, an extra confirmation (e.g., "This will drop schema nnnn as > well, do you wish 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 SCHEMA is 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 extension X installation depends on > other extensions Y & Z to be installed. I was thinking if X calls a function > from Y during installation, and Y does not have owned_schema, the search_path > confusion attack can be transitively applied. It could make 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.
