PostgreSQL: Using the .pgpass file

All we need is an easy explanation of the problem, so here it is.

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:[email protected]

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

.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 .pgass – 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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply