Thread: security permissions for functions

Hi!

How can I assign execute permission to a role for a single function
inside schema.

For example
I create schema example;
function example.simple_select()
and user test_user;

If I grant usage on schema example to user test_user as
GRANT USAGE ON SCHEMA example TO test_user;

I can do select * from example.simple_select();

But I don't want this user to be able to select from other functions, so I
want something like

GRANT EXECUTE ON FUNCTION example.simple_select() TO test_user;

but unless the user has usage rights on schema example he can't select
from this function;

I'm using SECURITY DEFINER so that user can execute function without
permissions for
single commands.

Is this a bug or something?
because grant usage on schema doesn't assume select rights on views and
tables, but it does on functions.


Regards,
Rikard

Rikard Pavelic <rikard.pavelic@zg.htnet.hr> writes:
> Is this a bug or something?

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

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

            regards, tom lane

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.)
>
> You'll need to revoke the default public EXECUTE privilege on any
> functions you don't want to be callable.
>
>             regards, tom lane
>

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?

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

Regards,
Rikard

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.)
> >
> > You'll need to revoke the default public EXECUTE privilege on any
> > functions you don't want to be callable.
>
> 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"?

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.

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

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

Rikard Pavelic wrote:
> 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.)
>>
>> You'll need to revoke the default public EXECUTE privilege on any
>> functions you don't want to be callable.
>>
>>             regards, tom lane
>>
>
> 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?

You can but the default is to allow the execution of all functions.

You need to revoke the existing permission of executing all functions
before you can allow only a single function to be run.

If you want a specific function to be accessed only by selected roles
then you revoke public access when you create it and allow access to the
one or more roles you want to allow access.


--

Shane Ambler
pgSQL@Sheeky.Biz

Get Sheeky @ http://Sheeky.Biz

Bill Moran wrote:
>> 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"?
>
> 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.
>
>


Urgh, I didn't meant it like that ;(
Sorry.

Anyway, maybe I didn't make myself clear enough.
Let's try again ;)

Let's say I have two functions in schema example
example.function1()
example.function2()
and two users
user1 and user2

Is there a way to grant user1 permission to execute function
example.function1(), but not example.function2()
And for user2 to be able to execute
example.function2() and not example.function1()?

To grant user1 permission to execute example.function1()
I need to do GRANT USAGE ON SCHEMA example to user1
But this permissions grants him also ability to execute example.function2()

GRANT EXECUTE ON FUNCTION example.function1() TO user1
or
REVOKE EXECUTE ON FUNCTION example.function1() FROM user1
don't have any affect.

Role PUBLIC don't have access to this schema so that is not an issue.

Regards,
Rikard

Shane Ambler wrote:
>
> You can but the default is to allow the execution of all functions.
>
> You need to revoke the existing permission of executing all functions
> before you can allow only a single function to be run.
>
> If you want a specific function to be accessed only by selected roles
> then you revoke public access when you create it and allow access to
> the one or more roles you want to allow access.
>
>

Oh, I see

Public access was being allowed by default when I created functions.

Oh my ;(
I'll go and hide somewhere now

Thanks,
Rikard

On 08/03/2007 22:21, Rikard Pavelic wrote:

> And for user2 to be able to execute
> example.function2() and not example.function1()?

<snip>

> REVOKE EXECUTE ON FUNCTION example.function1() FROM user1
> don't have any affect.

Surely you mean -

   REVOKE EXECUTE ON FUNCTION example.function1() FROM user2;

- in order to achieve what you described?

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------

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

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

So, I should be able to have "user "barbara" "execute" function
"somefunction()", but, though barbara must not have access of object alpha
lets say for data security reasons (and user sarah does), I could have
function somefunction invoke another function that stores information about
barbara's action to object alpha by changing user context temporarily and
without barbara's knowledge; basically saying within function
"somefunction()" something like "execute function 'someotherfunction()'
impersonating sarah and stop impersonating sarah once someotherfunction
returns.  Much like the way I can log in to Windows or Linux as one user and
temporarily impersonate another while executing a particular program or
administrative function (e,g, log into Linux as a mere mortal, start a bash
shell providing credentials for an admin account, do my admin type stuff and
then close the shell).

Or have I misunderstood you here WRT user ID context?

Ted

"Ted Byers" <r.ted.byers@rogers.com> writes:
> ... 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?

Certainly --- the point here is merely that that isn't the *default*
behavior.  We judged quite some time ago that allowing public execute
access was the most useful default.  Perhaps that was a bad choice, but
I think we're unlikely to change it now ...

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

Check.  Once you revoke the default public execute access, the function
is useless (well, except to superusers) until you grant somebody the
right to call it.

            regards, tom lane

In response to "Ted Byers" <r.ted.byers@rogers.com>:
> >
> > 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.
>
> So, I should be able to have "user "barbara" "execute" function
> "somefunction()", but, though barbara must not have access of object alpha
> lets say for data security reasons (and user sarah does), I could have
> function somefunction invoke another function that stores information about
> barbara's action to object alpha by changing user context temporarily and
> without barbara's knowledge; basically saying within function
> "somefunction()" something like "execute function 'someotherfunction()'
> impersonating sarah and stop impersonating sarah once someotherfunction
> returns.  Much like the way I can log in to Windows or Linux as one user and
> temporarily impersonate another while executing a particular program or
> administrative function (e,g, log into Linux as a mere mortal, start a bash
> shell providing credentials for an admin account, do my admin type stuff and
> then close the shell).
>
> Or have I misunderstood you here WRT user ID context?

No, you're on track.  Have a look at the docs for CREATE FUNCION:
http://www.postgresql.org/docs/8.1/static/sql-createfunction.html

Specifically the section on SECURITY INVOKER and SECURITY DEFINER.
SECURITY DEFINER gives you the equivalent of "setuid" capability

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

On Fri, Mar 09, 2007 at 01:07:23 -0500,
  Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Certainly --- the point here is merely that that isn't the *default*
> behavior.  We judged quite some time ago that allowing public execute
> access was the most useful default.  Perhaps that was a bad choice, but
> I think we're unlikely to change it now ...

At the time this choice was being made it was realized there was going to
be a lot of pain for people updating, as the previous releases didn't
limit access to functions. So it was unlikely to change then, for the same
reasons it is unlikely to change now.