Message-ID: <3F1A2396.4050907@travelamericas.com>
Date: Sat, 19 Jul 2003 22:07:34 -0700
From: Chris Travers <chris@travelamericas.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.3) Gecko/20030312
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: SECURITY DEFINER changes CURRENT_USER?
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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