March 2024
M T W T F S S
 123
45678910
11121314151617
18192021222324
25262728293031

Categories

March 2024
M T W T F S S
 123
45678910
11121314151617
18192021222324
25262728293031

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!

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>