Re: Re-enabling SET ROLE in security definer functions

Turner, Ian wrote:
>> -----Original Message-----
>> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
>> Actually, I don't find that to be a given.  Exactly what use-cases have
>> you got that aren't solved as well or better by calling a SECURITY DEFINER
>> function owned by the target role?
> 
> Oh, that's easy: If you want to do the equivalent of setreuid(geteuid(), getuid()); that is, if you want to drop
privilegesfor a particular operation. Our particular use case is that we want to evaluate an expression provided by the
callerbut with the caller's privileges.
 

Now *that's* what we should focus on. That's a reasonable use case, but
it doesn't seem like SET ROLE quite cuts it. For starters, wouldn't it
be possible for the caller's expression to call SET ROLE or RESET ROLE
to regain the privileges?

You could write a user-defined C function that does the same that
VACUUM/ANALYZE etc. do (now that we've fixed the vulnerabilities). Ie.
something like:

GetUserIdAndSecContext(&save_userid, &save_sec_context);
SetUserIdAndSecContext(<userid with less privileges>, save_sec_context |
SECURITY_RESTRICTED_OPERATION);
<call function>
/* Restore userid and security context */
SetUserIdAndSecContext(save_userid, save_sec_context);

No modifications to the server code required. Another question is, could
we provide some built-in support for dropping privileges like this?

--  Heikki Linnakangas EnterpriseDB   http://www.enterprisedb.com