On Thu, Feb 12, 2015 at 2:53 AM, Saimon Lim <aimon.slim@gmail.com> wrote:
> For clarification - I run the commands using psql program.
>
> 2015-02-11 12:54 GMT+03:00 Saimon Lim <aimon.slim@gmail.com>:
>>
>> Hi
>> I want to hide my own stored procedures' bodies from the specific user.
>> As far as I know, procedure's body is visible in the pg_catalog.pg_proc
>> table.
>>
>> So, I did the following:
>> REVOKE ALL ON pg_catalog.pg_proc FROM PUBLIC;
>>
>> And after it, when user tries:
>> SELECT * from pg_proc;
>>
>> The following error occurs:
>> ERROR: permission denied for relation pg_proc
>>
>> It turns out that user don't have access to the body of the procedure.
>> But I still can get stored procedure's body using
>> \sf function_name
>> or with
>> \ef function_name
>>
>> So, how can I completely hide my own stored procedure's bodies from this
>> user?
oddly, psql does not echo (via -E switch) \sf and \ef which is small bug IMO.
however, it's pretty easy to see what's going on via tailing the
server log. What's happening here is psql is calling the function
pg_catalog.pg_get_functiondef...you can revoke execute on that in a
similar way.
merlin