Re: question on the information_schema

[raghu ram <raghuchennuru@xxxxxxxxx>]

2013/8/23 Salvatore Barone <salvator.barone@xxxxxxxxx>

This is the output of \d from psql. I logged in as "ssaa"

                                  Lista delle relazioni
 Schema |            Nome            |  Tipo   | Proprietario | Dimensione | Descrizione
--------+----------------------------+---------+--------------+------------+-------------
 public | acquisti_persona_fisica    | tabella | platinet     | 8192 bytes |
 public | acquisti_persona_giuridica | tabella | platinet     | 0 bytes    |
 public | articolo                   | tabella | platinet     | 8192 bytes |
 public | fornitore                  | tabella | platinet     | 0 bytes    |
 public | giacenza                   | tabella | platinet     | 0 bytes    |
 public | persona_fisica             | tabella | platinet     | 8192 bytes |
 public | persona_giuridica          | tabella | platinet     | 8192 bytes |
(7 righe)

And this is the output of  show search_path;

 search_path  
----------------
 "$user",public
(1 riga)

If you are executing the SQL statement with "postgres" user [Superuser],then you will see all information related to all objects without any restrictions of the Ownership.

If you are executing the SQL Statements with "saas" user [Non-Superuser],then you will see the table information as owner of "saas" user.

Example as follows:

craft2402=# \c craft2402 raghu

You are now connected to database "craft2402" as user "raghu".

craft2402=# select * from information_schema.columns col

where col.table_schema!='pg_catalog'and col.table_schema!='information_schema';

 table_catalog | table_schema |         table_name          |        column_name        | ordinal_position |                 column_default                 | is_nullable |          data_type          | ch

aracter_maximum_length | character_octet_length | numeric_precision | numeric_precision_radix | numeric_scale | datetime_precision | interval_type | interval_precision | character_set_catalog | character_

set_schema | character_set_name | collation_catalog | collation_schema | collation_name | domain_catalog | domain_schema | domain_name | udt_catalog | udt_schema |  udt_name   | scope_catalog | scope_sche

ma | scope_name | maximum_cardinality | dtd_identifier | is_self_referencing | is_identity | identity_generation | identity_start | identity_increment | identity_maximum | identity_minimum | identity_cycl

e | is_generated | generation_expression | is_updatable 

---------------+--------------+-----------------------------+---------------------------+------------------+------------------------------------------------+-------------+-----------------------------+---

-----------------------+------------------------+-------------------+-------------------------+---------------+--------------------+---------------+--------------------+-----------------------+-----------

-----------+--------------------+-------------------+------------------+----------------+----------------+---------------+-------------+-------------+------------+-------------+---------------+-----------

---+------------+---------------------+----------------+---------------------+-------------+---------------------+----------------+--------------------+------------------+------------------+--------------

--+--------------+-----------------------+--------------

 craft2402     | public       | agency_for_registration     | state_code                |                1 |                                                | NO          | character varying           |   

                     2 |                      8 |                   |                         |               |                    |               |                    |                       |           

           |                    |                   |                  |                |                |               |             | craft2402   | pg_catalog | varchar     |               |           

   |            |                     | 1              | NO                  | NO          |                     |                |                    |                  |                  |              

  | NEVER        |                       | YES

craft2402=> \du

                             List of roles

 Role name |                   Attributes                   | Member of 

-----------+------------------------------------------------+-----------

 postgres  | Superuser, Create role, Create DB, Replication | {}

 raghu     | Superuser                                      | {}

 saas      |                                                | {}

craft2402=> \c craft2402 saas

You are now connected to database "craft2402" as user "saas".

craft2402=> create table test (id int);

CREATE TABLE

craft2402=> analyze test;

ANALYZE

craft2402=> select * from information_schema.columns col

where col.table_schema!='pg_catalog'and col.table_schema!='information_schema';

 table_catalog | table_schema | table_name | column_name | ordinal_position | column_default | is_nullable | data_type | character_maximum_length | character_octet_length | numeric_precision | numeric_pre

cision_radix | numeric_scale | datetime_precision | interval_type | interval_precision | character_set_catalog | character_set_schema | character_set_name | collation_catalog | collation_schema | collatio

n_name | domain_catalog | domain_schema | domain_name | udt_catalog | udt_schema | udt_name | scope_catalog | scope_schema | scope_name | maximum_cardinality | dtd_identifier | is_self_referencing | is_id

entity | identity_generation | identity_start | identity_increment | identity_maximum | identity_minimum | identity_cycle | is_generated | generation_expression | is_updatable 

---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+------------

-------------+---------------+--------------------+---------------+--------------------+-----------------------+----------------------+--------------------+-------------------+------------------+---------

-------+----------------+---------------+-------------+-------------+------------+----------+---------------+--------------+------------+---------------------+----------------+---------------------+------

-------+---------------------+----------------+--------------------+------------------+------------------+----------------+--------------+-----------------------+--------------

 craft2402     | public       | test       | id          |                1 |                | YES         | integer   |                          |                        |                32 |            

           2 |             0 |                    |               |                    |                       |                      |                    |                   |                  |         

       |                |               |             | craft2402   | pg_catalog | int4     |               |              |            |                     | 1              | NO                  | NO   

       |                     |                |                    |                  |                  |                | NEVER        |                       | YES

(1 row)

  

Thanks & Regards

Raghu Ram