Re: question on the information_schema

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux