Re: SET Role doesn't work from Security Definer Function... - Mailing list pgsql-general

From Tom Lane
Subject Re: SET Role doesn't work from Security Definer Function...
Date
Msg-id 909.1266870899@sss.pgh.pa.us
Whole thread Raw
In response to SET Role doesn't work from Security Definer Function...  (dipti shah <shahdipti1980@gmail.com>)
Responses Re: SET Role doesn't work from Security Definer Function...  (dipti shah <shahdipti1980@gmail.com>)
List pgsql-general
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

pgsql-general by date:

Previous
From: Daniele Varrazzo
Date:
Subject: Re: Info about concurrent sequential scans
Next
From: Yeb Havinga
Date:
Subject: Re: Sorting performance vs. MySQL