Function to dynamically change postgres password-CodePudding

I'm trying to create a function that dynamically changes the password for a client but I get an error "ERROR: syntax error at or near "=" ALTER USER postgres WITH PASSWORD = '$1';"

How do I fix this? Thank you for your time

CREATE OR REPLACE FUNCTION public."Change_password"(
    password character varying)
    RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
BEGIN
   ALTER USER postgres WITH PASSWORD= '$1';

END
$BODY$;

ALTER FUNCTION public."Change_password"(character varying)
    OWNER TO postgres;
    
    SELECT public."Change_password"(
    '10'
)

CodePudding user response：

Apart from the trivial syntax error of the superfluous =, you cannot use a parameter in an ALTER statement. You will have to use dynamic SQL:

EXECUTE format(
           'ALTER ROLE postgres PASSWORD %L',
           $1
        );

Let me add that changing a superuser's password from an application like that does not look like a sane security concept. You are not running your application with a database superuser, are you?

CodePudding user response：

A plain syntax error, according to ALTER USER documentation: there is no = after PASSWORD it should be: ALTER USER postgres WITH PASSWORD '$1';

EDIT: After looking into problem deeper, it seems that whole statement must be run dynamically. Check the answers to similar question: How to change user password in postgreSQL