PostgreSQL: Using the .pgpass file

I have been reading up on the .pgpass file, but I can’t get it working for me.

My .pgpass file looks something like this:

127.0.0.1:5432:accounts:fred:p@55w0rd

I set the privilege to 0600 (this is on CentOS Linux) and try:
psql


Where I get the message:

psql: error: FATAL: database "…" does not exist

where … is my user name.

I can successfully connect if I use:
psql -u … -d accounts


so I don’t think my .pgpass file is doing its job.

How can I get it to work?

.pgpass does not define a default database. It only provides the passwords for a combination of hostname, database and username.

But it's still up to you to provide the hostname, database and username when you start psql.

When you just run psql without any arguments you are not providing a username or password - and it's not taken from .pgpass - there could be hundreds of entries in there. Which one should psqltake?

As documented in the manual psql then assumes your current operating system user as the default user. If no database is provided psql assumes a database with the name of the user (so with the name of the current operating system user if you also don't provide a username).

You apparently want to connect to a database that has a different name than the username you want to use. Hence you have to provide a database name. If fred is your operating system user, then psql -d accounts should be enough.

If you want to use a user and database other than the defaults, use the environment variables PGUSER and PGDATABASE.

StackExchange Database Administrators Q#297741, answer score: 6