Re: Prevent extension creation in temporary schemas

On Wed, Mar 6, 2019 at 3:19 AM Michael Paquier <michael(at)paquier(dot)xyz> wrote:

> On Tue, Mar 05, 2019 at 12:47:54PM +0000, Chris Travers wrote:
> > I tried installing a test extension into a temp schema. I found
> > this was remarkably difficult to do because pg_temp did not work (I
> > had to create a temporary table and then locate the actual table it
> > was created in). While that might also be a bug it is not in the
> > scope of this patch so mostly noting in terms of future work.
>
> pgcrypto works in this case.
>

So the issue here is in finding the pg temp schema to install into. The
extension is less of an issue.

The point of my note above is that there are other sharp corners that have
to be rounded off in order to make this work really well.

>
> > After creating the extension I did as follows:
> > \dx in the current session shows the extension
> > \dx in a stock psql shows the extension in a separate session
> > \dx with a patched psql in a separate session does not show the
> > extension.
> >
> > In terms of the scope of this patch, I think this correctly and
> > fully solves the problem at hand.
>
> I was just looking at this patch this morning with fresh eyes, and I
> think that I have found one argument to *not* apply it. Imagine the
> following in one session:
> =# create extension pgcrypto with schema pg_temp_3;
> CREATE EXTENSION
> =# \dx
> List of installed extensions
> Name | Version | Schema | Description
> ----------+---------+------------+------------------------------
> pgcrypto | 1.3 | pg_temp_3 | cryptographic functions
> plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
> (2 rows)
>
> That's all good, we see that the session which created this extension
> has it listed. Now let's use in parallel a second session:
> =# create extension pgcrypto with schema pg_temp_4;
> ERROR: 42710: extension "pgcrypto" already exists
> LOCATION: CreateExtension, extension.c:1664
> =# \dx
> List of installed extensions
> Name | Version | Schema | Description
> ----------+---------+------------+------------------------------
> plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
> (1 row)
>
> This is actually also good, because the extension of the temporary
> schema of the first session does not show up. Now I think that this
> can bring some confusion to the user actually, because the extension
> becomes not listed via \dx, but trying to create it with a different
> schema fails.
>

Ok so at present I see three distinct issues here, where maybe three
different patches over time might be needed.

The issues are:

1. create extension pgcrypto with schema pg_temp; fails because there is
no schema actually named pg_temp.
2. If you work around this, the \dx shows temporary extensions in other
sessions. This is probably the most minor issue of the three.
3. You cannot create the same extension in two different schemas.

My expectation is that this may be a situation where other sharp corners
are discovered over time. My experience is that where things are difficult
to do in PostgreSQL and hence not common, these sharp corners exist
(domains vs constraints in table-based composite types for example,
multiple inheritance being another).

It is much easier to review patches if they make small, well defined
changes to the code. I don't really have an opinion on whether this should
be applied as is, or moved to next commitfest in the hope we can fix issue
#3 there too. But I would recommend not fixing the pg_temp naming (#1
above) until at least the other two are fixed. There is no sense in making
this easy yet. But I would prefer to review or write patches that address
these issues one at a time rather than try to get them all reviewed and
included together.

But I don't think there is likely to be a lot of user confusion here. It
is hard enough to install extensions in temporary schemas that those who do
can be expected to know more that \dx commands.

>
> Thoughts?
> --
> Michael
>

--
Best Regards,
Chris Travers
Head of Database

Tel: +49 162 9037 210 | Skype: einhverfr | www.adjust.com
Saarbrücker Straße 37a, 10405 Berlin