postgresql - 授予用户更改功能的权限

Question

我尝试用新用户ALTER 函数，但出现错误:

ERROR:  must be owner of function ACases
********** Error **********

ERROR: must be owner of function ACases
SQL state: 42501

我必须授予用户什么权限才能ALTER该功能？我找到的唯一方法是让用户成为函数的 OWNER。

但如果是这种情况，则只有一个用户(所有者)可以ALTER 函数。那么我该如何更改所有函数的 OWNER 呢？

CREATE OR REPLACE FUNCTION public."ACases"(caseid integer)
  RETURNS boolean AS
$BODY$
DECLARE
BEGIN
        RETURN FALSE;
END;
$BODY$
  LANGUAGE plpgsql;
ALTER FUNCTION public."ACases"(integer) OWNER TO postgres;

GRANT ALL PRIVILEGES ON FUNCTION public."ACases"(integer) TO user_name;

Answer 1

manual on ALTER FUNCTION对此很清楚:

You must own the function to use ALTER FUNCTION. To change a function's schema, you must also have CREATE privilege on the new schema. To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have CREATE privilege on the function's schema. (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the function. However, a superuser can alter ownership of any function anyway.)

大胆强调我的。
您还需要一些基本权限才能创建函数。 Per documentation:

To be able to define a function, the user must have the USAGEprivilege on the language.
...

To be able to create a function, you must have USAGE privilege on the argument types and the return type.

简单的解决方案是以 super 用户身份 更改函数。 (默认 super 用户是 postgres，但任何用户都可以成为 super 用户。)

如果您确实需要更改所有函数的所有权，可以这样做:

SELECT string_agg('ALTER FUNCTION '
            || quote_ident(n.nspname) || '.' 
            || quote_ident(p.proname) || '(' 
            || pg_catalog.pg_get_function_identity_arguments(p.oid)
            || ') OWNER TO foo;'
          , E'\n') AS _sql
FROM   pg_catalog.pg_proc p
JOIN   pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE  n.nspname = 'public';
-- AND p.relowner <> (SELECT oid FROM pg_roles WHERE rolname = 'foo')
-- AND p.proname ~~ 'f_%'

仅限于 public 架构。
有关详细信息和解释，请参阅 this more complete answer on dba.SE .
也密切相关:
DROP FUNCTION without knowing the number/type of parameters?