Thread: PLPGSQL 'SET SESSION ROLE' problems ..

Hi,

Is it possible to change the current role to another arbitrary role
using a PLPGSQL function?

In essence I've a function "authorise" created by the db superuser with
'SECURITY DEFINER' specificied.

However from within plpgsql the 'superuser' attribute isn't honored when
trying to change roles (ie: the non 'superuser' rules for role change
must be honoured).

Postgresql version 8.2.3

Is this a bug? .. If not, how do I achieve an 'adhoc' change of role
from within the "rules system"?

tnx,

   -greg

--
Dr. Greg Wickham                  Program Manager, e-Research
Phone: +61 2 6222 3552                                 AARNet
Mob: +61 4 0785 4566              CSIRO, Building 9, Banks St
Email: greg.wickham@aarnet.edu.au         Yarralumla ACT 2600

"Greg Wickham" <Greg.Wickham@aarnet.edu.au> writes:
> Is it possible to change the current role to another arbitrary role
> using a PLPGSQL function?

Yes.

> However from within plpgsql the 'superuser' attribute isn't honored when
> trying to change roles

IIRC we deliberately restrict inheritance of superuser status.  If you
want to argue there's a bug here you need to provide specific details of
what you did, as well as an argument why superuser status should be more
laxly controlled.

            regards, tom lane

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> To: Greg Wickham

> > However from within plpgsql the 'superuser' attribute isn't honored
> > when trying to change roles
>
> IIRC we deliberately restrict inheritance of superuser
> status.  If you want to argue there's a bug here you need to
> provide specific details of what you did, as well as an
> argument why superuser status should be more laxly controlled.

Hi Tom,

In the example there is a connection to a DB using a user with virtually
no privledges ('guest'). As part of the rules system changes to the
current 'role' of this connection should only succeed when the rules
system is happy.

 so for example (Hopefully this is easy to follow):

 1/ DB connection as role 'guest'

 2/ sql: select authorise( 'user1', 'password' )

    function authorise changes current role to 'user1' and returns
session key

 3/ all SQL commands can be checked (grant / allow) against user 'user1'

 example 2. Using the 'session key'

 1/ DB connection as role 'guest'

 2/ sql: select authorise( 'session key' )

      function authorise looks up the session key and assigns the
current role to 'user1'.

  example 3. Failure

  1/ DB connection as role 'guest'

  2/ sql: select authorise ('user1', 'wrong password' )

    function authorise would determine that the password was wrong
and would leave
    (or assign) the current role to 'guest'.

I've read the 'set role' and 'set session' a few times but it's not what
I'm after. I really want the role change mechanism to be wrapped in a
function to do the appropriate sanity checks etc before it succeeds.

I'm open to suggestions as to how else to achive this thou.

tnx,

   -greg

>             regards, tom lane
>

--