Pivotal Knowledge Base

Follow

HowTo - Restricting users from viewing function/stored procedure source code.

Problem

How to prevent users from viewing the source code of the function that they have execute permission on?

Solution

To prevent any users from viewing the source code, you would need to revoke access from pg_proc catalog view.

Example

-- The source code of the function min_max_test() that is currently on schema: public and owner:gpadmin can be viewed by the user a1.

[gpadmin@mdw xx]$ psql -U a1
Password for user a1:
psql (8.2.15)
Type "help" for help.

flightdata=> \df+
                                                                                           List of functions
 Schema |     Name     | Result data type | Argument data types |  Type  | Volatility |  Owner  | Language |                                 Source code                                  | Description
--------+--------------+------------------+---------------------+--------+------------+---------+----------+------------------------------------------------------------------------------+-------------
 public | min_max_test | text             |                     | normal | volatile   | gpadmin | plpgsql  |                                                                              |
                                                                                                           :   DECLARE
                                                                                                           :     row_data RECORD;
                                                                                                           :     _min_value timestamp:= '2014-12-03 12:00:00';
                                                                                                           :   BEGIN
                                                                                                           :
                                                                                                           :   select min("min_value"),max("min_value") into row_data from min_max_table;
                                                                                                           :     RETURN row_data.min||','||row_data.max;
                                                                                                           :
                                                                                                           :   END;
                                                                                                           :
(1 row)

flightdata=> \q

-- Revoking the permission of the pg_proc from the schema.

[gpadmin@mdw xx]$ psql
psql (8.2.15)
Type "help" for help.

flightdata=# revoke all on table pg_proc from public;;
REVOKE
flightdata=# \q

-- The user a1, can no more access that source code, but can execute the function provided the user a1 has all the permission on the tables in the function.

[gpadmin@mdw faisal]$ psql -U a1
Password for user a1:
psql (8.2.15)
Type "help" for help.

flightdata=> \df+
ERROR:  permission denied for relation pg_proc
flightdata=> select min_max_test();
              min_max_test
-----------------------------------------
 2014-12-03 12:00:00,2014-12-03 12:00:00
(1 row)

Comments

Powered by Zendesk