Re: Prevent extension creation in temporary schemas

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.

> 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.

Thoughts?
--
Michael