Debian – .pgpass file does not set default username to connect as

debianpostgresql

(Debian 8)

My .pgpass file is at my home folder (admin)

I and using the right format as indicated in the documentation

hostname:port:database:username:password

The same fields I put there work well when put explicitly into the psql command line. (of course I have to enter the password manually).

However, running psql by itself gives our an error:

psql: FATAL: role "admin" does not exist

Note that my postgres username is NOT "admin", which is my debian username.

What am I doing wrong? my goal is to get access to psql without having to use an elaborate command line including host/port/username/database

Best Answer

psql defaults to using your current unix username when connecting, unless you specify something different.

If you want a different default you can set the PGUSER environment variable.

See https://stackoverflow.com/q/17632278/398670

.pgpass is what psql looks up to find out the password for a given host, username, port, etc combo. It doesn't specify what connection to use, only the password to use for a given connection.

You can use env vars, a sevice file, and/or shell aliases to specify connection defaults.

Related Solutions

PostgreSQL – Default Superuser Username/Password After New Install

CAUTION The answer about changing the UNIX password for "postgres" through "$ sudo passwd postgres" is not preferred, and can even be DANGEROUS!

This is why: By default, the UNIX account "postgres" is locked, which means it cannot be logged in using a password. If you use "sudo passwd postgres", the account is immediately unlocked. Worse, if you set the password to something weak, like "postgres", then you are exposed to a great security danger. For example, there are a number of bots out there trying the username/password combo "postgres/postgres" to log into your UNIX system.

What you should do is follow Chris James's answer:

sudo -u postgres psql postgres

# \password postgres

Enter new password:

To explain it a little bit. There are usually two default ways to login to PostgreSQL server:

By running the "psql" command as a UNIX user (so-called IDENT/PEER authentication), e.g.: sudo -u postgres psql. Note that sudo -u does NOT unlock the UNIX user.
by TCP/IP connection using PostgreSQL's own managed username/password (so-called TCP authentication) (i.e., NOT the UNIX password).

So you never want to set the password for UNIX account "postgres". Leave it locked as it is by default.

Of course things can change if you configure it differently from the default setting. For example, one could sync the PostgreSQL password with UNIX password and only allow local logins. That would be beyond the scope of this question.

Ubuntu – .pgpass with postgreSQL not working

Is the pgpass file set to mode 0600 (i.e. read/write only by the owner)? The client library will ignore it if it's group- or world-readable.

Best Answer

Related Solutions

PostgreSQL – Default Superuser Username/Password After New Install

Ubuntu – .pgpass with postgreSQL not working

Related Topic