Re: PLPGSQL 'SET SESSION ROLE' problems ..

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)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
>

--