Re: How to hide stored procedure's bodies from specific user

On Fri, Feb 13, 2015 at 5:17 AM, Saimon Lim <aimon.slim@gmail.com> wrote:
> Thanks for your help
>
> I want to restrict some postgres users as much as possible and allow them to
> execute a few my own stored procedures only.
>
> If I block access using:
>
> REVOKE ALL ON pg_catalog.pg_proc FROM PUBLIC;
> REVOKE ALL ON FUNCTION pg_catalog.pg_get_functiondef(oid) FROM PUBLIC;
>
> the user will fail to select procedure's body from pg_catalog.pg_proc and
> using psql \sf of \ef.
> Is this method correct?

[FYI -- please try to avoid top-posting]

That is correct. However, I'm not guaranteeing that it's a 100% clean
solution...there may be other loopholes you have to close also.  For
example, if you get an unhandled error inside a function the database
will send the error context back to the client.  Basically you're
playing 'whack-a-mole' -- however, it's not too difficult to hide
stored procedure bodies from the *casual observer* if you take certain
precautions.

A stronger approach would be write a wrapper to pl/pgsql that
encrypted the function bodies (this is not trivial), or to write them
in C as John noted.

merlin