I'm completely helpless, maybe you guys can help me out.
I installed PostgreSQL under my new MacOSX Lion.
When I try to connect to my localhost with pgAdminIII.app it says:
Error connecting to the database: FATAL password authentication failed for user postgres
I just have no idea what to do? Non of my passwords work. Neither my adminpass nor "postgres" nor anyhting else.
I tried to install it again via the console where I found this helpful link: http://www.peerassembly.com/2011/08/…resql-on-lion/ However the problem is, that when I try to run createuser -a -d _postgres the same password problem appears again. I just can't seem to find a solution to this. Always wrong password.
Btw. I have a new User called "PostgreSQL" on my machine after I installed postgres.
Any ideas? I'm so stuck and I really need to make this work.
Best Answer
Easiest solution is to tell postgres to just let you in. Locate your pg_hba.conf. Location of this file depends how you installed it is but it is in the data directory of your postgresql install. Execute the following command in a terminal to let the computer locate it for you.
(sudo is needed because it might be in a folder you do not have access to as a normal user)
There is a lot of comments in this file to explain how it works. What you want todo is to set the authentication method for local and/or 127.0.0.1 connections to trust. When you have done this postgresql will no longer verify your password (thought pgAdmin still tends to ask for it just enter something). When you are in pgadmin you can change the password. Then you change the method in pg_hba.conf back to md5 to secure things.
How did you install PostgreSQL? Personally I find the one click installer very easy.
edit: response to comments
PostgreSQl has it's own internal list of usernames. These names have nothing todo with the OSX usernames. The OSX username PostgreSQL is used to run postgresql's postmaster process with limited access to the system. postgres is the database superuser name created by the one click installer.
The server doesn't listen message is a bit weird because password validation happens only after a connection is established so I would have expected you to get this error earlier to. Maybe you changed something? Anyway I suspect from the error message that pgAdmin is trying to connect to localhost which is resolved to ::1 (ipv6 address) but AFAIK postgresql only listens on ipv4 with the one click installer. When you right click on the Postgresql entry in pgAdmin and select properties you can change the host pgAdmin connects to. Change it to 127.0.0.1 and try again.