How to reset the postgres super user password on mac os x

Posted by Andrew Barinov on Super User See other posts from Super User or by Andrew Barinov
Published on 2012-01-26T01:25:47Z Indexed on 2012/07/06 3:18 UTC
Read the original article Hit count: 347

I installed postgres on my mac running 10.6.8 and I would like to reset the password for the postgres user (I believe this is the super user password) and then restart it.

All the directions I found do not work because I think my user name is not recognized by pg as having authority to change the password. (I am on the admin account of my mac)

Here is what I tried:

Larson-2:~ larson$ psql -U postgres
Password for user postgres: 
psql (9.0.4, server 9.1.2)
WARNING: psql version 9.0, server version 9.1.
         Some psql features might not work.
Type "help" for help.

postgres=# ALTER USER postgres with password 'mypassword'
postgres-# \q

and for restart I did:

Larson-2:~ larson$ su postgres -c 'pg_ctl -D /opt/local/var/db/postgresql84/defaultdb/ restart
> 

Which didn't work, as the password remained the same as it was before. Can someone provide directions for doing this and for making sure it's recognized by PG?

Update

I went ahead and edited the pg_hba.conf file located in /Library/PostgreSQL/9.1/data and set the settings as follows:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

However, like before, the password stayed the same after I changed it. I am not sure what further steps I can take from here.

© Super User or respective owner

Related posts about authentication

Related posts about postgresql