BUG #1937: Parts of information_schema only accessible to owner

The following bug has been logged online:

Bug reference: 1937
Logged by: Tony Marston
Email address: tony(at)marston-home(dot)demon(dot)co(dot)uk
PostgreSQL version: 8.0.3
Operating system: Windows XP
Description: Parts of information_schema only accessible to owner
Details:

I have been trying to access parts of the information_schema as an ordinary
user, not as owner, and I am encountering instances where I cannot retrieve
any rows at all, or where some of the columns are empty when they should not
be.

This sounds like a faulty implementation to me, with too many restrictions.
As far as I am concerned if I have access privileges on an object then I
should be able to see ALL information_schema details regarding that object.
Being able to view data in the information_schema does not give me the
ability to do anything that is not already permitted in the privilges
system, so why is access to such data being blocked?

As an example, in the view "information_schema.columns" I can only see the
entry in COLUMN_DEFAULT if I am the owner. Why is this? What is the logic
behind this decision? What possible security breach is blocked by witholding
this information?

Am Dienstag, 4. Oktober 2005 14:58 schrieb Tony Marston:
> As an example, in the view "information_schema.columns" I can only see the
> entry in COLUMN_DEFAULT if I am the owner. Why is this?

Because the SQL standard says so.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

On Tue, 4 Oct 2005, Tony Marston wrote:

> Description: Parts of information_schema only accessible to owner
> Details:
>
> I have been trying to access parts of the information_schema as an ordinary
> user, not as owner, and I am encountering instances where I cannot retrieve
> any rows at all, or where some of the columns are empty when they should not
> be.
>
> This sounds like a faulty implementation to me, with too many restrictions.
> As far as I am concerned if I have access privileges on an object then I
> should be able to see ALL information_schema details regarding that object.

Complain to the SQL committee then. ;) Many portions of information_schema
are defined in the spec with limitations based on the user.

> As an example, in the view "information_schema.columns" I can only see the
> entry in COLUMN_DEFAULT if I am the owner. Why is this? What is the logic
> behind this decision?

Because of this piece of the definition:

CASE WHEN EXISTS ( SELECT *
FROM DEFINITION_SCHEMA.SCHEMATA AS S
WHERE ( TABLE_CATALOG, TABLE_SCHEMA )
= (S.CATALOG_NAME, S.SCHEMA_NAME )
AND SCHEMA_OWNER = USER )
THEN COLUMN_DEFAULT
ELSE NULL
END AS COLUMN_DEFAULT,

It looks like we're using table owner rather than schema owner since we
allow mixed ownership of contents of a schema, but the general principle
is the same.

You might want to use http://pgfoundry.org/projects/newsysviews/
instead. We've also talked about changing our naming to come more
in-line with information schema.

On Tue, Oct 04, 2005 at 01:58:36PM +0100, Tony Marston wrote:
>
> The following bug has been logged online:
>
> Bug reference: 1937
> Logged by: Tony Marston
> Email address: tony(at)marston-home(dot)demon(dot)co(dot)uk
> PostgreSQL version: 8.0.3
> Operating system: Windows XP
> Description: Parts of information_schema only accessible to owner
> Details:
>
> I have been trying to access parts of the information_schema as an ordinary
> user, not as owner, and I am encountering instances where I cannot retrieve
> any rows at all, or where some of the columns are empty when they should not
> be.
>
> This sounds like a faulty implementation to me, with too many restrictions.
> As far as I am concerned if I have access privileges on an object then I
> should be able to see ALL information_schema details regarding that object.
> Being able to view data in the information_schema does not give me the
> ability to do anything that is not already permitted in the privilges
> system, so why is access to such data being blocked?
>
> As an example, in the view "information_schema.columns" I can only see the
> entry in COLUMN_DEFAULT if I am the owner. Why is this? What is the logic
> behind this decision? What possible security breach is blocked by witholding
> this information?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Is this something we need to patch?

---------------------------------------------------------------------------

Stephan Szabo wrote:
> On Tue, 4 Oct 2005, Tony Marston wrote:
>
> > Description: Parts of information_schema only accessible to owner
> > Details:
> >
> > I have been trying to access parts of the information_schema as an ordinary
> > user, not as owner, and I am encountering instances where I cannot retrieve
> > any rows at all, or where some of the columns are empty when they should not
> > be.
> >
> > This sounds like a faulty implementation to me, with too many restrictions.
> > As far as I am concerned if I have access privileges on an object then I
> > should be able to see ALL information_schema details regarding that object.
>
> Complain to the SQL committee then. ;) Many portions of information_schema
> are defined in the spec with limitations based on the user.
>
> > As an example, in the view "information_schema.columns" I can only see the
> > entry in COLUMN_DEFAULT if I am the owner. Why is this? What is the logic
> > behind this decision?
>
> Because of this piece of the definition:
>
> CASE WHEN EXISTS ( SELECT *
> FROM DEFINITION_SCHEMA.SCHEMATA AS S
> WHERE ( TABLE_CATALOG, TABLE_SCHEMA )
> = (S.CATALOG_NAME, S.SCHEMA_NAME )
> AND SCHEMA_OWNER = USER )
> THEN COLUMN_DEFAULT
> ELSE NULL
> END AS COLUMN_DEFAULT,
>
> It looks like we're using table owner rather than schema owner since we
> allow mixed ownership of contents of a schema, but the general principle
> is the same.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:
>
> Is this something we need to patch?

As soon as we get 50% votes on the SQL committee ...

--
Alvaro Herrera Architect, http://www.EnterpriseDB.com
"No necesitamos banderas
No reconocemos fronteras" (Jorge González)

Thread added to the TODO list.

---------------------------------------------------------------------------

Stephan Szabo wrote:
> On Tue, 4 Oct 2005, Tony Marston wrote:
>
> > Description: Parts of information_schema only accessible to owner
> > Details:
> >
> > I have been trying to access parts of the information_schema as an ordinary
> > user, not as owner, and I am encountering instances where I cannot retrieve
> > any rows at all, or where some of the columns are empty when they should not
> > be.
> >
> > This sounds like a faulty implementation to me, with too many restrictions.
> > As far as I am concerned if I have access privileges on an object then I
> > should be able to see ALL information_schema details regarding that object.
>
> Complain to the SQL committee then. ;) Many portions of information_schema
> are defined in the spec with limitations based on the user.
>
> > As an example, in the view "information_schema.columns" I can only see the
> > entry in COLUMN_DEFAULT if I am the owner. Why is this? What is the logic
> > behind this decision?
>
> Because of this piece of the definition:
>
> CASE WHEN EXISTS ( SELECT *
> FROM DEFINITION_SCHEMA.SCHEMATA AS S
> WHERE ( TABLE_CATALOG, TABLE_SCHEMA )
> = (S.CATALOG_NAME, S.SCHEMA_NAME )
> AND SCHEMA_OWNER = USER )
> THEN COLUMN_DEFAULT
> ELSE NULL
> END AS COLUMN_DEFAULT,
>
> It looks like we're using table owner rather than schema owner since we
> allow mixed ownership of contents of a schema, but the general principle
> is the same.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Thread added to the TODO list.

Does it need to be in TODO? I thought Peter fixed this when he updated
information_schema back in April.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Thread added to the TODO list.
>
> Does it need to be in TODO? I thought Peter fixed this when he updated
> information_schema back in April.

Uh, did he? Peter?

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Thread added to the TODO list.
>
> Does it need to be in TODO? I thought Peter fixed this when he updated
> information_schema back in April.

Commit says:

Update information schema for SQL:2003 and new PostgreSQL features.

so I assume he did. Thanks.

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +