Give a user permission to ALTER a function
plpgsql, postgresql, privileges
Solution
The manual on `ALTER FUNCTION` is clear on that:
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:
To be able to define a function, the user must have the `USAGE`privilege on the language. ...
To be able to create a function, you must have `USAGE` privilege on the argument types and the return type.
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_%'
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?
Problem
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 ``` 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. 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; ```