Обсуждение: 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