You are correct Tom that I want to perform some portion of function as postgres user and other portion as current user.
As per you suggestion I did refactor and separated the portion that needs to be executed as superuser to another function. But the thing is PostGreSQL recognize when I call this separated funtion from my original SECURITY DEFINER function and gives the same error. :(
For your reference I did something like this:
1. Create Function foo1 .... (this is without SECURITY DEFINER where I am using SET ROLE to current user).
2. Create Function foo2 with SECURITY DEFINER ...
spi_exe_query("select foo1()"); ==> Here it throws the error.
I am helpless now. Could you tell me what could be done in this situation?
Thanks,
Dipti
On Tue, Feb 23, 2010 at 2:04 AM, Tom Lane
<tgl@sss.pgh.pa.us> wrote:
dipti shah <
shahdipti1980@gmail.com> writes:
> I have just noticed that "SET ROLE" doesn't work from security definer
> function. I don;t know why but it clearly gives the error that SET role
> doesn;t work in security definer context.
> If I create function in postgres user with Security Definer enabled, it will
> allow to create any table with any foreign references etc...So I am setting
> role to current_user in my function and then creating a table to make sure
> that user has the appropriate privilege.
Well, if you are trying to set the role back to current, why don't you
just not have the function be security definer in the first place?
I suppose the answer to that is that you want it to do some things as
superuser and some things not. In which case, you need to refactor so
that those two classes of things are done by different functions.
regards, tom lane