blog

Image of Elephants by Mylon Ollila on Unsplash

Postgres Host-Based Authentication

by

I got this kind of error when trying to access postgres more than once:

psql: FATAL: Peer authentication failed for user "postgres"

Also I wanted to be able to use postgres from a script without having to type the password in. Here’s what’s happening with posgres authentication.

pg_hba.conf

Postgres is doing something it calls ‘host based authentication’. There is a config file for this called pg_hba.conf. pg_hba.conf allows you to configure an authentication method for a login attempt depending on whether the attempt is local or over the network and which user it is attempting to log in as. Authentication methods are many and varied but of particular interest are ‘peer’, ‘trust’, and ‘md5’
It looks like this:

## TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   awesome         some_user                               trust
local   postgres        fran                                    peer
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

Postgres uses the first line that matches the connection type/database/user to select the authentication method it will use. So if you’re trying to connect locally, to database awesome as user some_user:

$ psql awesome --user some_user

postgres will match the first line and use the ‘trust’ authentication. And if you’re trying to connect locally to db ‘postgres’ as fran it’ll use ‘peer’. Locally any other db/user combination gets caught in the 3rd line and then ‘md5’ is used.

  • ‘trust’ means that postgres will let you in without a password.
  • ‘peer’ means that postgres will let you in without a password as long as your operating system user has the same name as the postgres user.
  • ‘md5’ means it will ask for a password.

You can edit this file (it has helpful comments) and reload postgres:

$ sudo service postgresql reload

and try your new settings to see if they work any better. I couldn’t figure out how to get postgres to tell me which line in the pg_hba.conf it was using and why, so I found it helpful to temporarily reduce the config to the one line that I thought should be matching.

So if postgres tells you that ‘peer authentication failed for user "postgres"’ it means that it’s matching a line in pg_hba.conf that says it’s going to use peer authentication, and you are not logged in to the operating system as user ‘postgres’. Solutions are to log in as postgres for whatever it is you’re trying to do, or change the user you’re trying to access postgres as.

Using -h makes a host line match, not local.

I ran into a problem when I was trying a command like this:$ dropdb test --user fran -h localhost postgres kept asking me for a password even though I had a config line like this: local postgres fran peer that I thought should be being used. I removed all lines from the config except that one and instead of asking for a password postgres said:

FATAL: no pg_hba.conf entry for host "::1", user "fran", database "postgres", SSL off

and the clue is there where it says host "::1" instead of host "[local]". Using the -h switch made postgres see the login attempt as coming from over the network and it matched one of the host lines in pg_hba.conf instead of the local line I thought it would.

Can I just pass the password on the command line?

No. I’m pretty sure you can’t. However you can use a .pgpass file. Saved in your home directory and with lines like: localhost:5432:some_db:some_user:some_pass postgres will look to .pgpass for passwords before it asks them of you. It needs to have it’s perms set correctly though (as in the linked docs).

So if you need to pass a password you can use .pgpass, if you’re getting confusing authentication error messages trying to access postgres I hope this helps!

+ more

Accurate Timing

Accurate Timing

In many tasks we need to do something at given intervals of time. The most obvious ways may not give you the best results. Time? Meh. The most basic tasks that don't have what you might call CPU-scale time requirements can be handled with the usual language and...

read more