public inbox for [email protected]help / color / mirror / Atom feed
Small clarification in "34.41. schemata" 8+ messages / 5 participants [nested] [flat]
* Small clarification in "34.41. schemata" @ 2013-01-10 00:35 Ian Lawrence Barwick <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Ian Lawrence Barwick @ 2013-01-10 00:35 UTC (permalink / raw) To: pgsql-docs 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 protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs ^ permalink raw reply [nested|flat] 8+ messages in thread
* Privileges for INFORMATION_SCHEMA.SCHEMATA (was Re: Small clarification in "34.41. schemata") @ 2013-01-10 01:56 Tom Lane <[email protected]> parent: Ian Lawrence Barwick <[email protected]> 0 siblings, 2 replies; 8+ messages in thread From: Tom Lane @ 2013-01-10 01:56 UTC (permalink / raw) To: Ian Lawrence Barwick <[email protected]>; +Cc: pgsql-docs; [email protected]; Peter Eisentraut <[email protected]> Ian Lawrence Barwick <[email protected]> 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Privileges for INFORMATION_SCHEMA.SCHEMATA (was Re: Small clarification in "34.41. schemata") @ 2013-01-14 17:49 Casey Allen Shobe <[email protected]> parent: Tom Lane <[email protected]> 1 sibling, 1 reply; 8+ messages in thread From: Casey Allen Shobe @ 2013-01-14 17:49 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Ian Lawrence Barwick <[email protected]>; pgsql-docs; [email protected]; Peter Eisentraut <[email protected]> On Wed, Jan 9, 2013 at 8:56 PM, Tom Lane <[email protected]> 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 [email protected] ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [HACKERS] Re: Privileges for INFORMATION_SCHEMA.SCHEMATA (was Re: Small clarification in "34.41. schemata") @ 2013-01-14 18:53 Tom Lane <[email protected]> parent: Casey Allen Shobe <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Tom Lane @ 2013-01-14 18:53 UTC (permalink / raw) To: Casey Allen Shobe <[email protected]>; +Cc: Ian Lawrence Barwick <[email protected]>; pgsql-docs; [email protected]; Peter Eisentraut <[email protected]> Casey Allen Shobe <[email protected]> writes: > On Wed, Jan 9, 2013 at 8:56 PM, Tom Lane <[email protected]> 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Re: Privileges for INFORMATION_SCHEMA.SCHEMATA (was Re: [DOCS] Small clarification in "34.41. schemata") @ 2013-01-16 23:27 Ian Lawrence Barwick <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: Ian Lawrence Barwick @ 2013-01-16 23:27 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Casey Allen Shobe <[email protected]>; pgsql-docs; pgsql-hackers <[email protected]>; Peter Eisentraut <[email protected]> 2013/1/15 Tom Lane <[email protected]>: > Casey Allen Shobe <[email protected]> writes: >> On Wed, Jan 9, 2013 at 8:56 PM, Tom Lane <[email protected]> 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] 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/[email protected].... [6] http://www.postgresql.org/message-id/[email protected] [7] http://www.postgresql.org/message-id/[email protected] [8] Not that I'm claiming MySQL's implementation is authoritative or anything Regards Ian Lawrence Barwick -- Sent via pgsql-hackers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Privileges for INFORMATION_SCHEMA.SCHEMATA (was Re: Small clarification in "34.41. schemata") @ 2013-01-31 20:49 Peter Eisentraut <[email protected]> parent: Tom Lane <[email protected]> 1 sibling, 1 reply; 8+ messages in thread From: Peter Eisentraut @ 2013-01-31 20:49 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Ian Lawrence Barwick <[email protected]>; pgsql-docs; [email protected] 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 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. I agree it would make sense to change this. -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Re: Privileges for INFORMATION_SCHEMA.SCHEMATA (was Re: [DOCS] Small clarification in "34.41. schemata") @ 2013-09-07 18:01 Bruce Momjian <[email protected]> parent: Peter Eisentraut <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Bruce Momjian @ 2013-09-07 18:01 UTC (permalink / raw) To: Peter Eisentraut <[email protected]>; +Cc: Tom Lane <[email protected]>; Ian Lawrence Barwick <[email protected]>; [email protected] 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 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. > > I agree it would make sense to change this. Is this the patch you want applied? The docs are fine? -- Bruce Momjian <[email protected]> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers Attachments: [text/x-diff] schemata.diff (942B, 2-schemata.diff) download | inline diff: 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; ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Re: Privileges for INFORMATION_SCHEMA.SCHEMATA (was Re: [DOCS] Small clarification in "34.41. schemata") @ 2013-09-10 02:27 Peter Eisentraut <[email protected]> parent: Bruce Momjian <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: Peter Eisentraut @ 2013-09-10 02:27 UTC (permalink / raw) To: Bruce Momjian <[email protected]>; +Cc: Tom Lane <[email protected]>; Ian Lawrence Barwick <[email protected]>; [email protected] 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers ^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2013-09-10 02:27 UTC | newest] Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2013-01-10 00:35 Small clarification in "34.41. schemata" Ian Lawrence Barwick <[email protected]> 2013-01-10 01:56 ` Privileges for INFORMATION_SCHEMA.SCHEMATA (was Re: Small clarification in "34.41. schemata") Tom Lane <[email protected]> 2013-01-14 17:49 ` Re: Privileges for INFORMATION_SCHEMA.SCHEMATA (was Re: Small clarification in "34.41. schemata") Casey Allen Shobe <[email protected]> 2013-01-14 18:53 ` Re: [HACKERS] Re: Privileges for INFORMATION_SCHEMA.SCHEMATA (was Re: Small clarification in "34.41. schemata") Tom Lane <[email protected]> 2013-01-16 23:27 ` Re: Re: Privileges for INFORMATION_SCHEMA.SCHEMATA (was Re: [DOCS] Small clarification in "34.41. schemata") Ian Lawrence Barwick <[email protected]> 2013-01-31 20:49 ` Re: Privileges for INFORMATION_SCHEMA.SCHEMATA (was Re: Small clarification in "34.41. schemata") Peter Eisentraut <[email protected]> 2013-09-07 18:01 ` Re: Re: Privileges for INFORMATION_SCHEMA.SCHEMATA (was Re: [DOCS] Small clarification in "34.41. schemata") Bruce Momjian <[email protected]> 2013-09-10 02:27 ` Re: Re: Privileges for INFORMATION_SCHEMA.SCHEMATA (was Re: [DOCS] Small clarification in "34.41. schemata") Peter Eisentraut <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox