CODEWITHSUNDEEP
postgresqlpostgresql-9.4
Shamil Yakupov
Shamil Yakupov

Reputation: 5469

GRANT EXECUTE not granting access to related tables in function

I have user that should have access only for 2 functions, so I granted them to him:

REVOKE ALL ON FUNCTION some_func(firstid INT) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION some_func(firstid INT) TO some_user;

But I'm getting weird error:

42501: permission denied for relation some_table

when trying to access my function with:

SELECT * FROM offers_get(0);

My function looks like:

CREATE OR REPLACE FUNCTION public.some_func(firstid integer)
 RETURNS TABLE(/* something */)
 LANGUAGE plpgsql
AS $function$
DECLARE
    /* something */
BEGIN
    /* some other logic */
    RETURN QUERY SELECT * FROM some_table;
END;
$function$

So, access to related tables was not granted? How to grant access only for function execute?

Upvotes: 0

Views: 78

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269673

You want to define the function using SECURITY DEFINER (see the documentation).

By default, Postgres uses the permissions of the caller, not the definer.

CREATE OR REPLACE FUNCTION public.some_func(firstid integer)
 RETURNS TABLE(/* something */)
 LANGUAGE plpgsql
 SECURITY DEFINER
 . . .

Upvotes: 1

Related Questions