Query privileges for a function

Question

How can I query the access right privileges for a function?

I want to generate a GRANT EXECUTE script for all the functions in the database.

I am using PostgreSQL 8.3.

Answer 1

I created a function (function chain exactly) to generate the scripts of function (functrion stub, drop script, create script, owner script, grant rights script). We are using dbeaver (I am not sure about other tools) and from the grid results we can copy paste the function definition. The resulting function can also be used to backup function definitions to a table etc. Let me know if you run into issues to it (we are running postgre 8.3 and this works for our functions).

CREATE AGGREGATE public.textcat_all(
  basetype    = text,
  sfunc       = textcat,
  stype       = text,
  initcond    = ''
);


CREATE OR REPLACE FUNCTION public.getfunctionddl(functionOid oid)
  RETURNS text AS
$BODY$
DECLARE
    funcschema text;
    funcname text = NULL;
    paranames text;
    paramodes text;
    paratypes text;
    paraintypes text;
    function_body text = NULL;
    paranames_array text[];
    paramodes_array text[];
    paratypes_array text[];
    params_sql text = '';
    type_name text = '';
    return_type text;
    params_sql_no_name text ='';
    grants text;
    proc_owner text;
    proacl_txt text;
    lanname_txt text;
    function_sql text;
    upper_array int;
    in_param_cnt int = 0;
    out_param_cnt int = 0;
    prosecdef_b bool;
BEGIN
SELECT proargtypes, proallargtypes, proargnames, proargmodes, prosrc, ns.nspname, p.proname, prorettype, proacl, lanname, prosecdef, rolname
INTO paraintypes, paratypes, paranames, paramodes, function_body, funcschema, funcname, return_type, proacl_txt, lanname_txt, prosecdef_b, proc_owner
FROM pg_proc p
INNER JOIN pg_namespace ns ON ns.oid = p.pronamespace
INNER JOIN pg_language pl ON pl.oid = prolang
INNER JOIN pg_roles rl ON rl.oid = proowner
WHERE p.oid = functionOid
AND lanname <> 'internal';

IF COALESCE(funcname, '') = '' THEN
    RETURN NULL;
END IF;

paratypes := REPLACE(COALESCE(paratypes, paraintypes), ',', ' ');
return_type := format_type(return_type::oid,NULL);
return_type := CASE WHEN return_type = 'character varying' THEN 'varchar' ELSE return_type END;
if paranames IS NULL OR paranames = '' THEN
    params_sql := '()';
    params_sql_no_name := '()';
ELSE
    paratypes := REPLACE(REPLACE(paratypes, '{', ''), '}', '');
    paranames := REPLACE(REPLACE(paranames, '{', ''), '}', '');
    paramodes := REPLACE(REPLACE(paramodes, '{', ''), '}', '');

    paratypes_array:=string_to_array(paratypes,' ');
    paranames_array:=string_to_array(paranames,',');
    paramodes_array:=string_to_array(paramodes,',');
    upper_array := array_upper(paratypes_array,1);
    params_sql := '(' || CASE WHEN upper_array > 5 THEN '
    ' ELSE '' END;
    params_sql_no_name := '(';
    FOR i IN array_lower(paratypes_array,1) .. array_upper(paratypes_array,1)
    LOOP
        type_name := format_type(paratypes_array[i]::oid, NULL);
        type_name := CASE WHEN type_name = 'character varying' THEN 'varchar' ELSE type_name END;
        params_sql := params_sql || CASE WHEN paramodes IS NULL OR paramodes = '' THEN '' WHEN paramodes_array[i] = 'o' THEN 'OUT ' ELSE '' END || paranames_array[i] || ' ' || type_name || CASE WHEN i = upper_array THEN ')' WHEN upper_array <= 5 THEN ', ' ELSE ',
    ' END;
        params_sql_no_name := params_sql_no_name || CASE WHEN paramodes IS NULL OR paramodes = '' THEN '' WHEN paramodes_array[i] = 'o' THEN 'OUT ' ELSE '' END || type_name || CASE WHEN i = upper_array THEN ')' ELSE ',' END;
        in_param_cnt := in_param_cnt + CASE WHEN paramodes IS NULL OR paramodes = '' THEN 1 WHEN paramodes_array[i] = 'o' THEN 0 ELSE 1 END;
        out_param_cnt := out_param_cnt + CASE WHEN paramodes IS NULL OR paramodes = '' THEN 0 WHEN paramodes_array[i] = 'o' THEN 1 ELSE 0 END;
    END LOOP;
