Alvaro Herrera pointed out that pg_read_file requires superuser access which
these users won't have so revoking access to the function code should be
possible.

Joshua D. Drake suggested revoking pg_proc but that isn't the source code,
it just has the definition of the functions.

If it isn't a feature today, what table has the source code in it? Maybe I
can revoke that.


Jon
> -----Original Message-----
> From: Jonah H. Harris [mailto:jonah.harris@gmail.com]
> Sent: Friday, December 14, 2007 3:04 PM
> To: Bill Moran
> Cc: Joshua D. Drake; Roberts, Jon; pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] viewing source code
>
> On Dec 14, 2007 2:03 PM, Bill Moran <wmoran@collaborativefusion.com>
> wrote:
> > I disagree here. If they're connecting remotely to PG, they have no
> > direct access to the disk.
>
> pg_read_file?
>
> --
> Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
> EnterpriseDB Corporation | fax: 732.331.1301
> 499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
> Edison, NJ 08837 | http://www.enterprisedb.com/

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

On Dec 17, 2007 8:11 AM, Roberts, Jon <Jon.Roberts@asurion.com> wrote:
> Alvaro Herrera pointed out that pg_read_file requires superuser access which
> these users won't have so revoking access to the function code should be
> possible.
>
> Joshua D. Drake suggested revoking pg_proc but that isn't the source code,
> it just has the definition of the functions.
>
> If it isn't a feature today, what table has the source code in it? Maybe I
> can revoke that.

the table is pg_proc. you have to revoke select rights from public
and the user of interest. be aware this will make it very difficult
for that user to do certain things in psql and (especially) pgadmin.
it works.

a better solution to this problem is to make a language wrapper for
pl/pgsql that encrypts the source on disk. afaik, no one is working on
th is. it would secure the code from remote users but not necessarily
from people logged in to the server. the pg_proc hack works ok
though.

merlin

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Roberts, Jon wrote:
> Alvaro Herrera pointed out that pg_read_file requires superuser access which
> these users won't have so revoking access to the function code should be
> possible.
>
> Joshua D. Drake suggested revoking pg_proc but that isn't the source code,
> it just has the definition of the functions.

Actually I suggested using a obfuscation module.

>
> If it isn't a feature today, what table has the source code in it? Maybe I
> can revoke that.

If your pl is perl or plpgsql it will be in the prosrc (pro_src?) column
in pg_proc.

Joshua D. Drake

>
>
> Jon
>> -----Original Message-----
>> From: Jonah H. Harris [mailto:jonah.harris@gmail.com]
>> Sent: Friday, December 14, 2007 3:04 PM
>> To: Bill Moran
>> Cc: Joshua D. Drake; Roberts, Jon; pgsql-performance@postgresql.org
>> Subject: Re: [PERFORM] viewing source code
>>
>> On Dec 14, 2007 2:03 PM, Bill Moran <wmoran@collaborativefusion.com>
>> wrote:
>>> I disagree here. If they're connecting remotely to PG, they have no
>>> direct access to the disk.
>> pg_read_file?
>>
>> --
>> Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
>> EnterpriseDB Corporation | fax: 732.331.1301
>> 499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
>> Edison, NJ 08837 | http://www.enterprisedb.com/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>


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

On Mon, 17 Dec 2007, Merlin Moncure wrote:

> the table is pg_proc. you have to revoke select rights from public
> and the user of interest. be aware this will make it very difficult
> for that user to do certain things in psql and (especially) pgadmin.
> it works.
>
> a better solution to this problem is to make a language wrapper for
> pl/pgsql that encrypts the source on disk. afaik, no one is working on
> th is. it would secure the code from remote users but not necessarily
> from people logged in to the server. the pg_proc hack works ok
> though.
>

Another enhancement that would improve this situation would be to
implement per column permissions as the sql spec has, so that you could
revoke select on just the prosrc column and allow clients to retrieve the
metadata they need.

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings