Обсуждение: 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 > --