Skip site navigation (1) Skip section navigation (2)

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

From: dipti shah <shahdipti1980(at)gmail(dot)com>
To: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-general(at)postgresql(dot)org, pgsql-novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: SET Role doesn't work from Security Definer Function...
Date: 2010-02-23 17:21:24
Message-ID: d5b05a951002230921w4cdd12dap20f83d54d83a9cc3@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-novice
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/
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>

In response to

Responses

pgsql-novice by date

Next:From: Richard HuxtonDate: 2010-02-23 20:33:35
Subject: Re: Minor systax error but not able to resolve it...
Previous:From: dipti shahDate: 2010-02-23 17:15:37
Subject: Re: Minor systax error but not able to resolve it...

pgsql-general by date

Next:From: Tom LaneDate: 2010-02-23 17:36:14
Subject: Re: FOREIGN KEY composite_type.its_field REFERENCES ....
Previous:From: Greg SmithDate: 2010-02-23 17:20:54
Subject: Re: tuning bgwriter in 8.4.2

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group