How to alter the current user’s password with ALTER USER syntax

Question

I’m using PostgreSQL and I’d like to know how to change password of my current user. I know I can use \password but I’m curious how to make it with the ALTER USER syntax.

I know that if my current user is e.g. Yui, simply ALTER USER Yui WITH PASSWORD ‘123’ would work. But can I avoid explicitly setting Yui here? I tried something like ALTER USER (SELECT CURRENT_USER) WITH PASSWORD ‘QWE’ but only received syntax error.

Answer 1

USER is deprecated. From ALTER USER

ALTER USER is now an alias for ALTER ROLE.

Try:

ALTER ROLE current_user WITH PASSWORD 'QWE';

In Postgres 14+ it can be:

ALTER ROLE current_role WITH PASSWORD 'QWE';

You also can use SESSION_USER if you want access to the user that initiated the session.

See

https://www.postgresql.org/docs/current/sql-alterrole.html