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.
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