Thread: securing pg_proc

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

"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
> 1. Am I totally off my rocker for suggesting users without 'execute'
> priv. should not be able to view procedure source.

1. I don't particularly buy that, no.  Why draw the line at seeing
source code?  The mere name and argument list might be considered
'sensitive' information.

2. We haven't had a policy of hiding schema information in the past, and
I don't think it's the sort of thing that can usefully be bolted on
piecemeal.

3. The people who ask for this sort of thing frequently don't want those
with execute permission to look at the source, either, so your proposed
solution really isn't going to satisfy anybody.
        regards, tom lane

> "Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
> > 1. Am I totally off my rocker for suggesting users without 'execute'
> > priv. should not be able to view procedure source.
>
> 1. I don't particularly buy that, no.  Why draw the line at seeing
> source code?  The mere name and argument list might be considered
> 'sensitive' information.

Not a big deal considering where the line gets drawn, but this is moot
considering your next point.  However, I'm a little unclear about where
you stand on the relative merit (whatever the implementation) of hiding
at the very least prosrc from non-priv users.

> 2. We haven't had a policy of hiding schema information in the past,
and
> I don't think it's the sort of thing that can usefully be bolted on
> piecemeal.

Well, at least one system catalog is a view + function (pg_locks),
albeit for completely different reasons.

> 3. The people who ask for this sort of thing frequently don't want
those
> with execute permission to look at the source, either, so your
proposed
> solution really isn't going to satisfy anybody.

It wouldn't?  Your points #1 and #3 could be addressed by configuring
the view one way or another...so ISTM you are arguing for the
flexibility of a view, not against...

If the view approach is out, are there any other alternatives to
consider?  Adding a new priv. for functions to GRANT seems to also pull
pg_proc towards a view.

Merlin

"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
> However, I'm a little unclear about where you stand on the relative
> merit (whatever the implementation) of hiding at the very least prosrc
> from non-priv users.

OK, in words of one syllable: I'm agin it.

I think your proposal is a hack that solves one aspect of the general
schema-info-protection problem, for just one category of requirements,
and it does so in an intrusive and expensive fashion that will be a
permanent backwards-compatibility problem whenever someone comes up
with a more general solution.  (Sooner or later, someone probably
will, because we hear requests for this kind of thing often enough.)
Also, it imposes the policy, the runtime cost to enforce the policy,
and the compatibility issues on everyone whether they want that policy
or not.

There are other ways to solve it that have fewer side-effects.  For
instance you could imagine inventing a new PL "plprotected" in which
the prosrc entry is a reference to some row in another table.  The PL
just fetches the function body from that table and passes it off to the
"real" PL.  (A possible variant: the function body stays in prosrc, but
is encrypted.)  This approach makes the feature optional and imposes its
costs only on those who want to pay them.
        regards, tom lane

> "Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
> > However, I'm a little unclear about where you stand on the relative
> > merit (whatever the implementation) of hiding at the very least
prosrc
> > from non-priv users.
>
> OK, in words of one syllable: I'm agin it.
> I think your proposal is a hack that solves one aspect of the general
> schema-info-protection problem, for just one category of requirements,
> and it does so in an intrusive and expensive fashion that will be a
> permanent backwards-compatibility problem whenever someone comes up
> with a more general solution.  (Sooner or later, someone probably
> will, because we hear requests for this kind of thing often enough.)
> Also, it imposes the policy, the runtime cost to enforce the policy,
> and the compatibility issues on everyone whether they want that policy
> or not.

I don't buy the runtime cost issue at all.  Internally the server does
not access pg_proc via sql so any overhead of a view is totally cut out
of the equation.  But the other points are, I suppose, fairly
compelling.

However, I still maintain that views are the perfect security mechanism
for system catalogs.  Imagine that all the system catalogs were all
views, and could be redefined or even dropped by the dba.  They would
present exactly the same stuff they do now, with rules presenting them
just like the original table.

Now, for extreme situations like that government server that requires
catalog security, the dba can redefine the various rules for the catalog
views and lock various things down, using whatever methodology he/she
sees fit.  This would not affect the internal workings of the server but
would affect the client tools, which is really what I'm after.

> There are other ways to solve it that have fewer side-effects.  For
> instance you could imagine inventing a new PL "plprotected" in which
> the prosrc entry is a reference to some row in another table.  The PL
> just fetches the function body from that table and passes it off to
the
> "real" PL.  (A possible variant: the function body stays in prosrc,
but
> is encrypted.)  This approach makes the feature optional and imposes
its
> costs only on those who want to pay them.

You've lost me here.  What does securing the PL storage from low-priv
users have anything to with which PL I'm using?  This is squarely a
database administration concern.  If my requirement is to secure the
code on the server, then it should be irrespective of the PL language
choice.
Merlin

On Thu, 2005-03-17 at 13:36 -0500, Merlin Moncure wrote:
> However, I still maintain that views are the perfect security mechanism
> for system catalogs.  Imagine that all the system catalogs were all
> views, and could be redefined or even dropped by the dba.  They would
> present exactly the same stuff they do now, with rules presenting them
> just like the original table.

> Now, for extreme situations like that government server that requires
> catalog security, the dba can redefine the various rules for the catalog
> views and lock various things down, using whatever methodology he/she
> sees fit.  This would not affect the internal workings of the server but
> would affect the client tools, which is really what I'm after.

Configurable security? Sounds great to me.

This is exactly how Teradata implements this; they even present you with
a choice of views to load ontop of the catalog tables. Secure/Not. You
choose.

...but in this case:

> ( A possible variant: the function body stays in prosrc,
> but
> > is encrypted.) 

That sounds OK for this situation. Doesn't it Merlin?

That would open up the market for pay-for add-ons to PostgreSQL. It
would also encourage packaged app vendors to port their code to
PostgreSQL, in the knowledge that their source code would be secure.
[Dont jump on my case... not everybody thinks open source is cool enough
to actually do it themselves... and I accept their position]

Best Regards, Simon Riggs

> On Thu, 2005-03-17 at 13:36 -0500, Merlin Moncure wrote:
> > However, I still maintain that views are the perfect security
mechanism
> > for system catalogs.  Imagine that all the system catalogs were all
> > views, and could be redefined or even dropped by the dba.  They
would
> > present exactly the same stuff they do now, with rules presenting
them
> > just like the original table.
>
> > Now, for extreme situations like that government server that
requires
> > catalog security, the dba can redefine the various rules for the
catalog
> > views and lock various things down, using whatever methodology
he/she
> > sees fit.  This would not affect the internal workings of the server
but
> > would affect the client tools, which is really what I'm after.
>
> Configurable security? Sounds great to me.
>
> This is exactly how Teradata implements this; they even present you
with
> a choice of views to load ontop of the catalog tables. Secure/Not. You
> choose.

That would be just great.  Now why wouldn't this work?

> ...but in this case:
>
> > ( A possible variant: the function body stays in prosrc,
> > but
> > > is encrypted.)
>
> That sounds OK for this situation. Doesn't it Merlin?

Well, I think the idea has merit but there are complexities in the
implementation.

1. when is the encryption key first introduced (create function?)  or is
it somehow supplied by the server?
2. is the encryption key stored?  If so, where?
3. can the su decrypt functions without the key? (remembering he can
just attach a debugger and grab the source at some point)
4. can the decryption be integrated with the user security model, so
that decryption is tied to some other function?

In short, how could this be made to work? :-).


Merlin