Dear Jelte, I like the idea! In fact, I was thinking about the general search_path confusion issue in Postgres (see also [1]), and what the root cause is. IMHO, some search paths should always take priority - e.g., if a function is defined in both pg_catalog and as a UDF, the UDF should only be called if it is fully qualified, regardless of what the search_path is. But that would require an overhaul of the Postgres resolution mechanism, and is out of the scope of this patch.
For this patch, I have a few suggestions: (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). (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. (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 This way, even if the extension itself is not (yet) updated to have owned_schema in its control file, the DBA can rely on the schema lifecycle management that comes with owned_schema=true. 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. (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". [1] https://www.cybertec-postgresql.com/en/abusing-security-definer-functions/ -- Best Regards, Sadeq Dousti Trade Republic Bank GmbH
