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

2017-02-22 9:20 GMT+01:00 Joel Jacobson <joel(at)trustly(dot)com>:

> On Wed, Feb 22, 2017 at 9:07 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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().
>
>
can be solution to check a call stack and if it is not expected, than RAISE
some exception? I hope, so I understand to use case.

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();

The solution based on rights is elegant, but in this moment I cannot to see
all possible impacts on performance - because it means new check for any
call of any function. Maybe checking call stack can be good enough - I have
not idea how often use case it it.

Regards

Pavel

> 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.
>