Re: We broke the defense against accessing other sessions' temp tables

Hi

On 9/21/25 21:39, David G. Johnston wrote:
> On Sun, Sep 21, 2025 at 12:19 PM Jelte Fennema-Nio <postgres@jeltef.nl
> <mailto:postgres@jeltef.nl>> wrote:
> 
>     On Sun, 21 Sept 2025 at 19:44, Tom Lane <tgl@sss.pgh.pa.us
>     <mailto:tgl@sss.pgh.pa.us>> wrote:
>     > Yeah, if it were 18-only we'd need a powwow about whether to break
>     > release freeze.  But since it's been there since 17 and nobody
>     > noticed, I'm content to wait till after 18.0 to fix it.
> 
>     People definitely noticed[1], but I agree that there's no reason to
>     rush this fix out before the 18 release.
> 
>     Patch looks good, but obviously needs some tests.

Here a few tests:

== session 1 ==

$ /usr/local/postgres-dev/bin/psql postgres
psql (19devel)
Type "help" for help.

postgres=# CREATE TEMPORARY TABLE tmp AS SELECT 42 AS val;
SELECT 1

postgres=# \d tmp
              Table "pg_temp_81.tmp"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 val    | integer |           |          |


== session 2 ==

$ /usr/local/postgres-dev/bin/psql postgres
psql (19devel)
Type "help" for help.

-- previously returned O rows
postgres=# SELECT * FROM pg_temp_81.tmp;
ERROR:  cannot access temporary tables of other sessions
-- previously returned DELETE 0
postgres=# DELETE FROM pg_temp_81.tmp;
ERROR:  cannot access temporary tables of other sessions
postgres=# TRUNCATE TABLE pg_temp_81.tmp;
ERROR:  cannot truncate temporary tables of other sessions
-- previously returned UPDATE 0
postgres=# UPDATE pg_temp_81.tmp SET val = NULL;
ERROR:  cannot access temporary tables of other sessions
postgres=# INSERT INTO pg_temp_81.tmp VALUES (0);
ERROR:  cannot access temporary tables of other sessions
postgres=# COPY pg_temp_81.tmp TO '/tmp/x';
ERROR:  cannot access temporary tables of other sessions
postgres=# ALTER TABLE pg_temp_81.tmp ADD COLUMN foo text;
ERROR:  cannot alter temporary tables of other sessions

Is ALTER TABLE ... RENAME a loophole? I tested this in PostgreSQL 15.14
and the result was the same:

postgres=# ALTER TABLE pg_temp_81.tmp RENAME TO foo;
ALTER TABLE

== session 1 ==

postgres=# \d tmp
Did not find any relation named "tmp".
postgres=# \d foo
              Table "pg_temp_81.foo"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 val    | integer |           |          |


I also noticed that it is possible to LOCK a temp table from another
session (as superuser).

== session 1 ==

$ /usr/local/postgres-dev/bin/psql postgres
psql (19devel)
Type "help" for help.

postgres=# CREATE TEMPORARY TABLE tmp AS SELECT 42 AS val;
SELECT 1
postgres=# \d tmp
              Table "pg_temp_86.tmp"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 val    | integer |           |          |


== session 2 ==

$ /usr/local/postgres-dev/bin/psql postgres
psql (19devel)
Type "help" for help.

postgres=# BEGIN;
BEGIN
postgres=*# LOCK TABLE pg_temp_86.tmp IN ACCESS EXCLUSIVE MODE;
LOCK TABLE
postgres=*#

Doesn't it mean that one session can trivially DoS another session's
private temp table?

== session 1 ==

postgres=# SELECT locktype, relation::regclass, mode, granted, pid
FROM pg_locks
WHERE relation = 'pg_temp_86.tmp'::regclass::oid;
 locktype | relation |        mode         | granted |   pid
----------+----------+---------------------+---------+---------
 relation | tmp      | AccessExclusiveLock | t       | 3286840
(1 row)

postgres=# SELECT * FROM tmp;
^CCancel request sent
ERROR:  canceling statement due to user request
CONTEXT:  waiting for AccessShareLock on relation 47632 of database 5
postgres=#


I didn't test all possible LOCK modes, but I suspect they all work.

== session 2 ==

postgres=# BEGIN;
BEGIN
postgres=*# LOCK TABLE pg_temp_86.tmp IN SHARE MODE;
LOCK TABLE
postgres=*#

== session 1 ==

postgres=# SELECT locktype, relation::regclass, mode, granted, pid
FROM pg_locks
WHERE relation = 'pg_temp_86.tmp'::regclass::oid;
 locktype | relation |   mode    | granted |   pid
----------+----------+-----------+---------+---------
 relation | tmp      | ShareLock | t       | 3289767
(1 row)

postgres=# SELECT * FROM tmp;
 val
-----
  42
(1 row)

As expected with non-superusers it returns a permission denied for the
temp schema:

== session 2 ==

postgres=# CREATE USER u1;
CREATE ROLE
postgres=# SET ROLE u1;
SET
postgres=> BEGIN;
BEGIN
postgres=*> LOCK TABLE pg_temp_86.tmp IN ACCESS EXCLUSIVE MODE;
ERROR:  permission denied for schema pg_temp_86
postgres=!>



Best regards, Jim