login issues for superuser on MacOS X

I have successful installed PostgreSQL 8.4 on our Mac Server (10.6.4). In the installation wizzard I set a password for the superuser "postgres". I could also successful change the settings in the pg_hbd.conf file to establish a connection to the db server by pgAdminIII and other stuff. But now in the Terminal (console) I cannot login as user postgres. When typing < su postgres > and inserting the password I always get the return < su: Sorry >!?

On an identical 2nd Mac Server I have also set the password for superuser "postgres" in the installation wizzard. I also could login as postgres user to config the pg_hba file. But on this machine the connection to the db server is only working by setting the option from "md5" to "trust" without entering a password, although the password is set!?

And now on both Mac Servers I cannot login as "postgres" again, although nothing has changed and the system is untouched.

What's wrong?
Thanks in advance
Thomas

I am having this problem as well.

Hello,

The 'postgres' user account is a locked account (for security reasons) so you cannot login from non-super user account. To login fist become a super user as:

su or sudo su (which ever is applicable)

and then 'su postgres'. This way you can login to the locked account.

For the password issue:
Set auth type to 'trust' in the pg_hba.conf file.
Restart the server.
Connect to server.
Change the password of superuser. (See http://www.postgresql.org/docs/8.4/interactive/sql-alteruser.html)
Set auth type to 'md5' in the pg_hba.conf file.
Restart the server.

Try connecting with the changed password this time.

Hope it helps.

Thank you for your reply!

Login as 'postgres' user is now working. I forgot the sudo in front of the 'su - postgres' in the Mac Terminal. On the Linux Debian Server Console I was always on root and could login without the sudo.

Setting/changing the password for the 'postgres' user doesn't work! I have made your discribed steps, but I get always the error message about user password authentication failure!?

1. pg_hba.conf file was set to 'trust'
2. logged in the psql console
3. executed: ALTER USER postgres PASSWORD 'myPassword'
4. set the pg_hba.conf to 'md5'
5. restarted the db server

--> I cannot connect to the db by pgAdmin, because a password auth failure
--> if I reset the pg_hba.conf to 'md5' the connection can be established

Do I something wrong?

Hello,

Check for any pre-existing .pgpass file or PGPASSWORD env variable.

Sachin_S wrote:Check for any pre-existing .pgpass file or PGPASSWORD env variable.

Ok, but where should I check for?

Hello,

The .pgpass file is in the home directory of the service account user. (by default in postgres user's home directory).

You can check for PGPASSWORD env variable by doing an (echo $PGPASSWORD).

Hope it helps.

Thank you again!

I don't found a .pgpass file or PGPASSWORD variable and I repeated the command in the psql console : ALTER USER postgres PASSWORD 'myPassword'. Without success. It's still no password set for the user 'postgres'!?

Hello,

Can you post the exact error message you are getting when you try to connect to the database? I am sure we are missing a very basic thing here.

This message was edited 1 time. Last update was at 17 Aug 2010 16:50:53

That is the error dialog I get if I set the pg_hba.conf to 'md5'. I will translate the german part of the text:
"Password authentication for user >>postgres<< failed
FATAL: Password-authentication for user >>postgres<< failed"

This message was edited 2 times. Last update was at 1 Sep 2010 03:00:29

Hello,

I hope you still around with this.

Just to summarize what we have already done so far:

1) Stopped the server
2) Changed pg_hba.conf to set authentication to 'trust'
3) Restarted the server
4) Connect to the server using psql as (psql -U postgres -p 5432 -h localhost postgres)
5) Alter 'postgres' user password using ALTER USER postgres PASSWORD 'mypassword'
6) Stopped the server
7) Changed pg_hba.conf to set authentication to md5
8 ) Restarted the server
9) Checked No .pgpass file is present and no PGPASSWORD and PGPASSFILE env variable is set.
10) Now connect with the psql again (psql -U postgres -p 5432 -h localhost postgres)
When it asks for password give 'mypassword'

and you get "Password authentication failed for user postgres"

Please confirm so we can take this further.

That's right, but I will try it once more today and post the result here.

Regards,
Thomas

I am having the same issue. I am trying to work through the steps posted by Sachin. One question, where do I access pg_hba.conf ?

I have 8.4 and am not able to login with the 'postgres' superuser through pgAdmin or psql. both give me:

Error connecting to the server: FATAL: password failed for user "postgres"

One note is that contrary to the install guide (http://www.enterprisedb.com/learning/pginst_guide.do) I was never presented with a window to enter a password for the superuser account.

I am running Snow Leopard now and am pretty sure I had an older version of Postgres installed before I upgraded to Snow Leopard.

Any help or advice is appreciated.

Thank you,

Jason

Hello,

pg_hba.conf can be found in the data directory, For 8.4 default location on mac is /Library/PostgreSQL/8.4/data

Hope it helps.

Thanks Sachin_S,

I was able to get into the pg_hba.conf file to trust. After that I was able to get into psql and run the ALTER USER command and reset the password. After I turned the method back to MD5 I was no longer able to login though. What should my next step be to be able to login consistently? Do I need to set a password or can I leave the method in pg_hba.conf set to trust and login in with no password?

Thank you for your help,

Jason

Hello,

Can you let me know the exact command you are using to log-in?

Leaving the auth-mode to trust can be a security issue, so i will leave that to your discretion.

Have you checked about the existence of .pgpass file? or env Variable PGPASSWORD or PGPASSFILE?

Is your system's language other than "English"?

My system's language is definitely English. Where do I look for the .pgpass file?

When I tried loggin in, I went to pgadmin and doubleclickd on Postgres then I got the same login error message as before.

Thank you for your help.

Jason

Hello,

The .pgpass should be there in your $HOME folder.

Hi Sachin,

sorry for the delay, but I'm currently a little too busy. The other reason is, I would wait and upgrade from vers. 8.4.4 to 9.0.1. At the end my password issue is now gone with the new installation. All the PostgreSQL 9.0.1 servers and databases, running on Mac OS X and /or Linux Debian 5, are password proteced and working well.

Regards,
Thomas