How to Restrict Users from Viewing Function or Stored Procedure Source Code.

Products

• VMware Tanzu Greenplum

Issue/Introduction

This article describes how to prevent users from viewing the source code of the function that they have executed permission on.

 

Environment

Resolution

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)

Additional Information

+ Environment:

Pivotal Greenplum Database (GPDB) all versions