forgot passwords for postgresql users. how do I login?

Question

I forgot passwords for postgresql root user: postgres

So I changed pg_hba.conf to have trust method for everything (this is my local dev box)

local   all         postgres                          trust
local   all         all                               trust
host    all         all         127.0.0.1/32          trust
host    all         all         ::1/128               trust

after restarting the service it is still asking me for password.

@omnipresent:~$ su - postgres
Password: 
su: Authentication failure

Though, I can login as by psql -U postgres

@omnipresent:~$ psql -U postgres
psql (8.4.8)
Type "help" for help.

postgres=#

the mess I am in, at this point I'd just like to make a new role for postgresql and make that user an admin.

How can I do that being in this situation?

The key here is that system accounts (what you are logging into with `su`) and postgresql accounts (what you are logging into with `psql`) are entirely different sets of accounts. Use Antonius's advice to change the system password for the "postgres" system user. — DerfK, May 15 '11 at 20:19

score 6 · Accepted Answer · answered May 15 '11 at 20:08

6

To change the password of any linux user, including the Postgres root:

sudo passwd postgres

Then:

su - postgres
psql -U postgres template1 -c alter user postgres with password 'newpassword';

answered May 15 '11 at 20:08

Antonius Bloch

4,480
6
28
41

Don't forget to tighten up your pg_hba.conf when you are done too. – Antonius Bloch May 15 '11 at 20:12
1

the psql needed quotes for me: `psql -U postgres template1 -c "alter user postgres with password 'newpassword'"` – hultqvist Dec 03 '12 at 14:36

forgot passwords for postgresql users. how do I login?

1 Answers1