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

In response to "Ted Byers" <r.ted.byers@rogers.com>:
>
> ----- Original Message -----
> From: "Bill Moran" <wmoran@potentialtech.com>
>
> > 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?

No.  At least not at the design level.  Whether you might possibly be
able to accomplish that in effect, I'm not sure.

Functions are controlled by the same ACL mechanism that tables and everything
else follows.  Thus you have the idea of "user id X may do Y with object Z"
i.e. "user "barbara" may "execute" function "somefunction()".

But there's no real way to alter those permissions outside of changing the
user ID context.

ACLs have pretty much everything you'd want from ACLs, though.  Think more
like UNIX filesystem permissions than OO public/private/protected.  You can
take an executable on the filesystem and control what UIDs can execute it,
and you can do the same thing with functions in Postgres.

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

I think you understand.  You can grant permissions by user or group, though,
and best practice usually dictates allocating ACLs to groups, then adding
users to groups where appropriate.

--
Bill Moran
http://www.potentialtech.com