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