Re: [HACKERS] lowering privs in SECURITY DEFINER function

2011-04-11 Thread Alvaro Herrera
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 alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] lowering privs in SECURITY DEFINER function

2011-04-11 Thread Jim Nasby
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 done as a superuser, and something 
else you want to do in the middle as a non-super user. The goal here is to 
ensure that you 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, and then 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 that anyone 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's no way you can regain your 
higher-level privileges until the code block that requested lower privileges 
exits. That would actually work fine here, so long as you defined a 
sub-transaction (ie: an embedded BEGIN; END; block in plpgsql as 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   j...@nasby.net
512.569.9461 (cell) http://jim.nasby.net



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] lowering privs in SECURITY DEFINER function

2011-04-10 Thread Robert Haas
On Wed, Apr 6, 2011 at 6:39 PM, Jeff Davis pg...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] lowering privs in SECURITY DEFINER function

2011-04-08 Thread Alvaro Herrera
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 alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] lowering privs in SECURITY DEFINER function

2011-04-08 Thread Alvaro Herrera
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 alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] lowering privs in SECURITY DEFINER function

2011-04-08 Thread A.M.

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 the use-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
-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


[HACKERS] lowering privs in SECURITY DEFINER function

2011-04-06 Thread Alvaro Herrera
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 alvhe...@alvh.no-ip.org

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] lowering privs in SECURITY DEFINER function

2011-04-06 Thread A.M.

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. How would 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
-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] lowering privs in SECURITY DEFINER function

2011-04-06 Thread Jeff Davis
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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers