Horton

Thread

  1. BUG #1937: Parts of information_schema only accessible to owner

    Tony Marston <tony@marston-home.demon.co.uk> — 2005-10-04T12:58:36Z

    The following bug has been logged online:

Bug reference:      1937
Logged by:          Tony Marston
Email address:      tony@marston-home.demon.co.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?

  2. Re: BUG #1937: Parts of information_schema only accessible to owner

    Peter Eisentraut <peter_e@gmx.net> — 2005-10-04T15:00:16Z

    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/

  3. Re: BUG #1937: Parts of information_schema only accessible

    Stephan Szabo <sszabo@megazone.bigpanda.com> — 2005-10-04T15:02:06Z

    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.

  4. Re: BUG #1937: Parts of information_schema only accessible to owner

    Jim C. Nasby <jnasby@pervasive.com> — 2005-10-04T16:45:04Z

    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@marston-home.demon.co.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@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

  5. Re: BUG #1937: Parts of information_schema only accessible

    Bruce Momjian <pgman@candle.pha.pa.us> — 2005-10-08T16:26:33Z

    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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

  6. Re: BUG #1937: Parts of information_schema only accessible

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2005-10-08T17:18:37Z

    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)

  7. Re: BUG #1937: Parts of information_schema only accessible

    Bruce Momjian <pgman@candle.pha.pa.us> — 2006-06-14T21:10:58Z

    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. +

  8. Re: BUG #1937: Parts of information_schema only accessible

    Tom Lane <tgl@sss.pgh.pa.us> — 2006-06-14T21:53:55Z

    Bruce Momjian <pgman@candle.pha.pa.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

  9. Re: BUG #1937: Parts of information_schema only accessible

    Bruce Momjian <pgman@candle.pha.pa.us> — 2006-06-14T21:55:30Z

    Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.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. +

  10. Re: BUG #1937: Parts of information_schema only accessible

    Bruce Momjian <pgman@candle.pha.pa.us> — 2006-06-14T21:56:16Z

    Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.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. +