END IF;

params_sql_no_name := LOWER(quote_ident(funcschema) || '.' || quote_ident(funcname)) || params_sql_no_name || '';
params_sql := quote_ident(funcschema) || '.' || quote_ident(funcname) || params_sql;

drop table if exists tmp_grant;

create temporary table tmp_grant
AS
SELECT
      substring(a, 1, position('=X' in a) -1) as grantee_name
    , substring(a, position('=X' in a) + 3, char_length(a) - position('=X' in a)) as grantor_name
From regexp_split_to_table(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(proacl_txt, '}', ''), '{', ''), CHR(34) || chr(92), ''), CHR(34), ''), chr(92), ''), ',') a;

SELECT public.textcat_all('GRANT EXECUTE ON FUNCTION ' || params_sql_no_name || ' TO ' || quote_ident(grantee_name) || ';

') INTO grants
FROM tmp_grant a
WHERE grantee_name <> ''
AND NOT EXISTS (select * From tmp_grant b where b.grantor_name = a.grantee_name);

function_sql := '-- ' || params_sql_no_name || '
' || CASE WHEN in_param_cnt + out_param_cnt > 0 THEN '-- PARAMS ALL: ' || CAST(in_param_cnt + out_param_cnt as char(3)) || ', IN: ' || cast(in_param_cnt as char(3)) || ', OUT ' || CAST(out_param_cnt as char(3)) || '
' ELSE '' END || '
-- DROP FUNCTION IF EXISTS ' || params_sql_no_name || ';

CREATE OR REPLACE FUNCTION ' || params_sql || '
' || 'RETURNS ' || CASE WHEN return_type = 'record' then 'SETOF record' ELSE return_type END || '
LANGUAGE ' || lanname_txt || CASE WHEN prosecdef_b = true THEN ' SECURITY DEFINER' ELSE '' END || '
AS $' || '$
' || COALESCE(function_body, '') || '
$' || '$;';

function_sql := function_sql || '

-- ALTER FUNCTION ' || params_sql_no_name || ' OWNER TO ' || quote_ident(proc_owner) || ';' || COALESCE('

' || grants, '');

RETURN function_sql;
END $BODY$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION public.getfunctionddl(schema_name_like varchar(256), function_name_like varchar(256), OUT schema_name varchar(256), OUT function_name varchar(256), OUT owner varchar(256), OUT func_oid oid, OUT func_lang varchar(256), OUT ddl text)
RETURNS SETOF record AS $$
SELECT
      CAST(ns.nspname as varchar(256)) as schema_name
    , CAST(p.proname as varchar(256)) as proc_name
    , cast(rolname as varchar(256)) as owner
    , p.oid as func_oid
    , CAST(lanname as varchar(256)) as func_lang
    , public.getfunctionddl(p.oid) as ddl
FROM pg_proc p
INNER JOIN pg_namespace ns ON ns.oid = p.pronamespace
INNER JOIN pg_language pl ON pl.oid = prolang
INNER JOIN pg_roles rl ON rl.oid = proowner
WHERE ns.nspname ILIKE lower(coalesce($1, '%'))
AND p.proname ILIKE lower(coalesce($2, '%'))
AND lanname <> 'internal'
ORDER BY ns.nspname, p.proname, p.oid;
$$ LANGUAGE SQL;

-- HOW TO GET DEFINITION OF THE FUNCTION
SELECT * FROM public.getfunctionddl('%' /*schema_name_like*/,'%' /*function_name_like*/)