Horton
Menu

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Prevent access to other sessions' temp tables

  2. Add tests for cross-session temp table access

  3. Doc: use "an SQL" consistently rather than "a SQL"

  4. Modify the relcache to record the temp status of both local and nonlocal

  1. Fix bug with accessing to temporary tables of other sessions

    Daniil Davydov <3danissimo@gmail.com> — 2025-04-14T05:36:15Z

    Hi,

During previous commitfest this topic already has been discussed
within the "Forbid to DROP temp tables of other sessions" thread [1].
Unfortunately its name doesn't reflect the real problem, so I decided
to start a new thread, as David G. Johnston advised.

Here are the summary results of the discussion [1] :
The superuser is only allowed to DROP temporary relations of other
sessions. Other commands (like SELECT, INSERT, UPDATE, DELETE ...)
must be forbidden to him. Error message for this case will look like
this : `could not access temporary relations of other sessions`.
For now, superuser still can specify such operations because of a bug
in the code that mistakenly recognizes other session's temp table as
permanent (I've covered this topic in more detail in [2]). Attached
patch fixes this bug (targeted on
b51f86e49a7f119004c0ce5d0be89cdf98309141).

Opened issue:
Not everyone liked the idea that table's persistence can be assigned
to table during makeRangeVarXXX calls (inside gram.y).
My opinion - `As far as "pg_temp_" prefix is reserved by the postgres
kernel, we can definitely say that we have encountered a temporary
table when we see this prefix.`

I will be glad to hear your opinion.

--
Best regards,
Daniil Davydov

[1] https://www.postgresql.org/message-id/CAJDiXgj72Axj0d4ojKdRWG_rnkfs4uWY414NL%3D15sCvh7-9rwg%40mail.gmail.com
[2] https://www.postgresql.org/message-id/CAJDiXgj%2B5UKLWSUT5605rJhuw438NmEKecvhFAF2nnrMsgGK3w%40mail.gmail.com

  2. Re: Fix bug with accessing to temporary tables of other sessions

    Stepan Neretin <slpmcf@gmail.com> — 2025-07-28T03:42:58Z

    On Mon, Apr 14, 2025 at 12:36 PM Daniil Davydov <3danissimo@gmail.com>
wrote:

> Hi,
>
> During previous commitfest this topic already has been discussed
> within the "Forbid to DROP temp tables of other sessions" thread [1].
> Unfortunately its name doesn't reflect the real problem, so I decided
> to start a new thread, as David G. Johnston advised.
>
> Here are the summary results of the discussion [1] :
> The superuser is only allowed to DROP temporary relations of other
> sessions. Other commands (like SELECT, INSERT, UPDATE, DELETE ...)
> must be forbidden to him. Error message for this case will look like
> this : `could not access temporary relations of other sessions`.
> For now, superuser still can specify such operations because of a bug
> in the code that mistakenly recognizes other session's temp table as
> permanent (I've covered this topic in more detail in [2]). Attached
> patch fixes this bug (targeted on
> b51f86e49a7f119004c0ce5d0be89cdf98309141).
>
> Opened issue:
> Not everyone liked the idea that table's persistence can be assigned
> to table during makeRangeVarXXX calls (inside gram.y).
> My opinion - `As far as "pg_temp_" prefix is reserved by the postgres
> kernel, we can definitely say that we have encountered a temporary
> table when we see this prefix.`
>
> I will be glad to hear your opinion.
>
> --
> Best regards,
> Daniil Davydov
>
> [1]
> https://www.postgresql.org/message-id/CAJDiXgj72Axj0d4ojKdRWG_rnkfs4uWY414NL%3D15sCvh7-9rwg%40mail.gmail.com
> [2]
> https://www.postgresql.org/message-id/CAJDiXgj%2B5UKLWSUT5605rJhuw438NmEKecvhFAF2nnrMsgGK3w%40mail.gmail.com



 Hi Daniil,

Your patch for securing cross-session temp table access is a great
improvement. The RVR_OTHER_TEMP_OK flag elegantly handles the DROP case
while keeping the main restriction in place.

For schema name validation, an exact strcmp for "pg_temp" and proper
numeric parsing for "pg_temp_X" would be more precise than the current
prefix check. This would avoid any accidental matches to similarly named
schemas.

The error message could be adjusted to emphasize permissions, like
"permission denied for cross-session temp table access". This would make
the security intent clearer to users.

I noticed the Assert assumes myTempNamespace is always valid. While
correct, a brief comment explaining why this is safe would help future
maintainers. The relpersistence logic could also be centralized in one
place for consistency.

I've added an isolation test to verify the behavior when trying to access
another backend's temp tables. It confirms the restrictions work as
intended while allowing permitted operations.

Thanks for working on this important security enhancement!

Best regards,
Stepan Neretin

  3. Re: Fix bug with accessing to temporary tables of other sessions

    Daniil Davydov <3danissimo@gmail.com> — 2025-07-29T09:35:00Z

    Hi,

On Mon, Jul 28, 2025 at 10:43 AM Stepan Neretin <slpmcf@gmail.com> wrote:
>
>
> Your patch for securing cross-session temp table access is a great improvement. The RVR_OTHER_TEMP_OK flag elegantly handles the DROP case while keeping the main restriction in place.
>
> For schema name validation, an exact strcmp for "pg_temp" and proper numeric parsing for "pg_temp_X" would be more precise than the current prefix check. This would avoid any accidental matches to similarly named schemas.
>

Thanks for looking into it!

> The error message could be adjusted to emphasize permissions, like "permission denied for cross-session temp table access". This would make the security intent clearer to users.
>

I don't think that such an error message will be more appropriate. We
want to forbid this operation not because of "permission" reasons, but
because of the danger of this operation.
Yes, some people insist that dropping other sessions' temp tables might
be useful in some cases, but it is a "last resort" solution.

Even with this patch, DROP of other session temp tables can lead to
an error. I wrote about it here [1].

> I noticed the Assert assumes myTempNamespace is always valid. While correct, a brief comment explaining why this is safe would help future maintainers.

Well, v5 patch already contains comment for this assert :
 /*
  * If this table was recognized as temporary, it means that we
  * found it because the backend's temporary namespace was specified
  * in search_path. Thus, MyTempNamespace must contain valid oid.
  */

> The relpersistence logic could also be centralized in one place for consistency.

I don't see a reason to separate this logic into a new function, because
there will be no more cases when it will be useful to us.

> I've added an isolation test to verify the behavior when trying to access another backend's temp tables. It confirms the restrictions work as intended while allowing permitted operations.

Some time ago I also created a test for this situation, see patch in this [2]
message. it worked in a similar way (but covered more test cases).
It caused a mixed reaction from people, so I decided to abandon this idea.

I guess it might be a discussion point in the future, but first I'd like to
settle the core logic of the patch.

I attach a v7 patch to this letter. No changes yet, just rebased on the newest
commit in master branch.

[1] https://www.postgresql.org/message-id/flat/CAJDiXghoi-FM4d5XVZzUyiuhv8DDm9JdGOU8KC47emasqi1GUw%40mail.gmail.com
[2] https://www.postgresql.org/message-id/CAJDiXgi9CWaZCVcHmvAT604RrAqDN5zpOYxZq92adqkPq5QbnQ%40mail.gmail.com

--
Best regards,
Daniil Davydov

  4. Re: Fix bug with accessing to temporary tables of other sessions

    Jim Jones <jim.jones@uni-muenster.de> — 2025-09-25T10:45:20Z

    Hi Daniil,

On 7/29/25 11:35, Daniil Davydov wrote:
> I attach a v7 patch to this letter. No changes yet, just rebased on the newest
> commit in master branch.

A few days ago I reviewed one patch[1] that has a significant overlap
with this one. Perhaps they should be merged?

Here my first tests and comments:

== 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_75.tmp"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 val    | integer |           |          |


== session 2 ==

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

-- fixed: previously accessed the table but returning 0 rows
postgres=# SELECT * FROM pg_temp_75.tmp;
ERROR:  could not access temporary relations of other sessions
LINE 1: SELECT * FROM pg_temp_75.tmp;
                      ^
-- fixed: previously returning DELETE 0
postgres=# DELETE FROM pg_temp_75.tmp;
ERROR:  could not access temporary relations of other sessions
LINE 1: DELETE FROM pg_temp_75.tmp;
                    ^
postgres=# TRUNCATE TABLE pg_temp_75.tmp;
ERROR:  could not access temporary relations of other sessions

-- fixed: previously returning UPDATE 0
postgres=# UPDATE pg_temp_75.tmp SET val = NULL;
ERROR:  could not access temporary relations of other sessions
LINE 1: UPDATE pg_temp_75.tmp SET val = NULL;
               ^
-- error message changed: previously "ERROR:  cannot access temporary
tables of other sessions"
postgres=# INSERT INTO pg_temp_75.tmp VALUES (73);
ERROR:  could not access temporary relations of other sessions
LINE 1: INSERT INTO pg_temp_75.tmp VALUES (73);
                    ^
-- fixed: previously returning COPY 0
postgres=# COPY pg_temp_75.tmp TO '/tmp/foo';
ERROR:  could not access temporary relations of other sessions

-- error message changed. previously "ERROR:  cannot alter temporary
tables of other sessions"
postgres=# ALTER TABLE pg_temp_75.tmp ADD COLUMN foo int;
ERROR:  could not access temporary relations of other sessions

-- fixed: previously[2] it was possible to rename the temp table.
postgres=# ALTER TABLE pg_temp_75.tmp RENAME TO bar;
ERROR:  could not access temporary relations of other sessions

-- fixed: previously[3] it was possible to LOCK the temp table.
postgres=# BEGIN;
BEGIN
postgres=*# LOCK TABLE pg_temp_75.tmp IN ACCESS EXCLUSIVE MODE;
ERROR:  could not access temporary relations of other sessions

DROP TABLE still works, but I guess it is the main motivation of
RVR_OTHER_TEMP_OK :)

Thanks for the patch. It's a great improvement!

Best regards, Jim


[1]
https://www.postgresql.org/message-id/flat/2736425.1758475979%40sss.pgh.pa.us
[2] ALTER TABLE ... RENAME TO tests in PostgreSQL 14.19:

  == session 1 ==
  psql (14.19 (Debian 14.19-1.pgdg13+1))
  Geben Sie »help« für Hilfe ein.

  postgres=# CREATE TEMPORARY TABLE tmp AS SELECT 42 AS val;
  SELECT 1
  postgres=# \d tmp
                      Tabelle »pg_temp_4.tmp«
   Spalte |   Typ   | Sortierfolge | NULL erlaubt? | Vorgabewert
  --------+---------+--------------+---------------+-------------
   val    | integer |              |               |


  == session 2 ==
  psql (14.19 (Debian 14.19-1.pgdg13+1))
  Geben Sie »help« für Hilfe ein.

  postgres=# ALTER TABLE pg_temp_4.tmp RENAME TO foo;
  ALTER TABLE

  == session 1 ==

  postgres=# \d tmp
  Keine Relation namens »tmp« gefunden
  postgres=# \d foo
                      Tabelle »pg_temp_4.foo«
   Spalte |   Typ   | Sortierfolge | NULL erlaubt? | Vorgabewert
  --------+---------+--------------+---------------+-------------
   val    | integer |              |               |

[3] LOCK TABLE tests in PostgreSQL 14.19
  == session 2 ==
  postgres=# BEGIN;
  BEGIN
  postgres=*# LOCK TABLE pg_temp_4.foo IN ACCESS EXCLUSIVE MODE;
  LOCK TABLE
  postgres=*#

  == session 1 ==
  -- * owner of the temp table
  postgres=# SELECT locktype, relation::regclass, mode, granted, pid
  FROM pg_locks
  WHERE relation = 'pg_temp_4.foo'::regclass::oid;
   locktype | relation |        mode         | granted |  pid
  ----------+----------+---------------------+---------+--------
   relation | foo      | AccessExclusiveLock | t       | 277699
  (1 Zeile)

  5. Re: Fix bug with accessing to temporary tables of other sessions

    Daniil Davydov <3danissimo@gmail.com> — 2025-09-25T13:15:50Z

    Hi,

On Thu, Sep 25, 2025 at 5:45 PM Jim Jones <jim.jones@uni-muenster.de> wrote:
>
> A few days ago I reviewed one patch[1] that has a significant overlap
> with this one. Perhaps they should be merged?
>

Thanks for looking into it!
I don't know what exactly is meant by merging. Maybe we should just
apply a current
patch that fixes all problems ?..

> Here my first tests and comments:
> ....

OK, I'll replace "could not" with "cannot" in order to match previous comments.

>
> DROP TABLE still works, but I guess it is the main motivation of
> RVR_OTHER_TEMP_OK :)

Yep, motivation of this decision you can find here [1].


I'll attach a v8 patch that fixes error messages (could not -> cannot).

--
Best regards,
Daniil Davydov

[1] https://www.postgresql.org/message-id/Zx7oLCnqis3FjgCK%40paquier.xyz

  6. Re: Fix bug with accessing to temporary tables of other sessions

    Jim Jones <jim.jones@uni-muenster.de> — 2025-09-25T14:04:14Z

    
On 9/25/25 15:15, Daniil Davydov wrote:
> I don't know what exactly is meant by merging. Maybe we should just
> apply a current
> patch that fixes all problems ?..

Here I just wanted to bring to your attention that we have duplicate
efforts with these two patches. This one covers much more ground though ;)

> OK, I'll replace "could not" with "cannot" in order to match previous comments.

Small typo (you forgot to remove one "not")

errmsg("cannot not access temporary relations of other sessions")

It should be something like:

errmsg("cannot access temporary relations from other sessions")

Best regards, Jim

  7. Re: Fix bug with accessing to temporary tables of other sessions

    Daniil Davydov <3danissimo@gmail.com> — 2025-09-25T14:10:56Z

    Hi,

On Thu, Sep 25, 2025 at 9:04 PM Jim Jones <jim.jones@uni-muenster.de> wrote:
>
> Small typo (you forgot to remove one "not")
>
> errmsg("cannot not access temporary relations of other sessions")
>
> It should be something like:
>
> errmsg("cannot access temporary relations from other sessions")
>

Oh, my bad. Fixed. Thanks for noticing it.

--
Best regards,
Daniil Davydov

  8. Re: Fix bug with accessing to temporary tables of other sessions

    Jim Jones <jim.jones@uni-muenster.de> — 2025-09-26T11:19:16Z

    The code LGTM (commit message still missing though)

Given that the lack of tests allowed this bug to go undetected until
now, I'd suggest to include additional tests in this patch to prevent
similar issues in the future. Something like 0002 attached. What do you
think?

Best, Jim

  9. Re: Fix bug with accessing to temporary tables of other sessions

    Daniil Davydov <3danissimo@gmail.com> — 2025-09-27T09:20:45Z

    Hi,

On Fri, Sep 26, 2025 at 6:19 PM Jim Jones <jim.jones@uni-muenster.de> wrote:
>
> Given that the lack of tests allowed this bug to go undetected until
> now, I'd suggest to include additional tests in this patch to prevent
> similar issues in the future. Something like 0002 attached. What do you
> think?
>

