The code LGTM (commit message still missing though)
Given that the lack of tests allowed this bug to go undetected until
now, I'd suggest to include additional tests in this patch to prevent
similar issues in the future. Something like 0002 attached. What do you
think?
Best, Jim
From acef3ddfec92088ba0ff77115f3cc0b74b13a5d5 Mon Sep 17 00:00:00 2001
From: Daniil Davidov <d.davy...@postgrespro.ru>
Date: Thu, 25 Sep 2025 20:06:40 +0700
Subject: [PATCH v10 1/2] Fix accessing other sessions temp tables
---
src/backend/catalog/namespace.c | 56 ++++++++++++++++++++------------
src/backend/commands/tablecmds.c | 3 +-
src/backend/nodes/makefuncs.c | 6 +++-
src/backend/parser/gram.y | 11 ++++++-
src/include/catalog/namespace.h | 2 ++
5 files changed, 55 insertions(+), 23 deletions(-)
diff --git a/src/backend/catalog/namespace.c b/src/backend/catalog/namespace.c
index ed9aeee24b..3dee97e8ef 100644
--- a/src/backend/catalog/namespace.c
+++ b/src/backend/catalog/namespace.c
@@ -498,28 +498,44 @@ RangeVarGetRelidExtended(const RangeVar *relation, LOCKMODE lockmode,
*/
if (relation->relpersistence == RELPERSISTENCE_TEMP)
{
- if (!OidIsValid(myTempNamespace))
- relId = InvalidOid; /* this probably can't happen? */
- else
- {
- if (relation->schemaname)
- {
- Oid namespaceId;
+ Oid namespaceId;
- namespaceId = LookupExplicitNamespace(relation->schemaname, missing_ok);
+ if (relation->schemaname)
+ {
+ namespaceId = LookupExplicitNamespace(relation->schemaname, missing_ok);
+ /*
+ * If the user has specified an existing temporary schema
+ * owned by another user.
+ */
+ if (OidIsValid(namespaceId) && namespaceId != myTempNamespace)
+ {
/*
- * For missing_ok, allow a non-existent schema name to
- * return InvalidOid.
+ * We don't allow users to access temp tables of other
+ * sessions except for the case of dropping tables.
*/
- if (namespaceId != myTempNamespace)
+ if (!(flags & RVR_OTHER_TEMP_OK))
ereport(ERROR,
- (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
- errmsg("temporary tables cannot specify a schema name")));
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot access temporary relations of other sessions")));
}
+ }
+ else
+ {
+ namespaceId = myTempNamespace;
- relId = get_relname_relid(relation->relname, myTempNamespace);
+ /*
+ * If this table was recognized as temporary, it means that we
+ * found it because backend's temporary namespace was specified
+ * in search_path. Thus, MyTempNamespace must contain valid oid.
+ */
+ Assert(OidIsValid(namespaceId));
}
+
+ if (missing_ok && !OidIsValid(namespaceId))
+ relId = InvalidOid;
+ else
+ relId = get_relname_relid(relation->relname, namespaceId);
}
else if (relation->schemaname)
{
@@ -3620,21 +3636,19 @@ get_namespace_oid(const char *nspname, bool missing_ok)
RangeVar *
makeRangeVarFromNameList(const List *names)
{
- RangeVar *rel = makeRangeVar(NULL, NULL, -1);
+ RangeVar *rel;
switch (list_length(names))
{
case 1:
- rel->relname = strVal(linitial(names));
+ rel = makeRangeVar(NULL, strVal(linitial(names)), -1);
break;
case 2:
- rel->schemaname = strVal(linitial(names));
- rel->relname = strVal(lsecond(names));
+ rel = makeRangeVar(strVal(linitial(names)), strVal(lsecond(names)), -1);
break;
case 3:
+ rel = makeRangeVar(strVal(lsecond(names)), strVal(lthird(names)), -1);
rel->catalogname = strVal(linitial(names));
- rel->schemaname = strVal(lsecond(names));
- rel->relname = strVal(lthird(names));
break;
default:
ereport(ERROR,
@@ -3841,6 +3855,8 @@ GetTempNamespaceProcNumber(Oid namespaceId)
return INVALID_PROC_NUMBER; /* no such namespace? */
if (strncmp(nspname, "pg_temp_", 8) == 0)
result = atoi(nspname + 8);
+ else if (strcmp(nspname, "pg_temp") == 0)
+ result = MyProcNumber;
else if (strncmp(nspname, "pg_toast_temp_", 14) == 0)
result = atoi(nspname + 14);
else
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index fc89352b66..aa4a80f3f2 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -1624,7 +1624,8 @@ RemoveRelations(DropStmt *drop)
state.heapOid = InvalidOid;
state.partParentOid = InvalidOid;
- relOid = RangeVarGetRelidExtended(rel, lockmode, RVR_MISSING_OK,
+ relOid = RangeVarGetRelidExtended(rel, lockmode,
+ RVR_MISSING_OK | RVR_OTHER_TEMP_OK,
RangeVarCallbackForDropRelation,
&state);
diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c
index e2d9e9be41..62edf24b5c 100644
--- a/src/backend/nodes/makefuncs.c
+++ b/src/backend/nodes/makefuncs.c
@@ -478,10 +478,14 @@ makeRangeVar(char *schemaname, char *relname, int location)
r->schemaname = schemaname;
r->relname = relname;
r->inh = true;
- r->relpersistence = RELPERSISTENCE_PERMANENT;
r->alias = NULL;
r->location = location;
+ if (r->schemaname && strncmp(r->schemaname, "pg_temp", 7) == 0)
+ r->relpersistence = RELPERSISTENCE_TEMP;
+ else
+ r->relpersistence = RELPERSISTENCE_PERMANENT;
+
return r;
}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 9fd48acb1f..36f2f44cab 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -19406,7 +19406,11 @@ makeRangeVarFromAnyName(List *names, int position, core_yyscan_t yyscanner)
break;
}
- r->relpersistence = RELPERSISTENCE_PERMANENT;
+ if (r->schemaname && strncmp(r->schemaname, "pg_temp", 7) == 0)
+ r->relpersistence = RELPERSISTENCE_TEMP;
+ else
+ r->relpersistence = RELPERSISTENCE_PERMANENT;
+
r->location = position;
return r;
@@ -19446,6 +19450,11 @@ makeRangeVarFromQualifiedName(char *name, List *namelist, int location,
break;
}
+ if (r->schemaname && strncmp(r->schemaname, "pg_temp", 7) == 0)
+ r->relpersistence = RELPERSISTENCE_TEMP;
+ else
+ r->relpersistence = RELPERSISTENCE_PERMANENT;
+
return r;
}
diff --git a/src/include/catalog/namespace.h b/src/include/catalog/namespace.h
index f1423f28c3..b08d55f2c9 100644
--- a/src/include/catalog/namespace.h
+++ b/src/include/catalog/namespace.h
@@ -90,6 +90,8 @@ typedef enum RVROption
RVR_MISSING_OK = 1 << 0, /* don't error if relation doesn't exist */
RVR_NOWAIT = 1 << 1, /* error if relation cannot be locked */
RVR_SKIP_LOCKED = 1 << 2, /* skip if relation cannot be locked */
+ RVR_OTHER_TEMP_OK = 1 << 3 /* don't error if relation is temp relation of
+ other session (needed for DROP command) */
} RVROption;
typedef void (*RangeVarGetRelidCallback) (const RangeVar *relation, Oid relId,
--
2.43.0
From cb3144e6ea19b0c06b3c940cbfb6350eccf9ef69 Mon Sep 17 00:00:00 2001
From: Jim Jones <jim.jo...@uni-muenster.de>
Date: Fri, 26 Sep 2025 12:58:19 +0200
Subject: [PATCH v10 2/2] Test cross-session access restrictions on temporary
tables
These tests create a temporary table in one session and verify that another
session cannot perform SELECT, UPDATE, DELETE, TRUNCATE, INSERT, ALTER, COPY,
or LOCK operations on that table. It also confirms that DROP TABLE from
another session succeeds.
---
src/test/modules/test_misc/meson.build | 1 +
.../test_misc/t/009_temp_obj_multisession.pl | 126 ++++++++++++++++++
2 files changed, 127 insertions(+)
create mode 100644 src/test/modules/test_misc/t/009_temp_obj_multisession.pl
diff --git a/src/test/modules/test_misc/meson.build b/src/test/modules/test_misc/meson.build
index 6b1e730bf4..6b29ed90ef 100644
--- a/src/test/modules/test_misc/meson.build
+++ b/src/test/modules/test_misc/meson.build
@@ -17,6 +17,7 @@ tests += {
't/006_signal_autovacuum.pl',
't/007_catcache_inval.pl',
't/008_replslot_single_user.pl',
+ 't/009_temp_obj_multisession.pl',
],
},
}
diff --git a/src/test/modules/test_misc/t/009_temp_obj_multisession.pl b/src/test/modules/test_misc/t/009_temp_obj_multisession.pl
new file mode 100644
index 0000000000..9b0deaaf48
--- /dev/null
+++ b/src/test/modules/test_misc/t/009_temp_obj_multisession.pl
@@ -0,0 +1,126 @@
+# Copyright (c) 2025, PostgreSQL Global Development Group
+
+use strict;
+use warnings;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use PostgreSQL::Test::BackgroundPsql;
+use Test::More;
+
+# Set up a fresh node
+my $node = PostgreSQL::Test::Cluster->new('temp_lock');
+$node->init;
+$node->start;
+
+# Create a long-lived session
+my $psql1 = $node->background_psql('postgres');
+
+$psql1->query_safe(
+ q(CREATE TEMP TABLE foo AS SELECT 42 AS val;));
+
+my $tempschema = $node->safe_psql(
+ 'postgres',
+ q{
+ SELECT n.nspname
+ FROM pg_class c
+ JOIN pg_namespace n ON n.oid = c.relnamespace
+ WHERE relname = 'foo' AND relpersistence = 't';
+ }
+);
+chomp $tempschema;
+ok($tempschema =~ /^pg_temp_\d+$/, "got temp schema: $tempschema");
+
+
+# SELECT TEMPORARY TABLE from other session
+my ($stdout, $stderr);
+$node->psql(
+ 'postgres',
+ "SELECT val FROM $tempschema.foo;",
+ stderr => \$stderr
+);
+like($stderr, qr/cannot access temporary relations of other sessions/,
+ 'SELECT on other session temp table is not allowed');
+
+# UPDATE TEMPORARY TABLE from other session
+$node->psql(
+ 'postgres',
+ "UPDATE $tempschema.foo SET val = NULL;",
+ stderr => \$stderr
+);
+like($stderr, qr/cannot access temporary relations of other sessions/,
+ 'UPDATE on other session temp table is not allowed');
+
+# DELETE records from TEMPORARY TABLE from other session
+$node->psql(
+ 'postgres',
+ "DELETE FROM $tempschema.foo;",
+ stderr => \$stderr
+);
+like($stderr, qr/cannot access temporary relations of other sessions/,
+ 'DELETE on other session temp table is not allowed');
+
+# TRUNCATE TEMPORARY TABLE from other session
+$node->psql(
+ 'postgres',
+ "TRUNCATE TABLE $tempschema.foo;",
+ stderr => \$stderr
+);
+like($stderr, qr/cannot access temporary relations of other sessions/,
+ 'TRUNCATE on other session temp table is not allowed');
+
+# INSERT INTO TEMPORARY TABLE from other session
+$node->psql(
+ 'postgres',
+ "INSERT INTO $tempschema.foo VALUES (73);",
+ stderr => \$stderr
+);
+like($stderr, qr/cannot access temporary relations of other sessions/,
+ 'INSERT INTO on other session temp table is not allowed');
+
+# ALTER TABLE .. RENAME TEMPORARY TABLE from other session
+$node->psql(
+ 'postgres',
+ "ALTER TABLE $tempschema.foo RENAME TO bar;",
+ stderr => \$stderr
+);
+like($stderr, qr/cannot access temporary relations of other sessions/,
+ 'ALTER TABLE ... RENAME on other session temp table is blocked');
+
+# ALTER TABLE .. ADD COLUMN in TEMPORARY TABLE from other session
+$node->psql(
+ 'postgres',
+ "ALTER TABLE $tempschema.foo ADD COLUMN bar int;",
+ stderr => \$stderr
+);
+like($stderr, qr/cannot access temporary relations of other sessions/,
+ 'ALTER TABLE ... ADD COLUMN on other session temp table is blocked');
+
+# COPY TEMPORARY TABLE from other session
+$node->psql(
+ 'postgres',
+ "COPY $tempschema.foo TO '/tmp/x';",
+ stderr => \$stderr
+);
+like($stderr, qr/cannot access temporary relations of other sessions/,
+ 'COPY on other session temp table is blocked');
+
+# LOCK TEMPORARY TABLE from other session
+$node->psql(
+ 'postgres',
+ "BEGIN; LOCK TABLE $tempschema.foo IN ACCESS EXCLUSIVE MODE;",
+ stderr => \$stderr
+);
+like($stderr, qr/cannot access temporary relations of other sessions/,
+ 'LOCK on other session temp table is blocked');
+
+# DROP TEMPORARY TABLE from other session
+my $ok = $node->psql(
+ 'postgres',
+ "DROP TABLE $tempschema.foo;"
+);
+ok($ok == 0, 'DROP TABLE executed successfully');
+
+# Clean up
+$psql1->quit;
+
+done_testing();
--
2.43.0
