> Date: Sat, 30 Nov 2002 23:14:43 -0500
> From: Tom Lane <tgl@sss.pgh.pa.us>
> 
> Jochem van Dieten <jochemd@oli.tudelft.nl> writes:
> > Tom Lane wrote:
> >> This has nothing to do with USAGE on the schema; it is just a matter of
> >> being able to read the system catalogs.
> 
> > <quote>
> > By default, users cannot see the objects in schemas they do not own.
> > </quote>
> 
> 
> I agree that this isn't completely ideal, but I stand by my comment that
> it's not worth breaking every client that looks at system catalogs in
> order to prevent it.
>
What about the following scenario:

 - move the information in pg_class etc. to new tables pg_dba_class etc.
   to which only DBAs have access

 - redefine pg_class etc. as views which contain only the information the specific
   user has right to see

This mimics the way Oracle's data dictionary works and yet would not break existing
clients because the objects pg_class etc. still exist (though containing less data
for less privileged users).

Christoph Dalitz


Mon, 2 Dec 2002 12:21:27 +0100
Christoph Dalitz <christoph.dalitz@hs-niederrhein.de>
Re: USAGE on schema allowed by default?
Christoph Dalitz <christoph.dalitz@hs-niederrhein.de> writes:
>> I agree that this isn't completely ideal, but I stand by my comment that
>> it's not worth breaking every client that looks at system catalogs in
>> order to prevent it.
>> 
> What about [ hiding the real catalogs behind views ]

A good solution would need more than that.  For example, something I've
heard repeatedly is that people would like to hide the source code of
their SQL or PLxxx functions from users who are nonetheless allowed to
call those functions.  A row-wise selective view of pg_proc can't fix
that.  In many cases it's less than clear which rows of which catalogs
to hide anyway.

Ultimately, if you don't want other people to see any of your catalog
information, you shouldn't let 'em into your database.  There's still
the option of setting up distinct databases.

			regards, tom lane

Mon, 02 Dec 2002 10:16:34 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: USAGE on schema allowed by default? 
Tom Lane wrote:
> For example, something I've heard repeatedly is that people would like to
> hide the source code of their SQL or PLxxx functions from users who are
> nonetheless allowed to call those functions.  A row-wise selective view of
> pg_proc can't fix that.  In many cases it's less than clear which rows of
> which catalogs to hide anyway.

It wouldn't be terribly difficult to encrypt prosrc with 3des (or maybe aes) 
using the owner's passwd from pg_shadow. We would need a new bool column in 
pg_proc (proisencrypted?) and some logic in fmgr.c.

Is there sufficient interest to justify the effort?

Joe


Mon, 02 Dec 2002 09:50:03 -0800
Joe Conway <mail@joeconway.com>
protecting prosrc (was Re: USAGE on schema allowed by default?)
NOTE: redirecting to hackers

On Mon, 2002-12-02 at 12:50, Joe Conway wrote:
> Tom Lane wrote:
> > For example, something I've heard repeatedly is that people would like to
> > hide the source code of their SQL or PLxxx functions from users who are
> > nonetheless allowed to call those functions.  A row-wise selective view of
> > pg_proc can't fix that.  In many cases it's less than clear which rows of
> > which catalogs to hide anyway.
> 
> It wouldn't be terribly difficult to encrypt prosrc with 3des (or maybe aes) 
> using the owner's passwd from pg_shadow. We would need a new bool column in 
> pg_proc (proisencrypted?) and some logic in fmgr.c.
> 
> Is there sufficient interest to justify the effort?
> 

I think this would be a good idea, though there becomes a question of
what type of performance hit comes into play when doing this. I suppose
if you have an option whether to encrypt it or not that would help.  One
other thing is that it needs to be "decryptable" by owners and
superusers. 

Robert Treat


02 Dec 2002 15:45:22 -0500
Robert Treat <xzilla@users.sourceforge.net>
Re: protecting prosrc (was Re: [GENERAL] USAGE on schema allowed by
> > It wouldn't be terribly difficult to encrypt prosrc with 3des (or maybe
aes)
> > using the owner's passwd from pg_shadow. We would need a new bool column
in
> > pg_proc (proisencrypted?) and some logic in fmgr.c.
> >
> > Is there sufficient interest to justify the effort?
> >
>
> I think this would be a good idea, though there becomes a question of
> what type of performance hit comes into play when doing this. I suppose
> if you have an option whether to encrypt it or not that would help.  One
> other thing is that it needs to be "decryptable" by owners and
> superusers.

Surely a more generic column privileges implementation would be better?

Chris


Mon, 2 Dec 2002 13:17:18 -0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: protecting prosrc (was Re: [GENERAL] USAGE on schema allowed by
Robert Treat wrote:
> NOTE: redirecting to hackers

good idea!

> I think this would be a good idea, though there becomes a question of
> what type of performance hit comes into play when doing this. I suppose
> if you have an option whether to encrypt it or not that would help.  One
> other thing is that it needs to be "decryptable" by owners and
> superusers. 

For sure -- I would not propose doing this unless it was an optional thing.

Joe


Mon, 02 Dec 2002 13:24:31 -0800
Joe Conway <mail@joeconway.com>
Re: protecting prosrc (was Re: [GENERAL] USAGE on schema
Christopher Kings-Lynne wrote:
>>I think this would be a good idea, though there becomes a question of
>>what type of performance hit comes into play when doing this. I suppose
>>if you have an option whether to encrypt it or not that would help.  One
>>other thing is that it needs to be "decryptable" by owners and
>>superusers.
> 
> Surely a more generic column privileges implementation would be better?

I think column privileges is orthogonal to this issue. The ability to 
"protect" stored procedure or package source is common among at least the 
commercial databases I've worked with (as is column level grants).

Joe


Mon, 02 Dec 2002 13:30:13 -0800
Joe Conway <mail@joeconway.com>
Re: protecting prosrc (was Re: [GENERAL] USAGE on schema