Обсуждение: lowering privs in SECURITY DEFINER function
Hi, A customer of ours has for a long time the desire to be able to return to the previous privilege level (i.e. the caller privs) inside a SECURITY DEFINER function. I find that this notion is not at all covered in the SQL standard, yet the use case is certainly valid from a security-concious point of view. (Consider, for example, that you may want to enable a user to run some operation to which he is authorized, but you want to carry out some privileged operation before/after doing so: for example, disable triggers, run an update, re-enable triggers.) An easy way to "somewhat solve" this problem is to provide another security definer function that calls the intermediate operation, owned by a role with lower privileges. But this doesn't really solve the problem, because you are then providing a way to return to an arbitrary role, not to the specific role that's calling the function. I think part of the solution here would be to be able to tell what's the "previous role", i.e. the one just below the topmost stack item in the authorization stack. Then, at least you know what to call SET SESSION AUTHORIZATION to. Thoughts? This area seems fraught with security problems, yet it is a necessary piece on the security puzzle. -- Álvaro Herrera <alvherre@alvh.no-ip.org>
On Apr 6, 2011, at 5:33 PM, Alvaro Herrera wrote: > Hi, > > A customer of ours has for a long time the desire to be able to return > to the previous privilege level (i.e. the caller privs) inside a > SECURITY DEFINER function. I find that this notion is not at all > covered in the SQL standard, yet the use case is certainly valid from a > security-concious point of view. > > (Consider, for example, that you may want to enable a user to run some > operation to which he is authorized, but you want to carry out some > privileged operation before/after doing so: for example, disable > triggers, run an update, re-enable triggers.) > > An easy way to "somewhat solve" this problem is to provide another > security definer function that calls the intermediate operation, owned > by a role with lower privileges. But this doesn't really solve the > problem, because you are then providing a way to return to an arbitrary > role, not to the specific role that's calling the function. > > I think part of the solution here would be to be able to tell what's the > "previous role", i.e. the one just below the topmost stack item in the > authorization stack. Then, at least you know what to call SET SESSION > AUTHORIZATION to. > > Thoughts? This area seems fraught with security problems, yet it is a > necessary piece on the security puzzle. That's really strange considering that the new role may not normally have permission to switch to the original role. Howwould you handle the case where the security definer role is not the super user? How would you prevent general SQL attacks when manually popping the authentication stack is allowed? Cheers, M
On Wed, 2011-04-06 at 18:33 -0300, Alvaro Herrera wrote: > (Consider, for example, that you may want to enable a user to run some > operation to which he is authorized, but you want to carry out some > privileged operation before/after doing so: for example, disable > triggers, run an update, re-enable triggers.) I'm not sure I understand the use case. If it's within one function, why not just do it all as the privileged user in the security definer function? The only reason I can think of it if you wanted to make the unprivileged operation arbitrary SQL. But in the example you give, with triggers disabled, it's not safe to allow the user to execute arbitrary operations. In other words, if you wrap an unprivileged operation inside of privileged operations, it seems like the unprivileged operation then becomes privileged. Right? Regards,Jeff Davis
Excerpts from Jeff Davis's message of mié abr 06 19:39:27 -0300 2011: > On Wed, 2011-04-06 at 18:33 -0300, Alvaro Herrera wrote: > > (Consider, for example, that you may want to enable a user to run some > > operation to which he is authorized, but you want to carry out some > > privileged operation before/after doing so: for example, disable > > triggers, run an update, re-enable triggers.) > > I'm not sure I understand the use case. If it's within one function, why > not just do it all as the privileged user in the security definer > function? > > The only reason I can think of it if you wanted to make the unprivileged > operation arbitrary SQL. But in the example you give, with triggers > disabled, it's not safe to allow the user to execute arbitrary > operations. The point is precisely to let the caller to execute whatever operation he is already authorized to execute, given his regular permissions. (The actual request from the customer says "with londiste triggers removed", in case it makes any difference. I am not familiar with Londiste.) So there's a desire to check for permissions to execute the arbitrary SQL call; the security-definer wrapper is really only needed to remove the londiste triggers, not the operation in the middle. One idea we floated around was to make the before and after operations be part of security-definer function, and the user function would call those. But the problem with this is that the user is then able to call (say) only the "before" function and forget to call the "after" function to cleanup, which would be a disaster. Note that another possible option to go about this would be to have some sort of "commit trigger"; the "before" function would set a flag stating that the transaction is in unclean mode, and this commit trigger would raise an error if the "after" function was not called to cleanup properly. The same customer has asked for "commit triggers" in the past, so perhaps we should explore that option instead. Thoughts? > In other words, if you wrap an unprivileged operation inside of > privileged operations, it seems like the unprivileged operation then > becomes privileged. Right? Well, it's in the hands of the creator of the overall wrapper function to ensure that the before/after functions are "safe" in that sense. -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Excerpts from A.M.'s message of mié abr 06 19:08:35 -0300 2011: > That's really strange considering that the new role may not normally > have permission to switch to the original role. How would you handle > the case where the security definer role is not the super user? As I said to Jeff, it's up to the creator of the wrapper function to ensure that things are safe. Perhaps this new operation should only be superuser-callable, for example. > How would you prevent general SQL attacks when manually popping the > authentication stack is allowed? The popping and pushing operations would be restricted. You can only pop a single frame, and pushing it back before returning is mandatory. -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Apr 8, 2011, at 7:20 PM, Alvaro Herrera wrote: > Excerpts from A.M.'s message of mié abr 06 19:08:35 -0300 2011: > >> That's really strange considering that the new role may not normally >> have permission to switch to the original role. How would you handle >> the case where the security definer role is not the super user? > > As I said to Jeff, it's up to the creator of the wrapper function to > ensure that things are safe. Perhaps this new operation should only be > superuser-callable, for example. > >> How would you prevent general SQL attacks when manually popping the >> authentication stack is allowed? > > The popping and pushing operations would be restricted. You can only > pop a single frame, and pushing it back before returning is mandatory. It might be worth thinking about extending this functionality to cover the case for connection pooling. If some SQL can "re-tool"an existing connection to have the properties of a new connection by a different role, then that would reduce theuse-case of connection pooling. If that authorization chain can be pushed and popped by a password or some security token,for example, then that would cover the use cases I mention in this thread: http://archives.postgresql.org/pgsql-general/2006-04/msg00917.php Cheers, M
On Wed, Apr 6, 2011 at 6:39 PM, Jeff Davis <pgsql@j-davis.com> wrote: > On Wed, 2011-04-06 at 18:33 -0300, Alvaro Herrera wrote: >> (Consider, for example, that you may want to enable a user to run some >> operation to which he is authorized, but you want to carry out some >> privileged operation before/after doing so: for example, disable >> triggers, run an update, re-enable triggers.) > > I'm not sure I understand the use case. If it's within one function, why > not just do it all as the privileged user in the security definer > function? > > The only reason I can think of it if you wanted to make the unprivileged > operation arbitrary SQL. But in the example you give, with triggers > disabled, it's not safe to allow the user to execute arbitrary > operations. > > In other words, if you wrap an unprivileged operation inside of > privileged operations, it seems like the unprivileged operation then > becomes privileged. Right? It's maybe worth noting here that what's being asked for is roughly what you get from UNIX's distinction between euid and ruid. Many programs that run setuid root perform a few operations that require root privileges up front, and then drop privs. To what degree that model applies in an SQL environment I'm not sure, but it might be worth looking at some of the parallels, as well as some of the ways that the UNIX mechanism has managed to cause all sorts of privilege escalation bugs over the years, to make sure we don't repeat those mistakes. I *think* Windows has some kind of similar privilege-dropping mechanism. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Excerpts from Robert Haas's message of dom abr 10 13:37:46 -0300 2011: > It's maybe worth noting here that what's being asked for is roughly > what you get from UNIX's distinction between euid and ruid. Many > programs that run setuid root perform a few operations that require > root privileges up front, and then drop privs. To what degree that > model applies in an SQL environment I'm not sure, but it might be > worth looking at some of the parallels, as well as some of the ways > that the UNIX mechanism has managed to cause all sorts of privilege > escalation bugs over the years, to make sure we don't repeat those > mistakes. Thanks for mentioning that. It made me recall a couple of articles I read some time ago, http://lwn.net/Articles/416494/ and http://www.cis.upenn.edu/~KeyKOS/ConfusedDeputy.html -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Apr 8, 2011, at 6:17 PM, Alvaro Herrera wrote: >> In other words, if you wrap an unprivileged operation inside of >> privileged operations, it seems like the unprivileged operation then >> becomes privileged. Right? > > Well, it's in the hands of the creator of the overall wrapper function > to ensure that the before/after functions are "safe" in that sense. How do you do that in a safe way though? The problem you run into is if you have a pair of operations that need to be doneas a superuser, and something else you want to do in the middle as a non-super user. The goal here is to ensure thatyou MUST perform both operations out of the pair. The problem is: how do you enforce that the cleanup will actually happen? Right now, we're doing this through a single function that performs the first SU action, does whatever the user asked, andthen performs the second SU action. I don't think there's any other way to do that, at least not in 8.3. To make this robust, you can't just provide secdef functions that wrap your operations that require SU: that would mean thatanyone could still call them, which means they could potentially call the 1st operation and not the 2nd. I suspect there might be clever ways around this issue, but ISTM that there should be some reasonable way to handle this. BTW, Alvaro did some digging and discovered that the SQL spec allows you to drop to a lower privilege state, but then there'sno way you can regain your higher-level privileges until the code block that requested lower privileges exits. Thatwould actually work fine here, so long as you defined a sub-transaction (ie: an embedded BEGIN; END; block in plpgsqlas a code block. With such a facility, you could do: CREATE FUNCTION () SECURITY DEFINER AS $$ BEGIN; privileged operation... BEGIN; SET ROLE original_user; UNprivileged operation... END; privileged operation... END; $$; -- Jim C. Nasby, Database Architect jim@nasby.net 512.569.9461 (cell) http://jim.nasby.net