Re: [GENERAL] security permissions for functions
On Fri, Mar 09, 2007 at 01:07:23 -0500, Tom Lane <[EMAIL PROTECTED]> 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. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] security permissions for functions
In response to "Ted Byers" <[EMAIL PROTECTED]>: > > > > 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] security permissions for functions
"Ted Byers" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] security permissions for functions
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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] security permissions for functions
In response to "Ted Byers" <[EMAIL PROTECTED]>: > > - Original Message - > From: "Bill Moran" <[EMAIL PROTECTED]> > > > In response to Rikard Pavelic <[EMAIL PROTECTED]>: > > > >> 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 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] security permissions for functions
On 3/8/07, Rikard Pavelic <[EMAIL PROTECTED]> 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 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
Re: [GENERAL] security permissions for functions
On 08/03/2007 22:21, Rikard Pavelic wrote: And for user2 to be able to execute example.function2() and not example.function1()? 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 [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] security permissions for functions
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] security permissions for functions
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 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] security permissions for functions
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 [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] security permissions for functions
- Original Message - From: "Bill Moran" <[EMAIL PROTECTED]> To: "Rikard Pavelic" <[EMAIL PROTECTED]> Cc: "Tom Lane" <[EMAIL PROTECTED]>; Sent: Thursday, March 08, 2007 3:18 PM Subject: Re: [GENERAL] security permissions for functions In response to Rikard Pavelic <[EMAIL PROTECTED]>: 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 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] security permissions for functions
In response to Rikard Pavelic <[EMAIL PROTECTED]>: > 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 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] security permissions for functions
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] security permissions for functions
Rikard Pavelic <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