Thanks for the test! Some time ago I wrote an isolation test [1] for
this patch, but
it looked a bit ugly, so I decided to abandon it temporarily. Your
test looks much
better. I'd prefer to keep it.

[1] https://www.postgresql.org/message-id/CAJDiXgi9CWaZCVcHmvAT604RrAqDN5zpOYxZq92adqkPq5QbnQ@mail.gmail.com

--
Best regards,
Daniil Davydov

  10. Re: Fix bug with accessing to temporary tables of other sessions

    Daniil Davydov <3danissimo@gmail.com> — 2025-10-28T13:42:07Z

    Hi,

I've rebased patches on the newest master.

--
Best regards,
Daniil Davydov

  11. Re: Fix bug with accessing to temporary tables of other sessions

    Daniil Davydov <3danissimo@gmail.com> — 2025-10-30T12:23:05Z

    Hi,

> I've rebased patches on the newest master.

My apologies - in previous letter I had attached the wrong files.
Thanks Jim Jones for noticing it :)

I am attaching the correct patches to this email.

--
Best regards,
Daniil Davydov

  12. Re: Fix bug with accessing to temporary tables of other sessions

    Daniil Davydov <3danissimo@gmail.com> — 2026-02-27T14:02:13Z

    Hi,

Posting rebased set of patches.

--
Best regards,
Daniil Davydov

  13. Re: Fix bug with accessing to temporary tables of other sessions

    Soumya S Murali <soumyamurali.work@gmail.com> — 2026-03-23T10:22:36Z

    Hi all,
Thank you for the updated patches.

On Mon, Mar 23, 2026 at 12:56 PM Daniil Davydov <3danissimo@gmail.com> wrote:
>
> Hi,
>
> Posting rebased set of patches.

I have gone through the patches and tested the patch series.
I successfully reproduced the bug before applying the patch and found
that the cross-session SELECT via schema-qualified temp table did not
raise an error; instead, it returned an empty result, indicating
inconsistent behaviour. After applying the patch, regression tests
passed successfully and new regression tests behave as expected.
Temporary tables from other sessions are now no longer visible and all
the attempts to access them result in error. Also I verified:-
Schema-qualified SELECT, INSERT / UPDATE / DELETE, JOIN queries,
Subqueries and EXPLAIN worked as expected. Same-session access also
works as expected. Overall the patch LGTM.

Regards,
Soumya

  14. Re: Fix bug with accessing to temporary tables of other sessions

    Jim Jones <jim.jones@uni-muenster.de> — 2026-03-23T13:31:33Z

    
On 23/03/2026 11:22, Soumya S Murali wrote:
Overall the patch LGTM.


This is a step forward in really isolating contents of temp tables from
other sessions, but the more I think about it, the more I'm concerned
with the current approach -- I spent some time investigating this
problem a bit deeper last week.

My main concern is the usage of gram.y, as a parser is arguably fragile
for this kind of things. For instance, one can always change the
search_path and bypass this restriction:

(table t was created in a different session)

postgres=# SELECT * FROM pg_temp_81.t;
ERROR:  cannot access temporary relations of other sessions
LINE 1: SELECT * FROM pg_temp_81.t;
                      ^
postgres=# SET search_path = pg_temp_81, public;
SET
postgres=# SELECT * FROM t;
 ?column?
----------
(0 rows)

* See: if (relation->relpersistence == RELPERSISTENCE_TEMP) in
namespace.c for more details.

IMO, since it is an access control issue, I guess we better treat it as
such and modify aclchk.c instead.

Something like this the file attached. This breaks an unrelated test,
which is potentially a bug in REPACK ... but I'll describe it in another
thread.

Thoughts?

Best, Jim

  15. Re: Fix bug with accessing to temporary tables of other sessions

    Tom Lane <tgl@sss.pgh.pa.us> — 2026-03-23T13:44:06Z

    Jim Jones <jim.jones@uni-muenster.de> writes:
> This is a step forward in really isolating contents of temp tables from
> other sessions, but the more I think about it, the more I'm concerned
> with the current approach -- I spent some time investigating this
> problem a bit deeper last week.

Yeah.  I think this entire approach is wrongheaded: we do not enforce
permissions checks against superusers.  Moreover, if we try to fix it
at the permissions level, it seems nearly certain that there will be
bypass paths, simply because superusers bypass so many other checks.

The actual problem is that the buffer manager is incapable of dealing
with other sessions' temp tables, and we need to un-break the buffer
manager's defense for that implementation restriction.  So I feel the
correct approach is something similar to what I described here:

https://www.postgresql.org/message-id/flat/2736425.1758475979%40sss.pgh.pa.us

I'm not wedded to that specific patch, but that is the implementation
level where the fix is needed.

			regards, tom lane

  16. Re: Fix bug with accessing to temporary tables of other sessions

    Daniil Davydov <3danissimo@gmail.com> — 2026-03-24T17:26:13Z

    Hi,

On Mon, Mar 23, 2026 at 8:31 PM Jim Jones <jim.jones@uni-muenster.de> wrote:
>
> This is a step forward in really isolating contents of temp tables from
> other sessions, but the more I think about it, the more I'm concerned
> with the current approach -- I spent some time investigating this
> problem a bit deeper last week.
>
> My main concern is the usage of gram.y, as a parser is arguably fragile
> for this kind of things.

I don't actually want to use gram.y as a main solver of this issue. But
gram.y is setting the "relpersistence" field for the RangeVar and all
subsequent code is treating this value as truthful. In particular, v12 patch
is relying on this field in RangeVar during the resolution of access issues,
and IMHO it's not out of line with the current code base. For instance, we are
considering RangeVar to determine whether we can perform a CREATE operation
within the specified namespace (see RangeVarAdjustRelationPersistence).

Even if we decide not to touch the gram.y in this patch, I still think that
leaving the "relpesistence" field misleading may lead to more bugs appearing
in the future. I.e. it should be fixed anyway (maybe in another thread?).

> For instance, one can always change the
> search_path and bypass this restriction:
>
> (table t was created in a different session)
>
> postgres=# SELECT * FROM pg_temp_81.t;
> ERROR:  cannot access temporary relations of other sessions
> LINE 1: SELECT * FROM pg_temp_81.t;
>                       ^
> postgres=# SET search_path = pg_temp_81, public;
> SET
> postgres=# SELECT * FROM t;
>  ?column?
> ----------
> (0 rows)
>
> * See: if (relation->relpersistence == RELPERSISTENCE_TEMP) in
> namespace.c for more details.

Yeah, you are right. It turns out that the current patch doesn't fully
protect other temp tables from superuser. The first thought that comes to me
is to forbid setting other-temp-namespaces in a search_path parameter. I know
it's starting to look ugly. But actually, such a restriction seems quite
logical.

>
> IMO, since it is an access control issue, I guess we better treat it as
> such and modify aclchk.c instead.
>
> Something like this the file attached. This breaks an unrelated test,
> which is potentially a bug in REPACK ... but I'll describe it in another
> thread.
>
> Thoughts?

Thank you for the patch! It seems much more beautiful and convenient to
maintain, but I have a little concern about it.

Actually, in your implementation we can DROP other temp tables not because we
make an exception to the general rule ("don't touch other temp tables"), but
because we just can perform such operations with every object in pg_temp_0.
If other operations with the same access checking as DROP command appear in
the future, the user will be able to perform these operations for
other-temp-tables. I.e. we will need to manually prevent such new operations
from accessing other-temp-tables. Have I gone too far in my reasoning?

BTW, your implementation allows calling a VACUUM for other-temp-tables. I think
that we should forbid that too.

On Mon, Mar 23, 2026 at 8:44 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Jim Jones <jim.jones@uni-muenster.de> writes:
> > This is a step forward in really isolating contents of temp tables from
> > other sessions, but the more I think about it, the more I'm concerned
> > with the current approach -- I spent some time investigating this
> > problem a bit deeper last week.
>
> Yeah.  I think this entire approach is wrongheaded: we do not enforce
> permissions checks against superusers.  Moreover, if we try to fix it
> at the permissions level, it seems nearly certain that there will be
> bypass paths, simply because superusers bypass so many other checks.
>

Actually, v12 patch is not about a superuser rights restriction, but about
forbidding such operations for everyone. Anyway, we have a new perl test that
will prevent adding a code that will allow superuser to (somehow) break the
protection of both mine and Jim's patches. Isn't that a sufficient guarantee
that superuser will not bypass checks that it must not bypass?

> The actual problem is that the buffer manager is incapable of dealing
> with other sessions' temp tables, and we need to un-break the buffer
> manager's defense for that implementation restriction.  So I feel the
> correct approach is something similar to what I described here:
>
> https://www.postgresql.org/message-id/flat/2736425.1758475979%40sss.pgh.pa.us
>
> I'm not wedded to that specific patch, but that is the implementation
> level where the fix is needed.
>

Handling access to other-temp-tables on the buffer manager level seems to me
like fighting the symptom, not the cause. Protection of other-temp-tables is
kinda "upper-level logical restriction". At the same time, buffer manager is a
lower-level implementation which shouldn't face such upper-level issues.
Am I missing something?

--
Best regards,
Daniil Davydov

  17. Re: Fix bug with accessing to temporary tables of other sessions

    Tom Lane <tgl@sss.pgh.pa.us> — 2026-03-24T18:58:10Z

    Daniil Davydov <3danissimo@gmail.com> writes:
> On Mon, Mar 23, 2026 at 8:44 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Yeah.  I think this entire approach is wrongheaded: we do not enforce
>> permissions checks against superusers.  Moreover, if we try to fix it
>> at the permissions level, it seems nearly certain that there will be
>> bypass paths, simply because superusers bypass so many other checks.

> Actually, v12 patch is not about a superuser rights restriction, but about
> forbidding such operations for everyone.

... including superusers, who bypass permissions restrictions
everywhere else.  You are going to have to contort the ACL system
badly to make that happen at all, and I would not be surprised
if you introduce new bugs.

>> The actual problem is that the buffer manager is incapable of dealing
>> with other sessions' temp tables, and we need to un-break the buffer
>> manager's defense for that implementation restriction.  So I feel the
>> correct approach is something similar to what I described here:
>> https://www.postgresql.org/message-id/flat/2736425.1758475979%40sss.pgh.pa.us

> Handling access to other-temp-tables on the buffer manager level seems to me
> like fighting the symptom, not the cause.

No, it IS the cause.  If someday someone were to reimplement buffer
management in a way that didn't have this implementation restriction,
we would surely not arbitrarily restrict superusers from looking at
tables that they then would physically be able to look at.

> Am I missing something?

Mainly, that we had a setup that was working fine for decades,
until somebody made holes in it with careless refactoring.
We should fix that mistake, not introduce inconsistent-with-
decades-of-practice permissions behavior to hide the mistake
at an unrelated logical level.

Also, we need a defense at the buffer manager level anyway, because
otherwise C code could try to access another session's temp table
and we'd not realize it was getting bogus answers.  (Whether such
an attempt is a bug or not is a different discussion; but we at
least need some logic that detects that it won't work, and the ACL
system cannot be expected to stop C-level code from trying.)

Also, we really need a patch that's simple and non-invasive enough
to be back-patched into v17 and v18.  This proposal is not that.


> I don't actually want to use gram.y as a main solver of this issue. But
> gram.y is setting the "relpersistence" field for the RangeVar and all
> subsequent code is treating this value as truthful.

I do kind of agree with this concern, but the v12 patch simply moves
the untruthfulness around.  Reality is that we cannot know whether an
unqualified-name RangeVar references a temp table until we do a
catalog lookup, so IMO we should not have a relpersistence field there
at all.  At best it means something quite different from what it means
elsewhere, and that's a recipe for confusion.  But changing that would
not be a bug fix (AFAIK) but refactoring to reduce the probability of
future bugs.

			regards, tom lane

  18. Re: Fix bug with accessing to temporary tables of other sessions

    Daniil Davydov <3danissimo@gmail.com> — 2026-03-25T07:07:52Z

    Hi,

On Wed, Mar 25, 2026 at 1:58 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> > Actually, v12 patch is not about a superuser rights restriction, but about
> > forbidding such operations for everyone.
>
> ... including superusers, who bypass permissions restrictions
> everywhere else.  You are going to have to contort the ACL system
> badly to make that happen at all, and I would not be surprised
> if you introduce new bugs.
>

I've never dealt with the ACL system before, so it's hard for me to assess
the scale of the problem. I am inclined to believe you on this issue.

> >> The actual problem is that the buffer manager is incapable of dealing
> >> with other sessions' temp tables, and we need to un-break the buffer
> >> manager's defense for that implementation restriction.  So I feel the
> >> correct approach is something similar to what I described here:
> >> https://www.postgresql.org/message-id/flat/2736425.1758475979%40sss.pgh.pa.us
>
> > Handling access to other-temp-tables on the buffer manager level seems to me
> > like fighting the symptom, not the cause.
>
> No, it IS the cause.  If someday someone were to reimplement buffer
> management in a way that didn't have this implementation restriction,
> we would surely not arbitrarily restrict superusers from looking at
> tables that they then would physically be able to look at.
>

OK, now I fully understand your point (I hope so) :
We want to restrict backend access to other-temp-tables just because it is
physically impossible for them to read the pages of such tables. And if some
users has enough privileges, it is OK for us to allow them to
lock/vacuum/drop/... other-temp-tables. I.e. only operations with heap pages
access must be forbidden, and the buffer manager layer is an appropriate place
for it.

> > Am I missing something?
>
> Mainly, that we had a setup that was working fine for decades,
> until somebody made holes in it with careless refactoring.
> We should fix that mistake, not introduce inconsistent-with-
> decades-of-practice permissions behavior to hide the mistake
> at an unrelated logical level.
>

Yeah, we have a few checks in the bufmgr (PrefetchBuffer, ReadBufferExtended),
but they stopped coping with their task.

> Also, we need a defense at the buffer manager level anyway, because
> otherwise C code could try to access another session's temp table
> and we'd not realize it was getting bogus answers.  (Whether such
> an attempt is a bug or not is a different discussion; but we at
> least need some logic that detects that it won't work, and the ACL
> system cannot be expected to stop C-level code from trying.)
>
> Also, we really need a patch that's simple and non-invasive enough
> to be back-patched into v17 and v18.  This proposal is not that.
>

OK

>
> > I don't actually want to use gram.y as a main solver of this issue. But
> > gram.y is setting the "relpersistence" field for the RangeVar and all
> > subsequent code is treating this value as truthful.
>
> I do kind of agree with this concern, but the v12 patch simply moves
> the untruthfulness around.  Reality is that we cannot know whether an
> unqualified-name RangeVar references a temp table until we do a
> catalog lookup, ...

Yep, Jim's example shows us that we cannot always rely on the "relpersistence"
field.

> ...so IMO we should not have a relpersistence field there
> at all.  At best it means something quite different from what it means
> elsewhere, and that's a recipe for confusion.  But changing that would
> not be a bug fix (AFAIK) but refactoring to reduce the probability of
> future bugs.
>

I agree with the idea to get rid of this field. By now I cannot say for sure
whether we can fix a bug without modifying the RangeVar structure. But I'll
try to implement proposed logic only within the bufmgr.


