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"?

Benefic

Add some context on how you installed postgresql in the first place. For example if you used postgres.app the postgres user doesn't even exist. Applying instructions out of context is a frequent problem in postgres on Mac OS since it's released in many different packagings. The symbolic link mentioned is also an instance of that class of issues. – Andris

@DanielVérité I used graphical installer. – Benefic

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 passwd

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.

Monocyte

I have added the line to pg_hba.conf but it doesn't help. It still ask me for password. – Benefic

what if I dont find a user with named postgres in pg_hba.conf? Do I need to add this entry manually? – Solidarity

Answer 2

sudo doesn't want the password of the account you're switching to, it wants the password of the account you're switching from. It also requires that you be an admin (or otherwise listead in /etc/sudoers). su, on the other hand, requires the password for the account you're switching to.

Tinsley

I know this and I don't think this is the problem. Thank you. – Benefic

Answer 3

I was trying to setup postgres for Ruby on Rails and I was getting the the password authentication failed for user error. Check if the server is actually running:

pg_ctl -D /usr/local/var/postgres status

If you get

pg_ctl: no server running

Run

pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

Also you must include localhost in psql.

psql -U postgres -h localhost

Snazzy

still asks for password. – Mariettamariette

Answer 4

This worked for me:

ALTER USER my_user WITH PASSWORD 'my_password';

Babbittry

Answer 5

I tried a lot of different tricks on my Macbook. And here is the one which helped me.On the screen, right click PostgreSQL14 for 'properties', then in 'connection' tab try to change port number from 5432 to 5433(or vise versa) -> save. And try to open again, for password use "postgres". Should work

Mazdaism

Answer 6

It got work's for me when I tried below approach in MAC m1 air

1. Go to "data" folder of Postgres's install directory /Library/PostgreSQL/16/data

2. select data folder(Initially it is locked state, don't allow you to open it)

3.On your Mac, select the item, then choose File > Get Info or press Command-I.

4.Click the arrow next to Sharing & Permissions to expand the section. Click the pop-up menu next to your username to see the permissions settings.

5.If you’re not logged in as an administrator, you may need to click the lock to unlock it, then enter an administrator name and password (or use Touch ID or your Apple Watch).

6.Change the permissions to either Read & Write or “Read only”.

Then Try to login with credentials that you provided at the time of installation

In my case:

host : localhost port : 5432 name : postgres pass : root

Nannie

Answer 7

Anyway, it may be useful for someone: Sometimes it happens that your Mac/Macbook reboots, or goes into deep sleep mode. Then our primary port 5432 (or whatever port you are using) is already busy. That's why something like cloning of our postgres user is happening. To fix it:

1.Let's see what the devils are sitting on our port.

sudo lsof -i :5432

2.Now we kill these clones:

sudo pkill -u postgres

Voilà! Now log in to our pgAdmin and be happy that our password is now correct.

Ibadan