> -----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.

For instance, you could have a Finance group writing functions to calculate
your financial earnings. These calculations could be changing frequently
and should only be visible to a small group of people. If the calculations
were visible by anyone with database access, they could figure out earnings
prior to the release and thus have inside information on the stock.


Jon


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Dec 20, 2007, at 11:30 AM, Roberts, Jon 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.
>
> For instance, you could have a Finance group writing functions to
> calculate
> your financial earnings. These calculations could be changing
> frequently
> and should only be visible to a small group of people. If the
> calculations
> were visible by anyone with database access, they could figure out
> earnings
> prior to the release and thus have inside information on the stock.

Does everyone in your organization have login access to your
database? That seems like the main issue. Perhaps you should stick an
application server in between. The application server could also
upload functions from the "Finance group" and ensure that no one can
see stored procedures.

Cheers,
M

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Dec 20, 2007 12:39 PM, A.M. <agentm@themactionfaction.com> wrote:
> On Dec 20, 2007, at 11:30 AM, Roberts, Jon wrote:
> >> 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.
> >
> > For instance, you could have a Finance group writing functions to
> > calculate
> > your financial earnings. These calculations could be changing
> > frequently
> > and should only be visible to a small group of people. If the
> > calculations
> > were visible by anyone with database access, they could figure out
> > earnings
> > prior to the release and thus have inside information on the stock.
>
> Does everyone in your organization have login access to your
> database? That seems like the main issue. Perhaps you should stick an
> application server in between. The application server could also
> upload functions from the "Finance group" and ensure that no one can
> see stored procedures.

forcing all database access through an app server is a (too) high
price to pay in many scenarios. while it works great for some things
(web apps), in many companies the db is the 'brain' of the company
that must serve all kinds of different purposes across many
interfaces.

for example, ups provides software that communicates with databases
over odbc for purposes to apply tracking #s to parts. think about all
the report engines, etc etc that run over those type of interfaces.

merlin

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend