psql: FATAL: password authentication failed for user

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

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

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.