Resetting the root/postgres password for PostgreSQL

The following is required to reset the root/postgres user password for PostgreSQL. The distribution used in my example is CentOS 5.5 and PostgreSQL 8.4.

Note: By default there’s no password for the postgres user.

In step 2 and 5 you will most likely not be using “ident” but rather “password” or “md5?.

1. Shut down PostgreSQL

# service postgresql stop

2. Reset the authentication mechanism (assuming defaults are already being used)

Edit the /usr/lib/pgsql/data/pg_hba.conf file

# nano /usr/lib/pgsql/data/pg_hba.conf

Navigate down to the line that says

local all all ident

Edit it to

local all postgres trust

And now save the file.

3. Start PostgreSQL

# service postgresql start

4. Log in and change the password

# su - postgres

$ psql -d template1 -U postgres

alter user postgres with password 'new_password';

Or alternatively do it all in one go with the following command

> psql -U postgres template1 -c "alter user postgres with password 'new_password';"

5. Reverse the actions you did in step 2

Edit the /usr/lib/pgsql/data/pg_hba.conf file

# nano /usr/lib/pgsql/data/pg_hba.conf

Navigate down to the line that says

local all all trust

Edit it to

local all postgres ident

And now save the file.

6. Start PostgreSQL

# service postgresql start

Success!