15

I just installed postgresql on a Macbook with brew install postgresql. Then I try to psql, but it requires password and then show psql: FATAL: password authentication failed for user "<myname>".

I have not set up anything, and inputting my mac password does nothing. What should I do now?

Romulus Urakagi Ts'ai
  • 3,699
  • 10
  • 42
  • 68
  • hm. for localhost it should by default use peer authentication, not md5... what's in `pg_hba.conf`?.. (`sudo find / -name pg_hba.conf`) – Vao Tsun Oct 17 '17 at 07:29

6 Answers6

17

So your username probably does not exist, as the default username that ships with the db is postgres.

Further, I was prevented from the submission of an empty password, which is blank by default for the postgres user.

You might try 

cd ~/
sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'postgres';"
Password: YOUR_LOGIN_PWD_HERE (required for sudo)

and then to use 

psql -U postgres
password: postgres

I'm not 100% sure of which SO answer I got this from, perhaps here. Hope this helps.

nrako
  • 2,952
  • 17
  • 30
  • How will this work? for changing the password it will first ask the existing password... – leoOrion Dec 05 '19 at 09:27
  • 2
    In my case, it asked for password with the 2nd command, I fixed it by updating/reseting my postgres user, open System Preference->User Groups->PostgreSQL – aldrien.h Jun 07 '20 at 10:58
  • Thanks aldrien that is really the easiest way, all the other Stack overflow advice was useless. To anyone else, the section is "Users and Groups" in System preferences. – Cheetaiean Jun 09 '20 at 18:44
8

The above did not work for me. The below steps worked for me:

Step 1: Uninstall Postgres using the following steps:

sudo /Library/PostgreSQL/10/uninstall-postgresql.app/Contents/MacOS/uninstall-postgresql

PS: my postgres version is 10

Step 2: Remove Postgresql user

System Preference > userse & Groups > Unlock > remove postgresql user by clicking "-"

Step 3: Remove existing databases

rm -rf /usr/local/var/postgres/*

Step 4: Install and Start Postgres using brew

brew update
brew install postgresql
brew services start postgresql

Step 5: Create database

initdb /usr/local/var/postgres -E utf8

You can start accessing postgres

psql -h localhost -d postgres
Pramida
  • 99
  • 1
  • 2
2

The answer by Pramida almost worked for me... the difference is I was using 9.6 Postgres.

So I ran:

sudo /Library/PostgreSQL/9.6/uninstall-postgresql.app/Contents/MacOS/installbuilder.sh

and somehow that got rid of my username and almost all of postgres user. I think

I then blew away the directory

sudo rm -rf /Library/PostgreSQL/9.6

And then I installed using brew above.

Steve Iribarne
  • 21
  • 2
0

in my case macboook big sur v 11 you should create /var/postgresql@12 in Mackbook/usr/local and open terminal in /opt/homebrew/Cellar/postgresql@12/12.8/bin and run

/opt/homebrew/Cellar/postgresql@12/12.8/bin/initdb -D /usr/local/var/postgresql@12 then run in terminal

 echo 'export PATH="/opt/homebrew/Cellar/postgresql@12/12.8/bin:$PATH"'  >> ~/.zshrc

then

psql -h localhost -p 5432 -d postgres

and enjoy creating user

Ghazaleh Javaheri
  • 1,829
  • 19
  • 25
0

As aldrien.h mentioned in a comment, what worked for me was to simply go to System Preferences > Users & Groups and manually change the password of the PostgreSQL user.

tacticalmovephase
  • 1,119
  • 9
  • 16
-1

rm -rf /usr/local/var/postgres && initdb /usr/local/var/postgres -E utf8

user15221677
  • 1
  • 1
    Probably you should explain why this line answers the question. – Steve Feb 16 '21 at 16:18
  • Welcome to Stack Overflow! While your answer may solve the question, [including an explanation](https://meta.stackexchange.com/q/114762) of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. You can edit your answer to add explanations and give an indication of what limitations and assumptions apply. - [From Review](https://stackoverflow.com/review/late-answers/28337527) – Adam Marshall Feb 16 '21 at 16:44