Обсуждение: prevent users from seeing pl/pgsql code in pgadmin
Is there any relatively easy way to, uh, prevent your users from seeing some/all of your pl/pgsql code in pgAdmin? :) A by schema solution would be ideal. Merlin
> -----Original Message----- > From: pgadmin-hackers-owner@postgresql.org > [mailto:pgadmin-hackers-owner@postgresql.org] On Behalf Of > Merlin Moncure > Sent: 16 March 2005 16:04 > To: pgadmin-hackers@postgresql.org > Subject: [pgadmin-hackers] prevent users from seeing pl/pgsql > code in pgadmin > > Is there any relatively easy way to, uh, prevent your users > from seeing > some/all of your pl/pgsql code in pgAdmin? :) A by schema solution > would be ideal. Anything we did would be fairly pointless given that there's not much we can do to stop them doing 'SELECT prosrc FROM pg_proc'. I suppose you could remove select permissions from the table, but I wouldn't be surprise if that broke things horribly - it certainly breaks pgAdmin and pg_dump. Regards, Dave.
> > Is there any relatively easy way to, uh, prevent your users > > from seeing > > some/all of your pl/pgsql code in pgAdmin? :) A by schema solution > > would be ideal. > > Anything we did would be fairly pointless given that there's not much we > can do to stop them doing 'SELECT prosrc FROM pg_proc'. I suppose you > could remove select permissions from the table, but I wouldn't be I tried that. While the server allows the revoke, it does nothing. > surprise if that broke things horribly - it certainly breaks pgAdmin and > pg_dump. I also tried hacking the search path and putting a pg_proc table into the public schema. While this fixed select * from pg_proc (but not /df), pgAdmin still pulled the function source. Without checking, I'm assuming pgAdmin prefixes the catalog tables in the metadata queries (aside: should it?). Well, I was hoping for some easy trick but apparently there isn't one. I think this is one for -hackers. Merlin
> -----Original Message----- > From: Merlin Moncure [mailto:merlin.moncure@rcsonline.com] > Sent: 16 March 2005 16:33 > To: Dave Page > Cc: pgadmin-hackers@postgresql.org > Subject: RE: [pgadmin-hackers] prevent users from seeing > pl/pgsql code in pgadmin > > > I also tried hacking the search path and putting a pg_proc table into > the public schema. While this fixed select * from pg_proc > (but not /df), > pgAdmin still pulled the function source. Odd - it didn't here. Every query on pg_proc resulted in a message box telling me it couldn't select from pg_proc - protecting the source, but breaking pgAdmin. > Without checking, I'm > assuming pgAdmin prefixes the catalog tables in the metadata queries > (aside: should it?). Actually, no it doesn't - having just checked my server logs, it doesn't even set the search path to ensure it's sane. I don't suppose anyone ever hacked their master database around enough to cause problems there! > Well, I was hoping for some easy trick but apparently there isn't one. > I think this is one for -hackers. It seems to me that it needs a special privilege to grant select on that *column* to users that didn't create that row or already have appropriate privs. I suspect that would be quite a hack :-( Regards, Dave
> > I also tried hacking the search path and putting a pg_proc table
into
> > the public schema. While this fixed select * from pg_proc
> > (but not /df),
> > pgAdmin still pulled the function source.
>
> Odd - it didn't here. Every query on pg_proc resulted in a message box
> telling me it couldn't select from pg_proc - protecting the source,
but
> breaking pgAdmin.
What did you do exactly? Here's what I tried:
H:\>psql -h 205.217.85.89 test
test=# create user test;
CREATE USER
test=# revoke select on pg_proc from test;
REVOKE
test=# \q
H:\>psql -h 205.217.85.89 -U test test
test=> select * from pg_proc;
proname | pronamespace | proowner | prolan
isstrict | proretset | provolatile | pronargs | prorettype |
| proargnames |
[...]
Merlin Moncure wrote: > Is there any relatively easy way to, uh, prevent your users from seeing > some/all of your pl/pgsql code in pgAdmin? :) A by schema solution > would be ideal. I could think of an additional option for schema names that should be treated as system schema (like the Slony-I and pgAgent schema), but there's no solution to prevent reengineering that's not done on the server side. And coding sophisticated algorithms in plpgsql is for masochists only anyway ;-) Regards, Andreas
> -----Original Message-----
> From: Merlin Moncure [mailto:merlin.moncure@rcsonline.com]
> Sent: 16 March 2005 16:54
> To: Dave Page
> Cc: pgadmin-hackers@postgresql.org
> Subject: RE: [pgadmin-hackers] prevent users from seeing
> pl/pgsql code in pgadmin
>
> > > I also tried hacking the search path and putting a pg_proc table
> into
> > > the public schema. While this fixed select * from pg_proc
> > > (but not /df),
> > > pgAdmin still pulled the function source.
> >
> > Odd - it didn't here. Every query on pg_proc resulted in a
> message box
> > telling me it couldn't select from pg_proc - protecting the source,
> but
> > breaking pgAdmin.
>
> What did you do exactly? Here's what I tried:
>
REVOKE ALL ON TABLE pg_proc FROM public;
Revoking select doesn't help because your test user doesn't have it in
the first place - public does.
pgadmin=# create user test;
CREATE USER
pgadmin=# create table foo(bar int4);
CREATE TABLE
pgadmin=# select relacl from pg_class where relname = 'foo';
relacl
--------
(1 row)
pgadmin=# grant select on table foo to test;
GRANT
pgadmin=# select relacl from pg_class where relname = 'foo';
relacl
---------------------------------------------
{postgres=arwdRxt/postgres,test=r/postgres}
(1 row)
pgadmin=# revoke select on table foo from test;
REVOKE
pgadmin=# select relacl from pg_class where relname = 'foo';
relacl
-----------------------------
{postgres=arwdRxt/postgres}
(1 row)
pgadmin=# grant select on table foo to public;
GRANT
pgadmin=# select relacl from pg_class where relname = 'foo';
relacl
-----------------------------------------
{postgres=arwdRxt/postgres,=r/postgres}
(1 row)
pgadmin=# revoke select on table foo from test;
REVOKE
pgadmin=# select relacl from pg_class where relname = 'foo';
relacl
-----------------------------------------
{postgres=arwdRxt/postgres,=r/postgres}
(1 row)
Thinking about it - is that a bug or a feature?
Regards, Dave.
> -----Original Message----- > From: Dave Page [mailto:dpage@vale-housing.co.uk] > Sent: Wednesday, March 16, 2005 12:06 PM > To: Merlin Moncure > Cc: pgadmin-hackers@postgresql.org > Subject: RE: [pgadmin-hackers] prevent users from seeing pl/pgsql code in > pgadmin > > > > > -----Original Message----- > > From: Merlin Moncure [mailto:merlin.moncure@rcsonline.com] > > Sent: 16 March 2005 16:54 > > To: Dave Page > > Cc: pgadmin-hackers@postgresql.org > > Subject: RE: [pgadmin-hackers] prevent users from seeing > > pl/pgsql code in pgadmin > > > > > > I also tried hacking the search path and putting a pg_proc table > > into > > > > the public schema. While this fixed select * from pg_proc > > > > (but not /df), > > > > pgAdmin still pulled the function source. > > > > > > Odd - it didn't here. Every query on pg_proc resulted in a > > message box > > > telling me it couldn't select from pg_proc - protecting the source, > > but > > > breaking pgAdmin. > > > > What did you do exactly? Here's what I tried: > Ah. Ok, yes this certainly breaks pgAdmin. And true function code protection on the server side seems pretty nasty without some serious hacking. What about this: do think pgAdmin should prevent rendering the sql code for various database schema objects (but especially functions) if the pgAdmin user does not have appropriate access to that object? For example, if user does not have the 'execute' permission, disable sql render of the function object. I think this is pretty reasonable from a security standpoint until such time that the server gets this capability. Merlin
> Merlin Moncure wrote: > > Is there any relatively easy way to, uh, prevent your users from seeing > > some/all of your pl/pgsql code in pgAdmin? :) A by schema solution > > would be ideal. > > I could think of an additional option for schema names that should be > treated as system schema (like the Slony-I and pgAgent schema), but That sounds interesting, how would that be accomplished? > there's no solution to prevent reengineering that's not done on the > server side. And coding sophisticated algorithms in plpgsql is for > masochists only anyway ;-) hmm! I must be a masochist then! I've busily converting our erp server code from cobol to pl/pgsql atm. The performance of pl/pgsql is just amazing...I haven't worked with the other pl languages much (save sql), but I wonder what else would meet my requirements. Merlin
> -----Original Message----- > From: Merlin Moncure [mailto:merlin.moncure@rcsonline.com] > Sent: 16 March 2005 17:20 > To: Dave Page > Cc: pgadmin-hackers@postgresql.org; pgadmin-hackers@postgresql.org > Subject: RE: [pgadmin-hackers] prevent users from seeing > pl/pgsql code in pgadmin > > > What about this: do think pgAdmin should prevent rendering > the sql code > for various database schema objects (but especially functions) if the > pgAdmin user does not have appropriate access to that object? > > For example, if user does not have the 'execute' permission, > disable sql > render of the function object. I think this is pretty > reasonable from a > security standpoint until such time that the server gets this > capability. To be honest I'm not keen to expend time and add to the complexity of the code to add obscurity (I can't in good conscience call it security) that is so easily bypassed. This seems kinda like adding an entry control system to a door next to a large open window. Regards, Dave.
Dave Page wrote: > To be honest I'm not keen to expend time and add to the complexity of > the code to add obscurity (I can't in good conscience call it security) > that is so easily bypassed. This seems kinda like adding an entry > control system to a door next to a large open window. > > Regards, Dave. Well I agree 100% on the security point. I have some short-term needs stemming from management's concern about not being able to control non-privileged users from being able to see our code in pgAdmin. I was expecting your answer so my only recourse is to hack the server sources to meet my needs. :) Merlin