PostgreSQL: Using the .pgpass file

Question

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?

Answer 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 .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.

• Manngo: I understand. That makes sense, as then the .pgpass would also allow alternative connections. Thanks.
• Tony-Caffe: The original post had pgass instead of pgpass lol

Answer 2

To use the psql client remotely on *nix, you can create a password file on the client side and add the comment block shown below to it.

By default, psql looks for a password file named .pgpass in your client side user account to resolve CLI parameters to a unique line in the file. You need to set the file permissions appropriately; and optionally, you can change the location/name of the file using the PGPASSFILE environment variable.

The Usage section provides a few examples of using the psql tool with various CLI parameters. The parameters are used to find a unique line in the file which then provides the remaining parameters, including the password.

The Configuration section provide the syntax for each line.

The block below includes a couple of dummy examples (myhostname1, etc.) that you must replace with your own entries, of course.

/home/myusername/.pgpass

# File: /home/myusername/.pgpass
# chmod 600 /home/myusername/.pgpass
# export PGPASSFILE=/home/myusername/.pgpass
#
# Usage:
# psql -h myremotehostname -U postgres -d postgres
# psql -h myremotehostname -U postgres -d mydbname
# psql -h myremotehostname -U myremoteusername -d mydbname
# psql -h myremotehostname -U myremoteusername
#
# Configuration:
# hostname:port:database:username:password
#
myhostname1:myport1:mydatabase1:myusername1:mypassword1
myhostname2:myport2:mydatabase2:myusername2:mypassword2

• mustaccio: I don't think you're answering the question that was asked. Is adding the comment block to the .pgpass file really necessary?