Re: Private or publice function

On Tue, Jan 11, 2005 at 18:36:17 +0000,
Richard Huxton <dev(at)archonet(dot)com> wrote:
> Nirmalya Lahiri wrote:
> >Thanks Richard,
> > for your reply. Now I am explaining you what I want.
> >Let I have 4
> >functions......function_1(),function_2(),function_3(),function_4().
> [snip]
> > Now after creating these functions, I can easily call every function
> >from psql prompt. But I want to encapsulate function_2() and
> >function_3() withen function_1(), so that no one can call
> >function_2() and function_3() from psql prompt and from function_4(),
> >only from withen the function_1() I can call function_2() and
> >function_3().
> >
> > Is it possible in PostgreSQL?
>
> Not elegantly - plpgsql only has PostgreSQL's naming and permissions
> structure to use. Perhaps the simplest is to create a schema "private"
> only accessible to user "U1". Place function2(),function(3) in schema
> "private".
> Place function1() in the public schema, making sure you create it as
> user "U1" and mark it "SECURITY DEFINER".
>
> So - everyone can execute function1(), but only user U1 can run
> function2()/3(). Since function1() runs as user U1 everything should
> just work.

This is the right idea, but you can do it more elegantly using the
EXECUTE privilege for functions. Postgres grants EXECUTE access to
PUBLIC by default for functions, as they are usually meant to be
public. But you can revoke that access and use the scheme above without
needing to futz with schemas.