Hi
On 9/21/25 21:39, David G. Johnston wrote:
> On Sun, Sep 21, 2025 at 12:19 PM Jelte Fennema-Nio <[email protected]
> <mailto:[email protected]>> wrote:
>
> On Sun, 21 Sept 2025 at 19:44, Tom Lane <[email protected]
> <mailto:[email protected]>> 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
