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
001-patch-v01-Fix-other-sessions-temp-tables-access.patch
Description: Binary data
