strange behaviour by system tables

[nnani]

Hello All,

I observed a strange behaviour by the system tables.

I have a database "A"
schema "t_schema"
users - a1,a2,a3

I expect the v_catalog.tables should show me all the tables in the database "A" irrespective of the owner they are created from

Suppose user a1 creates a table , he can see the table in the v_catalog.tables, but user a2 cannot see the table in v_catalog.tables.
Suppose user a2 creates a table, he cannot see the table in v_catalog.tables niether can a1 see the table created by user a2 in the v_catalog.tables.
User a1 can see tables created by x,y,z owners in the v_catalog.tables
When granted access to user a2 by user a1, a2 can access the table in the select clause.
Can anybody explain this behaviour. please

[JimKnicely]

Hi,

I believe this is expected behavior. Users can only see tables in the system table TABLES in which they own or have been granted access privileges. A super user (those with dbadmin role) can see all tables all the time.

It works the same in Oracle...

[nnani]

Hello knicely,

I thought the same, and tried giving access to user a2 for a table created by user a1

Code: Select all

Grant all on table_name to a2;

Doing this, user a2 was able to query table created by user a1 but still he couldn't see the table in his system_tables v_catalog.tables
When user a1 revoked the permissions from a2, user a2 was not able to query the table created by user a1 again.

So, even if user a2 had access privileges on table created by user a1, still he couldn't find it in the system tables.
Moreover, user a1 can see table created by some other users in the system tables v_catalog.tables

[JimKnicely]

In the following example, notice how the user JANE cannot see the table named JIM_SCHEMA.JIM_TABLE in the V_CATALOG.TABLES system table until she is granted a privilege on JIM_SCHEMA.JIM_TABLE...

Code: Select all

dbadmin=> create schema jim_schema;
CREATE SCHEMA
dbadmin=> create user jim;
CREATE USER
dbadmin=> grant all on schema jim_schema to jim;
GRANT PRIVILEGE
dbadmin=> create user jane;
CREATE USER
dbadmin=> grant usage on schema jim_schema to jane;
GRANT PRIVILEGE
dbadmin=> \c dbadmin jim;
You are now connected to database "dbadmin" as user "jim".
dbadmin=> create table jim_schema.jim_table (c1 int);
CREATE TABLE
dbadmin=> insert into jim_schema.jim_table values (1);
 OUTPUT
--------
      1
(1 row)

dbadmin=> commit;
COMMIT
dbadmin=> select table_name from v_catalog.tables;
 table_name
------------
 jim_table
(1 row)

dbadmin=> \c dbadmin jane;
You are now connected to database "dbadmin" as user "jane".
dbadmin=> select table_name from v_catalog.tables;
 table_name
------------
(0 rows)

dbadmin=> select * from jim_schema.jim_table;
ERROR 4367:  Permission denied for relation jim_table
dbadmin=> \c dbadmin jim;
You are now connected to database "dbadmin" as user "jim".
dbadmin=> grant select on jim_schema.jim_table to jane;
GRANT PRIVILEGE
dbadmin=> \c dbadmin jane;
You are now connected to database "dbadmin" as user "jane".
dbadmin=> select table_name from v_catalog.tables;
 table_name
------------
 jim_table
(1 row)

dbadmin=> select * from jim_schema.jim_table;
 c1
----
  1
(1 row)

And the same goes if user JANE creates a table. User JIM can only see the table JANE creates in the V_CATALOG.TABLES system table if he has been given a privilege on JANE's table...

Code: Select all

dbadmin=> \c dbadmin dbadmin
You are now connected to database "dbadmin" as user "dbadmin".
dbadmin=> grant all on schema jim_schema to jane;
GRANT PRIVILEGE
dbadmin=> \c dbadmin jane
You are now connected to database "dbadmin" as user "jane".
dbadmin=> create table jim_schema.jane_table (c1 int);
CREATE TABLE
dbadmin=> insert into jim_schema.jane_table values (1);
 OUTPUT
--------
      1
(1 row)

dbadmin=> commit;
COMMIT
dbadmin=> select table_name from v_catalog.tables;
 table_name
------------
 jim_table
 jane_table
(2 rows)

dbadmin=> \c dbadmin jim;
You are now connected to database "dbadmin" as user "jim".
dbadmin=> select table_name from v_catalog.tables;
 table_name
------------
 jim_table
(1 row)

dbadmin=> select * from jim_schema.jane_table;
ERROR 4367:  Permission denied for relation jane_table

dbadmin=> \c dbadmin jane
You are now connected to database "dbadmin" as user "jane".
dbadmin=> grant delete on jim_schema.jane_table to jim;
GRANT PRIVILEGE
dbadmin=> \c dbadmin jim
You are now connected to database "dbadmin" as user "jim".
dbadmin=> select table_name from v_catalog.tables;
 table_name
