Thread: 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@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?
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.
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/
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
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
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)
Tony Marston wrote: > I have searched through the SQL 2003 standard and can find no such > restriction. In the volume titled "Information and Definition Schemas > (SQL/Schemata)" in section 5.20 (INORMATON_SCHEMA.COLUMNS view) it > states the following under the heading "Function": > > "Identify the columns of tables defined in this catalog that are > accessible to a given user or role." The information schema currently follows SQL 1999. Interestingly, the requirement to "blank out" the column defaults of non-owned tables was apparently dropped in SQL 2003. Clearly, we need to review the information schema for SQL 2003 conformance. -- Peter Eisentraut http://developer.postgresql.org/~petere/
> -----Original Message----- > From: Stephan Szabo [mailto:sszabo@megazone.bigpanda.com]=20 > Sent: 08 October 2005 16:44 > To: Tony Marston > Subject: RE: [BUGS] BUG #1937: Parts of information_schema=20 > only accessible to owner >=20 >=20 > On Sat, 8 Oct 2005, Tony Marston wrote: >=20 > > I have searched through the SQL 2003 standard and can find no such=20 > > restriction. In the volume titled "Information and=20 > Definition Schemas=20 > > (SQL/Schemata)" in section 5.20 (INORMATON_SCHEMA.COLUMNS view) it=20 > > states the following under the heading "Function": > > > > "Identify the columns of tables defined in this catalog that are=20 > > accessible to a given user or role." > > > > Note there that it does not say that the user must be the=20 > owner, but=20 > > that the user is allowed to access the table (i.e. has access=20 > > privileges). > > > > I take this to mean (as any reasonable person would) that if a user=20 > > has been granted the privilges to access an object then=20 > that same user=20 > > can view all the information on that object which is defined within=20 > > the information schema. > > > > Unless you can provide a direct quote from the SQL standard which=20 > > contradicts this I strongly suggest that you revise your opinion. >=20 > What I gave was *directly* part of the definition of the view from the > standard: >=20 > > > CASE WHEN EXISTS ( SELECT * > > > FROM DEFINITION_SCHEMA.SCHEMATA AS S > > > WHERE ( TABLE_CATALOG, TABLE_SCHEMA ) > > > =3D (S.CATALOG_NAME, S.SCHEMA_NAME ) > > > AND SCHEMA_OWNER =3D USER ) > > > THEN COLUMN_DEFAULT > > > ELSE NULL > > > END AS COLUMN_DEFAULT, >=20 > I think any "reasonable person" would read the definition=20 > portion above from that view and interpret that as give the=20 > column default if the table the the column is in came from a=20 > schema that is owned by USER otherwise give NULL. >=20 I disagree. The function description in the SQL 1999 standard says "Identify the columns of tables defined in this catalog that are accessible to a given user." It is clear that the actual code sample given does not conform to this description, so I would argue that the code is wrong and the description is right. Any reasonable person would assume that the code sample would conform to the description. After all, the description does not say "except for those items where the user must also be the owner". Tony Marston http://www.tonymarston.net=20
Peter Eisentraut <peter_e@gmx.net> writes:
> The information schema currently follows SQL 1999. Interestingly, the
> requirement to "blank out" the column defaults of non-owned tables was
> apparently dropped in SQL 2003. Clearly, we need to review the
> information schema for SQL 2003 conformance.
Yeah. Not only that, but they changed the WHERE clause: where formerly
it restricted you to seeing tables you own, it now allows you to see
anything you have any granted privileges on. SQL99 reads
WHERE (C.TABLE_CATALOG, C.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME) IN
(SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM DEFINITION_SCHEMA.COLUMN_PRIVILEGES
WHERE (SCHEMA_OWNER IN ( 'PUBLIC', CURRENT_USER )
OR
SCHEMA_OWNER IN ( SELECT ROLE_NAME FROM ENABLED_ROLES )))
but what I see in 2003 is
WHERE (C.TABLE_CATALOG, C.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME) IN
(SELECT CP.TABLE_CATALOG, CP.TABLE_SCHEMA, CP.TABLE_NAME, CP.COLUMN_NAME
FROM DEFINITION_SCHEMA.COLUMN_PRIVILEGES AS CP
WHERE (CP.GRANTEE IN ( 'PUBLIC', CURRENT_USER )
OR
CP.GRANTEE IN ( SELECT ROLE_NAME FROM ENABLED_ROLES )))
Probably there are similar changes in other views.
Not sure if there's time to do this for 8.1 ... I don't really have time
to grovel through it, do you?
regards, tom lane
> -----Original Message----- > From: Peter Eisentraut [mailto:peter_e@gmx.net]=20 > Sent: 08 October 2005 14:09 > To: Tony Marston > Cc: pgsql-bugs@postgresql.org > Subject: Re: [BUGS] BUG #1937: Parts of information_schema=20 > only accessible to owner >=20 >=20 > Please copy replies to the mailing list. >=20 > Tony Marston wrote: > > I have searched through the SQL 2003 standard and can find no such=20 > > restriction. In the volume titled "Information and=20 > Definition Schemas=20 > > (SQL/Schemata)" in section 5.20 (INORMATON_SCHEMA.COLUMNS view) it=20 > > states the following under the heading "Function": > > > > "Identify the columns of tables defined in this catalog that are=20 > > accessible to a given user or role." >=20 > The information schema currently follows SQL 1999.=20=20 > Interestingly, the=20 > requirement to "blank out" the column defaults of non-owned=20 > tables was=20 > apparently dropped in SQL 2003. Clearly, we need to review the=20 > information schema for SQL 2003 conformance. In the mean time I have amended my version of the INFORMATION_SCHEMA.COLUMNS view to conform to the 2003 standard, so this is now a non-problem for me. I just thought that I should bring this discrepancy between the 1999 and 2003 standards to your attention. Tony Marston http://www.tonymarston.net=20
Tom Lane wrote: > Probably there are similar changes in other views. > > Not sure if there's time to do this for 8.1 ... I don't really have > time to grovel through it, do you? I don't think it's reasonable to do this for 8.1. There are probably several conceptual changes across the board that need to be attacked as a whole. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Tony Marston wrote: > I disagree. The function description in the SQL 1999 standard says > "Identify the columns of tables defined in this catalog that are > accessible to a given user." It is clear that the actual code sample > given does not conform to this description, First of all, the current implementation certainly "identifies" all the desired columns, because you can clearly get the "identity" of all columns from that view. Moreover, the functional description does not say anything about the details of the view, leaving that to the formal definition below. If we followed your reading of the standard, the view would simply give the names of the columns along with table and schema name and that's all. -- Peter Eisentraut http://developer.postgresql.org/~petere/
> -----Original Message----- > From: Peter Eisentraut [mailto:peter_e@gmx.net] > Sent: 09 October 2005 17:42 > To: Tony Marston > Cc: pgsql-bugs@postgresql.org; 'Stephan Szabo' > Subject: Re: [BUGS] BUG #1937: Parts of information_schema > only accessible to owner > > > Tony Marston wrote: > > I disagree. The function description in the SQL 1999 standard says > > "Identify the columns of tables defined in this catalog that are > > accessible to a given user." It is clear that the actual > code sample > > given does not conform to this description, > > First of all, the current implementation certainly > "identifies" all the > desired columns, because you can clearly get the "identity" of all > columns from that view. > > Moreover, the functional description does not say anything about the > details of the view, leaving that to the formal definition below. If > we followed your reading of the standard, the view would simply give > the names of the columns along with table and schema name and that's > all. I'm sure that if you actually implemented that interpretation you would get more complaits than you could handle. Tony Marston http://www.tonymarston.net
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@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
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. +
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. +