Postgresql – Change user password in PostgreSQL

postgresql

I created a user in PostgreSQL, gave it a password using:

alter user username with password 'new_password';

But when I try to login using phpPgAdmin, it keeps saying use/password incorrect, do I have to do something after alter password, like on MySQL where you have to run flush privileges?

Thanks.

Best Answer

No, no flush is needed. The response given to the client is intentionally vague, to avoid disclosing information to a presumed attacker. Look in the database server's log file for a more complete error message.

Related Solutions

PostgreSQL Connection Issues – User Cannot Connect After Password Change

It's possible that you're being bitten by this PgAdmin bug (changelog):

2012-11-28 AV 1.16.1 Date picker controls returns a full timestamp by default, which can cause inadvertent date changes on jobs and role validty dates. Ignore the time part.

This bug has been seen to set password expiry dates far in the past, such as 1/1/1970. In this case the error message when trying to connect is no different than with a wrong password.

You can check these expiry dates with:

SELECT usename,valuntil FROM pg_user;

and if they're wrong, reset them with:

ALTER USER username VALID UNTIL 'infinity';

and upgrade pgAdmin.

Postgresql – Recover PostgreSQL password with admin access to host

This is easy to fix. Edit pg_hba.conf to add a line like this:

host all postgres 127.0.0.1/32 trust

(You may need to restart Postgres server after editing it).

Then, use this:

psql -d postgres -U postgres

You should be able to get admin access without password now (that's why you added trust rule to pg_hba.conf).

Once this command works, change your password:

ALTER ROLE postgres PASSWORD 'newpassword';

After doing this, edit pg_hba.conf to remove line you added.

Best Answer

Related Solutions

PostgreSQL Connection Issues – User Cannot Connect After Password Change

Postgresql – Recover PostgreSQL password with admin access to host

Related Question