How to Restrict the use of a Security Definer Function

Products

• VMware Tanzu Greenplum

Issue/Introduction

A Security Definer function is the function that can only be executed with the privileges of the user who created it. And, for the newly created functions, the execute privilege is granted to the public. If you want to restrict the use of a security definer function to only some users, you must revoke the default public privileges and then grant the execute privilege selectively.

 

Environment

Resolution

One role "502277954" created a "Security Definer" function. Another role "502277957" can also select this function.

gpadmin=> set role "502277954"; 
SET 
gpadmin=> create table geagp_systems_dba.starfish_test (logtime timestamp); 
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'logtime' as the Greenplum Database data distribution key for this table. 
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. 
CREATE TABLE 
gpadmin=> CREATE OR REPLACE FUNCTION geagp_systems_dba.func_test2() RETURNS integer AS $$ begin insert into geagp_systems_dba.starfish_test(logtime) 
select current_timestamp; 
return 1; 
end; 
$$ 
LANGUAGE plpgsql SECURITY DEFINER NO SQL; 
CREATE FUNCTION 
gpadmin=> select geagp_systems_dba.func_test2(); 
func_test2 
------------ 
1 

gpadmin=# set role "502277957";
SET
gpadmin=> select geagp_systems_dba.func_test2();
 func_test2
------------
1

Revoke the default PUBLIC privileges on function and then grant execute privilege to role "502277954", below are some tips that might be helpful:

gpadmin=> set role gpadmin;
SET
gpadmin=# REVOKE ALL ON FUNCTION geagp_systems_dba.func_test2() FROM PUBLIC;
REVOKE
gpadmin=# GRANT EXECUTE ON FUNCTION geagp_systems_dba.func_test2() TO "502277954";
GRANT
gpadmin=# set role "502277957";
SET
gpadmin=> select geagp_systems_dba.func_test2();
ERROR:  permission denied for function func_test2

 

Additional Information

To avoid having a window where the new function is accessible to all, create it and set the privileges within a single transaction.

BEGIN;
CREATE FUNCTION check_password(uname TEXT, pass TEXT) ... SECURITY DEFINER;
REVOKE ALL ON FUNCTION check_password(uname TEXT, pass TEXT) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION check_password(uname TEXT, pass TEXT) TO admins;
COMMIT;

+ Environment:

• Pivotal Greenplum Database (GPDB) 4.3.x
• Operating System- Red Hat Enterprise Linux 6.x