Thank you very much for your comments! I'll post a new patch in the near
future.

--
Best regards,
Daniil Davydov

  19. Re: Fix bug with accessing to temporary tables of other sessions

    Soumya S Murali <soumyamurali.work@gmail.com> — 2026-04-08T09:17:28Z

    Hi all,

On Wed, Mar 25, 2026 at 12:38 PM Daniil Davydov <3danissimo@gmail.com> wrote:
>
> Hi,
>
> On Wed, Mar 25, 2026 at 1:58 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >
> > > Actually, v12 patch is not about a superuser rights restriction, but about
> > > forbidding such operations for everyone.
> >
> > ... including superusers, who bypass permissions restrictions
> > everywhere else.  You are going to have to contort the ACL system
> > badly to make that happen at all, and I would not be surprised
> > if you introduce new bugs.
> >
>
> I've never dealt with the ACL system before, so it's hard for me to assess
> the scale of the problem. I am inclined to believe you on this issue.
>
> > >> The actual problem is that the buffer manager is incapable of dealing
> > >> with other sessions' temp tables, and we need to un-break the buffer
> > >> manager's defense for that implementation restriction.  So I feel the
> > >> correct approach is something similar to what I described here:
> > >> https://www.postgresql.org/message-id/flat/2736425.1758475979%40sss.pgh.pa.us
> >
> > > Handling access to other-temp-tables on the buffer manager level seems to me
> > > like fighting the symptom, not the cause.
> >
> > No, it IS the cause.  If someday someone were to reimplement buffer
> > management in a way that didn't have this implementation restriction,
> > we would surely not arbitrarily restrict superusers from looking at
> > tables that they then would physically be able to look at.
> >
>
> OK, now I fully understand your point (I hope so) :
> We want to restrict backend access to other-temp-tables just because it is
> physically impossible for them to read the pages of such tables. And if some
> users has enough privileges, it is OK for us to allow them to
> lock/vacuum/drop/... other-temp-tables. I.e. only operations with heap pages
> access must be forbidden, and the buffer manager layer is an appropriate place
> for it.
>
> > > Am I missing something?
> >
> > Mainly, that we had a setup that was working fine for decades,
> > until somebody made holes in it with careless refactoring.
> > We should fix that mistake, not introduce inconsistent-with-
> > decades-of-practice permissions behavior to hide the mistake
> > at an unrelated logical level.
> >
>
> Yeah, we have a few checks in the bufmgr (PrefetchBuffer, ReadBufferExtended),
> but they stopped coping with their task.
>
> > Also, we need a defense at the buffer manager level anyway, because
> > otherwise C code could try to access another session's temp table
> > and we'd not realize it was getting bogus answers.  (Whether such
> > an attempt is a bug or not is a different discussion; but we at
> > least need some logic that detects that it won't work, and the ACL
> > system cannot be expected to stop C-level code from trying.)
> >
> > Also, we really need a patch that's simple and non-invasive enough
> > to be back-patched into v17 and v18.  This proposal is not that.
> >
>
> OK
>
> >
> > > I don't actually want to use gram.y as a main solver of this issue. But
> > > gram.y is setting the "relpersistence" field for the RangeVar and all
> > > subsequent code is treating this value as truthful.
> >
> > I do kind of agree with this concern, but the v12 patch simply moves
> > the untruthfulness around.  Reality is that we cannot know whether an
> > unqualified-name RangeVar references a temp table until we do a
> > catalog lookup, ...
>
> Yep, Jim's example shows us that we cannot always rely on the "relpersistence"
> field.
>
> > ...so IMO we should not have a relpersistence field there
> > at all.  At best it means something quite different from what it means
> > elsewhere, and that's a recipe for confusion.  But changing that would
> > not be a bug fix (AFAIK) but refactoring to reduce the probability of
> > future bugs.
> >
>
> I agree with the idea to get rid of this field. By now I cannot say for sure
> whether we can fix a bug without modifying the RangeVar structure. But I'll
> try to implement proposed logic only within the bufmgr.
>
>
> Thank you very much for your comments! I'll post a new patch in the near
> future.


I worked on the issue of accessing temporary tables belonging to other
sessions and tried implementing the fix at the buffer manager level,
as suggested. I added checks in ReadBuffer_common() and
PrefetchBuffer() to reject access when a relation is temporary
(relpersistence = TEMP) but does not use local buffers
(!RelationUsesLocalBuffers) so that it ensures only heap page access
is blocked, while catalog lookups and other metadata operations
continue to work as before. While testing, I observed that in many
cases the query does not reach the buffer manager because name
resolution fails earlier with “relation does not exist”. However, the
added checks ensure that even if execution reaches the buffer layer,
access to other sessions’ temporary tables is safely rejected. The
change is minimal, and did not modify parser/ACL behavior and all
regression tests got passed successfully too.
Kindly review the attached patch herewith. Please let me know if this
approach aligns with expectations or if further adjustments are
needed.


Regards,
Soumya

  20. Re: Fix bug with accessing to temporary tables of other sessions

    Jim Jones <jim.jones@uni-muenster.de> — 2026-04-08T10:41:00Z

    Hi

On 08/04/2026 11:17, Soumya S Murali wrote:
> I worked on the issue of accessing temporary tables belonging to other
> sessions and tried implementing the fix at the buffer manager level,
> as suggested. I added checks in ReadBuffer_common() and
> PrefetchBuffer() to reject access when a relation is temporary
> (relpersistence = TEMP) but does not use local buffers
> (!RelationUsesLocalBuffers) so that it ensures only heap page access
> is blocked, while catalog lookups and other metadata operations
> continue to work as before. While testing, I observed that in many
> cases the query does not reach the buffer manager because name
> resolution fails earlier with “relation does not exist”. However, the
> added checks ensure that even if execution reaches the buffer layer,
> access to other sessions’ temporary tables is safely rejected. The
> change is minimal, and did not modify parser/ACL behavior and all
> regression tests got passed successfully too.
> Kindly review the attached patch herewith. Please let me know if this
> approach aligns with expectations or if further adjustments are
> needed.

A few comments:

== PrefetchBuffer ==

The condition nested inside the if (RelationUsesLocalBuffers(reln))
tests the opposite of the main if !RelationUsesLocalBuffers(reln):

if (RelationUsesLocalBuffers(reln))
{
  /* ACCESS DENIED CHECK */
  if (reln != NULL &&
  reln->rd_rel != NULL &&
  reln->rd_rel->relpersistence == RELPERSISTENCE_TEMP &&
  !RelationUsesLocalBuffers(reln))
  {
  ereport(ERROR,
  	(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
  	errmsg("cannot access temporary tables of other sessions")));
  }
...
}

So it'll be always false, making the ereport unreachable.

== ReadBufferExtended ==

These conditions cancel each other out:

if (reln->rd_rel->relpersistence == RELPERSISTENCE_TEMP &&
    !RelationUsesLocalBuffers(reln))

RelationUsesLocalBuffers(reln) expands to
((relation)->rd_rel->relpersistence == RELPERSISTENCE_TEMP), making the
error message unreachable. Perhaps you meant RELATION_IS_OTHER_TEMP?

== ReadBuffer_common ==

Same as in ReadBufferExtended and PrefetchBuffer.

== tests ==

You excluded the tests from the patch.

== patch version ==

You forgot to add the patch version.

Best, Jim

  21. Re: Fix bug with accessing to temporary tables of other sessions

    Jim Jones <jim.jones@uni-muenster.de> — 2026-04-08T14:04:35Z

    Hi,

I did some digging:

It seems that b7b0f3f2724 is what actually broke it. By switching from
ReadBufferExtended() to read_stream_next_buffer(), it silently routed
all SELECT/UPDATE/DELETE/COPY away from the check that was sitting in
ReadBufferExtended().

@@ -528,25 +599,23 @@ heap_fetch_next_buffer(HeapScanDesc scan,
ScanDirection dir)
         */
        CHECK_FOR_INTERRUPTS();

-       if (unlikely(!scan->rs_inited))
+       /*
+        * If the scan direction is changing, reset the prefetch block
to the
+        * current block. Otherwise, we will incorrectly prefetch the blocks
+        * between the prefetch block and the current block again before
+        * prefetching blocks in the new, correct scan direction.
+        */
+       if (unlikely(scan->rs_dir != dir))
        {
-               scan->rs_cblock = heapgettup_initial_block(scan, dir);
+               scan->rs_prefetch_block = scan->rs_cblock;
+               read_stream_reset(scan->rs_read_stream);
+       }

-               /* ensure rs_cbuf is invalid when we get
InvalidBlockNumber */
-               Assert(scan->rs_cblock != InvalidBlockNumber ||
-                          !BufferIsValid(scan->rs_cbuf));
+       scan->rs_dir = dir;

-               scan->rs_inited = true;
-       }
-       else
-               scan->rs_cblock = heapgettup_advance_block(scan,
scan->rs_cblock,
-
                          dir);
-
-       /* read block if valid */
-       if (BlockNumberIsValid(scan->rs_cblock))
-               scan->rs_cbuf = ReadBufferExtended(scan->rs_base.rs_rd,
MAIN_FORKNUM,
-
          scan->rs_cblock, RBM_NORMAL,
-
          scan->rs_strategy);
+       scan->rs_cbuf = read_stream_next_buffer(scan->rs_read_stream, NULL);
+       if (BufferIsValid(scan->rs_cbuf))
+               scan->rs_cblock = BufferGetBlockNumber(scan->rs_cbuf);
 }


It simply builds upon 210622c60e1 (introduces StartReadBuffers ->
PinBufferForBlock) and b5a9b18cd0b (builds read_stream_begin_relation on
top of it).

So I thought we can explore the option of adding this check directly in
read_stream_begin_relation():


if (RELATION_IS_OTHER_TEMP(rel))
  ereport(ERROR,
         (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
      errmsg("cannot access temporary relations of other sessions")));


Thoughts?

See v15 attached.

Daniil, feel free to revert it to your last patch if you disagree with
this approach.

Best, Jim

  22. Re: Fix bug with accessing to temporary tables of other sessions

    Daniil Davydov <3danissimo@gmail.com> — 2026-04-09T11:46:09Z

    Hi,

On Wed, Apr 8, 2026 at 9:04 PM Jim Jones <jim.jones@uni-muenster.de> wrote:
>
> I did some digging:
>
> It seems that b7b0f3f2724 is what actually broke it. By switching from
> ReadBufferExtended() to read_stream_next_buffer(), it silently routed
> all SELECT/UPDATE/DELETE/COPY away from the check that was sitting in
> ReadBufferExtended().
>

Yeah, I agree with your conclusions.

> It simply builds upon 210622c60e1 (introduces StartReadBuffers ->
> PinBufferForBlock) and b5a9b18cd0b (builds read_stream_begin_relation on
> top of it).
>
> So I thought we can explore the option of adding this check directly in
> read_stream_begin_relation():
>
>
> if (RELATION_IS_OTHER_TEMP(rel))
>   ereport(ERROR,
>          (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
>       errmsg("cannot access temporary relations of other sessions")));
>
>
> Thoughts?
>

Thank you very much for the patch! A few comments:

