BUG #2850: Cannot select from information_schema.schemat

"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