Monday, December 21, 2009

How to change postgresql root password

Environment: RHEL/CentOS

Unix user "postgres" is the super user for postgresql database. In /var/lib/pgsql/data/pg_hba.conf by default, "ident sameuser" is used for db access control, which means user "postgres" can access to any database without password.

Just like mysql, postgresql keep user records in the database as well. If you change the authentication method to "password"/"md5" in pg_hba.conf, you will need to assign a password to "postgres" user in the database in the first place for its initial password is blank and postgresql will denies the connection if no password is provided.

Here is how to change the password.

Make sure method is set to "ident sameuser" for "local".

# sudo su - postgres
# psql -d template1
template1=# ALTER USER postgres WITH PASSWORD 'some password';
# service postgresql restart

1 comment:

Lukáš Zapletal said...

Thanks for "ident sameuser" hint ;-)