 | |
Re: [BUGS] BUG #1937: Parts of information_schema only accessible
mailing.database.pgsql-bugs mailing.database.pgsql-bugs
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Re: [BUGS] BUG #1937: Parts of information_schema only accessible -
10-04-2005
, 10:08 AM
|
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. |
Many portions of information_schemaare defined in the spec with limitations based on the user.
Quote:
|
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? |
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
#2
| |||
| |||
|
|
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_schemaare 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.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
#3
| |||
| |||
|
| -----Original Message----- From: Stephan Szabo [mailto:sszabo (AT) megazone (DOT) bigpanda.com] Sent: 08 October 2005 16:44 To: Tony Marston Subject: RE: [BUGS] BUG #1937: Parts of information_schema only accessible to owner On Sat, 8 Oct 2005, 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." Note there that it does not say that the user must be the owner, but that the user is allowed to access the table (i.e. has access privileges). I take this to mean (as any reasonable person would) that if a user has been granted the privilges to access an object then that same user can view all the information on that object which is defined within the information schema. Unless you can provide a direct quote from the SQL standard which contradicts this I strongly suggest that you revise your opinion. What I gave was *directly* part of the definition of the view from the standard: 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, I think any "reasonable person" would read the definition portion above from that view and interpret that as give the column default if the table the the column is in came from a schema that is owned by USER otherwise give NULL. 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". |
"Function" with a description and "Definition" with a definition, I think
it's fairly ignorant to read the former as overriding the latter. The
latter *is* the definition.
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
#4
| |||
| |||
|
|
Is this something we need to patch? |
--
Alvaro Herrera Architect, http://www.EnterpriseDB.com
"No necesitamos banderas
No reconocemos fronteras" (Jorge González)
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly
#5
| |||
| |||
|
|
If there's two items: "Function" with a description and "Definition" with a definition, I think it's fairly ignorant to read the former as overriding the latter. The latter *is* the definition. Yes, but if the sample code disagrees with the description shouldn't you at least ask someone in authority which one is right? Shouldn't you ask WHY |
a definition, generally the definition wins and it's the description
that's wrong.
Quote:
|
some parts of the information schema should only be accessible if you are the owner when 99% of the information schema does NOT have this restriction? |
use this same restriction in ATTRIBUTES as well (but not in DOMAINS).
Quote:
|
Nowhere in any function descriptions does it say that the user must be the owner, so clearly whoever wrote the sample code made a minor mistake, and |
by a given user", "Identify the check constraints defined in this catalog
that are owned by a given user", "Identify the columns that are dependent
on a domain defined in this catalog and owned by a user", ...
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
#6
| |||
| |||
|
|
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_schemaare 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. +
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
#7
| |||
| |||
|
|
Thread added to the TODO list. |
information_schema back in April.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
#8
| |||
| |||
|
|
Bruce Momjian <pgman (AT) candle (DOT) 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. |
--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
#9
| |||
| |||
|
|
Bruce Momjian <pgman (AT) candle (DOT) 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. |
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. +
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.