= On Fri, Aug 23, 2013 at 7:49 AM, Salvatore Barone <salvator.barone= @gmail.com> wrote:

=20 =20 =20
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 da= ily, called "ssaa". By submitting, as user "ssaa", to "test" da= tabase the =C2=ABselect * from information_schema.columns with where col.table_schema! =3D 'Pg_catalog' and col.table_schema= ! =3D 'Information_schema'=C2=BB 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 wo= n't return what you expect. Try the following:

select *
from information_schema.columns
where table_schema not in ('informat= ion_schema','pg_catalog')

order by 1,2,3,4;

--20cf307d003e70672004e49ea1e2-- From polobo@yahoo.com Fri Aug 23 15:02:40 2013 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VCssq-0002kM-MS for pgsql-admin@arkaria.postgresql.org; Fri, 23 Aug 2013 15:02:40 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1VCssq-0006Zx-5r for pgsql-admin@arkaria.postgresql.org; Fri, 23 Aug 2013 15:02:40 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VCssp-0006Zq-44 for pgsql-admin@postgresql.org; Fri, 23 Aug 2013 15:02:39 +0000 Received: from sam.nabble.com ([216.139.236.26]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VCssg-0002dS-UK for pgsql-admin@postgresql.org; Fri, 23 Aug 2013 15:02:38 +0000 Received: from [192.168.236.26] (helo=sam.nabble.com) by sam.nabble.com with esmtp (Exim 4.72) (envelope-from ) id 1VCssg-0007r4-4y for pgsql-admin@postgresql.org; Fri, 23 Aug 2013 08:02:30 -0700 Date: Fri, 23 Aug 2013 08:02:30 -0700 (PDT) From: David Johnston To: pgsql-admin@postgresql.org Message-ID: <1377270150147-5768391.post@n5.nabble.com> In-Reply-To: <52177664.5030908@gmail.com> References: <52177664.5030908@gmail.com> Subject: Re: question on the information_schema MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Pg-Spam-Score: 1.8 (+) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org 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 =C2=ABselect * from > information_schema.columns with where col.table_schema! =3D 'Pg_catalog' > and col.table_schema! =3D 'Information_schema'=C2=BB query, instead of th= e list > of columns, I have an empty table. I have to give some special permit > ssaa user? > How do I resolve? > Thanks. >=20 > 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/quest= ion-on-the-information-schema-tp5768389p5768391.html Sent from the PostgreSQL - admin mailing list archive at Nabble.com. --=20 Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin From salvator.barone@gmail.com Fri Aug 23 15:03:09 2013 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VCstJ-0002l6-J3 for pgsql-admin@arkaria.postgresql.org; Fri, 23 Aug 2013 15:03:09 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1VCstI-0006ys-Sb for pgsql-admin@arkaria.postgresql.org; Fri, 23 Aug 2013 15:03:08 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VCstH-0006wl-11 for pgsql-admin@postgresql.org; Fri, 23 Aug 2013 15:03:07 +0000 Received: from mail-ee0-x22d.google.com ([2a00:1450:4013:c00::22d]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VCstD-0002eP-PL for pgsql-admin@postgresql.org; Fri, 23 Aug 2013 15:03:06 +0000 Received: by mail-ee0-f45.google.com with SMTP id c50so348730eek.18 for ; Fri, 23 Aug 2013 08:03:03 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=message-id:disposition-notification-to:date:from:user-agent :mime-version:to:cc:subject:references:in-reply-to:content-type; bh=zGrcVgqIxMoV/ENtKt4WV4QlBNqnB23KLqGSK5P2TM8=; b=HkHxhAE7ddnsBVBiPi81KqLOLB7lpDzS2NZu7MOr36Erw0ie9ChAJQCOxS1fh/S5cm TJ5jTBz0Wcsb8/rejGqUF1BqmjPFDbe/PDhpjjRTXToqt3KGncpr9reIeJyWqOM+879B XXE3f98L5eeckrC+LftG58eic8MUZmMbj1sKnotNQBWfBYidNxxEzshlrEQRHpAEXBOT pI6+7qNY33IZRbDYb46ClKIP9Vb6decOPjxA4kms/B3OIrRar6pSfsrrL6+KFIaxblfg EvDd2z5CYWg9dytNMCers6D1PxomsBuf4yn7pZDEwKaiAjvr4kWrW+JZdjW4JThXGW1K r/yg== X-Received: by 10.15.53.73 with SMTP id q49mr123105eew.78.1377270183312; Fri, 23 Aug 2013 08:03:03 -0700 (PDT) Received: from [192.168.1.3] ([151.77.181.153]) by mx.google.com with ESMTPSA id bn13sm270716eeb.11.1969.12.31.16.00.00 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Fri, 23 Aug 2013 08:03:02 -0700 (PDT) Message-ID: <52177997.7020306@gmail.com> Date: Fri, 23 Aug 2013 17:02:47 +0200 From: Salvatore Barone User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:17.0) Gecko/20130704 Icedove/17.0.7 MIME-Version: 1.0 To: bricklen CC: "pgsql-admin@postgresql.org" Subject: Re: question on the information_schema References: <52177664.5030908@gmail.com> In-Reply-To: Content-Type: multipart/alternative; boundary="------------000808000300080204060505" X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org This is a multi-part message in MIME format. --------------000808000300080204060505 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit 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 --------------000808000300080204060505 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit

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

--------------000808000300080204060505-- From bricklen@gmail.com Fri Aug 23 15:05:08 2013 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VCsvE-0002ny-7V for pgsql-admin@arkaria.postgresql.org; Fri, 23 Aug 2013 15:05:08 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1VCsvC-0001IX-Mz for pgsql-admin@arkaria.postgresql.org; Fri, 23 Aug 2013 15:05:06 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VCsut-0000ql-9c for pgsql-admin@postgresql.org; Fri, 23 Aug 2013 15:04:47 +0000 Received: from mail-ve0-x22a.google.com ([2607:f8b0:400c:c01::22a]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VCsum-0005x8-Ar for pgsql-admin@postgresql.org; Fri, 23 Aug 2013 15:04:46 +0000 Received: by mail-ve0-f170.google.com with SMTP id 15so547429vea.29 for ; Fri, 23 Aug 2013 08:04:39 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; bh=j+Bm8lWeCv3BIPMtNhqGRlY6fHg8Y0lFo2iUljSKKJo=; b=IkzHwGwA7wi26M1s5I00sNDHrSsICr4CAy+sRpG+yXjFJO42+OAaGZ+kEApQ9Ze/vY yMNH09uXv67hqIh0zmN2xm4VDvuJf32qYsoTmvCFO2g59Kpc8q5FFtGvIGB1u26ZSUZS CndPUhCInhVYpFBiq+9mCR2PKuyBMIbblcqMraFAWEVhSAOtiLh3TpDzE/d/Z7fuWQhN va6GVo/Jm0e8J275nprFj8fzlBLeplNaydOlGkvGyY765VwNyI5Poz4wgu9x7rqdNQ6H BoWaPoK8YhTC/aGkve0sOAppJ12o9M/ck72araDMZQsXK1uqZDgXBhQhjt3SIk6sUm1l YIfQ== MIME-Version: 1.0 X-Received: by 10.52.170.43 with SMTP id aj11mr19449vdc.91.1377270279573; Fri, 23 Aug 2013 08:04:39 -0700 (PDT) Received: by 10.58.161.113 with HTTP; Fri, 23 Aug 2013 08:04:39 -0700 (PDT) In-Reply-To: <52177997.7020306@gmail.com> References: <52177664.5030908@gmail.com> <52177997.7020306@gmail.com> Date: Fri, 23 Aug 2013 08:04:39 -0700 Message-ID: Subject: Re: question on the information_schema From: bricklen To: Salvatore Barone Cc: "pgsql-admin@postgresql.org" Content-Type: multipart/alternative; boundary=047d7b6dcc2ec6174404e49eb919 X-Pg-Spam-Score: 1.7 (+) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org --047d7b6dcc2ec6174404e49eb919 Content-Type: text/plain; charset=UTF-8 On Fri, Aug 23, 2013 at 8:02 AM, Salvatore Barone 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). --047d7b6dcc2ec6174404e49eb919 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

= On Fri, Aug 23, 2013 at 8:02 AM, Salvatore Barone <salvator.barone= @gmail.com> wrote:

=20 =20 =20

On 23/08/2013 16:57, bricklen wrote:

select *
from information_schema.columns
where table_schema not in ('information_schema','pg_catal= og')

Both queries, whether to send as user "ssaa&= quot;, 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 wh= y you are not seeing anything (no tables to show columns of).

--047d7b6dcc2ec6174404e49eb919-- From salvator.barone@gmail.com Fri Aug 23 15:12:15 2013 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VCt27-00038b-Ov for pgsql-admin@arkaria.postgresql.org; Fri, 23 Aug 2013 15:12:15 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1VCt27-0002Bo-7A for pgsql-admin@arkaria.postgresql.org; Fri, 23 Aug 2013 15:12:15 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VCt26-0002Ah-4x for pgsql-admin@postgresql.org; Fri, 23 Aug 2013 15:12:14 +0000 Received: from mail-we0-x22f.google.com ([2a00:1450:400c:c03::22f]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VCt23-0002rS-Gf for pgsql-admin@postgresql.org; Fri, 23 Aug 2013 15:12:13 +0000 Received: by mail-we0-f175.google.com with SMTP id n5so669758wev.20 for ; Fri, 23 Aug 2013 08:12:11 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=message-id:disposition-notification-to:date:from:user-agent :mime-version:to:cc:subject:references:in-reply-to:content-type; bh=r9WOIr7944PkYi1J2RAaYrWk/4xm1g5jhgK7SBT+ibc=; b=oc4063Xoy7SXtT+3XCOCvkvM/xsK+z3j0Q+8jlHC1hvah1AvNTNo3j9S0N86sR6dTy kyeWfoehrqkD88F8xg3yVeobqekP2wxn+lh9fWriHgajS2HESrMd9QJRm+W8uG5MHHxp earDFU8Z+9zCQL5rZBZOeALUjigyrUbgcHoNWxqe+fd8ul8v3TA8MNzuMJ7HUzu1Xr4H FnaM2am8r+g/JORQx662Nr4miCHjAjmIbqJRSeXU7ci6RrhCLV3yNhy206zRRHNDz7MG YZaCtOPciLjWBwmpjC59b7mAWJfdBsRP4kPNixOnqn4E2rQGXqH0qJnOHf5V0om56Tga KDmw== X-Received: by 10.194.7.234 with SMTP id m10mr141314wja.42.1377270731085; Fri, 23 Aug 2013 08:12:11 -0700 (PDT) Received: from [192.168.1.3] ([151.77.181.153]) by mx.google.com with ESMTPSA id eb3sm4629799wic.10.1969.12.31.16.00.00 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Fri, 23 Aug 2013 08:12:10 -0700 (PDT) Message-ID: <52177BC0.1060007@gmail.com> Date: Fri, 23 Aug 2013 17:12:00 +0200 From: Salvatore Barone User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:17.0) Gecko/20130704 Icedove/17.0.7 MIME-Version: 1.0 To: David Johnston CC: pgsql-admin@postgresql.org Subject: Re: question on the information_schema References: <52177664.5030908@gmail.com> <1377270150147-5768391.post@n5.nabble.com> In-Reply-To: <1377270150147-5768391.post@n5.nabble.com> Content-Type: multipart/alternative; boundary="------------020603090901020308050108" X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org This is a multi-part message in MIME format. --------------020603090901020308050108 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit 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 --------------020603090901020308050108 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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

--------------020603090901020308050108-- From bricklen@gmail.com Fri Aug 23 15:25:57 2013 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VCtFM-0003fw-R7 for pgsql-admin@arkaria.postgresql.org; Fri, 23 Aug 2013 15:25:57 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1VCtFM-0006pv-3O for pgsql-admin@arkaria.postgresql.org; Fri, 23 Aug 2013 15:25:56 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VCtFK-0006po-Pl for pgsql-admin@postgresql.org; Fri, 23 Aug 2013 15:25:54 +0000 Received: from mail-ve0-x234.google.com ([2607:f8b0:400c:c01::234]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VCtFC-0006Qa-Ny for pgsql-admin@postgresql.org; Fri, 23 Aug 2013 15:25:53 +0000 Received: by mail-ve0-f180.google.com with SMTP id pb11so528773veb.25 for ; Fri, 23 Aug 2013 08:25:45 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; bh=/IQzphYVen4QUoCyu5QDlrPu71oxhR8aT0/0Aew9gZw=; b=xfaEQOvZ2fBS9lNf7wDx9GFfhup0F/oa4JmP8Mj0Art536Yp9hmZGhA0y7eaVRrMl/ XRxuaXRWYmUd4Ej5b1mQ4bnq4Sfoa+nuc9lLJv2eFUbaecG8SZf1Qn6eeFprQWCkvWwz NPgNfzznLxKTMuLLqpKXfyskp/v7W/JsYYb811vYXWQCD/zlSJO7VBzxkPofPSndnmW9 5BwiAkLrTjIRMfl8WX5wc8H+thrGGbu1OaRaRWQphlLitB0TDj8KcugA8t4ivDJbkX4/ 4Du2Ez+uPmL7aNyy0WAC5r7lrf4RjQbukbZTUwGPXw5y38tqrT5xsCsk9muZHjBuNBdi K/ew== MIME-Version: 1.0 X-Received: by 10.58.230.135 with SMTP id sy7mr87636vec.42.1377271545891; Fri, 23 Aug 2013 08:25:45 -0700 (PDT) Received: by 10.58.161.113 with HTTP; Fri, 23 Aug 2013 08:25:45 -0700 (PDT) In-Reply-To: <52177BC0.1060007@gmail.com> References: <52177664.5030908@gmail.com> <1377270150147-5768391.post@n5.nabble.com> <52177BC0.1060007@gmail.com> Date: Fri, 23 Aug 2013 08:25:45 -0700 Message-ID: Subject: Re: question on the information_schema From: bricklen To: Salvatore Barone Cc: "pgsql-admin@postgresql.org" Content-Type: multipart/alternative; boundary=047d7bdc87be40942104e49f0563 X-Pg-Spam-Score: 0.9 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org --047d7bdc87be40942104e49f0563 Content-Type: text/plain; charset=UTF-8 On Fri, Aug 23, 2013 at 8:12 AM, Salvatore Barone 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; --047d7bdc87be40942104e49f0563 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

On F= ri, Aug 23, 2013 at 8:12 AM, Salvatore Barone <salvator.barone@gma= il.com> wrote:

=20 =20 =20
I'm not stupid,

No one said y= ou 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 su= ggestion might help someone else having a similar problem in the future.

=C2=A0

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!=3D'pg_catalog'and col.table_schema!=3D'information_schema'

if i sent the query=C2=A0 as "postgres", the result is a tabl= e that contain the the right column list. If I sent the query as "ssaa&qu= ot;, the result is an empty table.
I must to be able to retrieve the list of tables as a user "ssaa&q= uot;.

Logged in as ssaa, can you show the outpu= t of the following (in "psql"):

=C2=A0\dt+

and

show search_path;

--047d7bdc87be40942104e49f0563-- From salvator.barone@gmail.com Fri Aug 23 15:32:48 2013 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VCtM0-0003tG-9x for pgsql-admin@arkaria.postgresql.org; Fri, 23 Aug 2013 15:32:48 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1VCtLz-0000MK-Ks for pgsql-admin@arkaria.postgresql.org; Fri, 23 Aug 2013 15:32:47 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VCtLy-0000Lp-BD for pgsql-admin@postgresql.org; Fri, 23 Aug 2013 15:32:46 +0000 Received: from mail-wi0-x22d.google.com ([2a00:1450:400c:c05::22d]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VCtLr-0006Wb-99 for pgsql-admin@postgresql.org; Fri, 23 Aug 2013 15:32:45 +0000 Received: by mail-wi0-f173.google.com with SMTP id ey16so906107wid.6 for ; Fri, 23 Aug 2013 08:32:37 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=message-id:disposition-notification-to:date:from:user-agent :mime-version:to:cc:subject:references:in-reply-to:content-type; bh=eOpu2b7PsUhFaU17dHQwHD09M4hRDKVClUqqxeaLSNc=; b=of74o5pc1EP+v8cUrj27cQv/Qm/o2Z9pAt9i2MeVu2YHKNe6VvlhiZhHFHN/tU3mqs fTClVhsabZXsH68KLHXY+dVapEl5+Sx8Me4VMIFuLaoIyIcq1RTyTA/5uDI+OLHuD5Jl v9xrAqKmLVgyfo1PD65S46ujhShS/aP3iveDBH0wNO3CUQxGTj92mIyp2UfyQ2EttI97 M715jQQLB7CQ00LbPPTZP/aPQ1sZHVG270v3ML3JKCXURDSeX1H+oCl0u16ZfBuXyStF o2oriffQnSUt3xXUCUoHbMt2BpTDLGMSMdkIZ/wNOOjWKlXxrytJf95tu6bz+ntbd/Dv XfOA== X-Received: by 10.180.189.9 with SMTP id ge9mr133997wic.52.1377271957293; Fri, 23 Aug 2013 08:32:37 -0700 (PDT) Received: from [192.168.1.3] ([151.77.181.153]) by mx.google.com with ESMTPSA id li9sm578907wic.4.1969.12.31.16.00.00 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Fri, 23 Aug 2013 08:32:36 -0700 (PDT) Message-ID: <52178080.7010908@gmail.com> Date: Fri, 23 Aug 2013 17:32:16 +0200 From: Salvatore Barone User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:17.0) Gecko/20130704 Icedove/17.0.7 MIME-Version: 1.0 To: bricklen CC: "pgsql-admin@postgresql.org" Subject: Re: question on the information_schema References: <52177664.5030908@gmail.com> <1377270150147-5768391.post@n5.nabble.com> <52177BC0.1060007@gmail.com> In-Reply-To: Content-Type: multipart/alternative; boundary="------------000500080001080707070305" X-Pg-Spam-Score: 0.7 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org This is a multi-part message in MIME format. --------------000500080001080707070305 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit 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 --------------000500080001080707070305 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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

--------------000500080001080707070305-- From raghuchennuru@gmail.com Fri Aug 23 15:39:33 2013 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VCtSX-00047K-1l for pgsql-admin@arkaria.postgresql.org; Fri, 23 Aug 2013 15:39:33 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1VCtSW-0002OZ-If for pgsql-admin@arkaria.postgresql.org; Fri, 23 Aug 2013 15:39:32 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VCtSU-0002NO-P9 for pgsql-admin@postgresql.org; Fri, 23 Aug 2013 15:39:30 +0000 Received: from mail-oa0-x232.google.com ([2607:f8b0:4003:c02::232]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VCtSR-0003Nw-CE for pgsql-admin@postgresql.org; Fri, 23 Aug 2013 15:39:30 +0000 Received: by mail-oa0-f50.google.com with SMTP id i4so925965oah.23 for ; Fri, 23 Aug 2013 08:39:25 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type; bh=KZv6SXUnaf2CoCwbTe4LhjAPFIGR3aOfIs23HvVCr74=; b=TYgLmIA0xA1rLIwzvFMpeOI9L5rz2L0u40ttsn0WjOgsQn6utHJyP5AuxckbN8wdOg OZP9+cRQhIffhlUzAnUzbvicY7uoIn1hsj8uWN8SZYP9f+sHM+z0Sx72CqTQLh09M2PP tWlRYv/kXC5DCPrTkgcYdLcN6NlUiSnQVadhIgxBApGtaI+blv/PFBD7PrL9bKKIc1Ej SQ+UnC6hiCw8/70qCNajgmZVDwpUP4RTLCwjMGs6BZGd1WMyGd1e4rrYheU+6flvxZdJ XxU0IdkavyHoAbnllwFTYjPh6sMxrgldZjstljAS0XYzNON+MgUm9UnGr149HhliWXmb FQ7w== X-Received: by 10.182.214.98 with SMTP id nz2mr263237obc.37.1377272365323; Fri, 23 Aug 2013 08:39:25 -0700 (PDT) MIME-Version: 1.0 Received: by 10.76.122.65 with HTTP; Fri, 23 Aug 2013 08:39:05 -0700 (PDT) In-Reply-To: <52178080.7010908@gmail.com> References: <52177664.5030908@gmail.com> <1377270150147-5768391.post@n5.nabble.com> <52177BC0.1060007@gmail.com> <52178080.7010908@gmail.com> From: raghu ram Date: Fri, 23 Aug 2013 21:09:05 +0530 Message-ID: Subject: Re: question on the information_schema To: Salvatore Barone Cc: bricklen , "pgsql-admin@postgresql.org" Content-Type: multipart/alternative; boundary=e89a8ff1cefe1818db04e49f369e X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org --e89a8ff1cefe1818db04e49f369e Content-Type: text/plain; charset=ISO-8859-1 2013/8/23 Salvatore Barone > 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 --e89a8ff1cefe1818db04e49f369e Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable

2013/8/23 Salvatore Bar= one <salvator.barone@gmail.com>

=20 =20 =20
This is the output of \d from psql. I logged in as "ssaa"
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Lista delle relazioni=A0Schema |=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Nome=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0 |=A0 Tipo=A0=A0 | Proprietario | Dimensione | Descrizione--------+----------------------------+---------+--------------= +------------+-------------=A0public | acquisti_persona_fisica=A0=A0=A0 | tabella | platinet=A0=A0=A0=A0 | 8192 bytes |=A0public | acquisti_persona_giuridica | tabella | platinet=A0=A0=A0=A0 | 0 bytes=A0=A0=A0 |=A0public | articolo=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0 | tabella | platinet=A0=A0=A0=A0 | 8192 bytes |=A0public | fornitore=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0 | tabella | platinet=A0=A0=A0=A0 | 0 bytes=A0=A0=A0 |=A0public | giacenza=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0 | tabella | platinet=A0=A0=A0=A0 | 0 bytes=A0=A0=A0 |=A0public | persona_fisica=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= | tabella | platinet=A0=A0=A0=A0 | 8192 bytes |=A0public | persona_giuridica=A0=A0=A0=A0=A0=A0=A0=A0=A0 | tab= ella | platinet=A0=A0=A0=A0 | 8192 bytes |(7 righe)

And this is the output of=A0 show search_path;

=A0search_path=A0=A0 ----------------=A0"$user",public(1 riga)

If you are executing the SQL statement with "postgres" user [Sup= eruser],then you will see all information related to all objects without an= y restrictions of the Ownership.

If you are executing the SQL Statements wit= h "saas" user [Non-Superuser],then you will see the table informa= tion as owner of "saas" user.

Example as follows:

craft2402=3D# \c craft2402 raghu

You are now co= nnected to database "craft2402" as user "raghu".

craft2402=3D# = select * from information_schema.columns col

where col.tabl= e_schema!=3D'pg_catalog'and col.table_schema!=3D'information_sc= hema';

e | is_generat= ed | generation_expression | is_updatable=A0

--------------= -+--------------+-----------------------------+---------------------------+= ------------------+------------------------------------------------+-------= ------+-----------------------------+---

--------------= ---------+------------------------+-------------------+--------------------= -----+---------------+--------------------+---------------+----------------= ----+-----------------------+-----------

-----------+--= ------------------+-------------------+------------------+----------------+= ----------------+---------------+-------------+-------------+------------+-= ------------+---------------+-----------

---+----------= --+---------------------+----------------+---------------------+-----------= --+---------------------+----------------+--------------------+------------= ------+------------------+--------------

--+-----------= ---+-----------------------+--------------

=A0 | NEVER=A0= =A0 =A0 =A0 | =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | YES

craft2402=3D&g= t; \du

=A0=A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 List of roles

=A0Role name |= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Attributes =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 | Member of=A0

-----------+--= ----------------------------------------------+-----------

=A0postgres=A0= | Superuser, Create role, Create DB, Replication | {}

=A0raghu =A0 = =A0 | Superuser=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 | {}

=A0saas=A0 =A0= =A0 |=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 | {}

craft2402=3D> \c craft2402 saas

You are now con= nected to database "craft2402" as user "saas".

craft2402=3D> create table test (id int);

CREATE TABLE

craft2402=3D> analyze test;

ANALYZE

craft2402=3D&= gt; select * from information_schema.columns col

where col.table_= schema!=3D'pg_catalog'and col.table_schema!=3D'information_sche= ma';

---------------+--------------+------------+-------------+------------= ------+----------------+-------------+-----------+-------------------------= -+------------------------+-------------------+------------

-------------+---------------+--------------------+---------------+--------= ------------+-----------------------+----------------------+---------------= -----+-------------------+------------------+---------

-------+----------------+---------------+-------------+-------------+-= -----------+----------+---------------+--------------+------------+--------= -------------+----------------+---------------------+------

-------+---------------------+----------------+--------------------+-------= -----------+------------------+----------------+--------------+------------= -----------+--------------

(1 row)

=A0=A0

Thanks & Regards

Raghu Ram