1) Right now, read stream seems like an appropriate place for this restriction.
But actually the StartReadBuffers is not "binded" to the read stream logic. I
mean that if someone calls it bypassing the "read_stream_begin_relation"
function (it is OK to do so), then our restriction will be violated again.
I think that it will be more reliable to add the restriction directly to the
StartReadBuffers. Also, we can add an assertion ("relation is not other temp
table") to the PinBufferForBlock. What do you think?

2) If we decide to leave restriction in the "read_stream_begin_relation"
function, I would suggest adding a "rel != NULL" check here (read_stream.c):
+    if (RELATION_IS_OTHER_TEMP(rel))
+        ereport(ERROR,
+                (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+                 errmsg("cannot access temporary relations of other
sessions")));

3) The "rel != NULL" checks may use the "RelationIsValid" macro, which seems
more pretty to me.

> Daniil, feel free to revert it to your last patch if you disagree with
> this approach.

This approach looks good to me after Tom's explanations.

--
Best regards,
Daniil Davydov

  23. Re: Fix bug with accessing to temporary tables of other sessions

    Jim Jones <jim.jones@uni-muenster.de> — 2026-04-09T14:35:29Z

    Hi Daniil

On 09/04/2026 13:46, Daniil Davydov wrote:
> 1) Right now, read stream seems like an appropriate place for this restriction.
> But actually the StartReadBuffers is not "binded" to the read stream logic. I
> mean that if someone calls it bypassing the "read_stream_begin_relation"
> function (it is OK to do so), then our restriction will be violated again.
> I think that it will be more reliable to add the restriction directly to the
> StartReadBuffers. Also, we can add an assertion ("relation is not other temp
> table") to the PinBufferForBlock. What do you think?> 2) If we decide to leave restriction in the "read_stream_begin_relation"
> function, I would suggest adding a "rel != NULL" check here (read_stream.c):
> +    if (RELATION_IS_OTHER_TEMP(rel))
> +        ereport(ERROR,
> +                (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
> +                 errmsg("cannot access temporary relations of other
> sessions")));
> 3) The "rel != NULL" checks may use the "RelationIsValid" macro, which seems
> more pretty to me.


Mm, not so sure...
AFAICT moving the check to StartReadBuffersImpl would require an extra
NULL guard that isn't needed in read_stream_begin_relation, as the
callers already pass valid Relations. So, rel != NULL is not needed.

Also, wouldn't it potentially make this check multiple times in a table
scan?

Am I missing something?

Best, Jim

  24. Re: Fix bug with accessing to temporary tables of other sessions

    Daniil Davydov <3danissimo@gmail.com> — 2026-04-09T15:29:52Z

    Hi,

On Thu, Apr 9, 2026 at 9:35 PM Jim Jones <jim.jones@uni-muenster.de> wrote:
>
> On 09/04/2026 13:46, Daniil Davydov wrote:
> > 1) Right now, read stream seems like an appropriate place for this restriction.
> > But actually the StartReadBuffers is not "binded" to the read stream logic. I
> > mean that if someone calls it bypassing the "read_stream_begin_relation"
> > function (it is OK to do so), then our restriction will be violated again.
> > I think that it will be more reliable to add the restriction directly to the
> > StartReadBuffers. Also, we can add an assertion ("relation is not other temp
> > table") to the PinBufferForBlock. What do you think?> 2) If we decide to leave restriction in the "read_stream_begin_relation"
> > function, I would suggest adding a "rel != NULL" check here (read_stream.c):
> > +    if (RELATION_IS_OTHER_TEMP(rel))
> > +        ereport(ERROR,
> > +                (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
> > +                 errmsg("cannot access temporary relations of other
> > sessions")));
> > 3) The "rel != NULL" checks may use the "RelationIsValid" macro, which seems
> > more pretty to me.
>
>
> Mm, not so sure...
> AFAICT moving the check to StartReadBuffersImpl would require an extra
> NULL guard that isn't needed in read_stream_begin_relation, as the
> callers already pass valid Relations. So, rel != NULL is not needed.

Hm. I see that read_stream_begin_relation immediately calls
read_stream_begin_impl, where we have a "rel != NULL" check (read_stream.c:787).
Anyway, I think that we shouldn't rely on the fact that a given Relation will
always be valid. Please, correct me if I am wrong.

I see that you don't really like the idea of moving this check. But since a
vectored variant of ReadBuffer() may be used by anyone, don't we need to take
it into account?

> Also, wouldn't it potentially make this check multiple times in a table
> scan?

Yep, it will. It is exactly the same logic as for ReadBuffer_common,
PrefetchBuffer and ReadBufferExtended (i.e. checking this constraint before
each buffer read). I don't see anything wrong with this approach. More
precisely, it would be good to avoid multiple checks, but I don't see a way to
do that.

--
Best regards,
Daniil Davydov

  25. Re: Fix bug with accessing to temporary tables of other sessions

    Jim Jones <jim.jones@uni-muenster.de> — 2026-04-09T17:46:02Z

    
On 09/04/2026 17:29, Daniil Davydov wrote:
> On Thu, Apr 9, 2026 at 9:35 PM Jim Jones <jim.jones@uni-muenster.de> wrote:
> Hm. I see that read_stream_begin_relation immediately calls
> read_stream_begin_impl, where we have a "rel != NULL" check (read_stream.c:787).
> Anyway, I think that we shouldn't rely on the fact that a given Relation will
> always be valid. Please, correct me if I am wrong.
> 
> I see that you don't really like the idea of moving this check. But since a
> vectored variant of ReadBuffer() may be used by anyone, don't we need to take
> it into account?
>> Also, wouldn't it potentially make this check multiple times in a table
>> scan?
> Yep, it will. It is exactly the same logic as for ReadBuffer_common,
> PrefetchBuffer and ReadBufferExtended (i.e. checking this constraint before
> each buffer read). I don't see anything wrong with this approach. More
> precisely, it would be good to avoid multiple checks, but I don't see a way to
> do that.

This check exists because read_stream_begin_smgr_relation() passes NULL,
but I see your point. I guess a check in read_stream_begin_relation()
and in StartReadBuffersImpl() would be the best solution? If you agree,
could you add it in v16?

Thanks!

Best, Jim

  26. Re: Fix bug with accessing to temporary tables of other sessions

    Daniil Davydov <3danissimo@gmail.com> — 2026-04-10T07:10:02Z

    Hi,

On Fri, Apr 10, 2026 at 12:46 AM Jim Jones <jim.jones@uni-muenster.de> wrote:
>
> I guess a check in read_stream_begin_relation()
> and in StartReadBuffersImpl() would be the best solution? If you agree,
> could you add it in v16?

Having both checks might look a bit redundant since the read stream will
eventually call the StartReadBuffersImpl function. On the other hand, there are
many places which are checking this restriction even if subsequent functions
(from bufmgr) also have this check.

So, I'll keep both checks and a bit reduce the comments in the bufmgr.c .

BTW, what do you think about making this comment less "concrete"? :
# SELECT via index scan from other session.
# Sequential scans are blocked at read_stream_begin_relation(); index scans
# bypass that path entirely and reach ReadBufferExtended() in bufmgr.c
# (nbtree's _bt_getbuf calls ReadBuffer directly for individual page fetches).
# enable_seqscan=off forces the planner to use the index.

I mean that if the described logic changes, this comment will become confusing.
We can describe the test in general words. For example :
# Index scans can use a different code path from the one sequential scans are
# following. Make sure that we cannot access other sessions' temp tables during
# index scan either.


Thank you for the comments! Please, see an updated set of patches.

--
Best regards,
Daniil Davydov

  27. Re: Fix bug with accessing to temporary tables of other sessions

    Jim Jones <jim.jones@uni-muenster.de> — 2026-04-10T10:28:59Z

    Hi Daniil

On 10/04/2026 09:10, Daniil Davydov wrote:
> Having both checks might look a bit redundant since the read stream will
> eventually call the StartReadBuffersImpl function. On the other hand, there are
> many places which are checking this restriction even if subsequent functions
> (from bufmgr) also have this check.
> 
> So, I'll keep both checks and a bit reduce the comments in the bufmgr.c .

Putting this check a level deeper sounds good to me.

> BTW, what do you think about making this comment less "concrete"? :
> # SELECT via index scan from other session.
> # Sequential scans are blocked at read_stream_begin_relation(); index scans
> # bypass that path entirely and reach ReadBufferExtended() in bufmgr.c
> # (nbtree's _bt_getbuf calls ReadBuffer directly for individual page fetches).
> # enable_seqscan=off forces the planner to use the index.
> 
> I mean that if the described logic changes, this comment will become confusing.
> We can describe the test in general words. For example :
> # Index scans can use a different code path from the one sequential scans are
> # following. Make sure that we cannot access other sessions' temp tables during
> # index scan either.

+1

Yeah, it's indeed too verbose. I guess these comments were originally
just for me so I wouldn't get too confused along the way :)

I don't have anything else to add at this point. Unless there are any
objections, I'll mark the CF entry as 'Ready for Committer.'

Thanks!

Best, Jim

  28. Re: Fix bug with accessing to temporary tables of other sessions

    Daniil Davydov <3danissimo@gmail.com> — 2026-04-10T15:28:01Z

    Hi,

On Fri, Apr 10, 2026 at 5:29 PM Jim Jones <jim.jones@uni-muenster.de> wrote:
>
> > BTW, what do you think about making this comment less "concrete"? :
> > # SELECT via index scan from other session.
> > # Sequential scans are blocked at read_stream_begin_relation(); index scans
> > # bypass that path entirely and reach ReadBufferExtended() in bufmgr.c
> > # (nbtree's _bt_getbuf calls ReadBuffer directly for individual page fetches).
> > # enable_seqscan=off forces the planner to use the index.
> >
> > I mean that if the described logic changes, this comment will become confusing.
> > We can describe the test in general words. For example :
> > # Index scans can use a different code path from the one sequential scans are
> > # following. Make sure that we cannot access other sessions' temp tables during
> > # index scan either.
>
> +1
>
> Yeah, it's indeed too verbose. I guess these comments were originally
> just for me so I wouldn't get too confused along the way :)

OK :)

>
> I don't have anything else to add at this point. Unless there are any
> objections, I'll mark the CF entry as 'Ready for Committer.'
>

Great, thank you!

Please, see an updated set of patches (only perl test has been changed) :
1) Rephrase the discussed comment.
2) Use safe_psql whenever possible.
3) Run pgperltidy.

--
Best regards,
Daniil Davydov

  29. Re: Fix bug with accessing to temporary tables of other sessions

    Soumya S Murali <soumyamurali.work@gmail.com> — 2026-04-13T12:36:03Z

    Hi all,

On Wed, Apr 8, 2026 at 4:11 PM Jim Jones <jim.jones@uni-muenster.de> wrote:
>
> Hi
>
> On 08/04/2026 11:17, Soumya S Murali wrote:
> > I worked on the issue of accessing temporary tables belonging to other
> > sessions and tried implementing the fix at the buffer manager level,
> > as suggested. I added checks in ReadBuffer_common() and
> > PrefetchBuffer() to reject access when a relation is temporary
> > (relpersistence = TEMP) but does not use local buffers
> > (!RelationUsesLocalBuffers) so that it ensures only heap page access
> > is blocked, while catalog lookups and other metadata operations
> > continue to work as before. While testing, I observed that in many
> > cases the query does not reach the buffer manager because name
> > resolution fails earlier with “relation does not exist”. However, the
> > added checks ensure that even if execution reaches the buffer layer,
> > access to other sessions’ temporary tables is safely rejected. The
> > change is minimal, and did not modify parser/ACL behavior and all
> > regression tests got passed successfully too.
> > Kindly review the attached patch herewith. Please let me know if this
> > approach aligns with expectations or if further adjustments are
> > needed.
>
> A few comments:
>
> == PrefetchBuffer ==
>
> The condition nested inside the if (RelationUsesLocalBuffers(reln))
> tests the opposite of the main if !RelationUsesLocalBuffers(reln):
>
> if (RelationUsesLocalBuffers(reln))
> {
>   /* ACCESS DENIED CHECK */
>   if (reln != NULL &&
>   reln->rd_rel != NULL &&
>   reln->rd_rel->relpersistence == RELPERSISTENCE_TEMP &&
>   !RelationUsesLocalBuffers(reln))
>   {
>   ereport(ERROR,
>         (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
>         errmsg("cannot access temporary tables of other sessions")));
>   }
> ...
> }
>
> So it'll be always false, making the ereport unreachable.
>
> == ReadBufferExtended ==
>
> These conditions cancel each other out:
>
> if (reln->rd_rel->relpersistence == RELPERSISTENCE_TEMP &&
>     !RelationUsesLocalBuffers(reln))
>
> RelationUsesLocalBuffers(reln) expands to
> ((relation)->rd_rel->relpersistence == RELPERSISTENCE_TEMP), making the
> error message unreachable. Perhaps you meant RELATION_IS_OTHER_TEMP?
>
> == ReadBuffer_common ==
>
> Same as in ReadBufferExtended and PrefetchBuffer.
>
> == tests ==
>
> You excluded the tests from the patch.
>
> == patch version ==
>
> You forgot to add the patch version.


Thank you for the detailed review and for pointing out these issues. I
understood that the condition I used with RelationUsesLocalBuffers()
was incorrect and made the checks unreachable. Also the
RELATION_IS_OTHER_TEMP() was not the appropriate macro to use here.
Regarding PrefetchBuffer, placing the check inside the
RelationUsesLocalBuffers() branch was also logically inconsistent.
Apologies for missing the tests and patch versioning in my submission.
I will ensure these are included properly in future iterations. Based
on the subsequent discussion and the analysis of the regression
introduced by routing through read_stream_next_buffer(), I agree that
the fix is better in the read stream / buffer read entry points rather
than only in bufmgr.
Thank you for your guidance. It really helped clarify both the root
cause and the correct placement of the fix.

Regards,
Soumya

  30. Re: Fix bug with accessing to temporary tables of other sessions

    Soumya S Murali <soumyamurali.work@gmail.com> — 2026-04-13T12:40:09Z

    Hi all,

On Fri, Apr 10, 2026 at 12:40 PM Daniil Davydov <3danissimo@gmail.com> wrote:
>
> Hi,
>
> On Fri, Apr 10, 2026 at 12:46 AM Jim Jones <jim.jones@uni-muenster.de> wrote:
> >
> > I guess a check in read_stream_begin_relation()
> > and in StartReadBuffersImpl() would be the best solution? If you agree,
> > could you add it in v16?
>
> Having both checks might look a bit redundant since the read stream will
> eventually call the StartReadBuffersImpl function. On the other hand, there are
> many places which are checking this restriction even if subsequent functions
> (from bufmgr) also have this check.
>
> So, I'll keep both checks and a bit reduce the comments in the bufmgr.c .
>
> BTW, what do you think about making this comment less "concrete"? :
> # SELECT via index scan from other session.
> # Sequential scans are blocked at read_stream_begin_relation(); index scans
> # bypass that path entirely and reach ReadBufferExtended() in bufmgr.c
> # (nbtree's _bt_getbuf calls ReadBuffer directly for individual page fetches).
> # enable_seqscan=off forces the planner to use the index.
>
> I mean that if the described logic changes, this comment will become confusing.
> We can describe the test in general words. For example :
> # Index scans can use a different code path from the one sequential scans are
> # following. Make sure that we cannot access other sessions' temp tables during
> # index scan either.
>
>
> Thank you for the comments! Please, see an updated set of patches.
>


I tested the v16 patch on a clean tree and verified the behavior
across multiple execution paths. Same-session access to temporary
tables works as expected. Cross-session access is consistently
blocked; all attempts result in an error - "relation does not exist"
and no incorrect or empty result sets were observed. In many cases,
access is blocked earlier with “relation does not exist”, while the
patch ensures that deeper execution paths are also protected. Verified
both sequential and index scan paths (after disabling seqscan), and
access is correctly rejected in all the cases. Tested various query
forms including SELECT, COUNT(*), JOINs, subqueries, DML operations,
and EXPLAIN ANALYZE none allowed access to other sessions’ temporary
tables. pg_relation_size() returns metadata as expected and does not
expose incorrect data.
Please let me know if there are additional scenarios I should
validate. Looking forward to more feedback.

Regards,
Soumya

  31. Re: Fix bug with accessing to temporary tables of other sessions

    Jim Jones <jim.jones@uni-muenster.de> — 2026-04-13T13:56:09Z

    Hi

On 13/04/2026 14:40, Soumya S Murali wrote:
> Please let me know if there are additional scenarios I should
> validate. Looking forward to more feedback.

Thanks for testing it. You can take a look at
012_temp_obj_multisession.pl and check if we missed any path.

Due to changes introduced in b2a17ba7a5d the patch was no longer
applying. See rebased v18 attached.

Best, Jim

  32. Re: Fix bug with accessing to temporary tables of other sessions

    Daniil Davydov <3danissimo@gmail.com> — 2026-04-13T14:18:07Z

    Hi,

On Mon, Apr 13, 2026 at 7:39 PM Soumya S Murali
<soumyamurali.work@gmail.com> wrote:
>
>
> I tested the v16 patch on a clean tree and verified the behavior
> across multiple execution paths.

Thanks!

> Cross-session access is consistently
> blocked; all attempts result in an error - "relation does not exist"

Hm, our patch should give an error like "cannot access temporary relations of
other sessions". If you see "relation does not exist" I guess you try to access
other session's temp table only by its name. It will not work since postgres
will search this name within the "pg_temp" schema of the current session. You
should explicitly specify other session's temp schema name in order to access
its temp tables. You can find examples in our new test.

Please, let me know if I am wrong and the current patch actually allows
"relation does not exist" error to occur. In this case, it should be fixed.

--
Best regards,
Daniil Davydov

  33. Re: Fix bug with accessing to temporary tables of other sessions

    Soumya S Murali <soumyamurali.work@gmail.com> — 2026-04-16T05:56:09Z

    Hi all,

Thank you for the clarification and the updated patch.

On Fri, Apr 10, 2026 at 8:58 PM Daniil Davydov <3danissimo@gmail.com> wrote:
>
> Hi,
>
> On Fri, Apr 10, 2026 at 5:29 PM Jim Jones <jim.jones@uni-muenster.de> wrote:
> >
> > > BTW, what do you think about making this comment less "concrete"? :
> > > # SELECT via index scan from other session.
> > > # Sequential scans are blocked at read_stream_begin_relation(); index scans
> > > # bypass that path entirely and reach ReadBufferExtended() in bufmgr.c
> > > # (nbtree's _bt_getbuf calls ReadBuffer directly for individual page fetches).
> > > # enable_seqscan=off forces the planner to use the index.
> > >
> > > I mean that if the described logic changes, this comment will become confusing.
> > > We can describe the test in general words. For example :
> > > # Index scans can use a different code path from the one sequential scans are
> > > # following. Make sure that we cannot access other sessions' temp tables during
> > > # index scan either.
> >
> > +1
> >
> > Yeah, it's indeed too verbose. I guess these comments were originally
> > just for me so I wouldn't get too confused along the way :)
>
> OK :)
>
> >
> > I don't have anything else to add at this point. Unless there are any
> > objections, I'll mark the CF entry as 'Ready for Committer.'
> >
>
> Great, thank you!
>
> Please, see an updated set of patches (only perl test has been changed) :
> 1) Rephrase the discussed comment.
> 2) Use safe_psql whenever possible.
> 3) Run pgperltidy.


