Thread: BUG #2850: Cannot select from information_schema.schemat

The following bug has been logged online:

Bug reference:      2850
Logged by:          Tony Marston
Email address:      tony@marston-home.demon.co.uk
PostgreSQL version: 8.2
Operating system:   Windows XP
Description:        Cannot select from information_schema.schemat
Details:

If I am logged on as a user other than 'postgres' and I try the query

SELECT * FROM information_schema.schemata

I get no results. Yet if I try the equivalent query

SELECT * FROM pg_namespace

I can see all the available schema names.

Why is there a difference when the two queries are supposed to provide the
same results?

"Tony Marston" <tony@marston-home.demon.co.uk> writes:
> If I am logged on as a user other than 'postgres' and I try the query
> SELECT * FROM information_schema.schemata
> I get no results. Yet if I try the equivalent query
> SELECT * FROM pg_namespace
> I can see all the available schema names.

> Why is there a difference when the two queries are supposed to provide the
> same results?

They're not "supposed to provide the same results".  Per SQL99, the
schemata view is supposed to

         Identify the schemata in a catalog that are owned by a given user.

and the SQL definition in the spec makes it clear that it only shows
schemas owned by CURRENT_USER or a role that CURRENT_USER is a member
of.

            regards, tom lane

--On Donnerstag, Dezember 21, 2006 15:47:40 +0000 Tony Marston
<tony@marston-home.demon.co.uk> wrote:

> The fact that the SQL standard says that the schemata view is supposed to
> "Identify the schemata in a catalog that are owned by a given user" does
> not automatically mean that the information can *ONLY* be accessed by the
> owner. Any user should be able to see the schema to which they have
> access, owner or not.

The standard doesn't specify any given user, it specifies CURRENT_USER. Only
if CURRENT_USER is the owner of a schema (or CURRENT_USER inherits
ownership by membership) you are able (and allowed) to see the schema. While
I admit that this makes it hard to identify catalog schemata by an DBA via
the information_schema, the standard is quite clear here. I don't see any
ambiguity here....

If you need to go beyond what the standard allows, you have to use the
system
catalog.

--
  Thanks

                    Bernd