Give a user permission to ALTER a function
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 haveCREATE
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 haveCREATE
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?
Juan Carlos Oropeza
Note to all SO beginner: Please read (and embrace!) Jon Skeet's helpful hints on how to write a good question (or at least his short version here ) - one that has a chance that someone can answer it.
Updated on June 04, 2022Comments
-
Juan Carlos Oropeza almost 2 years
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 theOWNER
of the function.But if that is the case, only one user (owner) can
ALTER
the function. So how would I change theOWNER
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;