授予用户更改功能的权限 [英] Give a user permission to ALTER a function

问题描述

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

I try to ALTER a function with a new user and I get the error:

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

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

我必须授予用户什么权限，以便他可以ALTER该功能? 我发现的唯一方法是使用户成为函数的OWNER.

What permission do I have to give to a user so he can ALTER that function? The only way I found was to make the user the OWNER of the function.

但是如果是这种情况，则只有一个用户(所有者)可以ALTER该功能.那么如何更改所有功能的OWNER?

But if that is the case, only one user (owner) can ALTER the function. So how would I change the OWNER for all functions?

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;

推荐答案

The manual on ALTER FUNCTION is clear on that:

您必须拥有该功能才能使用ALTER FUNCTION. 架构，您还必须在新架构上具有CREATE特权. 要更改所有者，您还必须是直接或间接的 新拥有角色的成员，并且该角色必须具有CREATE 功能模式的特权. (这些限制强制要求 更改所有者并没有做任何您不能通过删除和执行的操作 重新创建功能.但是，超级用户可以更改以下内容的所有权: 任何功能.)

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.)

强调粗体的人.
您还需要几个基本权限才能创建函数. 每个文档:

Bold emphasis mine.
You also need a couple of basic privileges to create functions. Per documentation:

要能够定义函数，用户必须具有语言的USAGE特权.
...

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

要创建函数，必须对参数类型和返回类型具有USAGE特权.

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

简单的解决方案是以超级用户身份更改功能. (默认超级用户为postgres，但任何用户 都可以成为超级用户.)

The simple solution would be make changes to functions as superuser. (Default superuser is postgres, but any user can be made superuser.)

如果您确实需要更改所有功能的所有权，那么就可以解决问题:

If you really need to change ownership on all functions, this would do the trick:

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架构.
有关更多详细信息和说明，请参阅 DROP FUNCTION，但不知道参数的数量/类型?

Restricted to the public schema.
For more details and explanation refer to this more complete answer on dba.SE.
Also closely related:
DROP FUNCTION without knowing the number/type of parameters?

这篇关于授予用户更改功能的权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！