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