pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedquestion on the information_schema
12+ messages / 4 participants
[nested] [flat]
* question on the information_schema
@ 2013-08-23 14:49 Salvatore Barone <salvator.barone@gmail.com>
0 siblings, 2 replies; 12+ messages in thread
From: Salvatore Barone @ 2013-08-23 14:49 UTC (permalink / raw)
To: pgsql-admin
Hi all,
I created a database named "test" with the user "postgres" and I have
given all privileges on database "test" to a user that I use daily,
called "ssaa".
By submitting, as user "ssaa", to "test" database the «select * from
information_schema.columns with where col.table_schema! = 'Pg_catalog'
and col.table_schema! = 'Information_schema'» query, instead of the list
of columns, I have an empty table. I have to give some special permit
ssaa user?
How do I resolve?
Thanks.
Ps. I apologize for my poor english.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: question on the information_schema
@ 2013-08-23 14:57 bricklen <bricklen@gmail.com>
parent: Salvatore Barone <salvator.barone@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: bricklen @ 2013-08-23 14:57 UTC (permalink / raw)
To: Salvatore Barone <salvator.barone@gmail.com>; +Cc: pgsql-admin
On Fri, Aug 23, 2013 at 7:49 AM, Salvatore Barone <salvator.barone@gmail.com
> wrote:
> Hi all,
> I created a database named "test" with the user "postgres" and I have
> given all privileges on database "test" to a user that I use daily, called
> "ssaa".
> By submitting, as user "ssaa", to "test" database the «select * from
> information_schema.columns with where col.table_schema! = 'Pg_catalog' and
> col.table_schema! = 'Information_schema'» query, instead of the list of
> columns, I have an empty table. I have to give some special permit ssaa
> user?
>
Your query as you've written it won't return what you expect. Try the
following:
select *
from information_schema.columns
where table_schema not in ('information_schema','pg_catalog')
order by 1,2,3,4;
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: question on the information_schema
@ 2013-08-23 15:02 David Johnston <polobo@yahoo.com>
parent: Salvatore Barone <salvator.barone@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: David Johnston @ 2013-08-23 15:02 UTC (permalink / raw)
To: pgsql-admin
Salvatore Barone wrote
> Hi all,
> I created a database named "test" with the user "postgres" and I have
> given all privileges on database "test" to a user that I use daily,
> called "ssaa".
> By submitting, as user "ssaa", to "test" database the «select * from
> information_schema.columns with where col.table_schema! = 'Pg_catalog'
> and col.table_schema! = 'Information_schema'» query, instead of the list
> of columns, I have an empty table. I have to give some special permit
> ssaa user?
> How do I resolve?
> Thanks.
>
> Ps. I apologize for my poor english.
You have not provided an actual query. Copy/Paste the exact query you are
executing.
Note, however, that a newly created database does not have any "columns", or
tables for that matter, aside from those in information_schema and
pg_catalog. Why do you expect the query to return any results?
More generally it is very helpful to explain "why" you are doing something
and not just show - especially incompletely - what you are doing.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/question-on-the-information-schema-tp5768389p5768391.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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: question on the information_schema
@ 2013-08-23 15:02 Salvatore Barone <salvator.barone@gmail.com>
parent: bricklen <bricklen@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Salvatore Barone @ 2013-08-23 15:02 UTC (permalink / raw)
To: bricklen <bricklen@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: question on the information_schema
@ 2013-08-23 15:04 bricklen <bricklen@gmail.com>
parent: Salvatore Barone <salvator.barone@gmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: bricklen @ 2013-08-23 15:04 UTC (permalink / raw)
To: Salvatore Barone <salvator.barone@gmail.com>; +Cc: pgsql-admin
On Fri, Aug 23, 2013 at 8:02 AM, Salvatore Barone <salvator.barone@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).
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: question on the information_schema
@ 2013-08-23 15:12 Salvatore Barone <salvator.barone@gmail.com>
parent: David Johnston <polobo@yahoo.com>
0 siblings, 1 reply; 12+ messages in thread
From: Salvatore Barone @ 2013-08-23 15:12 UTC (permalink / raw)
To: David Johnston <polobo@yahoo.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: question on the information_schema
@ 2013-08-23 15:25 bricklen <bricklen@gmail.com>
parent: Salvatore Barone <salvator.barone@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: bricklen @ 2013-08-23 15:25 UTC (permalink / raw)
To: Salvatore Barone <salvator.barone@gmail.com>; +Cc: pgsql-admin
On Fri, Aug 23, 2013 at 8:12 AM, Salvatore Barone <salvator.barone@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;
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: question on the information_schema
@ 2013-08-23 15:32 Salvatore Barone <salvator.barone@gmail.com>
parent: bricklen <bricklen@gmail.com>
0 siblings, 3 replies; 12+ messages in thread
From: Salvatore Barone @ 2013-08-23 15:32 UTC (permalink / raw)
To: bricklen <bricklen@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: question on the information_schema
@ 2013-08-23 15:39 raghu ram <raghuchennuru@gmail.com>
parent: Salvatore Barone <salvator.barone@gmail.com>
2 siblings, 1 reply; 12+ messages in thread
From: raghu ram @ 2013-08-23 15:39 UTC (permalink / raw)
To: Salvatore Barone <salvator.barone@gmail.com>; +Cc: bricklen <bricklen@gmail.com>; pgsql-admin
2013/8/23 Salvatore Barone <salvator.barone@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
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: question on the information_schema
@ 2013-08-23 15:39 bricklen <bricklen@gmail.com>
parent: Salvatore Barone <salvator.barone@gmail.com>
2 siblings, 0 replies; 12+ messages in thread
From: bricklen @ 2013-08-23 15:39 UTC (permalink / raw)
To: Salvatore Barone <salvator.barone@gmail.com>; +Cc: pgsql-admin
2013/8/23 Salvatore Barone <salvator.barone@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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: question on the information_schema
@ 2013-08-23 15:53 David Johnston <polobo@yahoo.com>
parent: Salvatore Barone <salvator.barone@gmail.com>
2 siblings, 0 replies; 12+ messages in thread
From: David Johnston @ 2013-08-23 15:53 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: question on the information_schema
@ 2013-08-23 16:04 David Johnston <polobo@yahoo.com>
parent: raghu ram <raghuchennuru@gmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: David Johnston @ 2013-08-23 16:04 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2013-08-23 16:04 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-08-23 14:49 question on the information_schema Salvatore Barone <salvator.barone@gmail.com>
2013-08-23 14:57 ` bricklen <bricklen@gmail.com>
2013-08-23 15:02 ` Salvatore Barone <salvator.barone@gmail.com>
2013-08-23 15:04 ` bricklen <bricklen@gmail.com>
2013-08-23 15:02 ` David Johnston <polobo@yahoo.com>
2013-08-23 15:12 ` Salvatore Barone <salvator.barone@gmail.com>
2013-08-23 15:25 ` bricklen <bricklen@gmail.com>
2013-08-23 15:32 ` Salvatore Barone <salvator.barone@gmail.com>
2013-08-23 15:39 ` raghu ram <raghuchennuru@gmail.com>
2013-08-23 16:04 ` David Johnston <polobo@yahoo.com>
2013-08-23 15:39 ` bricklen <bricklen@gmail.com>
2013-08-23 15:53 ` David Johnston <polobo@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox