Date: Sat, 7 Sep 2013 14:01:52 -0400
From: Bruce Momjian <bruce@momjian.us>
To: Peter Eisentraut <peter_e@gmx.net>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Ian Lawrence Barwick <barwick@gmail.com>,
 pgsql-hackers@postgresql.org
Subject: Re: Re: Privileges for INFORMATION_SCHEMA.SCHEMATA (was
 Re: [DOCS] Small clarification in "34.41. schemata")
Message-ID: <20130907180152.GF11757@momjian.us>
References: 
 <CAB8KJ=g317u8GVyoi+E8cByOGVa+iNVsriTgC+XFA-k2wqpCTA@mail.gmail.com>
 <11650.1357782995@sss.pgh.pa.us> <510AD8E0.9020005@gmx.net>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="PNTmBPCT7hxwcZjr"
Content-Disposition: inline
In-Reply-To: <510AD8E0.9020005@gmx.net>
User-Agent: Mutt/1.5.20 (2009-06-14)
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org


--PNTmBPCT7hxwcZjr
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

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  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

--PNTmBPCT7hxwcZjr
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="schemata.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;
  

--PNTmBPCT7hxwcZjr
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


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

--PNTmBPCT7hxwcZjr--