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

From David Legault
Subject Re: security permissions for functions
Date
Msg-id e0b20d410703081448ja18f5d8u11b17d0836dedbbc@mail.gmail.com
Whole thread Raw
In response to Re: security permissions for functions  (Rikard Pavelic <rikard.pavelic@zg.htnet.hr>)
List pgsql-general


On 3/8/07, Rikard Pavelic <rikard.pavelic@zg.htnet.hr> wrote:
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.

I have a GRANT USAGE on schema "name" to PUBLIC

and I have a GRANT EXECUTE on function1 to user1 and a GRANT EXECUTE on function2 to user 2 and I can't access function2 as user1.

But when I created the functions I did a REVOKE EXECUTE on functionN from PUBLIC


Exception: SQLSTATE[42501]: Insufficient privilege: 7 ERROR: permission denied for function function2 ...

Regards,
Rikard

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings


David

pgsql-general by date:

Previous
From: Raymond O'Donnell
Date:
Subject: Re: security permissions for functions
Next
From: Jorge Godoy
Date:
Subject: Re: Setting week starting day