You were right. In my earlier testing I was not using the explicit
temporary schema, which resulted in “relation does not exist” due to
namespace resolution. I have now re-tested using the correct temporary
schema of the owning session, and I can confirm that the patch behaves
as expected. Cross-session access consistently throws: ERROR: cannot
access temporary relations of other sessions
Verified across multiple execution paths including SELECT, COUNT(*),
JOINs, subqueries, and DML operations. Index scan paths (with seqscan
disabled) are also correctly blocked with ERROR: cannot access
temporary relations of other sessions. Same-session access continues
to work as expected. Metadata access (pg_relation_size) behaves
correctly and does not expose incorrect data. Cases where “relation
does not exist” appears are due to referencing an
incorrect temp schema, which is expected. Overall, the patch correctly
prevents access across all tested paths.
Thank you for pointing this out.

Regards,
Soumya

  34. Re: Fix bug with accessing to temporary tables of other sessions

    Soumya S Murali <soumyamurali.work@gmail.com> — 2026-04-16T06:41:53Z

    Hi all,

Thank you for the guidance and the updated patch.

On Mon, Apr 13, 2026 at 7:26 PM Jim Jones <jim.jones@uni-muenster.de> wrote:
>
> Hi
>
> On 13/04/2026 14:40, Soumya S Murali wrote:
> > Please let me know if there are additional scenarios I should
> > validate. Looking forward to more feedback.
>
> Thanks for testing it. You can take a look at
> 012_temp_obj_multisession.pl and check if we missed any path.
>
> Due to changes introduced in b2a17ba7a5d the patch was no longer
> applying. See rebased v18 attached.
>


I tested the rebased v18 patch on a clean tree and verified that it
applies cleanly and behaves consistently with previous results.
Cross-session access is correctly blocked with: ERROR: cannot access
temporary relations of other sessions
Index scan paths are also properly restricted, and same-session access
continues to work as expected.
The updated test changes look good. Everything works as expected, +1
from my side.

Regards,
Soumya

  35. Re: Fix bug with accessing to temporary tables of other sessions

    Alexander Korotkov <aekorotkov@gmail.com> — 2026-04-20T20:07:23Z

    Hi!

I've checked the thread.  Thanks to all the participants for their
work.  I think there is a general agreement on the design.

On Thu, Apr 16, 2026 at 9:41 AM Soumya S Murali
<soumyamurali.work@gmail.com> wrote:
> Thank you for the guidance and the updated patch.
>
> On Mon, Apr 13, 2026 at 7:26 PM Jim Jones <jim.jones@uni-muenster.de> wrote:
> >
> > Hi
> >
> > On 13/04/2026 14:40, Soumya S Murali wrote:
> > > Please let me know if there are additional scenarios I should
> > > validate. Looking forward to more feedback.
> >
> > Thanks for testing it. You can take a look at
> > 012_temp_obj_multisession.pl and check if we missed any path.
> >
> > Due to changes introduced in b2a17ba7a5d the patch was no longer
> > applying. See rebased v18 attached.
> >
>
>
> I tested the rebased v18 patch on a clean tree and verified that it
> applies cleanly and behaves consistently with previous results.
> Cross-session access is correctly blocked with: ERROR: cannot access
> temporary relations of other sessions
> Index scan paths are also properly restricted, and same-session access
> continues to work as expected.
> The updated test changes look good. Everything works as expected, +1
> from my side.

I see the patch changes the error wording.  Previously the error was
"cannot access temporary tables of other sessions", but we change it
to "cannot access temporary relation of other sessions".  I see the
intention here: we trigger an error while accessing some relation (not
necessarily a table) then we should reflect this directly to the error
message.  However, old message is already here for quite a while and
translated into many languages.  Also, is old message incorrect?  We
trigger an error on buffer access.  That is, we trigger an error only
for relation with a storage: table, index, sequence or matview.
Matview can't be temporary.  Also, if you access an index with a
query, that means you're querying its table.  But sequence can be
temporary and it can be not directly associated with a table.  So,
yes, new error message is more correct.  But I would prefer to make it
a separate patch, and replace all the occurrences including contrib.

------
Regards,
Alexander Korotkov
Supabase

  36. Re: Fix bug with accessing to temporary tables of other sessions

    Soumya S Murali <soumyamurali.work@gmail.com> — 2026-04-21T05:45:06Z

    Hi all,

Thank you for reviewing the patch and for the detailed explanation.

On Tue, Apr 21, 2026 at 1:37 AM Alexander Korotkov <aekorotkov@gmail.com> wrote:
>
> Hi!
>
> I've checked the thread.  Thanks to all the participants for their
> work.  I think there is a general agreement on the design.
>
> On Thu, Apr 16, 2026 at 9:41 AM Soumya S Murali
> <soumyamurali.work@gmail.com> wrote:
> > Thank you for the guidance and the updated patch.
> >
> > On Mon, Apr 13, 2026 at 7:26 PM Jim Jones <jim.jones@uni-muenster.de> wrote:
> > >
> > > Hi
> > >
> > > On 13/04/2026 14:40, Soumya S Murali wrote:
> > > > Please let me know if there are additional scenarios I should
> > > > validate. Looking forward to more feedback.
> > >
> > > Thanks for testing it. You can take a look at
> > > 012_temp_obj_multisession.pl and check if we missed any path.
> > >
> > > Due to changes introduced in b2a17ba7a5d the patch was no longer
> > > applying. See rebased v18 attached.
> > >
> >
> >
> > I tested the rebased v18 patch on a clean tree and verified that it
> > applies cleanly and behaves consistently with previous results.
> > Cross-session access is correctly blocked with: ERROR: cannot access
> > temporary relations of other sessions
> > Index scan paths are also properly restricted, and same-session access
> > continues to work as expected.
> > The updated test changes look good. Everything works as expected, +1
> > from my side.
>
> I see the patch changes the error wording.  Previously the error was
> "cannot access temporary tables of other sessions", but we change it
> to "cannot access temporary relation of other sessions".  I see the
> intention here: we trigger an error while accessing some relation (not
> necessarily a table) then we should reflect this directly to the error
> message.  However, old message is already here for quite a while and
> translated into many languages.  Also, is old message incorrect?  We
> trigger an error on buffer access.  That is, we trigger an error only
> for relation with a storage: table, index, sequence or matview.
> Matview can't be temporary.  Also, if you access an index with a
> query, that means you're querying its table.  But sequence can be
> temporary and it can be not directly associated with a table.  So,
> yes, new error message is more correct.  But I would prefer to make it
> a separate patch, and replace all the occurrences including contrib.
>


This makes sense. While the new wording is indeed more precise, I
agree that changing an existing error message, especially one that has
been present for a long time and is already translated, should be
handled
separately from the bug fix. Keeping the current message for this
patch and addressing wording improvements in a dedicated follow-up
patch sounds like the right approach.
Thanks for pointing this out.

Regards,
Soumya

  37. Re: Fix bug with accessing to temporary tables of other sessions

    Alexander Korotkov <aekorotkov@gmail.com> — 2026-04-21T10:54:47Z

    On Tue, Apr 21, 2026 at 8:44 AM Soumya S Murali
<soumyamurali.work@gmail.com> wrote:
> Thank you for reviewing the patch and for the detailed explanation.
>
> On Tue, Apr 21, 2026 at 1:37 AM Alexander Korotkov <aekorotkov@gmail.com> wrote:
> >
> > Hi!
> >
> > I've checked the thread.  Thanks to all the participants for their
> > work.  I think there is a general agreement on the design.
> >
> > On Thu, Apr 16, 2026 at 9:41 AM Soumya S Murali
> > <soumyamurali.work@gmail.com> wrote:
> > > Thank you for the guidance and the updated patch.
> > >
> > > On Mon, Apr 13, 2026 at 7:26 PM Jim Jones <jim.jones@uni-muenster.de> wrote:
> > > >
> > > > Hi
> > > >
> > > > On 13/04/2026 14:40, Soumya S Murali wrote:
> > > > > Please let me know if there are additional scenarios I should
> > > > > validate. Looking forward to more feedback.
> > > >
> > > > Thanks for testing it. You can take a look at
> > > > 012_temp_obj_multisession.pl and check if we missed any path.
> > > >
> > > > Due to changes introduced in b2a17ba7a5d the patch was no longer
> > > > applying. See rebased v18 attached.
> > > >
> > >
> > >
> > > I tested the rebased v18 patch on a clean tree and verified that it
> > > applies cleanly and behaves consistently with previous results.
> > > Cross-session access is correctly blocked with: ERROR: cannot access
> > > temporary relations of other sessions
> > > Index scan paths are also properly restricted, and same-session access
> > > continues to work as expected.
> > > The updated test changes look good. Everything works as expected, +1
> > > from my side.
> >
> > I see the patch changes the error wording.  Previously the error was
> > "cannot access temporary tables of other sessions", but we change it
> > to "cannot access temporary relation of other sessions".  I see the
> > intention here: we trigger an error while accessing some relation (not
> > necessarily a table) then we should reflect this directly to the error
> > message.  However, old message is already here for quite a while and
> > translated into many languages.  Also, is old message incorrect?  We
> > trigger an error on buffer access.  That is, we trigger an error only
> > for relation with a storage: table, index, sequence or matview.
> > Matview can't be temporary.  Also, if you access an index with a
> > query, that means you're querying its table.  But sequence can be
> > temporary and it can be not directly associated with a table.  So,
> > yes, new error message is more correct.  But I would prefer to make it
> > a separate patch, and replace all the occurrences including contrib.
> >
>
>
> This makes sense. While the new wording is indeed more precise, I
> agree that changing an existing error message, especially one that has
> been present for a long time and is already translated, should be
> handled
> separately from the bug fix. Keeping the current message for this
> patch and addressing wording improvements in a dedicated follow-up
> patch sounds like the right approach.
> Thanks for pointing this out.

OK. I'm going to push and backpatch if no objections.

------
Regards,
Alexander Korotkov
Supabase

  38. Re: Fix bug with accessing to temporary tables of other sessions

    Jim Jones <jim.jones@uni-muenster.de> — 2026-04-21T12:52:25Z

    
On 21/04/2026 12:54, Alexander Korotkov wrote:
> OK. I'm going to push and backpatch if no objections.

+1

Thanks!

Best, Jim

  39. Re: Fix bug with accessing to temporary tables of other sessions

    Daniil Davydov <3danissimo@gmail.com> — 2026-04-21T13:17:15Z

    Hi,

On Tue, Apr 21, 2026 at 3:07 AM Alexander Korotkov <aekorotkov@gmail.com> wrote:
>
> I've checked the thread.  Thanks to all the participants for their
> work.  I think there is a general agreement on the design.
>
> I see the patch changes the error wording.  Previously the error was
> "cannot access temporary tables of other sessions", but we change it
> to "cannot access temporary relation of other sessions".  I see the
> intention here: we trigger an error while accessing some relation (not
> necessarily a table) then we should reflect this directly to the error
> message.  However, old message is already here for quite a while and
> translated into many languages.  Also, is old message incorrect?  We
> trigger an error on buffer access.  That is, we trigger an error only
> for relation with a storage: table, index, sequence or matview.
> Matview can't be temporary.  Also, if you access an index with a
> query, that means you're querying its table.  But sequence can be
> temporary and it can be not directly associated with a table.  So,
> yes, new error message is more correct.

Thank you very much for the review!

> But I would prefer to make it
> a separate patch, and replace all the occurrences including contrib.

OK, no problem.
BTW, do I understand correctly that I don't need to touch the .po files?

I have also noticed this code in the localbuf.c :
```
    if (IsParallelWorker())
        ereport(ERROR,
                (errcode(ERRCODE_INVALID_TRANSACTION_STATE),
                 errmsg("cannot access temporary tables during a
parallel operation")));
```
This code is related to initialization of local buffers which can be performed
not only for tables, obviously. Since this is a small step away from our
original idea, I'll fix it in a separate patch. IMHO it will be an appropriate
fix since we have already taken on this task. Looking forward to your comments.

--
Best regards,
Daniil Davydov

  40. Re: Fix bug with accessing to temporary tables of other sessions

    Michael Paquier <michael@paquier.xyz> — 2026-04-21T23:41:49Z

    On Tue, Apr 21, 2026 at 01:54:47PM +0300, Alexander Korotkov wrote:
> OK. I'm going to push and backpatch if no objections.

Timing is interesting here.  Last week I have been doing a on-site
patch review with a couple of colleagues and myself, and this thread
has been chosen as part of this exercise.  I am attaching them in CC
of this thread, and replying to this thread was an action item I had
to act on.

Here is some feedback, based on v18 posted here:
https://www.postgresql.org/message-id/b6614954-6c71-451a-a1d7-345d255b4afb@uni-muenster.de

We have found that the thread does not state clearly what it aims to
fix.  The subject states that it is a bug, perhaps it is but the
thread does not seem to do a good job in explaining why the current
superuser behavior is bad, while the behavior of the patch to block
some commands is better.  This should be clearer in explaining why
this new behavior is better.

The test script is under-documented.  There are zero comments that
actually explain why the patch does what it does.  The few comments
present could be removed: they are copy-pasted of the descriptions of
the test cases.  A much worse thing is this thing:

+# DROP TEMPORARY TABLE from other session
+$node->safe_psql('postgres', "DROP TABLE $tempschema.foo;");

DROP TABLE on a temporary relation for a superuser is a very useful
behavior to unstuck autovacuum if a temp relation has been orphaned.
It looks critical to me to explain that we want to keep this behavior
for this reason.  Using safe_psql() is not really adapted.  You should
use a psql() where we check that the command does not fail, so as the
test can generate a full report.

The test coverage actually has holes.  The three DML patterns INSERT,
UPDATE and DELETE are covered, and it is missing MERGE.  Also, what
about other patterns like ALTER TABLE, ALTER INDEX, ALTER FUNCTION,
DROP FUNCTION and the like?  There are many object patterns that can
be schema-qualified, and none of this is covered.  What about the new
REPACK and a bunch of other maintenance commands?  There is nothing
about VACUUM, TRUNCATE, CLUSTER, etc.  Just to name a few.

Another question is how do we make sure that new command patterns
follow the same rule as what is enforced here.  It looks like this
should be at least documented somewhere to be less surprising, but the
patch does nothing of the kind.

As a whole the patch lacks documentation, comments, and explanations,
making it difficult to act on.

As a whole, we were not really convinced that this is something that
needs any kind of specific fix, especially not something that should
be backpatched.

After saying all that, there is some value in what you are doing here:
it is true that we lack test coverage in terms of interactions of
temporary objects across multiple sessions, and that we should have
some.  TAP is adapted for this purpose, isolation tests could be an
extra one but the schema names make that unpredictible in output.  The
patch unfortunately does a poor job in showing what it wants to
change.  One thing that I would suggest is to *reverse* the order of
the patches:
- First have a patch that introduces new tests, that shows the
original behavior.  This needs to be more complete in terms of command
patterns.  The DROP TABLE is one case that we want to keep.  This
should be kept as-is, and it is critical to document the reason why we
want to keep things this way (aka autovacuum and orphaned tables,
AFAIK).
- Then implement the second patch that updates the tests introduced in
the first patch, so as one can track *what* has changed, and so as one
does not have to test manually what the original behavior was.
 
