Re: question on the information_schema

2013/8/23 Salvatore Barone <salvator(dot)barone(at)gmail(dot)com>

> 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