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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: dipti shah <shahdipti1980(at)gmail(dot)com>
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-22 20:34:59
Message-ID: 909.1266870899@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-novice

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

Browse pgsql-general by date

  From Date Subject
Next Message Yeb Havinga 2010-02-22 20:37:55 Re: Sorting performance vs. MySQL
Previous Message Daniele Varrazzo 2010-02-22 20:09:33 Re: Info about concurrent sequential scans

Browse pgsql-novice by date

  From Date Subject
Next Message Jayadevan M 2010-02-23 03:41:32 Re:
Previous Message Scott Bailey 2010-02-22 18:07:56 Re: What is unsecure postgres languages? How to disable them?