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