Extension schemas

[marcoslot]

What are people's thoughts on extension schemas?

So far, I think the only right way of creating extensions is to use:

relocatable = false
schema = pg_catalog

and to explicitly do a CREATE SCHEMA myextschema in the SQL script, which deliberately fails if the schema already exists. This effectively disables extension schemas altogether.

The reason why this is important is that extension schemas can exist prior to the extension being created and a less privileged user may be able to create functions in the schema. The extension schema always is always in the search_path during the execution of CREATE/ALTER extension scripts, which is usually done as superuser.

When you do an unqualified function or operator call in PostgreSQL, pg_catalog takes precedence of whatever is in the search_path, but not if the function is overloaded and there's a better match for the argument types. For instance, if you call fn('hello') and there are two functions pg_catalog.fn(name) and myextschema.fn(text), PostgreSQL will prefer to call the latter because it does not require an implicit cast.

Hence, a CREATE/ALTER extension that performs some unqualified function/operator call (luckily not super common, most commands are DDL) could be tricked into calling a function in an extension schema created by a less privileged user. That's a privilege escalation path that frequently hits managed services.

Jelte has a proposal for requiring that the extension owns the schema, which does make sense:
https://www.postgresql.org/message-id/flat/CAFMSG9HKmbD%2Bu8PamLRsM3uQXvFC-CV-vR_fUbpUNhb4_Z71jQ%40mail.gmail.com#50bac4bf4cd1c0ba265255bb57f76278

Note: an unqualified operator call looks like =, a qualified operator operator call looks like OPERATOR(pg_catalog.=)

[df7cb]

Putting custom stuff into pg_catalog feels very wrong to me. Is doing that even supported?

[marcoslot]

Using schema = pg_catalog does not imply putting stuff into pg_catalog, it only affects the search_path and the @extschema@ macro. An extension can put stuff wherever it wants regardless of the extension schema. My position is that an extension should always create its own schema to make sure it's owned by the extension and put stuff in there. Using pg_catalog as the extension schema is to negate the search_path.

(but to answer the question: yes, it is possible to put stuff into pg_catalog, e.g. Timescale and Citus put functions there)

[fabriziomello]

we do not put things on pg_catalog ... we have our own schema for catalog tables _timescaledb_catalog. But we lock down all search_path related stuff to pg_catalog. And we also developed a small tool to spot possible vulnerabilities on sql files (https://github.com/timescale/pgspot).

[df7cb]

I asked about supported, not possible 😁

[JohnHVancouver]

What do you mean by supported? You can create tables in pg_catalog and those will carry over fine on pg_upgrade as long as you're not manipulating the Oids directly so they clash on the newer version. The documentation has a call out [0] for naming conflicts but beyond I think it just works.

[0] https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-CATALOG

[JohnHVancouver]

So far, I think the only right way of creating extensions is to use:
...
could be tricked into calling a function in an extension schema created by a less privileged user

Is there a reason you prefer forcing schema = 'pg_catalog' as opposed to setting search_path in the extension script? e.g.

CREATE schema myextschema;
SET search_path = 'myextschema, pg_catalog'

could be tricked into calling a function in an extension schema created by a less privileged user

I really like the example you provided, and I think it demonstrates how unintuitive it is for people who aren't familiar with Postgres that CREATE/ALTER extension can lead to privilege escalation.

[keithf4]

As I discussed elsewhere (pgq/pgq#22 & citusdata/pg_cron#274), I am against this for several reasons the main ones being

• This breaks the lookup for what schema an extension is installed to by querying the system catalogs. When you query what schema an extension is installed to when doing this, it tells you pg_catalog even though no objects are actually located there. When one extension has a dependency on another extension, or you just have some custom code that needs to use an extension object, you need to be able to easily look up what schema its objects are located in. To find what schema an extension object is installed to with this proposed method you either have to search through the extension's installation script (external to the database) or look up each individual object's catalog location. Unless you have full control of all extensions you're dependent on, you cannot control what schema someone may be installing something into.

• Discussions with any core developer will tell you not to touch pg_catalog with user objects. Yes, some extensions may not follow this rule, but that doesn't mean it's a good idea that we should be encouraging as an extension best practice. While you're not putting any objects into that schema, you are touching a schema that is basically in control of upstream. If some issue ever came up around doing this and it was disallowed upstream, any extension doing this would be severely broken. Or even simply if the issues brought up here were addressed in core, now these extensions are stuck in pg_catalog and not easy to return to being more flexible with their schema options. Once an extension's installation schema is set to pg_catalog, it cannot be changed since objects placed into pg_catalog cannot be changed to another schema via ALTER SCHEMA (https://dba.stackexchange.com/questions/107389/is-it-recommended-to-install-extensions-into-pg-catalog-schema/107406#107406).

• Forcing the schema an extension is installed to to be part of the extension may not be desirable to everyone. If you're the author and that's what you want to do that's your prerogative. However, I've found the flexibility of allowing users to set their own schema to be preferable. Some users may want to put all extensions into a general schema where they're all installed to so users know where to find custom extension code. Or maybe keep related extensions in the same schema (Ex. put all the PostGIS extensions into a postgis schema). With this method every single extension would need to explicitly name its schema and users would have never have any options as to what schema to use, contrary to what core PG seems to allow. I'm fully on board with the proposed option to set the schema to be owned by the extension.

• The security concern of unqualified object calls can be addressed by qualifying object calls. This should be the best practice that is encouraged, not this workaround. If there is further security concerns around this that cannot be addressed by fully qualifying object calls, that should be brought up with core to see if there's some better way to handle it. I see in that discussion of the schema ownership, this looks like it is being discussed.

[postsql]

"pg_catalog takes precedence of whatever is in the search_path" is true only if pg_catalog is not in the search path itself. If it is, then seach happens in search_path-order and pg_catalog is not moved to the front to take preference. This is a known way to trick a privileged user to execute unqualified function or operator.
The proper way to avoid it is for the said privileged user to set the search_path yourself.