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:
The difference between
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
#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.