password authentication failed for user "postgres" on mac

Question

I have problem creating new psql user because I cannot log in psql as "postgres", I have tried

1. sudo -u postgres psql

2. sudo -u postgres createuser img_site -P -s -e

and they are all ask for password of "postgres" which I don't know. I have tried to change unix password of user "postgres"(I know it's dangerous) and it still tells me: password authentication failed for user "postgres". I also have tried GUI pgAdmin but it's the same error.

I don't know if it's related: I have created a symbolic link

sudo ln -s /private/tmp/.s.PGSQL.5432 /var/pgsql_socket/

in order to get rid of error

createuser: could not connect to database postgres: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

Answer 1

Check pg_hba.conf. it should have a line like this at the top (before all other entries):

local   all             postgres                                peer

This allows local Unix-domain socket access by postgres db user to all databases with no password required.

Now clear and redefine the password for postgres system user (which is automatically created during PostgreSQL installation):

sudo passwd -d postrges
sudo su postgres -c passed

The special thing about this user account is that postgres server allows it to connect to the database, no questions asked.

Now, to define an explicit password for postgres db user using which you can login via other means than local Unix-domain socket connection, run:

su postgres -c psql template1
psql> ALTER USER postgres WITH PASSWORD '<password>';

You will be asked for the postgres system user account password before this command can be run. On successful completion, type \q to quit psql shell, and you are done with resetting the password for postgres db user.