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

On 9/22/25 10:14, Jim Jones wrote:
> I also noticed that it is possible to LOCK a temp table from another
> session (as superuser).

It gets even stranger if the owner's session closes while there is a
LOCK in a different session:

== session 1 ==

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

postgres=# SELECT pg_backend_pid();
pg_backend_pid
----------------
3520132
(1 row)

postgres=# CREATE TEMPORARY TABLE foo AS SELECT 42 AS val;
SELECT 1
postgres=# \d foo
Table "pg_temp_6.foo"
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_6.foo IN ACCESS EXCLUSIVE MODE;
LOCK TABLE
postgres=*# SELECT pg_backend_pid();
pg_backend_pid
----------------
3520608
(1 row)

== session 1 ==

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

postgres=# \q

== session 3 ==

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

postgres=# SELECT pg_backend_pid();
pg_backend_pid
----------------
3521711
(1 row)

postgres=# SELECT locktype, relation::regclass, mode, granted, pid
FROM pg_locks
WHERE relation = 'pg_temp_6.foo'::regclass::oid;
locktype | relation | mode | granted | pid
----------+---------------+---------------------+---------+---------
relation | pg_temp_6.foo | AccessExclusiveLock | t | 3520608
relation | pg_temp_6.foo | AccessExclusiveLock | f | 3520132
(2 rows)

== session 4 ==

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

postgres=# SELECT pid, wait_event_type, wait_event, state
FROM pg_stat_activity
WHERE pid = 3520132;
pid | wait_event_type | wait_event | state
---------+-----------------+------------+-------
3520132 | Lock | relation | idle
(1 row)

pid 3520132 lives as long as session 2 holds a lock on pg_temp_6.foo.

Best, Jim