BUG with accessing to temporary tables of other sessions still exists

Started by Daniil Davydov8 days ago6 messageshackers
Jump to latest
#1Daniil Davydov
3danissimo@gmail.com

Hi,

Recently, we have added two commits ([1]40927d458fe1a8c96bcf418b47169f0f6eb15946, [2]ce146621f7860d2e19c509f1466feca3bf777678) that are fixing a bug with
accessing temporary tables of other sessions. I found out that this bug didn't
go away completely :

== session 1 ==

postgres=# CREATE TABLE empty_table (id INT);
CREATE TABLE

== session 2 ==

postgres=# INSERT INTO pg_temp_0.empty_table VALUES (1);
INSERT 0 1

As you can see, the INSERT command completes successfully. This happens because
empty_table has no pages, so the insert path looks like this :
heap_insert -> RelationGetBufferForTuple -> RelationAddBlocks -> ....

The RelationAddBlocks extends relations's smgr and allocates a new temp buffer
without calling ReadBufferExtended. Thus, we are 1) bypassing the
"RELATION_IS_OTHER_TEMP" check and 2) creating a buffer in our own temp buffers
pool. The (2) will lead to an error [3]ERROR: could not open file "base/5/t3_16386": No such file or directory if we attempt to flush such a buffer.

I suggest fixing it by checking whether the relation is other-temp-rel inside
the ExtendBufferedRelLocal function. As far as I can see, all
temp-relation-extend paths include this function.

Please, see the attached patch that fixes a problem and adds a new test.

[1]: 40927d458fe1a8c96bcf418b47169f0f6eb15946
[2]: ce146621f7860d2e19c509f1466feca3bf777678
[3]: ERROR: could not open file "base/5/t3_16386": No such file or directory

P.S.
Jim, I attach you in CC of this thread since you are the co-author of the
original fix. I hope you don't mind :)

--
Best regards,
Daniil Davydov

Attachments:

0001-Prevent-access-to-other-sessions-empty-temp-tables.patchtext/x-patch; charset=US-ASCII; name=0001-Prevent-access-to-other-sessions-empty-temp-tables.patchDownload+30-4
#2Jim Jones
jim.jones@uni-muenster.de
In reply to: Daniil Davydov (#1)
Re: BUG with accessing to temporary tables of other sessions still exists

Hi Daniil,
Good catch!

On 03.06.26 15:23, Daniil Davydov wrote:

Recently, we have added two commits ([1], [2]) that are fixing a bug with
accessing temporary tables of other sessions. I found out that this bug didn't
go away completely :

== session 1 ==

postgres=# CREATE TABLE empty_table (id INT);
CREATE TABLE

== session 2 ==

postgres=# INSERT INTO pg_temp_0.empty_table VALUES (1);
INSERT 0 1

Session 1 here does not create a temporary table (most likely a copy &
paste error), but I could reproduce this error as you suggested:

== session 1 ==

postgres=# CREATE TEMPORARY TABLE t (id int);

== session 2 ==

postgres=# \d pg_temp*.*
Table "pg_temp_2.t"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |

postgres=# INSERT INTO pg_temp_2.t VALUES (42);
INSERT 0 1
postgres=# INSERT INTO pg_temp_2.t VALUES (42);
ERROR: cannot access temporary tables of other sessions

It only fails after the second INSERT attempt.

With your patch applied it returns an error also in the first query:

psql (19devel)
Type "help" for help.

postgres=# INSERT INTO pg_temp_0.t VALUES (42);
ERROR: cannot access temporary tables of other sessions

As you can see, the INSERT command completes successfully. This happens because
empty_table has no pages, so the insert path looks like this :
heap_insert -> RelationGetBufferForTuple -> RelationAddBlocks -> ....

The RelationAddBlocks extends relations's smgr and allocates a new temp buffer
without calling ReadBufferExtended. Thus, we are 1) bypassing the
"RELATION_IS_OTHER_TEMP" check and 2) creating a buffer in our own temp buffers
pool. The (2) will lead to an error [3] if we attempt to flush such a buffer.

I suggest fixing it by checking whether the relation is other-temp-rel inside
the ExtendBufferedRelLocal function. As far as I can see, all
temp-relation-extend paths include this function.

Please, see the attached patch that fixes a problem and adds a new test.

At a first glance the check seems reasonable. One tiny wording nit: the
comment in ExtendBufferedRelLocal says "... covering any attempt to
extend local relation.", but to avoid any confusing with the meaning of
RELATION_IS_LOCAL I'd argue that "covering any attempt to extend a
temporary relation" would be slightly clearer.

Thanks for the fix!

Best, Jim

