Right now in postgres it is impossible to hide stored procedure source
code from non-privileged users...namely the prosrc column of the pg_proc
table. For those of you in a hurry skip to the summary at the end :-).
From my point of view (not sure if others would agree), it would be nice
to suppress being able to see stored procedure source code by any means
(select * from pg_proc, \df, etc) if the user does not have execute
permission on the function. If Matthias's proposed enhancements to
grant/revoke make it in, so much the better.
I tried various tricky ways to do this...
REVOKEing public access to pg_proc (and reGRANTing to specific users)
actually works, but also prevents those users from using various client
tools, such as pg_dump and pgAdmin, because those tools depend on system
SPs for various things.
Created a pg_proc view in the public schema and arranged search path so
that this comes up first. Interestingly, pgAdmin was still able to
render SQL definitions from functions even though it does not explicitly
prefix catalog access with pg_catalog.
Anyways, what I would like to see is pg_proc be implemented as a view
over the real procedure table, which is only available to superusers
(currently pg_proc is readable to public and writable from superusers).
The view definition provides a convention place to CASE prosrc to NULL,
or <access denied> or some such.
Ok. In summary, here's what I'm looking for:
1. Am I totally off my rocker for suggesting users without 'execute'
priv. should not be able to view procedure source.
2. If not, is it unreasonably intrusive to split pg_proc into a hidden
system catalog + a public view.
3. If not, is there any chance that a patch basically doing the above
would get accepted? :)
Merlin