Accessing other session's temp table

Hi hackers,

I was experimenting with temporary tables and noticed many odd things with
them.

Short story: Having appropriate privileges, user can access other session's
temp
tables and it is a complete mess. Let's fix it.

Longer story.
Let's open two sessions for one postgres user. In the first one we will
create
a temporary table:

postgres=# create temp table t(val int);
CREATE TABLE
postgres=# \dt
List of relations
Schema | Name | Type | Owner
-----------+------+-------+----------
pg_temp_0 | t | table | postgres
(1 row)

Now, in the second session we will try to insert couple of rows into this
table:

postgres=# insert into pg_temp_0.t values (1);
INSERT 0 1
postgres=# insert into pg_temp_0.t values (2);
ERROR: cannot access temporary tables of other sessions

Wow! First row insertion succeeded, the second one - failed. But this is
just the
beginning. Now we will switch back to the first session, insert couple of
rows from
there and check, what do we have in the table:

postgres=# insert into pg_temp_0.t values (3);
INSERT 0 1
postgres=# insert into pg_temp_0.t values (4);
INSERT 0 1
postgres=# select * from pg_temp_0.t;
val
-----
3
4
(2 rows)

We only see the values added in this session. The row successfully inserted
from the
second session is absent. Now let's switch to the second session and check
the table there:

postgres=# select * from pg_temp_0.t;
val
-----
1
(1 row)

Wow again! In both sessions we only see the rows this very session inserted.

Looks like very bad behavior to me.

The same situation with DELETE and in fact with all commands handled
in ExecModifyTable. By the way the second session does not have to be
opened by
the same user: it can be some other user, who was given privileges on the
temp
schema and table.

The problem is that temp tables are not intended to be accessed by other
sessions
at all, and most of the work with them is done in local buffers with no
disk syncing.
There are already some checks and restrictions, but these are definitely
not enough.
Attached is a proposed patch fixing the above problems. Maybe relation_open
is not
the prettiest place for such checks, but it's quite obvious, it works and
it covers all
the remaining problems. And the check is very cheap so we should not be
afraid of
doing it often.

What do you think?
--
best regards,
Mikhail A. Gribkov

Mikhail Gribkov <youzhick(at)gmail(dot)com> writes:
> What do you think?

I think this will break cases we don't want to break.

Accessing the metadata of other temp tables is fine, and indeed
necessary for operations like dropping them. It's access to
the table contents that needs to be blocked. I'm surprised
that we don't have sufficient tests at that level.

[ experiments... ] It looks like this did work as expected up
through v15. So somebody broke it fairly recently, perhaps
as a side effect of the table-AM work. Might be worth bisecting
to see where it broke.

(Realistically though, this is all only a problem for superusers,
who are supposed to Know Better. For ordinary users the permissions
set on temp schemas ought to be enough to prevent such things.)

regards, tom lane

On Wed, Nov 20, 2024 at 5:47 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Mikhail Gribkov <youzhick(at)gmail(dot)com> writes:
> > What do you think?
>
> I think this will break cases we don't want to break.
>
> Accessing the metadata of other temp tables is fine, and indeed
> necessary for operations like dropping them. It's access to
> the table contents that needs to be blocked. I'm surprised
> that we don't have sufficient tests at that level.
>
> [ experiments... ] It looks like this did work as expected up
> through v15. So somebody broke it fairly recently, perhaps
> as a side effect of the table-AM work. Might be worth bisecting
> to see where it broke.

Yeah, this is really odd. I don't understand why the first insert
didn't immediately fail, right here, in ReadBufferExtended:

/*
* Reject attempts to read non-local temporary relations; we would be
* likely to get wrong data since we have no visibility into the owning
* session's local buffers.
*/
if (RELATION_IS_OTHER_TEMP(reln))
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot access temporary tables of other sessions")));

--
Robert Haas
EDB: http://www.enterprisedb.com

> On 20 Nov 2024, at 23:35, Mikhail Gribkov <youzhick(at)gmail(dot)com> wrote:
>
> Now, in the second session we will try to insert couple of rows into this table:

Recently I saw a report and proposed fix here [0]. I did not dig into, just connection internets. Thanks!

Best regards, Andrey Borodin.

[0] https://www.postgresql.org/message-id/flat/CAJDiXgj6TBzn%3D6Ezx7%2B9BNa9HpBitBU%2BMuv-N3mHeN_Zs3NBDw%40mail.gmail.com#1d10becdc71994655653b44dc709e4e5

> Recently I saw a report and proposed fix here [0]. I did not dig into,
just connection internets. Thanks!

Oh, I missed it. Then I'll consider my issue closed and continue discussion
in the older thread if there is something to discuss.
Thanks a lot!

--
best regards,
Mikhail A. Gribkov

e-mail: youzhick(at)gmail(dot)com
*http://www.flickr.com/photos/youzhick/albums
<http://www.flickr.com/photos/youzhick/albums>*
http://www.strava.com/athletes/5085772
phone: +7(916)604-71-12
Telegram: @youzhick