As a whole, this patch needs more work, based on what has been
currently posted on the lists.  That's not ready yet.  The backpatch
question is a separate matter.

Thanks,
--
Michael

  41. Re: Fix bug with accessing to temporary tables of other sessions

    Jim Jones <jim.jones@uni-muenster.de> — 2026-04-24T22:09:39Z

    Hi Michael,

Thanks for the review.

On 22/04/2026 01:41, Michael Paquier wrote:
> As a whole, we were not really convinced that this is something that
> needs any kind of specific fix, especially not something that should
> be backpatched.

This surprises me a little bit. Until now I was under the impression
that fixing it was the consensus here. Just the "how" was being discussed.

> After saying all that, there is some value in what you are doing here:
> it is true that we lack test coverage in terms of interactions of
> temporary objects across multiple sessions, and that we should have
> some.  TAP is adapted for this purpose, isolation tests could be an
> extra one but the schema names make that unpredictible in output.  The
> patch unfortunately does a poor job in showing what it wants to
> change.  One thing that I would suggest is to *reverse* the order of
> the patches:
> - First have a patch that introduces new tests, that shows the
> original behavior.  This needs to be more complete in terms of command
> patterns.  The DROP TABLE is one case that we want to keep.  This
> should be kept as-is, and it is critical to document the reason why we
> want to keep things this way (aka autovacuum and orphaned tables,
> AFAIK).
> - Then implement the second patch that updates the tests introduced in
> the first patch, so as one can track *what* has changed, and so as one
> does not have to test manually what the original behavior was.


Fair point on tests comments and coverage. But before we start with the
refactoring, I'd like to make sure I understood your suggestion
correctly. You're suggesting:

0001 - TAP tests with improved coverage and comments that pass on
current master, documenting the existing behaviour, which means broken
commands silently succeed (e.g. SELECT returns 0 rows, no error)

0002 - read_stream.c and bufmgr.c fix + updated test expectations (the
same commands now raise errors)

Is it what you had in mind?

Best, Jim

  42. Re: Fix bug with accessing to temporary tables of other sessions

    David G. Johnston <david.g.johnston@gmail.com> — 2026-04-24T22:30:51Z

    On Fri, Apr 24, 2026 at 3:09 PM Jim Jones <jim.jones@uni-muenster.de> wrote:

>
> 0001 - TAP tests with improved coverage and comments that pass on
> current master, documenting the existing behaviour, which means broken
> commands silently succeed (e.g. SELECT returns 0 rows, no error)


> 0002 - read_stream.c and bufmgr.c fix + updated test expectations (the
> same commands now raise errors)
>

If you can run the tests against v17 (that is the behavior we are trying to
restore here, correct?) and v18 that would help demonstrate why the
backpatch is needed.

David J.

  43. Re: Fix bug with accessing to temporary tables of other sessions

    Jim Jones <jim.jones@uni-muenster.de> — 2026-04-24T23:01:21Z

    Hi David

On 25/04/2026 00:30, David G. Johnston wrote:
> If you can run the tests against v17 (that is the behavior we are trying
> to restore here, correct?) and v18 that would help demonstrate why the
> backpatch is needed.

Tests for PG18 and PG17:

== PG 18 ==

psql (18.3 (Debian 18.3-1.pgdg13+1))
Type "help" for help.

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

postgres=# SELECT * FROM pg_temp_36.t;
 generate_series
-----------------
(0 rows)


== PG 17 ==

psql (17.7 (Debian 17.7-3.pgdg13+1))
Geben Sie »help« für Hilfe ein.

postgres=# \d pg_temp*.*
                         Tabelle »pg_temp_13.t«
     Spalte      |   Typ   | Sortierfolge | NULL erlaubt? | Vorgabewert
-----------------+---------+--------------+---------------+-------------
 generate_series | integer |              |               |

postgres=# SELECT * FROM pg_temp_13.t;
 generate_series
-----------------
(0 Zeilen)


Until PG16 an error message was raised:

psql (16.13 (Debian 16.13-1.pgdg13+1))
Type "help" for help.

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

postgres=# SELECT * FROM pg_temp_3.t;
ERROR:  cannot access temporary tables of other sessions


Best, Jim

  44. Re: Fix bug with accessing to temporary tables of other sessions

    Daniil Davydov <3danissimo@gmail.com> — 2026-04-25T08:34:22Z

    Hi,

On Wed, Apr 22, 2026 at 6:41 AM Michael Paquier <michael@paquier.xyz> wrote:
>
> On Tue, Apr 21, 2026 at 01:54:47PM +0300, Alexander Korotkov wrote:
> > OK. I'm going to push and backpatch if no objections.
>
> Timing is interesting here.  Last week I have been doing a on-site
> patch review with a couple of colleagues and myself, and this thread
> has been chosen as part of this exercise.  I am attaching them in CC
> of this thread, and replying to this thread was an action item I had
> to act on.
>
> Here is some feedback, based on v18 posted here:
> https://www.postgresql.org/message-id/b6614954-6c71-451a-a1d7-345d255b4afb@uni-muenster.de
>

Thank you for looking into this!

> We have found that the thread does not state clearly what it aims to
> fix.  The subject states that it is a bug, perhaps it is but the
> thread does not seem to do a good job in explaining why the current
> superuser behavior is bad, while the behavior of the patch to block
> some commands is better.  This should be clearer in explaining why
> this new behavior is better.

This patch doesn't provide any new behavior. It returns consistent behavior,
which we have lost after the appearance of streaming I/O in sequential scans.
Jim wrote about it here [1]. You can also find this in the commit message of
the v19 patch.

IMHO, both thread and attached patch do the job of explaining why this fix is
necessary. But I think I understand what confused you. Please, see my comments
below.

> The test script is under-documented.  There are zero comments that
> actually explain why the patch does what it does.  The few comments
> present could be removed: they are copy-pasted of the descriptions of
> the test cases.  A much worse thing is this thing:
>
> +# DROP TEMPORARY TABLE from other session
> +$node->safe_psql('postgres', "DROP TABLE $tempschema.foo;");

Yeah, I agree. It can be (and will be) improved.

> DROP TABLE on a temporary relation for a superuser is a very useful
> behavior to unstuck autovacuum if a temp relation has been orphaned.
> It looks critical to me to explain that we want to keep this behavior
> for this reason.

Do you mean that we should add something to the documentation? Again, postgres
has always allowed DROP TABLE operation to be done for other temp tables, if
the user has enough privileges. We are disallowing everyone to look at other
temp tables' pages because there is no capability to do it. DROP TABLE doesn't
require access to backend-private data, so it is OK. Tom Lane wrote about it
here [2]. I want to say that described behavior is pretty natural. Why should
we additionally describe that the user can do operations that he is allowed
to do?

On the other hand, we have an error message that says "cannot access...", which
may look like every kind of "access" is forbidden. I bet that this is the place
that has confused you. More accurate error message would be "cannot access
pages..." or "cannot access content...". I think we can change our error
message in this way. What do you think?

We can also explicitly write in the documentation that users cannot access
*pages/content* of other temp tables. But the original patch [3] didn't do it,
so I am not sure that we should either.

> Using safe_psql() is not really adapted.  You should
> use a psql() where we check that the command does not fail, so as the
> test can generate a full report.

Fair enough.

> The test coverage actually has holes.  The three DML patterns INSERT,
> UPDATE and DELETE are covered, and it is missing MERGE.  Also, what
> about other patterns like ALTER TABLE, ALTER INDEX, ALTER FUNCTION,
> DROP FUNCTION and the like?
> There are many object patterns that can
> be schema-qualified, and none of this is covered.  What about the new
> REPACK and a bunch of other maintenance commands?  There is nothing
> about VACUUM, TRUNCATE, CLUSTER, etc.  Just to name a few.

I have concerns about it. If we try the "brute force" approach (i.e. test all
available commands), the test will increase unreasonably and will need constant
support. I guess that covering all available code paths that lead to reading
temp table's pages would be enough.

> As a whole, we were not really convinced that this is something that
> needs any kind of specific fix

It is a bug, obviously. TBH, I don't see any reason for not fixing it.

> especially not something that should
> be backpatched.

I couldn't answer for a long time, and Jim had already clearly demonstrated
why the backpatch is needed (for which I am grateful).


[1] https://www.postgresql.org/message-id/b13dc5ba-6c11-429c-b6fe-673c1c767bcf%40uni-muenster.de
[2] https://www.postgresql.org/message-id/4075754.1774378690%40sss.pgh.pa.us
[3] 948d6ec90fd35d6e1a59d0b1af8d6efd8442f0ad

--
Best regards,
Danill Davydov

  45. Re: Fix bug with accessing to temporary tables of other sessions

    Jim Jones <jim.jones@uni-muenster.de> — 2026-04-25T13:53:41Z

    
On 25/04/2026 01:01, Jim Jones wrote:
> Until PG16 an error message was raised:
> 
> psql (16.13 (Debian 16.13-1.pgdg13+1))
> Type "help" for help.
> 
> postgres=# \d pg_temp*.*
>                     Table "pg_temp_3.t"
>      Column      |  Type   | Collation | Nullable | Default
> -----------------+---------+-----------+----------+---------
>  generate_series | integer |           |          |
> 
> postgres=# SELECT * FROM pg_temp_3.t;
> ERROR:  cannot access temporary tables of other sessions


The PG16 test above was against a non-empty TEMPORARY TABLE. If the
table is empty, the same behaviour from PG17 and PG18 can be observed:

psql (16.13 (Debian 16.13-1.pgdg13+1))
Type "help" for help.

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

postgres=# SELECT * FROM pg_temp_4.t;
 id
----
(0 rows)

The same applies for PG14 and PG15

psql (14.22 (Debian 14.22-1.pgdg13+1))
Type "help" for help.

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

postgres=# SELECT * FROM pg_temp_3.t;
 id
----
(0 rows)

psql (15.17 (Debian 15.17-1.pgdg13+1))
Type "help" for help.

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

postgres=# SELECT * FROM pg_temp_3.t;
 id
----
(0 rows)


Since the table is indeed empty, the result is actually correct. But I'd
argue that we should raise an ERROR even if the table is empty. IMHO,
getting an error message or a "0 rows" result depending on the table row
count isn't ideal.

After populating the TEMPORARY TABLE the expected error message appears:

ERROR:  cannot access temporary tables of other sessions

Best, Jim

  46. Re: Fix bug with accessing to temporary tables of other sessions

    Michael Paquier <michael@paquier.xyz> — 2026-04-27T00:50:28Z

    On Tue, Apr 21, 2026 at 08:17:15PM +0700, Daniil Davydov wrote:
> BTW, do I understand correctly that I don't need to touch the .po files?

The .po files in the tree are refreshed periodically based on the work
done by pgsql-translators.  There is no need to touch them while
working on the in-core code.
--
Michael

  47. Re: Fix bug with accessing to temporary tables of other sessions

    Jim Jones <jim.jones@uni-muenster.de> — 2026-05-01T19:16:39Z

    Hi

On 25/04/2026 10:34, Daniil Davydov wrote:
> On Wed, Apr 22, 2026 at 6:41 AM Michael Paquier <michael@paquier.xyz> wrote:
>>
>> On Tue, Apr 21, 2026 at 01:54:47PM +0300, Alexander Korotkov wrote:
>>> OK. I'm going to push and backpatch if no objections.
>>
>> As a whole, we were not really convinced that this is something that
>> needs any kind of specific fix
> 
> It is a bug, obviously. TBH, I don't see any reason for not fixing it.
> 
>> especially not something that should
>> be backpatched.


I see there are differing opinions among committers on this. How should
we proceed? Do we really consider the current behaviour acceptable, or
should we invest effort in improving the tests (and potentially
backpatch it)?

Best, Jim

  48. Re: Fix bug with accessing to temporary tables of other sessions

    Alexander Korotkov <aekorotkov@gmail.com> — 2026-05-02T14:16:39Z

    Hi, Michael!

On Wed, Apr 22, 2026 at 2:41 AM Michael Paquier <michael@paquier.xyz> wrote:
>
> On Tue, Apr 21, 2026 at 01:54:47PM +0300, Alexander Korotkov wrote:
> > OK. I'm going to push and backpatch if no objections.
>
> Timing is interesting here.  Last week I have been doing a on-site
> patch review with a couple of colleagues and myself, and this thread
> has been chosen as part of this exercise.  I am attaching them in CC
> of this thread, and replying to this thread was an action item I had
> to act on.
>
> Here is some feedback, based on v18 posted here:
> https://www.postgresql.org/message-id/b6614954-6c71-451a-a1d7-345d255b4afb@uni-muenster.de
>
> We have found that the thread does not state clearly what it aims to
> fix.  The subject states that it is a bug, perhaps it is but the
> thread does not seem to do a good job in explaining why the current
> superuser behavior is bad, while the behavior of the patch to block
> some commands is better.  This should be clearer in explaining why
> this new behavior is better.
>
> The test script is under-documented.  There are zero comments that
> actually explain why the patch does what it does.  The few comments
> present could be removed: they are copy-pasted of the descriptions of
> the test cases.  A much worse thing is this thing:
>
> +# DROP TEMPORARY TABLE from other session
> +$node->safe_psql('postgres', "DROP TABLE $tempschema.foo;");
>
> DROP TABLE on a temporary relation for a superuser is a very useful
> behavior to unstuck autovacuum if a temp relation has been orphaned.
> It looks critical to me to explain that we want to keep this behavior
> for this reason.  Using safe_psql() is not really adapted.  You should
> use a psql() where we check that the command does not fail, so as the
> test can generate a full report.
>
> The test coverage actually has holes.  The three DML patterns INSERT,
> UPDATE and DELETE are covered, and it is missing MERGE.  Also, what
> about other patterns like ALTER TABLE, ALTER INDEX, ALTER FUNCTION,
> DROP FUNCTION and the like?  There are many object patterns that can
> be schema-qualified, and none of this is covered.  What about the new
> REPACK and a bunch of other maintenance commands?  There is nothing
> about VACUUM, TRUNCATE, CLUSTER, etc.  Just to name a few.
>
> Another question is how do we make sure that new command patterns
> follow the same rule as what is enforced here.  It looks like this
> should be at least documented somewhere to be less surprising, but the
> patch does nothing of the kind.
>
> As a whole the patch lacks documentation, comments, and explanations,
> making it difficult to act on.
>
> As a whole, we were not really convinced that this is something that
> needs any kind of specific fix, especially not something that should
> be backpatched.
>
> After saying all that, there is some value in what you are doing here:
> it is true that we lack test coverage in terms of interactions of
> temporary objects across multiple sessions, and that we should have
> some.  TAP is adapted for this purpose, isolation tests could be an
> extra one but the schema names make that unpredictible in output.  The
> patch unfortunately does a poor job in showing what it wants to
> change.  One thing that I would suggest is to *reverse* the order of
> the patches:
> - First have a patch that introduces new tests, that shows the
> original behavior.  This needs to be more complete in terms of command
> patterns.  The DROP TABLE is one case that we want to keep.  This
> should be kept as-is, and it is critical to document the reason why we
> want to keep things this way (aka autovacuum and orphaned tables,
> AFAIK).
> - Then implement the second patch that updates the tests introduced in
> the first patch, so as one can track *what* has changed, and so as one
> does not have to test manually what the original behavior was.
>
> As a whole, this patch needs more work, based on what has been
> currently posted on the lists.  That's not ready yet.  The backpatch
> question is a separate matter.

