Thread: SECURITY DEFINER changes CURRENT_USER?

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

Hi Chris,

You want to use "session_user".

> 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?

Cheers