Function to dynamically change postgres password

Question

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'
)

PASSWORD= THE equal SIGN is WRONG!!!! BEWARE

U.V.
– U.V.

2023-03-27 14:57:57 +00:00
Commented Mar 27, 2023 at 14:57 — U.V.
– U.V., Commented Mar 27, 2023 at 14:57

Laurenz Albe · Accepted Answer · 2022-01-13 07:16:20Z

1

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?

answered Jan 13, 2022 at 7:16

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Michael Efraimidis Over a year ago

I'm running a localhost server for a client and I wanted the client to easily change the password if need be.

Korfu · Accepted Answer · 2022-01-13 08:36:49Z

0

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

edited Jan 13, 2022 at 8:36

answered Jan 12, 2022 at 21:52

Korfu

8511 gold badge8 silver badges23 bronze badges

5 Comments

Michael Efraimidis Over a year ago

This is not correct for my version. It's setting the password to $1

Korfu Over a year ago

Well so the error does not appear anymore and code works. If you want it to be changed dynamically just replace ‘$1’ with ‘password’ parameter

Michael Efraimidis Over a year ago

That doesn't work either, it sets the password to string "password" without quotes. Changing the input parameter to something else like pass doesn't work either.

Korfu Over a year ago

ALTER USER postgres WITH PASSWORD password; - is that what you have tried ?

Michael Efraimidis Over a year ago

It throws syntax error.

Collectives™ on Stack Overflow

Function to dynamically change postgres password

2 Answers 2

1 Comment

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related