Thread: Quirk of pg_temp schemas ...

While fixing up a patch I had dealing with temporary tables I noticed
a bit of a quirk with pg_temp schemas. Namely that we have no actual
meta data marking them as temporary aside from their names. And we
don't do anything to protect that -- superuser can happily issue ALTER
SCHEMA RENAME to rename it to a name that doesn't match pg_temp*. The
rest of the system then treats it as a perfectly normal schema that
just happens to contain temporary tables....

postgres=# create temporary table t(i integer);
CREATE TABLE

postgres=# \d t
                Table "pg_temp_4.t"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 i      | integer |           |          |

postgres=# alter schema  pg_temp_4 rename to fnord;
ALTER SCHEMA

postgres=# \d t
                  Table "fnord.t"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 i      | integer |           |          |


We could, I suppose, say this is just not a problem. Most, perhaps
all, of the existing code doesn't seem bothered by this situation. But
it seems a bit fragile. The worst side effect I've found is that
autovacuum won't drop orphaned temp tables because it can't check if
the backend is still alive connected to them.


A related point is that super-user is allowed to drop the temp schema.
If super-user does do this we still allow new temporary tables to be
created in the now-nonexistent schema resulting in tables that don't
print correctly:

postgres=# drop schema pg_temp_3 cascade;
NOTICE:  drop cascades to table t3
DROP SCHEMA

postgres=# create temporary table t4( i integer);
CREATE TABLE

postgres=# \d t4
                    Table ".t4"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 i      | integer |           |          |

I suspect there are sites that will try to fprintf NULL using %s here
which on glibc prints "(null)" but may crash elsewhere...

At the very least we should probably disallow creating temporary
tables if the temporary schema has been dropped. That's just creating
broken references in the catalog tables. Alternately we could rig
something so that dropping the schema unsets myTempNamespace.

The real fix seems to me adding a "istemp" and "backendid" columns to
pg_namespace and not depending on the actual name of the schema to
store this info in. But I guess the current scheme has worked fine for
ages so I dunno. Perhaps the global temp table work will have to
invest in this area.

-- 
greg

Greg Stark <stark@mit.edu> writes:
> While fixing up a patch I had dealing with temporary tables I noticed
> a bit of a quirk with pg_temp schemas. Namely that we have no actual
> meta data marking them as temporary aside from their names. And we
> don't do anything to protect that -- superuser can happily issue ALTER
> SCHEMA RENAME to rename it to a name that doesn't match pg_temp*.

This seems to me to be not very different from the 1001 other ways that
a superuser can break a database.  If *non* superusers could rename
those schemas then I'd agree there's a problem to be solved.

            regards, tom lane

On Tue, Aug 10, 2021 at 11:30:35AM -0400, Tom Lane wrote:
> Greg Stark <stark@mit.edu> writes:
>> While fixing up a patch I had dealing with temporary tables I noticed
>> a bit of a quirk with pg_temp schemas. Namely that we have no actual
>> meta data marking them as temporary aside from their names. And we
>> don't do anything to protect that -- superuser can happily issue ALTER
>> SCHEMA RENAME to rename it to a name that doesn't match pg_temp*.

The fun does not stop here.  Here is one: drop the existing temporary
schema as superuser, keep the connection that dropped it opened, and
play with various temporary objects, even types or functions.

> This seems to me to be not very different from the 1001 other ways that
> a superuser can break a database.  If *non* superusers could rename
> those schemas then I'd agree there's a problem to be solved.

If non-superusers could do anything that change what's stored in
pg_namespace and make things inconsistent with the backend-specific
state stored in memory, we are in trouble.
--
Michael