BUG #1937: Parts of information_schema only accessible to owner

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/

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.

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

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

Tony Marston

http://www.tonymarston.net

On Sat, 8 Oct 2005, Tony Marston wrote:

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

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.

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)

-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone.bigpanda.com]
Sent: 08 October 2005 18:01
To: Tony Marston
Cc: pgsql-bugs@postgresql.org
Subject: RE: [BUGS] BUG #1937: Parts of information_schema
only accessible to owner

On Sat, 8 Oct 2005, Tony Marston wrote:

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

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

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/

On Sat, 8 Oct 2005, Tony Marston wrote:

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

It's not sample code. It's a definition. If a description doesn't match
a definition, generally the definition wins and it's the description
that's wrong.

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?

Why would this be any more consistent than anywhere else in SQL? And they
use this same restriction in ATTRIBUTES as well (but not in DOMAINS).

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

Let's see, "Identify the assertions defined in this catalog that are owned
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", ...

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]
Sent: 08 October 2005 14:09
To: Tony Marston
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #1937: Parts of information_schema
only accessible to owner

Please copy replies to the mailing list.

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.

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

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