Small clarification in "34.41. schemata"
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 (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
Ian Lawrence Barwick <barwick@gmail.com> writes:
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.
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
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
On Wed, Jan 9, 2013 at 8:56 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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.
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
casey@shobe.info
Casey Allen Shobe <casey@shobe.info> writes:
On Wed, Jan 9, 2013 at 8:56 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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.
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.
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
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
2013/1/15 Tom Lane <tgl@sss.pgh.pa.us>:
Casey Allen Shobe <casey@shobe.info> writes:
On Wed, Jan 9, 2013 at 8:56 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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.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.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
My original assumption here was that the documentation [1]http://www.postgresql.org/docs/9.2/static/infoschema-schemata.html 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]http://en.wikipedia.org/wiki/Information_schema, 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]http://msdn.microsoft.com/en-us/library/ms182642.aspx 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]http://dev.mysql.com/doc/refman/5.5/en/schemata-table.html 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]/messages/by-id/CAFjNrYv4MrkbXi-usroCqNiaSyEAzvJ7GjtsEJW2RK7-R=8hiw@mail.gmail.com[6]/messages/by-id/200612211146.kBLBkLqA001218@wwwmaster.postgresql.org[7]/messages/by-id/50AFF3FE.4030502@gmail.com;
also, resolving it would also make it consistent with MySQL's output [8]Not that I'm claiming MySQL's implementation is authoritative or anything
[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]: /messages/by-id/CAFjNrYv4MrkbXi-usroCqNiaSyEAzvJ7GjtsEJW2RK7-R=8hiw@mail.gmail.com
[6]: /messages/by-id/200612211146.kBLBkLqA001218@wwwmaster.postgresql.org
[7]: /messages/by-id/50AFF3FE.4030502@gmail.com
[8]: Not that I'm claiming MySQL's implementation is authoritative or anything
Regards
Ian Lawrence Barwick
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 1/9/13 8:56 PM, Tom Lane wrote:
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 likepg_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.
I agree it would make sense to change this.
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
On Thu, Jan 31, 2013 at 03:49:36PM -0500, Peter Eisentraut wrote:
On 1/9/13 8:56 PM, Tom Lane wrote:
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 likepg_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.I agree it would make sense to change this.
Is this the patch you want applied? The docs are fine?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
Attachments:
schemata.difftext/x-diff; charset=us-asciiDownload
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;
On Sat, 2013-09-07 at 14:01 -0400, Bruce Momjian wrote:
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.I agree it would make sense to change this.
Is this the patch you want applied? The docs are fine?
I have committed it with a documentation update.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers