On Sun, Jun 2, 2024, 02:08 Jelte Fennema-Nio <m...@jeltef.nl> wrote: > This patch adds a new "owned_schema" option to the extension control > file that can be set to true to indicate that this extension wants to > own the schema in which it is installed.
Huge +1 Many managed PostgreSQL services block superuser access, but provide a way for users to trigger a create/alter extension as superuser. There have been various extensions whose SQL scripts can be tricked into calling a function that was pre-created in the extension schema. This is usually done by finding an unqualified call to a pg_catalog function/operator, and overloading it with one whose arguments types are a closer match for the provided values, which then takes precedence regardless of search_path order. The custom function can then do something like "alter user foo superuser". The sequence of steps assumes the user already has some kind of admin role and is gaining superuser access to their own database server. However, the superuser implicitly has shell access, so it gives attackers an additional set of tools to poke around in the managed service. For instance, they can change the way the machine responds to control plane requests, which can sometimes trigger further escalations. In addition, many applications use the relatively privileged default user, which means SQL injection issues can also escalate into superuser access and beyond. There are some static analysis tools like https://github.com/timescale/pgspot that address this issue, though it seems like a totally unnecessary hole. Using schema = pg_catalog, relocatable = false, and doing an explicit create schema (without "if not exists") plugs the hole by effectively disabling extension schemas. For extensions I'm involved in, I consider this to be a hard requirement. I think Jelte's solution is preferable going forward, because it preserves the flexibility that extension schemas were meant to provide, and makes the potential hazards of reusing a schema more explicit. cheers, Marco