DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=WyOfRhAdO3Ue++GSDJ2WO/b9zJU8OV6J0vbtkXqC3T2el8MWZrWOim/FQcJaBow8mLoDW+0vr5GLT58p8Dr1oC1BKYVt62gtiAi55NB0/cCp1sPriG0bqCayDbX9GYgoYHWRGaGinHUAfhVrhetcT8a+QUz7KM0Pmdsxh0kHrJQ=
Message-ID: <b42b73150712200943p2ca480c4gfc5d29fadff94d1f@mail.gmail.com>
Date: Thu, 20 Dec 2007 12:43:16 -0500
From: "Merlin Moncure" <mmoncure@gmail.com>
To: "Roberts, Jon" <Jon.Roberts@asurion.com>
Subject: Re: viewing source code
Cc: "Alvaro Herrera" <alvherre@commandprompt.com>,
	"Trevor Talbot" <quension@gmail.com>,
	"Joshua D. Drake" <jd@commandprompt.com>,
	"Kris Jurka" <books@ejurka.com>,
	"Jonah H. Harris" <jonah.harris@gmail.com>,
	"Bill Moran" <wmoran@collaborativefusion.com>,
	pgsql-performance@postgresql.org
In-Reply-To: 
 <1A6E6D554222284AB25ABE3229A92762112A49@nrtexcus702.int.asurion.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: 
 <1A6E6D554222284AB25ABE3229A92762112A49@nrtexcus702.int.asurion.com>

On Dec 20, 2007 11:30 AM, Roberts, Jon <Jon.Roberts@asurion.com> wrote:
> > -----Original Message-----
> > From: Merlin Moncure [mailto:mmoncure@gmail.com]
> > Sent: Thursday, December 20, 2007 8:30 AM
> > To: Roberts, Jon
> > Cc: Alvaro Herrera; Trevor Talbot; Joshua D. Drake; Kris Jurka; Jonah H.
> > Harris; Bill Moran; pgsql-performance@postgresql.org
> > Subject: Re: [PERFORM] viewing source code
> >
>
> > On Dec 20, 2007 9:07 AM, Roberts, Jon <Jon.Roberts@asurion.com> wrote:
> > > So your suggestion is first to come up with a query that dynamically
> > checks
> > > permissions and create a view for it.  Secondly, change pgAdmin to
> > reference
> > > this view in place of pg_proc.  Actually, it should be extended to all
> >
> > This solution will not work.  It requires cooperation from pgAdmin
> > which is not going to happen and does nothing about psql or direct
> > queries from within pgadmin.  Considered from a security/obfuscation
> > perspective,  its completely ineffective.  As I've said many times,
> > there are only two solutions to this problem:
> >
> > 1. disable permissions to pg_proc and deal with the side effects
> > (mainly, pgadmin being broken).
> >
> > 2. wrap procedure languages in encrypted handler (pl/pgsql_s) so that
> > the procedure code is encrypted in pg_proc.  this is an ideal
> > solution, but the most work.
> >
>
> I think there is an option 3.  Enhance the db to have this feature built in
> which is more inline with commercial databases.  This feature would drive
> adoption of PostgreSQL.  It isn't feasible in most companies to allow
> everyone with access to the database to view all code written by anyone and
> everyone.

option 3 is really option 2. having this option is all the flexibility
you need.  i understand in certain cases you want to prevent code from
being available to see from certain users, but i don't buy the
adoption argument...most people dont actually become aware of
implications of pg_proc until after development has started.  simply
having a choice, either directly community supported or maintained
outside in pgfoundry should be enough.  in the majority of cases, who
can see the code doesn't matter.

i do however strongly disagree that hiding the code is bad in
principle... i was in the past  in this exact situation for business
reasons out of my control (this is why I know the pgadmin route wont
work, i've chased down that angle already), so i'm highly sympathetic
to people who need to do this.  i opted for revoke from pg_proc route,
which, while crude was highly effective.

merlin