Re: SET Role doesn't work from Security Definer Function...

This issue is driving me crazy. Could any one please suggest me any
workaround?

For summary of issue,

1. I don't want any users to perform any action on mydb schema without
using my stored procedure. So I revoke ALL permissions from mydb schema and
assigned only USAGE permissions.
2. As my stored procedure allows creating table in mydb schema and users
have only USAGE permissions on mydb schama, I have to defined my stored
procedure with SECURITY DEFINER so that it allows to create table in mydb
schema.
3. To prevent creating unauthenticated foreign references to other
tables, I want to make sure that current user has the required permissions
to create table before creating table. For this I have to use SET ROLE to
current user but it is not allowed in SECURITY DEFINER context.

Any help would be much appreciated.

Thanks,
Dipti
On Tue, Feb 23, 2010 at 10:51 PM, dipti shah <shahdipti1980(at)gmail(dot)com>wrote:

> No, I tried that but that can't be done in my requirements because my
> function has to be run in super user context to create the table in schema
> where normal users have only USAGE permissions. If I remove SECURITY DEFINER
> then my stored procedure will be failed for all users by saying "permission
> denied on schema myschema".
>
> Moreover, I want to run only create table code in normal user context and
> other things in stored procedure should be done in super user context.
>
> I tried all possible ways but couldn't find to get out of this yet.
>
> Thanks,
> Dipti
>
>
> On Tue, Feb 23, 2010 at 8:36 PM, Alvaro Herrera <
> alvherre(at)commandprompt(dot)com> wrote:
>
>> dipti shah escribió:
>>
>> > 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.
>>
>> Shouldn't it be the other way around? The normal function calls the
>> security-definer one.
>>
>> --
>> Alvaro Herrera
>> http://www.CommandPrompt.com/ <http://www.commandprompt.com/>
>> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>>
>
>