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: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: 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 09:58:16
Message-ID: d5b05a951002230158x4d4f148exa1c509201ec3270a@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-novice
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 <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> dipti shah <shahdipti1980(at)gmail(dot)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
>

In response to

Responses

pgsql-novice by date

Next:From: dipti shahDate: 2010-02-23 11:25:52
Subject: Minor systax error but not able to resolve it...
Previous:From: Jayadevan MDate: 2010-02-23 03:41:32
Subject: Re:

pgsql-general by date

Next:From: Carsten KropfDate: 2010-02-23 10:00:46
Subject: Implementing an Index Access Method in PG 8.4
Previous:From: Magnus HaganderDate: 2010-02-23 09:55:20
Subject: Re: [GENERAL] pg_dump: aborting because of version mismatch

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