Re: [HACKERS] Re: Privileges for INFORMATION_SCHEMA.SCHEMATA (was Re: [DOCS] Small clarification in 34.41. schemata)

2013-09-09 Thread Peter Eisentraut
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


Re: [HACKERS] Re: Privileges for INFORMATION_SCHEMA.SCHEMATA (was Re: [DOCS] Small clarification in 34.41. schemata)

2013-09-07 Thread Bruce Momjian
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  br...@momjian.ushttp://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;
  

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


[HACKERS] Re: Privileges for INFORMATION_SCHEMA.SCHEMATA (was Re: [DOCS] Small clarification in 34.41. schemata)

2013-01-31 Thread Peter Eisentraut
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-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Re: Privileges for INFORMATION_SCHEMA.SCHEMATA (was Re: [DOCS] Small clarification in 34.41. schemata)

2013-01-16 Thread Ian Lawrence Barwick
2013/1/15 Tom Lane t...@sss.pgh.pa.us:
 Casey Allen Shobe ca...@shobe.info writes:
 On Wed, Jan 9, 2013 at 8:56 PM, Tom Lane t...@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] 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=8...@mail.gmail.com
[6] 
http://www.postgresql.org/message-id/200612211146.kblbklqa001...@wwwmaster.postgresql.org
[7] http://www.postgresql.org/message-id/50aff3fe.4030...@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


[HACKERS] Re: Privileges for INFORMATION_SCHEMA.SCHEMATA (was Re: [DOCS] Small clarification in 34.41. schemata)

2013-01-14 Thread Casey Allen Shobe
On Wed, Jan 9, 2013 at 8:56 PM, Tom Lane t...@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
ca...@shobe.info


Re: [HACKERS] Re: Privileges for INFORMATION_SCHEMA.SCHEMATA (was Re: [DOCS] Small clarification in 34.41. schemata)

2013-01-14 Thread Tom Lane
Casey Allen Shobe ca...@shobe.info writes:
 On Wed, Jan 9, 2013 at 8:56 PM, Tom Lane t...@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-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers