Re: security permissions for functions - Mailing list pgsql-general

----- Original Message -----
From: "Bill Moran" <wmoran@potentialtech.com>
To: "Rikard Pavelic" <rikard.pavelic@zg.htnet.hr>
Cc: "Tom Lane" <tgl@sss.pgh.pa.us>; <pgsql-general@postgresql.org>
Sent: Thursday, March 08, 2007 3:18 PM
Subject: Re: [GENERAL] security permissions for functions


> In response to Rikard Pavelic <rikard.pavelic@zg.htnet.hr>:
>
>> Tom Lane wrote:
>> > No, it's operating as designed. Per the GRANT reference page:
>> > : Depending on the type of object, the initial default privileges may
>> > : include granting some privileges to PUBLIC. The default is no public
>> > : access for tables, schemas, and tablespaces; CONNECT privilege and
>> > TEMP
>> > : table creation privilege for databases; EXECUTE privilege for
>> > functions;
>> > : and USAGE privilege for languages. The object owner may of course
>> > revoke
>> > : these privileges. (For maximum security, issue the REVOKE in the same
>> > : transaction that creates the object; then there is no window in which
>> > : another user may use the object.)
>> >

This seems clear enough.

>> > You'll need to revoke the default public EXECUTE privilege on any
>> > functions you don't want to be callable.

As does this.
>>
>> Hmm, so the answer to my question
>> "How can I assign execute permission to a role for a single function
>> inside schema."
>> is I can't?
>
> How did you interpret "do it like this" to mean "you can't do it"?
>
I too can not understand how he came to this conclusion, unless it wasn't
obvious to him how to grant certain permissions to roles.

I am curious, though.  I shape my understanding of this using a metaphore of
private, protected and public member functions in C++ classes.  There is, of
course, no point in having a function that can't be called under any
circumstances, but it is often useful to have a variety of protected and
private functions (and other members) that can only be called by other
member functions or member frunctions of derived classes.  Does the
permission granting procedure for functions work in a similar fashion?  Can
I make a function as a part of a schema that is executable only by the owner
and other functions in the schema, and no-one else, and still have a
publically callable function in that schema invoke the "private" function?
Or is my C++ based understanding leading me astray here?

> REVOKE ALL ON <function name> FROM PUBLIC;
>
>> So this basically means that I can't fine tune the permissions through
>> functions, but I
>> can through views and tables?
>> This looks like a bug in design to me ;(
>
> Relax.  You (somehow) misunderstood Tom.
>
I wonder if he's worried about granting permissions to roles or groups
rather than to individual users.  I mean the obvious statement, for the fine
tuning he appears to me to want to do, would be to follow the REVOKE
statement you show with a GRANT statement for a specific user.    At least
that is what I'd infer from what you and Tom wrote.  Did I misunderstand
either of you, or what Rikard is worried about?

Ted