Re: BUG #1937: Parts of information_schema only accessible to owner - Mailing list pgsql-bugs

> -----Original Message-----
> From: Stephan Szabo [mailto:sszabo@megazone.bigpanda.com]=20
> Sent: 08 October 2005 18:01
> To: Tony Marston
> Cc: pgsql-bugs@postgresql.org
> Subject: RE: [BUGS] BUG #1937: Parts of information_schema=20
> only accessible to owner
>=20
>=20
>=20
> On Sat, 8 Oct 2005, Tony Marston wrote:
>=20
> >
> >
> >
> >
> > > -----Original Message-----
> > > From: Stephan Szabo [mailto:sszabo@megazone.bigpanda.com]
> > > Sent: 08 October 2005 16:44
> > > To: Tony Marston
> > > Subject: RE: [BUGS] BUG #1937: Parts of information_schema only=20
> > > accessible to owner
> > >
> > >
> > > On Sat, 8 Oct 2005, Tony Marston wrote:
> > >
> > > > I have searched through the SQL 2003 standard and can=20
> find no such=20
> > > > restriction. In the volume titled "Information and
> > > Definition Schemas
> > > > (SQL/Schemata)" in section 5.20=20
> (INORMATON_SCHEMA.COLUMNS view) it=20
> > > > states the following under the heading "Function":
> > > >
> > > > "Identify the columns of tables defined in this catalog=20
> that are=20
> > > > 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=20
> > > > privileges).
> > > >
> > > > I take this to mean (as any reasonable person would) that if a=20
> > > > 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=20
> > > > within the information schema.
> > > >
> > > > Unless you can provide a direct quote from the SQL=20
> standard which=20
> > > > contradicts this I strongly suggest that you revise=20
> your opinion.
> > >
> > > What I gave was *directly* part of the definition of the=20
> view from=20
> > > the
> > > standard:
> > >
> > > > >                 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,
> > >
> > > I think any "reasonable person" would read the definition portion=20
> > > above from that view and interpret that as give the=20
> column default=20
> > > if the table the the column is in came from a schema that=20
> is owned=20
> > > by USER otherwise give NULL.
> > >
> >
> > I disagree. The function description in the SQL 1999 standard says=20
> > "Identify the columns of tables defined in this catalog that are=20
> > accessible to a given user." It is clear that the actual=20
> code sample=20
> > given does not conform to this description, so I would=20
> argue that the=20
> > code is wrong and the description is right. Any reasonable person=20
> > would assume that the code sample would conform to the description.=20
> > After all, the description does not say "except for those=20
> items where=20
> > the user must also be the owner".
>=20
> If there's two items:
> "Function" with a description and "Definition" with a=20
> definition, I think it's fairly ignorant to read the former=20
> as overriding the latter.  The latter *is* the definition.
>=20

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