Re: change user password - Mailing list pgsql-general

> hi, is it possible to change the current user's password from a
> function/stored procedure , I mean,  is there a system function/stored
> procedure to do it? like the dbo.sp_password found in adaptive server
> anywhere.
>


CREATE OR REPLACE FUNCTION public.alter_password(name, name)
  RETURNS "varchar" AS
'
DECLARE
  l_user ALIAS FOR $1;
  l_pwd ALIAS FOR $2;
  CMD VARCHAR;
BEGIN
  CMD := \'ALTER USER "\' || l_user || \'" WITH ENCRYPTED PASSWORD \' ||
\'\'\'\' || l_pwd || \'\'\'\' || \' VALID UNTIL \' || \'\'\'\' ||
CURRENT_DATE+INTERVAL \'30 days\' || \'\'\'\';
  EXECUTE CMD;
  RETURN \'ALTER USER\';
END;
'
  LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;


-- Of course, you'ld want to be careful about whom was granted execute
permission on this.

-- BMT