Most common security definer functions in GPDB

1. CREATE OR REPLACE FUNCTION public.sdf_pg_stat_activity()

RETURNS SETOF pg_stat_activity AS

$BODY$

DECLARE

r RECORD ;

BEGIN

FOR r IN SELECT * FROM pg_stat_activity LOOP

RETURN NEXT r ;

END LOOP ;

RETURN ;

END ;

$BODY$

LANGUAGE plpgsql VOLATILE SECURITY DEFINER;

2. CREATE OR REPLACE FUNCTION public.sdf_pg_cancel_backend(v_pid integer)

RETURNS boolean AS

$BODY$

DECLARE

b boolean;

a text;

BEGIN

SELECT usename from pg_stat_activity where procpid=v_pid INTO a;

if a<>session_user then

RAISE NOTICE 'Process user is % but the current user is %, cannot cancel.', a,session_user;

return 0;

else SELECT pg_cancel_backend(v_pid) INTO b;

RETURN b;

END IF;

END;

$BODY$

LANGUAGE plpgsql VOLATILE SECURITY DEFINER;

3. CREATE OR REPLACE FUNCTION public.analyze_table(tname text)
RETURNS void AS
$BODY$
BEGIN
     EXECUTE 'ANALYZE ' || tname || ';' ;
EXCEPTION
     WHEN undefined_table THEN
         RAISE EXCEPTION 'table "%" does not exist', tname;
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER;

4. CREATE OR REPLACE FUNCTION public.truncate_table(tname name)
RETURNS void AS
$BODY$
BEGIN
     EXECUTE 'TRUNCATE TABLE '|| tname ||';';
EXCEPTION
     WHEN undefined_table THEN
         RAISE EXCEPTION 'table "%" does not exist', tname;
END;
5. CREATE OR REPLACE FUNCTION public.reorg_table(tname name)
RETURNS void AS
$BODY$
BEGIN
     EXECUTE 'alter table ' || tname || ' set with (reorganize=true);' ;
EXCEPTION
     WHEN undefined_table THEN
         RAISE EXCEPTION 'table "%" does not exist', tname;
END;

$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER;

Comments