The following bug has been logged online:

Bug reference: 1937
Logged by: Tony Marston
Email address: tony (AT) marston-home (DOT) 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

Am Dienstag, 4. Oktober 2005 14:58 schrieb Tony Marston:
Because the SQL standard says so.

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

---------------------------(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

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:
--
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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Tony Marston wrote:
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/

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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




---------------------------(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

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
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?
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
you are perpetuationg that mistake. Which is the most logical answer? Any
user with privileges or no-one but the owner? If you were to ask 10
different developers for their opinion on this subject how many would agree
with you and how many would agree with me?

Tony Marston

http://www.tonymarston.net=20




---------------------------(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

Peter Eisentraut <peter_e (AT) gmx (DOT) net> writes:
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

---------------------------(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

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




---------------------------(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

Tom Lane wrote:
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/

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Tony Marston wrote:
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/

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster