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(at)jeltef(dot)nl
> <mailto:postgres(at)jeltef(dot)nl>> wrote:
>
> On Sun, 21 Sept 2025 at 19:44, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us
> <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)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