Thank you for your feedback.  I think the scope of this patch is well
described in [1].  We don't want to restrict the superuser from
something, but our buffer manager just technically can't access the
local buffers of other sessions.  Read streams introduced a new code
path for reading relations, which was lacking of the proper check for
local buffers of other sessions.  And this patch attempts to fix that.
DROP TABLE is an exclusion.  It actually don't need to read contents
of buffers, just drop them.  And DropRelationBuffers() have a special
exclusion for this case.  So, DROP TABLE appears to be the only
operation that makes sense, it's a conscious exclusion, and there is
no intention to forbid it.

I've revised the patch.  0001 contains tests and states the current
behavior.  0002 contains fix and the corresponding changes in the
tests.  I made a change in 0001: removed the check in
ReadBufferExtended().  We added the same check to ReadBuffer_common(),
and I don't think it makes sense to do this check twice in the row.

Links.
1. https://www.postgresql.org/message-id/3529398.1774273446%40sss.pgh.pa.us

------
Regards,
Alexander Korotkov
Supabase

  49. Re: Fix bug with accessing to temporary tables of other sessions

    Daniil Davydov <3danissimo@gmail.com> — 2026-05-02T15:37:35Z

    Hi,

On Sat, May 2, 2026 at 9:16 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
>
> Thank you for your feedback.  I think the scope of this patch is well
> described in [1].  We don't want to restrict the superuser from
> something, but our buffer manager just technically can't access the
> local buffers of other sessions.  Read streams introduced a new code
> path for reading relations, which was lacking of the proper check for
> local buffers of other sessions.  And this patch attempts to fix that.
> DROP TABLE is an exclusion.  It actually don't need to read contents
> of buffers, just drop them.  And DropRelationBuffers() have a special
> exclusion for this case.  So, DROP TABLE appears to be the only
> operation that makes sense, it's a conscious exclusion, and there is
> no intention to forbid it.

Yep, exactly.

> I've revised the patch.  0001 contains tests and states the current
> behavior.  0002 contains fix and the corresponding changes in the
> tests.  I made a change in 0001: removed the check in
> ReadBufferExtended().  We added the same check to ReadBuffer_common(),
> and I don't think it makes sense to do this check twice in the row.

Thank you! But I'm afraid that you forgot to attach the patches..

BTW, what do you think about this proposal? :
> On the other hand, we have an error message that says "cannot access...", which
> may look like every kind of "access" is forbidden. I bet that this is the place
> that has confused you. More accurate error message would be "cannot access
> pages..." or "cannot access content...". I think we can change our error
> message in this way. What do you think?

We can easily include it in the first patch.

--
Best regards,
Daniil Davydov

  50. Re: Fix bug with accessing to temporary tables of other sessions

    Alexander Korotkov <aekorotkov@gmail.com> — 2026-05-02T16:34:00Z

    On Sat, May 2, 2026 at 6:37 PM Daniil Davydov <3danissimo@gmail.com> wrote:
> On Sat, May 2, 2026 at 9:16 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
> >
> > Thank you for your feedback.  I think the scope of this patch is well
> > described in [1].  We don't want to restrict the superuser from
> > something, but our buffer manager just technically can't access the
> > local buffers of other sessions.  Read streams introduced a new code
> > path for reading relations, which was lacking of the proper check for
> > local buffers of other sessions.  And this patch attempts to fix that.
> > DROP TABLE is an exclusion.  It actually don't need to read contents
> > of buffers, just drop them.  And DropRelationBuffers() have a special
> > exclusion for this case.  So, DROP TABLE appears to be the only
> > operation that makes sense, it's a conscious exclusion, and there is
> > no intention to forbid it.
>
> Yep, exactly.
>
> > I've revised the patch.  0001 contains tests and states the current
> > behavior.  0002 contains fix and the corresponding changes in the
> > tests.  I made a change in 0001: removed the check in
> > ReadBufferExtended().  We added the same check to ReadBuffer_common(),
> > and I don't think it makes sense to do this check twice in the row.
>
> Thank you! But I'm afraid that you forgot to attach the patches..

Here they are.

> BTW, what do you think about this proposal? :
> > On the other hand, we have an error message that says "cannot access...", which
> > may look like every kind of "access" is forbidden. I bet that this is the place
> > that has confused you. More accurate error message would be "cannot access
> > pages..." or "cannot access content...". I think we can change our error
> > message in this way. What do you think?
>
> We can easily include it in the first patch.

This is possible, but I would keep that in a separate patch.  We now
have clear scope for both patches: 0001 includes additional tests,
0002 fixes the bug and restores old behavior.

------
Regards,
Alexander Korotkov
Supabase

  51. Re: Fix bug with accessing to temporary tables of other sessions

    Jim Jones <jim.jones@uni-muenster.de> — 2026-05-02T17:32:30Z

    Hi

On 02/05/2026 18:34, Alexander Korotkov wrote:
> On Sat, May 2, 2026 at 6:37 PM Daniil Davydov <3danissimo@gmail.com> wrote:
>> On Sat, May 2, 2026 at 9:16 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
>>>
>>> Thank you for your feedback.  I think the scope of this patch is well
>>> described in [1].  We don't want to restrict the superuser from
>>> something, but our buffer manager just technically can't access the
>>> local buffers of other sessions.  Read streams introduced a new code
>>> path for reading relations, which was lacking of the proper check for
>>> local buffers of other sessions.  And this patch attempts to fix that.
>>> DROP TABLE is an exclusion.  It actually don't need to read contents
>>> of buffers, just drop them.  And DropRelationBuffers() have a special
>>> exclusion for this case.  So, DROP TABLE appears to be the only
>>> operation that makes sense, it's a conscious exclusion, and there is
>>> no intention to forbid it.
>>
>> Yep, exactly.

+1

>>> I've revised the patch.  0001 contains tests and states the current
>>> behavior.  0002 contains fix and the corresponding changes in the
>>> tests.  I made a change in 0001: removed the check in
>>> ReadBufferExtended().  We added the same check to ReadBuffer_common(),
>>> and I don't think it makes sense to do this check twice in the row.
>>
>> Thank you! But I'm afraid that you forgot to attach the patches..
> 
> Here they are.


Thanks for the comprehensive additional tests!

In addition to the DROP TABLE exception:

It is also possible to LOCK temporary tables from other sessions:

postgres=# BEGIN;
BEGIN
postgres=*# LOCK TABLE pg_temp_91.t IN ACCESS SHARE MODE ;
LOCK TABLE

pg_temp_91.t lives as long the transaction is open -- even after the
origin session closes, which is totally expected. I'd say it falls into
the same category of DROP TABLE, where the table contents are never
read, so I'd argue it's ok.

> 
>> BTW, what do you think about this proposal? :
>>> On the other hand, we have an error message that says "cannot access...", which
>>> may look like every kind of "access" is forbidden. I bet that this is the place
>>> that has confused you. More accurate error message would be "cannot access
>>> pages..." or "cannot access content...". I think we can change our error
>>> message in this way. What do you think?
>>
>> We can easily include it in the first patch.
> 
> This is possible, but I would keep that in a separate patch.  We now
> have clear scope for both patches: 0001 includes additional tests,
> 0002 fixes the bug and restores old behavior.

+1 for a separate patch. I think the scope of the current patch is good
as-is.

Thanks!

Best, Jim

  52. Re: Fix bug with accessing to temporary tables of other sessions

    Daniil Davydov <3danissimo@gmail.com> — 2026-05-03T08:53:39Z

    Hi,

On Sun, May 3, 2026 at 12:32 AM Jim Jones <jim.jones@uni-muenster.de> wrote:
>
> In addition to the DROP TABLE exception:
>
> It is also possible to LOCK temporary tables from other sessions:
>
> postgres=# BEGIN;
> BEGIN
> postgres=*# LOCK TABLE pg_temp_91.t IN ACCESS SHARE MODE ;
> LOCK TABLE
>
> pg_temp_91.t lives as long the transaction is open -- even after the
> origin session closes, which is totally expected. I'd say it falls into
> the same category of DROP TABLE, where the table contents are never
> read, so I'd argue it's ok.

Autovacuum locks orphaned table's oid before deleting it. LOCK TABLE command
also locks the oid of the specified table. If we want to make sure that
autovacuum can acquire the mentioned lock (i.e. cover this behavior using
tests), I suggest testing it using the LOCK TABLE command.

Please, see the attached patch that ensures that cross-session LOCK TABLE works
properly.

--
Best regards,
Daniil Davydov

  53. Re: Fix bug with accessing to temporary tables of other sessions

    Jim Jones <jim.jones@uni-muenster.de> — 2026-05-03T12:49:03Z

    Hi Daniil

On 03/05/2026 10:53, Daniil Davydov wrote:
> Autovacuum locks orphaned table's oid before deleting it. LOCK TABLE command
> also locks the oid of the specified table. If we want to make sure that
> autovacuum can acquire the mentioned lock (i.e. cover this behavior using
> tests), I suggest testing it using the LOCK TABLE command.

+1 for the extra test.

> Please, see the attached patch that ensures that cross-session LOCK TABLE works
> properly.

I guess you should either add it to the 0001 sent by Alexander, or 
create a 0003. Right now the cfbot is complaining, as 
013_temp_obj_multisession.pl still does not exist :)

Best, Jim

  54. Re: Fix bug with accessing to temporary tables of other sessions

    Daniil Davydov <3danissimo@gmail.com> — 2026-05-04T09:31:18Z

    Hi,

On Sun, May 3, 2026 at 7:49 PM Jim Jones <jim.jones@uni-muenster.de> wrote:
>
> On 03/05/2026 10:53, Daniil Davydov wrote:
> > Please, see the attached patch that ensures that cross-session LOCK TABLE works
> > properly.
>
> I guess you should either add it to the 0001 sent by Alexander, or
> create a 0003. Right now the cfbot is complaining, as
> 013_temp_obj_multisession.pl still does not exist :)
>

OK, I'll attach this patch as a third one, just for review purposes. After
agreement on its content, it should be included into the 0001 and 0002 patches.

--
Best regards,
Daniil Davydov

  55. Re: Fix bug with accessing to temporary tables of other sessions

    Alexander Korotkov <aekorotkov@gmail.com> — 2026-05-07T08:04:16Z

    On Mon, May 4, 2026 at 12:31 PM Daniil Davydov <3danissimo@gmail.com> wrote:
>
> On Sun, May 3, 2026 at 7:49 PM Jim Jones <jim.jones@uni-muenster.de> wrote:
> >
> > On 03/05/2026 10:53, Daniil Davydov wrote:
> > > Please, see the attached patch that ensures that cross-session LOCK TABLE works
> > > properly.
> >
> > I guess you should either add it to the 0001 sent by Alexander, or
> > create a 0003. Right now the cfbot is complaining, as
> > 013_temp_obj_multisession.pl still does not exist :)
> >
>
> OK, I'll attach this patch as a third one, just for review purposes. After
> agreement on its content, it should be included into the 0001 and 0002 patches.

Thank you.  I've integrated your check into 0001, and added an
explicit check that the table gets removed once the lock by other
session is removed.

Let me do a quick summary:
 * Our buffer manager is not capable for reading temp tables of other sessions.
 * This was covered by explicit checks, but broken since b7b0f3f27241
introduced alternative code path for reading tables.
 * This doesn't apply to DROP TABLE.  DROP TABLE is a conscious
exclusion and the only operation we can do correctly for other
session' temp tables.  There is an explicit exclusion in the code to
skip the attempt to cleanup buffers of other session' temp tables.
 * This patchset consists of tests (0001) for various operations with
other session's temp tables including buggy behavior, and the fix
(0002) including changes for tests.

Thus, I don't see the reason why this shouldn't be committed and
backpatched to PG17 (first release containing b7b0f3f27241).
Opinions?  Michael?

------
Regards,
Alexander Korotkov
Supabase

  56. Re: Fix bug with accessing to temporary tables of other sessions

    Jim Jones <jim.jones@uni-muenster.de> — 2026-05-07T08:43:01Z

    On 07/05/2026 10:04, Alexander Korotkov wrote:
> Thus, I don't see the reason why this shouldn't be committed and
> backpatched to PG17 (first release containing b7b0f3f27241).

Quick question: should we work on a separate patch for PG14-PG16? In 
these versions, the error message is raised depending on the temporary 
table's tuple count, as demonstrated in [1]:

== session 1 ==

psql (14.20 (Debian 14.20-1.pgdg13+1))
Type "help" for help.

postgres=# CREATE TEMPORARY TABLE t (id int);
CREATE TABLE
postgres=# \d pg_temp*.*
                 Table "pg_temp_3.t"
  Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
  id     | integer |           |          |

== session 2 ==

psql (14.20 (Debian 14.20-1.pgdg13+1))
Type "help" for help.

postgres=# SELECT * FROM pg_temp_3.t;
  id
----
(0 rows)

== session 1 ==

postgres=# INSERT INTO t VALUES (42);
INSERT 0 1

== session 2 ==

postgres=# SELECT * FROM pg_temp_3.t;
ERROR:  cannot access temporary tables of other sessions

Thanks!

Best, Jim

1 - 
https://www.postgresql.org/message-id/800c75af-9bd0-48ac-b4bf-54cadf2bc723%40uni-muenster.de

  57. Re: Fix bug with accessing to temporary tables of other sessions

    Alexander Korotkov <aekorotkov@gmail.com> — 2026-05-07T10:43:40Z

    On Thu, May 7, 2026 at 11:43 AM Jim Jones <jim.jones@uni-muenster.de> wrote:
> On 07/05/2026 10:04, Alexander Korotkov wrote:
> > Thus, I don't see the reason why this shouldn't be committed and
> > backpatched to PG17 (first release containing b7b0f3f27241).
>
> Quick question: should we work on a separate patch for PG14-PG16? In
> these versions, the error message is raised depending on the temporary
> table's tuple count, as demonstrated in [1]:
>
> == session 1 ==
>
> psql (14.20 (Debian 14.20-1.pgdg13+1))
> Type "help" for help.
>
> postgres=# CREATE TEMPORARY TABLE t (id int);
> CREATE TABLE
> postgres=# \d pg_temp*.*
>                  Table "pg_temp_3.t"
>   Column |  Type   | Collation | Nullable | Default
> --------+---------+-----------+----------+---------
>   id     | integer |           |          |
>
> == session 2 ==
>
> psql (14.20 (Debian 14.20-1.pgdg13+1))
> Type "help" for help.
>
> postgres=# SELECT * FROM pg_temp_3.t;
>   id
> ----
> (0 rows)
>
> == session 1 ==
>
> postgres=# INSERT INTO t VALUES (42);
> INSERT 0 1
>
> == session 2 ==
>
> postgres=# SELECT * FROM pg_temp_3.t;
> ERROR:  cannot access temporary tables of other sessions

