Why is supplying the hostname sometimes optional and otherwise not in psql?

Question

After having used createuser and createdb to create a user and database both called teamcity I wanted to log in. Using both of these commands, I never said anything about which host the database was running on, so I would assume that when using psql it would use the same settings. This is what happens if I do not supply a hostname:

$ psql -U teamcity
psql: FATAL:  Peer authentication failed for user "teamcity"

No password prompt and immediate failing. If I supply -W to force a prompt, the password does not work. I am logged in as a normal unix user, not the postgres user.

Now, if I supply -h localhost everything works as it should:

$ psql -h localhost -U teamcity
Password for user teamcity: 
Timing is on.
Line style is unicode.
Border style is 2.
Null display is "[NULL]".
Expanded display is used automatically.
psql (10.11 (Debian 10.11-1.pgdg80+1))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

localhost teamcity@teamcity=> \q

What can explain this behaviour? I assume some of the commands connect to the postgres using unix sockets and some using TCP, but it should be the same database, no matter, right, so why is the authentication failing in some cases?

Answer 1

The default for all Postgres command line tools is to use "local" connections, i.e. unix sockets (unless this is "overruled" by environment variables).

The next step in the authentication process is to look up if and how the user should authenticate - which is defined by the entries in pg_hba.conf.

Most probably the default entry in pg_hba.conf says "all local connections are authenticated using peer" which is the default and is controlled by the following line:

local   all             all                                     peer

So psql -U teamcity tries to use a "local" connection and pg_hba.conf requires peer authentication for the user teamcity - most probably your current Linux user is not named teamcity and thus the authentication fails.

If you add -h localhost to the command line, a "host" connection using TCP is established, which is controlled by a different set of rules in pg_hba.conf. Maybe our file contains something like:

host    all             all             all                     md5

which allows TCP connection from any client if, with any database user as long as the correct password is provided.

You probably ran createuser and createdb while being logged in as the postgres Linux user, and for that peer authentication succeeds as the Linux username matches the Postgres database username.