Subject: Small clarification in "34.41. schemata"

From: Ian Lawrence Barwick <[email-masked]>
Date: Thu, 10 Jan 2013 09:35:10 +0900

The documentation says:

  The view schemata contains all schemas in the current database that
are owned by a currently enabled role.

( http://www.postgresql.org/docs/9.2/static/infoschema-schemata.html )

However it shows all schemas if the user is a superuser, regardless of
whether the schema is owned by the superuser.

Does the documentation need clarifying? I'd suggest something like:

  The view schemata contains all schemas in the current database that
are owned by a currently enabled role, or all schemas if the currently
enabled role is a superuser.

Regards

Ian Lawrence Barwick

-- 
Sent via pgsql-docs mailing list ([email-masked])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs

---

From: Tom Lane <[email-masked]>
Date: Wed, 09 Jan 2013 20:56:35 -0500

> enabled role is a superuser.

Well, that's wrong anyway, or at least it only represents touching a
small portion of the elephant.  The actual test, per
information_schema.sql, is

	pg_has_role(n.nspowner, 'USAGE');

so you can see any schemas owned by roles you have the ability to SET
ROLE to.  Superusers have that ability a fortiori; there is no special
case involved here.

The SQL standard says "Identify the schemata in a catalog that are owned
by a given user or role", and gives the pseudocode

WHERE ( SCHEMA_OWNER = CURRENT_USER
          OR
            SCHEMA_OWNER IN
            ( SELECT ROLE_NAME
              FROM ENABLED_ROLES ) )

so this behavior conforms with the spec, modulo the fact that there's
nothing about superusers in the spec.

However, it seems to me that this behavior is actually wrong for our
purposes, as it represents a too-literal reading of the spec.  The SQL
standard has no concept of privileges on schemas, only ownership.
We do have privileges on schemas, so it seems to me that the consistent
thing would be for this view to show any schema that you either own or
have some privilege on.  That is the test should be more like 

	pg_has_role(n.nspowner, 'USAGE')
	OR has_schema_privilege(n.oid, 'CREATE, USAGE')

As things stand, a non-superuser won't see "public", "pg_catalog",
nor even "information_schema" itself in this view, which seems a
tad silly.

Thoughts?

			regards, tom lane

---

From: Casey Allen Shobe <[email-masked]>
Date: Mon, 14 Jan 2013 12:49:32 -0500

On Wed, Jan 9, 2013 at 8:56 PM, Tom Lane <[email-masked]> wrote:

> have some privilege on.

IMHO, schemata should follow the standard as it does today.  Other
platforms have privileges on schemas as well, and this sort of thing seems
to fall into the same bucket as other platform compatibilities outside the
scope of what the standard thinks about, which means you use pg_catalog to
access that information rather than information_schema, which should be
expected to work consistently on all platforms that implement it.

-- 
Casey Allen Shobe
[email-masked]

---

From: Tom Lane <[email-masked]>
Date: Mon, 14 Jan 2013 13:53:39 -0500

> expected to work consistently on all platforms that implement it.

Meh.  To me, standards compliance requires that if you have created a
SQL-compliant database, you'd better see spec-compliant output from the
information schema.  As soon as you do something outside the standard
(in this instance, grant some privileges on a schema), it becomes a
judgment call whether and how that should affect what you see in the
information schema.

It may be that the current behavior of this view is actually the best
thing, but a standards-compliance argument doesn't do anything to
convince me.

			regards, tom lane

---

From: Ian Lawrence Barwick <[email-masked]>
Date: Thu, 17 Jan 2013 08:27:23 +0900

> thing, but a standards-compliance argument doesn't do anything to

My original assumption here was that the documentation [1] was in need of
clarification. On the other hand the current output of
information_schema.schemata
isn't quite I was expecting, which would be as Tom writes:

> the consistent thing would be for this view to show any schema that you
> either own or have some privilege on.

As it stands, the only way of extracting a list of visible schemas from
PostgreSQL's information_schema (i.e. without relying on PostgreSQL-specific
system functions) is doing something like this:

  SELECT DISTINCT(table_schema) FROM information_schema.tables

Digging about a bit [2], it seems the only other RDBMSes with a fully-fledged
information_schema are Microsoft SQL Server and MySQL. I don't have access to
SQL Server; the documentation [3] says "Returns one row for each schema in the
current database", which also strikes me as incorrect (can someone confirm this
behaviour?).

For MySQL, the documentation [4] indicates that their implementation shows
all schemas (in MySQL: databases) visible to the current user, and
I've confirmed
this behaviour with MySQL 5.5.

Personally I'd support modifying PostgreSQL's information_schema.schemata to
show all schemas the current user owns/has privileges on, providing it's not
an egregious violation of the SQL standard.

It seems I'm not the only user who has been stymied by this issue [5][6][7];
also, resolving it would also make it consistent with MySQL's output [8]

[1] http://www.postgresql.org/docs/9.2/static/infoschema-schemata.html
[2] http://en.wikipedia.org/wiki/Information_schema
[3] http://msdn.microsoft.com/en-us/library/ms182642.aspx
[4] http://dev.mysql.com/doc/refman/5.5/en/schemata-table.html
[5] http://www.postgresql.org/message-id/CAFjNrYv4MrkbXi-usroCqNiaSyEAzvJ7GjtsEJW2RK7-R=[email-masked]
[6] http://www.postgresql.org/message-id/[email-masked]
[7] http://www.postgresql.org/message-id/[email-masked]
[8] Not that I'm claiming MySQL's implementation is authoritative or anything

Regards

Ian Lawrence Barwick

-- 
Sent via pgsql-hackers mailing list ([email-masked])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

---

From: Peter Eisentraut <[email-masked]>
Date: Thu, 31 Jan 2013 15:49:36 -0500

> nor even "information_schema" itself in this view, which seems a

I agree it would make sense to change this.

---

From: Bruce Momjian <[email-masked]>
Date: Sat, 7 Sep 2013 14:01:52 -0400

> I agree it would make sense to change this.

Is this the patch you want applied?  The docs are fine?

-- 
  Bruce Momjian  <[email-masked]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +
diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
new file mode 100644
index 95f267f..605bcbd
*** a/src/backend/catalog/information_schema.sql
--- b/src/backend/catalog/information_schema.sql
*************** CREATE VIEW schemata AS
*** 1502,1508 ****
             CAST(null AS sql_identifier) AS default_character_set_name,
             CAST(null AS character_data) AS sql_path
      FROM pg_namespace n, pg_authid u
!     WHERE n.nspowner = u.oid AND pg_has_role(n.nspowner, 'USAGE');
  
  GRANT SELECT ON schemata TO PUBLIC;
  
--- 1502,1509 ----
             CAST(null AS sql_identifier) AS default_character_set_name,
             CAST(null AS character_data) AS sql_path
      FROM pg_namespace n, pg_authid u
!     WHERE n.nspowner = u.oid AND (pg_has_role(n.nspowner, 'USAGE') OR 
!           has_schema_privilege(n.oid, 'CREATE, USAGE'));
  
  GRANT SELECT ON schemata TO PUBLIC;

---

From: Peter Eisentraut <[email-masked]>
Date: Mon, 09 Sep 2013 22:27:26 -0400

> Is this the patch you want applied?  The docs are fine? 

I have committed it with a documentation update.