Message-ID: <dbd3cc5a5edde04941c8624dde7e4bd2a063f366.camel@j-davis.com>
Subject: Re: Extension security improvement: Add support for extensions with
 an owned schema
From: Jeff Davis <pgsql@j-davis.com>
To: Jelte Fennema-Nio <me@jeltef.nl>, PostgreSQL-development
	 <pgsql-hackers@postgresql.org>
Date: Wed, 05 Jun 2024 10:53:31 -0700
In-Reply-To: <CAGECzQQzDqDzakBkR71ZkQ1N1ffTjAaruRSqppQAKu3WF+6rNQ@mail.gmail.com>
References: 
	<CAGECzQQzDqDzakBkR71ZkQ1N1ffTjAaruRSqppQAKu3WF+6rNQ@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.44.4-0ubuntu2 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/dbd3cc5a5edde04941c8624dde7e4bd2a063f366.camel%40j-davis.com>
Precedence: bulk

On Sat, 2024-06-01 at 17:08 -0700, Jelte Fennema-Nio 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. What that means is that the
> schema should not exist before creating the extension, and will be
> created during extension creation. This thus gives the extension
> author
> an easy way to use a safe search_path, while still allowing all
> objects
> to be grouped together in a schema. The implementation also has the
> pleasant side effect that the schema will be automatically dropped
> when
> the extension is dropped.

Is this orthogonal to relocatability?

When you say "an easy way to use a safe search_path": the CREATE
EXTENSION command already sets the search_path, so your patch just
ensures that it's empty (and therefore safe) first, right?

Should we go further and try to prevent creating objects in an
extension-owned schema with normal SQL?

Approximately how many extensions should be adjusted to use
owned_schema=3Dtrue? What are the reasons an extension would not want to
own the schema in which the objects are created? I assume some would
still create objects in pg_catalog, but ideally we'd come up with a
better solution to that as well.

This protects the extension script, but I'm left wondering if we could
do something here to make it easier to protect extension functions
called from outside the extension script, also. It would be nice if we
could implicitly tack on a "SET search_path TO @extschema@, pg_catalog,
pg_temp" to each function in the extension. I'm not proposing that, but
perhaps a related idea might work. Probably outside the scope of your
proposal.

Regards,
	Jeff Davis