#3Daniil Davydov
3danissimo@gmail.com
In reply to: Jim Jones (#2)
Re: BUG with accessing to temporary tables of other sessions still exists

Hi,

On Wed, Jun 3, 2026 at 10:20 PM Jim Jones <jim.jones@uni-muenster.de> wrote:

Session 1 here does not create a temporary table (most likely a copy &
paste error), but I could reproduce this error as you suggested:

Sorry, I wrote it manually and forgot to specify "TEMP". It is implied here,
of course.

At a first glance the check seems reasonable. One tiny wording nit: the
comment in ExtendBufferedRelLocal says "... covering any attempt to
extend local relation.", but to avoid any confusing with the meaning of
RELATION_IS_LOCAL I'd argue that "covering any attempt to extend a
temporary relation" would be slightly clearer.

Thanks for looking into this! I agree with your comment.

Please, see v2 patch with fixed comment.

--
Best regards,
Daniil Davydov

Attachments:

v2-0001-Prevent-access-to-other-sessions-empty-temp-table.patchtext/x-patch; charset=US-ASCII; name=v2-0001-Prevent-access-to-other-sessions-empty-temp-table.patchDownload+30-4
#4Imran Zaheer
imran.zhir@gmail.com
In reply to: Daniil Davydov (#3)
Re: BUG with accessing to temporary tables of other sessions still exists

Hi

During testing this patch I also noticed that other sessions are also
able to drop the temporary table.

postgres=# drop table pg_temp_0.empty_table;
DROP TABLE

Above command works just fine from the other session.

I was able to fix that by adding the same check in
heap_drop_with_catalog in heap.c, but I'm not sure whether it's the
right place to add this check.

@@ -1847,6 +1847,11 @@ heap_drop_with_catalog(Oid relid)
*/
rel = relation_open(relid, AccessExclusiveLock);

+       if (RELATION_IS_OTHER_TEMP(rel))
+               ereport(ERROR,
+                               (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+                               errmsg("cannot access temporary tables
of other sessions")));
+
        /*
         * There can no longer be anyone *else* touching the relation, but we
         * might still have open queries or cursors, or pending
trigger events, in

Let me know if I am missing something.

Thanks
Imran Zaheer

Show quoted text

On Wed, Jun 3, 2026 at 8:51 PM Daniil Davydov <3danissimo@gmail.com> wrote:

Hi,

On Wed, Jun 3, 2026 at 10:20 PM Jim Jones <jim.jones@uni-muenster.de> wrote:

Session 1 here does not create a temporary table (most likely a copy &
paste error), but I could reproduce this error as you suggested:

Sorry, I wrote it manually and forgot to specify "TEMP". It is implied here,
of course.

At a first glance the check seems reasonable. One tiny wording nit: the
comment in ExtendBufferedRelLocal says "... covering any attempt to
extend local relation.", but to avoid any confusing with the meaning of
RELATION_IS_LOCAL I'd argue that "covering any attempt to extend a
temporary relation" would be slightly clearer.

Thanks for looking into this! I agree with your comment.

Please, see v2 patch with fixed comment.

--
Best regards,
Daniil Davydov

#5Daniil Davydov
3danissimo@gmail.com
In reply to: Imran Zaheer (#4)
Re: BUG with accessing to temporary tables of other sessions still exists

Hi,

On Thu, Jun 4, 2026 at 12:43 AM Imran Zaheer <imran.zhir@gmail.com> wrote:

During testing this patch I also noticed that other sessions are also
able to drop the temporary table.

postgres=# drop table pg_temp_0.empty_table;
DROP TABLE

Above command works just fine from the other session.

I was able to fix that by adding the same check in
heap_drop_with_catalog in heap.c, but I'm not sure whether it's the
right place to add this check.

Let me know if I am missing something.

It may be counter intuitive, but we allow dropping other session's temp tables.
You can find the rationale for this in the 013_temp_obj_multisession.pl test.
You can also read this message [1]/messages/by-id/4075754.1774378690@sss.pgh.pa.us in the previous discussion. In short, we
prohibit looking at other-temp-table's pages not because they belong to another
session, but because current temp_buffers implementation doesn't provide the
ability to do so. Moreover, the ability to DROP other temp tables can be useful
for autovacuum (see orphaned temp tables removal logic) and administrators.

[1]: /messages/by-id/4075754.1774378690@sss.pgh.pa.us

--
Best regards,
Daniil Davydov

#6Imran Zaheer
imran.zhir@gmail.com
In reply to: Daniil Davydov (#5)
Re: BUG with accessing to temporary tables of other sessions still exists

Got it, Thanks for making this clean.

Regards,
Imran Zaheer

Show quoted text

On Wed, Jun 3, 2026 at 11:33 PM Daniil Davydov <3danissimo@gmail.com> wrote:

Hi,

On Thu, Jun 4, 2026 at 12:43 AM Imran Zaheer <imran.zhir@gmail.com> wrote:

During testing this patch I also noticed that other sessions are also
able to drop the temporary table.

postgres=# drop table pg_temp_0.empty_table;
DROP TABLE

Above command works just fine from the other session.

I was able to fix that by adding the same check in
heap_drop_with_catalog in heap.c, but I'm not sure whether it's the
right place to add this check.

Let me know if I am missing something.

It may be counter intuitive, but we allow dropping other session's temp tables.
You can find the rationale for this in the 013_temp_obj_multisession.pl test.
You can also read this message [1] in the previous discussion. In short, we
prohibit looking at other-temp-table's pages not because they belong to another
session, but because current temp_buffers implementation doesn't provide the
ability to do so. Moreover, the ability to DROP other temp tables can be useful
for autovacuum (see orphaned temp tables removal logic) and administrators.

[1] /messages/by-id/4075754.1774378690@sss.pgh.pa.us

--
Best regards,
Daniil Davydov