Thank you for your question.  Yes, PostgreSQL 14-16 first does
RelationGetNumberOfBlocks(), then scans the relation through the
buffer manager.  If RelationGetNumberOfBlocks() returns 0, then no
buffers accessed and no error thrown.  So, it could scan empty tables
because it doesn't requires accessing local buffers of other sessions.
I would investigate this further.  If this doesn't have negative side
effects (like execution of other commands that couldn't be performed
correctly), I would avoid backpatching to these versions.

------
Regards,
Alexander Korotkov
Supabase

  58. Re: Fix bug with accessing to temporary tables of other sessions

    Tom Lane <tgl@sss.pgh.pa.us> — 2026-05-07T14:22:44Z

    Alexander Korotkov <aekorotkov@gmail.com> writes:
> Thus, I don't see the reason why this shouldn't be committed and
> backpatched to PG17 (first release containing b7b0f3f27241).

We are less than 48 hours from code freeze for this month's back
branch releases.  I think it's already too late for any inessential
changes, especially if you're less than 100.00% sure of them.
Please hold off until after the release cycle.

			regards, tom lane

  59. Re: Fix bug with accessing to temporary tables of other sessions

    Alexander Korotkov <aekorotkov@gmail.com> — 2026-05-07T16:13:33Z

    Hi, Tom!

On Thu, May 7, 2026 at 5:22 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Alexander Korotkov <aekorotkov@gmail.com> writes:
> > Thus, I don't see the reason why this shouldn't be committed and
> > backpatched to PG17 (first release containing b7b0f3f27241).
>
> We are less than 48 hours from code freeze for this month's back
> branch releases.  I think it's already too late for any inessential
> changes, especially if you're less than 100.00% sure of them.
> Please hold off until after the release cycle.

Thank you for noticing.  Yes, I'm not 100% sure new tap tests wouldn't
break buildfarm.  It would be pity to break it so close to the release
freeze.  I'll wait.

------
Regards,
Alexander Korotkov
Supabase

  60. Re: Fix bug with accessing to temporary tables of other sessions

    Michael Paquier <michael@paquier.xyz> — 2026-05-08T06:19:32Z

    On Thu, May 07, 2026 at 11:04:16AM +0300, Alexander Korotkov wrote:
> Let me do a quick summary:
>  * Our buffer manager is not capable for reading temp tables of other sessions.
>  * This was covered by explicit checks, but broken since b7b0f3f27241
> introduced alternative code path for reading tables.
>  * This doesn't apply to DROP TABLE.  DROP TABLE is a conscious
> exclusion and the only operation we can do correctly for other
> session' temp tables.  There is an explicit exclusion in the code to
> skip the attempt to cleanup buffers of other session' temp tables.
>  * This patchset consists of tests (0001) for various operations with
> other session's temp tables including buggy behavior, and the fix
> (0002) including changes for tests.
> 
> Thus, I don't see the reason why this shouldn't be committed and
> backpatched to PG17 (first release containing b7b0f3f27241).
> Opinions?  Michael?

Hmm.  I don't have any counter-arguments against a backpatch based on
your argument related to b7b0f3f27241.  Thanks for reorganizing the
patch set so as the tests happen first, and the changes in the code
become second.

If you wish me to look at this patch set in details, I may be able to
do so around the beginning of next week.  I'm not sure that there is a
strong urgency in tackling this issue for this minor release, this
could wait a bit more..
--
Michael

  61. Re: Fix bug with accessing to temporary tables of other sessions

    Alexander Korotkov <aekorotkov@gmail.com> — 2026-05-08T06:39:09Z

    On Fri, May 8, 2026 at 9:19 AM Michael Paquier <michael@paquier.xyz> wrote:
> On Thu, May 07, 2026 at 11:04:16AM +0300, Alexander Korotkov wrote:
> > Let me do a quick summary:
> >  * Our buffer manager is not capable for reading temp tables of other sessions.
> >  * This was covered by explicit checks, but broken since b7b0f3f27241
> > introduced alternative code path for reading tables.
> >  * This doesn't apply to DROP TABLE.  DROP TABLE is a conscious
> > exclusion and the only operation we can do correctly for other
> > session' temp tables.  There is an explicit exclusion in the code to
> > skip the attempt to cleanup buffers of other session' temp tables.
> >  * This patchset consists of tests (0001) for various operations with
> > other session's temp tables including buggy behavior, and the fix
> > (0002) including changes for tests.
> >
> > Thus, I don't see the reason why this shouldn't be committed and
> > backpatched to PG17 (first release containing b7b0f3f27241).
> > Opinions?  Michael?
>
> Hmm.  I don't have any counter-arguments against a backpatch based on
> your argument related to b7b0f3f27241.  Thanks for reorganizing the
> patch set so as the tests happen first, and the changes in the code
> become second.
>
> If you wish me to look at this patch set in details, I may be able to
> do so around the beginning of next week.  I'm not sure that there is a
> strong urgency in tackling this issue for this minor release, this
> could wait a bit more..

Absolutely, no urgency to include it into this minor release as I
already agreed [1].  You're very welcome if you could take a look in
the beginning of the next week.

Links.
1. https://www.postgresql.org/message-id/CAPpHfdsEr6RF-SkzVGD6PzFusENbpNPxKnRY7iQQ%3DcZQYJia6w%40mail.gmail.com

------
Regards,
Alexander Korotkov
Supabase

  62. Re: Fix bug with accessing to temporary tables of other sessions

    Alexander Korotkov <aekorotkov@gmail.com> — 2026-05-14T06:12:58Z

    Hi, Michael!

On Fri, May 8, 2026 at 9:19 AM Michael Paquier <michael@paquier.xyz> wrote:
>
> On Thu, May 07, 2026 at 11:04:16AM +0300, Alexander Korotkov wrote:
> > Let me do a quick summary:
> >  * Our buffer manager is not capable for reading temp tables of other sessions.
> >  * This was covered by explicit checks, but broken since b7b0f3f27241
> > introduced alternative code path for reading tables.
> >  * This doesn't apply to DROP TABLE.  DROP TABLE is a conscious
> > exclusion and the only operation we can do correctly for other
> > session' temp tables.  There is an explicit exclusion in the code to
> > skip the attempt to cleanup buffers of other session' temp tables.
> >  * This patchset consists of tests (0001) for various operations with
> > other session's temp tables including buggy behavior, and the fix
> > (0002) including changes for tests.
> >
> > Thus, I don't see the reason why this shouldn't be committed and
> > backpatched to PG17 (first release containing b7b0f3f27241).
> > Opinions?  Michael?
>
> Hmm.  I don't have any counter-arguments against a backpatch based on
> your argument related to b7b0f3f27241.  Thanks for reorganizing the
> patch set so as the tests happen first, and the changes in the code
> become second.
>
> If you wish me to look at this patch set in details, I may be able to
> do so around the beginning of next week.  I'm not sure that there is a
> strong urgency in tackling this issue for this minor release, this
> could wait a bit more..

Any news from your side?

------
Regards,
Alexander Korotkov
Supabase

  63. Re: Fix bug with accessing to temporary tables of other sessions

    Michael Paquier <michael@paquier.xyz> — 2026-05-14T06:58:18Z

    On Thu, May 14, 2026 at 09:12:58AM +0300, Alexander Korotkov wrote:
> On Fri, May 8, 2026 at 9:19 AM Michael Paquier <michael@paquier.xyz> wrote:
>> If you wish me to look at this patch set in details, I may be able to
>> do so around the beginning of next week.  I'm not sure that there is a
>> strong urgency in tackling this issue for this minor release, this
>> could wait a bit more..
> 
> Any news from your side?

(Forgot -hackers and other folks in CC, sorry about that.)

Unfortunately I have not been able to get back to it this week, and
next week is moot.  Perhaps it is better to not wait for me here, so
feel free to go ahead as you feel. 
--
Michael

  64. Re: Fix bug with accessing to temporary tables of other sessions

    Alexander Korotkov <aekorotkov@gmail.com> — 2026-05-14T13:39:04Z

    On Thu, May 14, 2026 at 9:58 AM Michael Paquier <michael@paquier.xyz> wrote:
> On Thu, May 14, 2026 at 09:12:58AM +0300, Alexander Korotkov wrote:
> > On Fri, May 8, 2026 at 9:19 AM Michael Paquier <michael@paquier.xyz> wrote:
> >> If you wish me to look at this patch set in details, I may be able to
> >> do so around the beginning of next week.  I'm not sure that there is a
> >> strong urgency in tackling this issue for this minor release, this
> >> could wait a bit more..
> >
> > Any news from your side?
>
> (Forgot -hackers and other folks in CC, sorry about that.)
>
> Unfortunately I have not been able to get back to it this week, and
> next week is moot.  Perhaps it is better to not wait for me here, so
> feel free to go ahead as you feel.

Thank you for noticing.  I've pushed this today.  I have to slightly
revise the tests to run on 18 and 17 (different log messages, and
default value of log_lock_waits).

------
Regards,
Alexander Korotkov
Supabase

  65. Re: Fix bug with accessing to temporary tables of other sessions

    Jim Jones <jim.jones@uni-muenster.de> — 2026-05-14T16:48:47Z

    Hi Alexander

On 14/05/2026 15:39, Alexander Korotkov wrote:
> Thank you for noticing.  I've pushed this today.  I have to slightly
> revise the tests to run on 18 and 17 (different log messages, and
> default value of log_lock_waits).

Awesome. Thanks for taking care of it!

Best, Jim

  66. Re: Fix bug with accessing to temporary tables of other sessions

    Daniil Davydov <3danissimo@gmail.com> — 2026-05-14T16:48:48Z

    Hi,

On Thu, May 14, 2026 at 8:39 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
>
> On Thu, May 14, 2026 at 9:58 AM Michael Paquier <michael@paquier.xyz> wrote:
> > On Thu, May 14, 2026 at 09:12:58AM +0300, Alexander Korotkov wrote:
> > > On Fri, May 8, 2026 at 9:19 AM Michael Paquier <michael@paquier.xyz> wrote:
> > >> If you wish me to look at this patch set in details, I may be able to
> > >> do so around the beginning of next week.  I'm not sure that there is a
> > >> strong urgency in tackling this issue for this minor release, this
> > >> could wait a bit more..
> > >
> > > Any news from your side?
> >
> > (Forgot -hackers and other folks in CC, sorry about that.)
> >
> > Unfortunately I have not been able to get back to it this week, and
> > next week is moot.  Perhaps it is better to not wait for me here, so
> > feel free to go ahead as you feel.
>
> Thank you for noticing.  I've pushed this today.  I have to slightly
> revise the tests to run on 18 and 17 (different log messages, and
> default value of log_lock_waits).
>

Thank you very much for your help!)

BTW, we still have another problem with temp tables. Tom wrote [1] about it
within this thread:

> Reality is that we cannot know whether an
> unqualified-name RangeVar references a temp table until we do a
> catalog lookup, so IMO we should not have a relpersistence field there
> at all. At best it means something quite different from what it means
> elsewhere, and that's a recipe for confusion. But changing that would
> not be a bug fix (AFAIK) but refactoring to reduce the probability of
> future bugs.

I'll try to implement that next month. But if someone gets ahead of me, please
attach me in CC of the new thread.

[1] https://www.postgresql.org/message-id/4075754.1774378690%40sss.pgh.pa.us

--
Best regards,
Daniil Davydov

  67. Re: Fix bug with accessing to temporary tables of other sessions

    Alexander Korotkov <aekorotkov@gmail.com> — 2026-06-30T11:46:45Z

    Hi, Daniil!

On Thu, May 14, 2026 at 7:49 PM Daniil Davydov <3danissimo@gmail.com> wrote:
> On Thu, May 14, 2026 at 8:39 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
> >
> > On Thu, May 14, 2026 at 9:58 AM Michael Paquier <michael@paquier.xyz> wrote:
> > > On Thu, May 14, 2026 at 09:12:58AM +0300, Alexander Korotkov wrote:
> > > > On Fri, May 8, 2026 at 9:19 AM Michael Paquier <michael@paquier.xyz> wrote:
> > > >> If you wish me to look at this patch set in details, I may be able to
> > > >> do so around the beginning of next week.  I'm not sure that there is a
> > > >> strong urgency in tackling this issue for this minor release, this
> > > >> could wait a bit more..
> > > >
> > > > Any news from your side?
> > >
> > > (Forgot -hackers and other folks in CC, sorry about that.)
> > >
> > > Unfortunately I have not been able to get back to it this week, and
> > > next week is moot.  Perhaps it is better to not wait for me here, so
> > > feel free to go ahead as you feel.
> >
> > Thank you for noticing.  I've pushed this today.  I have to slightly
> > revise the tests to run on 18 and 17 (different log messages, and
> > default value of log_lock_waits).
> >
>
> Thank you very much for your help!)

You're welcome )

> BTW, we still have another problem with temp tables. Tom wrote [1] about it
> within this thread:
>
> > Reality is that we cannot know whether an
> > unqualified-name RangeVar references a temp table until we do a
> > catalog lookup, so IMO we should not have a relpersistence field there
> > at all. At best it means something quite different from what it means
> > elsewhere, and that's a recipe for confusion. But changing that would
> > not be a bug fix (AFAIK) but refactoring to reduce the probability of
> > future bugs.
>
> I'll try to implement that next month. But if someone gets ahead of me, please
> attach me in CC of the new thread.
>
> [1] https://www.postgresql.org/message-id/4075754.1774378690%40sss.pgh.pa.us

Just a friendly ping on how is it going.

------
Regards,
Alexander Korotkov
Supabase

  68. Re: Fix bug with accessing to temporary tables of other sessions

    Daniil Davydov <3danissimo@gmail.com> — 2026-06-30T12:01:20Z

    Hi,

On Tue, Jun 30, 2026 at 6:46 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
>
> > BTW, we still have another problem with temp tables. Tom wrote [1] about it
> > within this thread:
> >
> > > Reality is that we cannot know whether an
> > > unqualified-name RangeVar references a temp table until we do a
> > > catalog lookup, so IMO we should not have a relpersistence field there
> > > at all. At best it means something quite different from what it means
> > > elsewhere, and that's a recipe for confusion. But changing that would
> > > not be a bug fix (AFAIK) but refactoring to reduce the probability of
> > > future bugs.
> >
> > I'll try to implement that next month. But if someone gets ahead of me, please
> > attach me in CC of the new thread.
>
> Just a friendly ping on how is it going.

Sorry, I was too busy with my studies and completely forgot about it...
I will start working on the patch within this week. Thanks for reminding me.

BTW, recently I found out that we haven't completely gotten rid of the problem
discussed here, so I started a new thread [1]. I think it might interest you.

[1] https://www.postgresql.org/message-id/flat/CAJDiXgiX2XZBHDNo%2BzBbvku%2BtchrUurvPRaN1_40mEQ1_sG90g%40mail.gmail.com

--
Best regards,
Daniil Davydov