has_table/schema_privilige() returns incorrect info on temp tables
Hi,
While running 2 sessions in different terminals for the same user, what
happens in the second session here looks a bit weird:
=========
<session 1>
=========
db=# create temp table t_test (id int);
CREATE TABLE
db=# select table_schema from information_schema.tables
where table_name = 't_test';
table_schema
--------------
pg_temp_2
(1 row)
db=# select has_schema_privilege('pg_temp_2', 'usage');
has_schema_privilege
----------------------
t
(1 row)
db=# select has_table_privilege('pg_temp_2.t_test', 'insert');
has_table_privilege
---------------------
t
(1 row)
db=# select has_table_privilege('t_test', 'insert');
has_table_privilege
---------------------
t
(1 row)
db=# insert into t_test values (1);
INSERT 0 1
=========
<session 2>
=========
db=# select table_schema from information_schema.tables
where table_name = 't_test';
table_schema
--------------
pg_temp_2
(1 row)
db=# select has_schema_privilege('pg_temp_2', 'usage');
has_schema_privilege
----------------------
t
(1 row)
db=# select has_table_privilege('pg_temp_2.t_test', 'insert');
has_table_privilege
---------------------
t
(1 row)
db=# select has_table_privilege('t_test', 'insert');
ERROR: relation "t_test" does not exist
db=# insert into t_test values (1);
ERROR: relation "t_test" does not exist
db=# select version();
version
------------------------------------------------------------------------
PostgreSQL 8.1.3 on i586-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
(1 row)
In this light, is there a possibility to find out what schema will be used for
temporary tables created during the current session? I need to find out
whether a specific temporary table exists and is accessible for the current
user in the current session.
--
Best,
Frank.
Frank van Vugt <ftm.van.vugt@foxi.nl> writes:
While running 2 sessions in different terminals for the same user, what
happens in the second session here looks a bit weird:
In the first place, you are evidently running as superuser, which means
that has_foo_privilege will ALWAYS say 't' (except possibly if the
target object doesn't exist, in which case I think you get an error).
In the second place, trying to access another session's temp table is
unsupported.
In this light, is there a possibility to find out what schema will be used for
temporary tables created during the current session?
After you've created at least one temp table, you can look at the result
of "current_schemas(true)". There's no guarantee that the schema even
exists before you've created something...
regression=# select current_schemas(true);
current_schemas
---------------------
{pg_catalog,public}
(1 row)
regression=# create temp table t(f1 int);
CREATE TABLE
regression=# select current_schemas(true);
current_schemas
-------------------------------
{pg_temp_1,pg_catalog,public}
(1 row)
regression=# select (current_schemas(true))[1];
current_schemas
-----------------
pg_temp_1
(1 row)
regression=#
regards, tom lane
Hi,
In the first place, you are evidently running as superuser, which means
that has_foo_privilege will ALWAYS say 't'
Ok, seems reasonable ;)
(except possibly if the
target object doesn't exist, in which case I think you get an error).
Yep, one does.
In the second place, trying to access another session's temp table is
unsupported.
I understand, it's more the opposite, I was fixing a bug in a plpgsql function
that would fail when the user has created a certain (temporary) table in a
second session, because the code only checked the existence of the table_name
without taking into account the proper schema.
After you've created at least one temp table, you can look at the result
of "current_schemas(true)". There's no guarantee that the schema even
exists before you've created something...
Got that, looks like an acceptable workaround in this case, though.
Is there a guaranteed order of the resulting array, i.e. is this guaranteed to
return the temp schema, given there is one:
'select (current_schemas(true))[1]'.....?
(obviously, regexp's will also do the trick, I'm just asking)
--
Best,
Frank.
Frank van Vugt <ftm.van.vugt@foxi.nl> writes:
After you've created at least one temp table, you can look at the result
of "current_schemas(true)". There's no guarantee that the schema even
exists before you've created something...
Is there a guaranteed order of the resulting array, i.e. is this guaranteed to
return the temp schema, given there is one:
'select (current_schemas(true))[1]'.....?
Yes, in the current implementation and for the foreseeable future (else
temp tables would fail to mask permanent tables).
regards, tom lane