Re: Prevent extension creation in temporary schemas

[Michael Paquier]

On Wed, Feb 13, 2019 at 12:08:50PM +0100, Chris Travers wrote:
> If the point is visibility in \dx it seems to me we want to fix the \dx
> query.

Yes, I got to think a bit more about that case, and there are cases
where this actually works properly as this depends on the objects
defined in the extension.  Fixing \dx to not show up extensions
defined in temp schemas of other sessions is definitely a must in my
opinion, and I would rather drop the rest of the proposal for now.  A
similar treatment is needed for \dx+.

> For example: suppose I create a type in pg_temp and create a table in
> public with a column using that type.

I am wondering if this scenario could make sense to populate data on
other, existing, relations for a schema migration, and that a two-step
process is done, with temporary tables used as intermediates.  But
that sounds like the thoughts of a crazy man..

> What is the expected visibility in other sessions?
> 
> What happens to the table when I log out?

Anything depending on a temporary object will be dropped per
dependency links once the session is over.

Attached is a patch to adjust \dx and \dx+.  What do you think?
--
Michael

diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 4da6719ce7..59c26c6eee 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -5150,14 +5150,16 @@ listExtensions(const char *pattern)
 					  "FROM pg_catalog.pg_extension e "
 					  "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace "
 					  "LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid "
-					  "AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass\n",
+					  "AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass "
+					  "WHERE (n.nspname !~ 'pg_temp_' "
+					  "       OR n.oid = pg_catalog.pg_my_temp_schema())\n",
 					  gettext_noop("Name"),
 					  gettext_noop("Version"),
 					  gettext_noop("Schema"),
 					  gettext_noop("Description"));
 
 	processSQLNamePattern(pset.db, &buf, pattern,
-						  false, false,
+						  true, false,
 						  NULL, "e.extname", NULL,
 						  NULL);
 
@@ -5203,10 +5205,13 @@ listExtensionContents(const char *pattern)
 	initPQExpBuffer(&buf);
 	printfPQExpBuffer(&buf,
 					  "SELECT e.extname, e.oid\n"
-					  "FROM pg_catalog.pg_extension e\n");
+					  "FROM pg_catalog.pg_extension e "
+					  "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace "
+					  "WHERE (n.nspname !~ 'pg_temp_' "
+					  "       OR n.oid = pg_catalog.pg_my_temp_schema())\n");
 
 	processSQLNamePattern(pset.db, &buf, pattern,
-						  false, false,
+						  true, false,
 						  NULL, "e.extname", NULL,
 						  NULL);
 

signature.asc
Description: PGP signature