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 <t...@sss.pgh.pa.us> wrote: > dipti shah <shahdipti1...@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. > > This is intentional because allowing it creates security holes. > > > 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 >