Need to do some simple operations using postgres, but the authentication methods supported by Postgres is not so obvious, so this note presents my understanding after one-day searching on this topic.

Authentication Methods is the definite reference for 9.3 version (the one I am using while this post is being composed), and I shall only cover three basic methods: trust, ident, peer.

The difference between ident and peer lies between how the database is accessed, via TCP or socket, which could be trivially distinguished by inspecting the shell command used. ident is used for TCP connections, while peer is for socket connection.

psql [<database>] # using socket, the default database is the one with the same name as the user name
psql -h <host> [<database>] # using TCP, mostly it's localhost for development

With above knowledge, we could have a look at the pg_hba.conf comes with the fresh install of Postgres.

#TPYE         DATABASE            USER              ADDRESS         METHOD
local         all                 postgress                         peer

This means that postgres user could access all databases via socket, which is why we can do this, if I am one of sudoers.

sudo -u postgres psql -- change user to postgres in order to access the postgres databse

The effect of peer becomes explicit when the authentication fails, which happens if you do this:

psql -U postgres

This means that “I claim to be user postgres, and please grant me the access to postgres database”, and peer authentication would get the user name from OS, and compare it with this claim. Since the current user isn’t postgres, it would fails with error message like this:

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

This is where trust come to rescue; let’s change peer to trust, in pg_hba.conf.

#TPYE         DATABASE            USER              ADDRESS         METHOD
local         all                 postgress                         trust

Then, this command would succeed, so trust basically means that any claim from the user is trusted unconditionally.