psql: FATAL: password authentication failed for user

Question

I am trying to connnect to Postgresql 9.1 database on remote ubuntu 12.04 server from my windows pc using putty.i have created user with password and when i run the command

psql -U opentaps -d opentaps (opentaps is the user i created for database opentaps) i get the below mesaage . psql: FATAL: password authentication failed for user "opentaps" i did try psql -U opentaps -d opentaps -h localhost but still same message after i supply the password . my password for the opentaps user is correct .also even when i try psql -U postgres -d postgres i get the same failure message .

I made some changes in pg_hba.conf and it looks like this

https://docs.google.com/document/d/13ymGYj9e7YPFiaffwixLzsWJygp_OfBmgBi4Axgrg5A/edit?usp=sharing

also in postgresql.conf i made the change by uncomenting listen_addresses = '*' i know i am missing something here .need your help to know what else i need to change to connect to the server.

thanks for your help

Answer 1

A little late response but here it goes:

First, your .pgpass file should be in your Ubuntu user's home directory. If there isn't a file you can create it with nano ~/.pgpass

Second, your file should contain this format hostname:port:database:username:password. Don't forget to chmod 0600 ~/.pgpass so you can disallow any access to world or group, as it says here.

Now, the command should be something like this: psql -h localhost -w -U opentaps -d opentaps -c "select * from mytable". Where:

-h is your host.

-w is to use your .pgpass file, in case you don't want to type your password. Ohterwise use -W to force psql to prompt a password.

-U is your postgres username.

-d is your database name.

and -c is to execute a string sql command.

Answer 2

One hack around this is to edit pg_hba.conf

sudo vi /etc/postgresql/9.3/main/pg_hba.conf

To temporarily

# Database administrative login by Unix domain socket
local   all             postgres                                   trust

Then go and

sudo -u postgres psql template1
ALTER USER postgres with encrypted password 'your_password';

then go back and set pg_hba.conf back to

# Database administrative login by Unix domain socket
local   all             postgres                                   md5

Answer 3

Be sure that you are logged in as the user opentaps when you launch psql from the command line.

you should also fill the .pgpass file with correct information, with the following format:

hostname:port:database:username:password

if no .pgpass file has been created, you should create one. cf. postgresql docs for reference