Re: [HACKERS] GRANT EXECUTE ON FUNCTION foo() TO bar();

On Wed, Feb 22, 2017 at 9:07 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> Usage of X functions can be locked in schema.

I think that's also a good idea. Both are useful I think. They solve
two different use-cases.

If there are multiple callers of a private function within a schema,
it would be useful if you could just declare it PRIVATE,
to prevent any top-level usage of the function,
and to prevent any other function than functions in the same schema
from calling it.

This would be similar to how e.g. a private method in a Java class can
be called by any other method in the same class.
I think that's also a useful idea, but a different use-case.

This would be useful if you have lots of callers of a function,
and it would of course be tedious if you had to explicitly GRANT EXECUTE
for each function that you want should be allowed to call the function.
In that case, a PRIVATE declaration of the function would be better.

But if it is important a function is absolutely not called by any
other function than a a single very specific function,
then it would be better with a specific GRANT EXECUTE ON FUNCTION
foo() to bar() in the function definition file for bar(),
which would make it clear to a developer looking at the bar() source
code that the function is only supposed to be called by foo().

CREATE OR REPLACE FUNCTION outer_func() RETURNS integer AS $$
BEGIN RETURN inner_func();
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION inner_func() RETURNS integer AS $$
DECLARE stack text;
BEGIN GET DIAGNOSTICS stack = PG_CONTEXT; RAISE NOTICE E'--- Call Stack ---\n%', stack; RETURN 1;
END;
$$ LANGUAGE plpgsql;

SELECT outer_func();

But like I said, I also like your PRIVATE idea. I think most functions
in my schemas would actually be PRIVATE, and only a few would be
PUBLIC, since you usually have more internal functions in a schema,
that are not supposed to be called outside of the schema and doesn't
even make sense outside of the schema.