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

Hi Jelte,

1. There is another dependency cycle reachable through CREATE EXTENSION
... SCHEMA ... CASCADE. Unlike the earlier two-object cycle, this one
produces a dump that fails to restore. A same-version pg_upgrade
completes but loses the prerequisite-extension dependency.

On v12 over 3c5d9d914fa, I used these SQL-only extensions:

os_base.control:
    default_version = '1.0'
    relocatable = true
    superuser = false

os_owned.control:
    default_version = '1.0'
    relocatable = true
    superuser = false
    owned_schema = true
    requires = 'os_base'

os_base--1.0.sql:
    CREATE FUNCTION os_base_value() RETURNS integer
    LANGUAGE SQL AS 'SELECT 1';

os_owned--1.0.sql:
    CREATE FUNCTION os_owned_value() RETURNS integer
    LANGUAGE SQL AS 'SELECT 1';

In a fresh database, this succeeds:

CREATE EXTENSION os_owned SCHEMA shared_schema CASCADE;

Both extensions end up in shared_schema. pg_depend then contains the
following cycle, where each arrow means "depends on":

    os_owned -> os_base -> shared_schema -> os_owned

In v12, getDependencies() already skips the direct dependency from
os_owned to shared_schema when ordering the dump. However, the longer
cycle above remains. Plain and directory dumps both warn:

pg_dump: warning: could not resolve dependency loop among these items:
pg_dump: detail: EXTENSION os_owned  (ID 2 OID 16389)
pg_dump: detail: EXTENSION os_base  (ID 3 OID 16387)
pg_dump: detail: SCHEMA shared_schema  (ID 9 OID 16386)

The plain dump and the SQL generated by pg_restore from the directory
archive contain these extension commands in this order:

CREATE EXTENSION IF NOT EXISTS os_owned WITH SCHEMA shared_schema;
CREATE EXTENSION IF NOT EXISTS os_base WITH SCHEMA shared_schema;

The first command requires os_base to be installed already, but it has
no CASCADE to install it. Plain restore with ON_ERROR_STOP and directory
restore with --exit-on-error --jobs=2 therefore fail at that command:

ERROR:  required extension "os_base" is not installed

However, pg_upgrade reports success but loses os_owned's dependency
on os_base.

I used the v12 build for both the old and new clusters. Before the
upgrade, pg_depend recorded that os_owned depends on os_base.
Both extensions still existed after the upgrade, but pg_depend
no longer recorded that os_owned depends on os_base.

Two possible approaches are:

a) Reject cross-extension dependency cycles involving an owned schema.
   Check CREATE EXTENSION, ALTER EXTENSION UPDATE and SET SCHEMA,
   including indirect dependencies. For this case, the error could
   suggest installing os_base in another schema first.

b) Support this layout in dump/restore and binary upgrade. Adding
   CASCADE to normal CREATE EXTENSION output might address ordinary
   restore, but binary upgrade needs its own handling, without running
   extension installation scripts or dropping prerequisite dependencies.

I'd prefer (b). It preserves the existing schema-placement rules and
keeps the reconstruction logic in the dump/upgrade path. Option (a)
would need checks across several DDL paths, including concurrent
changes, to avoid admitting the same cycle another way.
Binary upgrade still needs to preserve the original extension
dependencies, independently of any changes made for dump ordering.

2. In doc/src/sgml/ref/alter_extension.sgml:

> This form moves the extension's objects into another schema.

Could we replace that paragraph with:

    For an extension without an owned schema, this form moves its
    objects to an existing schema. If the extension owns its schema,
    this form instead renames that schema to new_schema, which must
    not already exist unless it is the current name. The extension
    must be relocatable.

3. In doc/src/sgml/catalogs.sgml, the pg_extension section is missing
extownedschema. Could we add the boolean column, for example with:

    True if the extension creates and owns the schema identified by
    extnamespace.

Regards,
Rui