Question 271203 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql grant not successful

I am creating a function in postgres which is created but does not have the permissions to be accessed by the application. It is giving me

WARNING: no privileges were granted for "public"

I restarted with "open as administrator" option, this time I got the following error.

ERROR: must be owner of function

Here is my function

-- FUNCTION: traffic.get_user_route_folders_new()

--DROP FUNCTION IF EXISTS traffic.get_user_route_folders_new();

CREATE OR REPLACE FUNCTION traffic.get_user_route_folders_new(
)
RETURNS json
LANGUAGE 'sql'
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
SELECT row_to_json(t) 
FROM  (
    SELECT id, name, parent_folder_id
    FROM traffic.here_traffic_route_folders 
) t
$BODY$;

ALTER FUNCTION traffic.get_user_route_folders_new()
OWNER TO bcadmin;

GRANT EXECUTE ON FUNCTION traffic.get_user_route_folders_new() TO PUBLIC;

GRANT EXECUTE ON FUNCTION traffic.get_user_route_folders_new() TO bcadmin;

when I try to delete function again I get

ERROR: must be owner of function xyz

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Problem here was a bit tricky for me and could not be answered with the limited information I had provided. I was logged-in by user samra while I was running GRANT for a different user. In our environment bcadmin is production user while dev user was me, samra. To confuse things more, there was another user test that I tried and it only had drop function privileges available to both environments.!!


  2. 0

    The error msg shows that you’re attempting to change the function’s ownership or delete it—actions that are restricted to the function’s owner.You must grant the PUBLIC role the EXECUTE privilege on the function in order to resolve the issue. Try the following SQL statement to achieve this:

    GRANT EXECUTE ON FUNCTION traffic.get_user_route_folders_new() TO PUBLIC;

    You must either be the function’s owner or have the DROP FUNCTION access in order to delete the function. Therefore request that the function’s owner give you the ability to DROP FUNCTION if you are not the function’s owner.

    Use the following SQL query to delete the function:

    DROP FUNCTION traffic.get_user_route_folders_new();

    Hope it works for you 🙂

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search