How to change in postgresql password of the user using SQL

Question

How to change in postgresql password of the user using SQL. I have user (alex) and his password (e.g. pass) i need to change using sql statement his password to NULL...

Answer 1

The syntax for changing a user's password is

ALTER USER username WITH PASSWORD 'password';

Answer 2

You want

ALTER ROLE alex SET PASSWORD TO NULL

You will of course have to do this as a Postgres superuser.

Unfortunately, that doesn't let you log in with a blank password. You can only log in without a password if your pg_hba.conf entry specifies an auth type of 'trust' instead of 'md5' or 'password'.

So this SQL command is just cleaning up the password for a user that used to have one, but who is now trusted to get in without a password. You can't actually authenticate with a blank password. The distinction is slight.