Re: [ADMIN] question on the information_schema
On 23/08/2013 16:57, bricklen wrote:
select *
from information_schema.columns
where table_schema not in ('information_schema','pg_catalog')
Both queries, whether to send as user ssaa, ie the user that I use
daily, return an empty table. If sending as user postgres instead,
they return the correct list of columns.
--
Saluti,
Salvatore Barone
Re: [ADMIN] question on the information_schema
On Fri, Aug 23, 2013 at 8:02 AM, Salvatore Barone salvator.bar...@gmail.com
wrote:
On 23/08/2013 16:57, bricklen wrote:
select *
from information_schema.columns
where table_schema not in ('information_schema','pg_catalog')
Both queries, whether to send as user ssaa, ie the user that I use
daily, return an empty table. If sending as user postgres instead, they
return the correct list of columns.
See David Johnston's response, I think that might be the reason why you are
not seeing anything (no tables to show columns of).
Re: [ADMIN] question on the information_schema
I'm not stupid, I added some columns before sending the query. I'm trying to write a query that returns the name and other information about the columns of a database. the query that is sent is the following select * from information_schema.columns col where col.table_schema!='pg_catalog'and col.table_schema!='information_schema' if i sent the query as postgres, the result is a table that contain the the right column list. If I sent the query as ssaa, the result is an empty table. I must to be able to retrieve the list of tables as a user ssaa. -- Saluti, Salvatore Barone
Re: [ADMIN] question on the information_schema
On Fri, Aug 23, 2013 at 8:12 AM, Salvatore Barone salvator.bar...@gmail.com wrote: I'm not stupid, No one said you were. These lists see people of a wide range of skillsets and expertise. In this case it might not be the answer for your issue, but David's suggestion might help someone else having a similar problem in the future. I added some columns before sending the query. I'm trying to write a query that returns the name and other information about the columns of a database. the query that is sent is the following select * from information_schema.columns col where col.table_schema!='pg_catalog'and col.table_schema!='information_schema' if i sent the query as postgres, the result is a table that contain the the right column list. If I sent the query as ssaa, the result is an empty table. I must to be able to retrieve the list of tables as a user ssaa. Logged in as ssaa, can you show the output of the following (in psql): \dt+ and show search_path;
Re: [ADMIN] question on the information_schema
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) -- Saluti, Salvatore Barone
Re: [ADMIN] question on the information_schema
2013/8/23 Salvatore Barone salvator.bar...@gmail.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) I'm stumped. I am unable to reproduce the lack of output from your query when logged in as a non-superuser, and I can't think of a reason why you are seeing no results. Hopefully someone else can chime in with more suggestions or an answer.
Re: [ADMIN] question on the information_schema
2013/8/23 Salvatore Barone salvator.bar...@gmail.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
Re: [ADMIN] question on the information_schema
Salvatore Barone wrote 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) One thing to try is after you run your query as the postgres user issue a SET ROLE ssaa; command to immediately pretend you are the ssaa user and then execute the query again. Also, as ssaa (both via SET ROLE and as a separate login), try issuing a simple SELECT * FROM table for whatever table(s) you expect to see results for. One common problem that occurs in situations like this is that user postgres and user ssaa are not connecting to the same database. The SET ROLE will help diagnose that problem though there are other ways as well. Likely the issue is either a mis-connect as mentioned above or it has to do with table permissions; the information_schema query is not likely to be broken. The problem, for me in particular, is that I do not know what questions to ask to get you to adequately show the current permissions in your setup. Assuming it is not the first problem then you, with the help of others probably, will need to be much more verbose in showing what specific setups are in place (GRANTs, REVOKEs, GROUP/ROLE, etc...) for the database in question. It is a worthwhile exercise to use a newly created test database and a newly created user to try and get a minimal example working (even/especially if incorrectly) and then supply the entirety of that example so we can review exactly what you are doing. If you can get the example to work then the system is functioning and it is only your specific configuration that is broken - which again means we need much more specific information. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/question-on-the-information-schema-tp5768389p5768403.html Sent from the PostgreSQL - admin mailing list archive at Nabble.com. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] question on the information_schema
raghu ram-4 wrote 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. Per the documentation: http://www.postgresql.org/docs/9.0/static/infoschema-columns.html Only those columns are shown that the current user has access to (by way of being the owner or having some privilege). So the restriction is NOT based on ownership but visibility and any tables created in the public schema are (by default) visible to all users and so should show up in a information_schema.column query for all users as well. I toss this out there for now but I have not been doing any kind of testing for this thread so I'm just reporting my existing understanding and what is shown in the documentation without verification. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/question-on-the-information-schema-tp5768389p5768404.html Sent from the PostgreSQL - admin mailing list archive at Nabble.com. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
