[SQL] SECURITY DEFINER changes CURRENT_USER?

[Chris Travers]

Hi all;

I found an unexpected behavior while trying to write a function to allow 
users to change their own passwords.  The function is as follows:

CREATE OR REPLACE FUNCTION change_password(VARCHAR)
RETURNS BOOL AS '
DECLARE
   username VARCHAR;
   CMD VARCHAR;
   password ALIAS FOR $1;
BEGIN
   SELECT INTO username CURRENT_USER;
   CMD := ''ALTER USER '' || username || '' WITH PASSWORD '';
   CMD := CMD || '''''''' || password || '''''''';   
   EXECUTE CMD;
   RETURN TRUE;
end;
' LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER

I would expect this to change the password of the user currently logged 
in but instead it changes MY password.  Evidently when a function is 
called which is set to SECURITY DEFINER, it changes the context of the 
current user.  The CURRENT_USER then returns the name of the definer 
rather than the invoker of the function.

So this being said-- are there any workarounds that don't allow anyone 
to change anyone else's password?

Best Wishes,
Chris Travers
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])