------------
 jim_table
 jane_table
(2 rows)

dbadmin=> select * from jim_schema.jane_table;
ERROR 4367:  Permission denied for relation jane_table
dbadmin=> delete from jim_schema.jane_table;
 OUTPUT
--------
      1
(1 row)

[nnani]

Hello knicely,

Thanks for a detailed explaination on this issue.

I tried this with two different users and this works very good.

But the problem still exists with the users a1 and a2.
I checked their privileges and they are identical. Still when user a1 grants user a2 'select' access to a table, The user a2 can only \d+ table and cannot see the table in the v_catalog.tables.

[JimKnicely]

Sorry, but I cannot reproduce your problem

I tried to again, but my database us acting as expected:

Code: Select all

dbadmin=> create user a1;
CREATE USER

dbadmin=> create user a2;
CREATE USER

dbadmin=> create schema t_schema;
CREATE SCHEMA

dbadmin=> grant all on schema t_schema to a1;
GRANT PRIVILEGE

dbadmin=> grant usage on schema t_schema to a2;
GRANT PRIVILEGE

dbadmin=> \c dbadmin a1;
You are now connected to database "dbadmin" as user "a1".

dbadmin=> create table t_schema.a1 (c1 int);
CREATE TABLE

dbadmin=> insert into t_schema.a1 values (1);
 OUTPUT 
--------
      1
(1 row)

dbadmin=> commit;
COMMIT

dbadmin=> \c dbadmin a2;
You are now connected to database "dbadmin" as user "a2".

dbadmin=> select * from t_schema.a1;
ERROR 4367:  Permission denied for relation a1

dbadmin=> \d t_schema.a1;
Did not find any relation.

dbadmin=> select * from v_catalog.tables where table_name = 'a1';
 table_schema_id | table_schema | table_id | table_name | owner_id | owner_name | is_temp_table | is_system_table | system_table_creator | partition_expression | create_time | table_definition 
-----------------+--------------+----------+------------+----------+------------+---------------+-----------------+----------------------+----------------------+-------------+------------------
(0 rows)

dbadmin=> \c dbadmin a1
You are now connected to database "dbadmin" as user "a1".

dbadmin=> grant select on t_schema.a1 to a2;
GRANT PRIVILEGE

dbadmin=> \c dbadmin a2;
You are now connected to database "dbadmin" as user "a2".

dbadmin=> select * from t_schema.a1;
 c1 
----
  1
(1 row)

dbadmin=> \d t_schema.a1;
                                 List of Fields by Tables
  Schema  | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key 
----------+-------+--------+------+------+---------+----------+-------------+-------------
 t_schema | a1    | c1     | int  |    8 |         | f        | f           | 
(1 row)

dbadmin=> \x
Expanded display is on.

dbadmin=> select * from v_catalog.tables where table_name = 'a1';
-[ RECORD 1 ]--------+------------------------------
table_schema_id      | 45035996273892722
table_schema         | t_schema
table_id             | 45035996273892728
table_name           | a1
owner_id             | 45035996273892714
owner_name           | a1
is_temp_table        | f
is_system_table      | f
system_table_creator | 
partition_expression | 
create_time          | 2013-07-09 11:13:34.347909+00
table_definition     | 

dbadmin=> select * from grants where object_name = 'a1';
-[ RECORD 1 ]----------+------------------
grant_id               | 45035996273892858
grantor_id             | 45035996273892714
grantor                | a1
privileges_description | SELECT
object_schema          | t_schema
object_name            | a1
object_id              | 45035996273892728
object_type            | TABLE
grantee_id             | 45035996273892718
grantee                | a2

dbadmin=> \c dbadmin dbadmin
You are now connected to database "dbadmin" as user "dbadmin".

dbadmin=> select * from grants where object_name = 'a1';
-[ RECORD 1 ]----------+------------------------------------------------
grant_id               | 45035996273892856
grantor_id             | 45035996273892714
grantor                | a1
privileges_description | INSERT*, SELECT*, UPDATE*, DELETE*, REFERENCES*
object_schema          | t_schema
object_name            | a1
object_id              | 45035996273892728
object_type            | TABLE
grantee_id             | 45035996273892714
grantee                | a1
-[ RECORD 2 ]----------+------------------------------------------------
grant_id               | 45035996273892858
grantor_id             | 45035996273892714
grantor                | a1
privileges_description | SELECT
object_schema          | t_schema
object_name            | a1
object_id              | 45035996273892728
object_type            | TABLE
grantee_id             | 45035996273892718
grantee                | a2