UPDATE (2012-06-24): Version 9.1 of PostgreSQL renamed ident to peer (for local connections). So if you're having errors about “Peer authentication failed" – it is the same as “Ident authentication failed", and all described in this blogpost is still relevant.

ever seen one of those? i mean the “fatal: ident authentication failed"?

or, ever seen anyone having this problem when connecting to postgresql?

how often is this problem related to debian/post-debian linux distributions? 99%? 100%?

on #postgresql on irc.freenode.net it is the most common problem. my own irc logs show that “ident" showed over 300 times over last 41 days. now, that's something. and how come we have this problem? what can be done with it? read on.

now, before this tale of bloody revenge … erm, sorry.

before i tell you how to fix the problem, let me describe something called pg_hba.conf in postgresql.

as you all know we can grant and revoke rights to specific objects in our database. databases themselves, schemas, tables, views, sequences, function, languages and even tablespaces.

but since very old versions (i would say that from beginning but i can't check it for sure) there is very important configuration way – pg_hba.conf file.

this file is usually located in $PGDATA directory, but not always (hi, “genius, let's do it differently" debian guys). if you're using modern postgresql you can check where the file is located:

# show hba_file ;
           hba_file
------------------------------
 /home/pgdba/data/pg_hba.conf
(1 row)

or set it explicitly in postgresql.conf.

this file is the first line of defense against hackers. it tells postgresql from which ip numbers, which users to which databases can connect and how they can connect.

to understand this config file you have to understand couple of facts:

• postgresql “listens" for connections using unix sockets (located in /tmp or another place – you can check using: netstat -nxl | grep PGSQL)
• postgresql can listen for connections on tcp/ip port (check: show listen_addresses; show port;)

now, in pg_hba.conf there are rules which allow or prevent logging using unix/tcpip sockets, and various combinations of ip/database/username.

let's see an example:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
local   all         pgdba                             md5
local   all         all                               trust
host    all         pgdba       0.0.0.0/0             reject
host    all         all         127.0.0.1/32          trust
host    all         all         ::1/128               trust
host    all         all         192.168.1.69/32       trust
host    all         all         192.168.1.0/24        md5
host    all         all         0.0.0.0/0             reject

that's a lot of lines, but what they all mean?

first, you have to understand, that postgresql stops “processing" the file when it finds first line that matches given connection.

so, in our example when i will try to login as “pgdba" to database “xxx" using tcp/ip socket, connecting from 192.168.1.222 – used line will be “host all pgdba 0.0.0.0/0 reject", and not “host all all 192.168.1.0/24 md5" (which matches more specific ip range) – simply because it's first.

so, now to the meaning of pg_hba.conf lines:

local   all         pgdba                             md5

local means it is for unix-socket connections only. all – means any database. pgdba – means that this rule will be applied when logging using pgdba account (postgresql pgdba account, not shell account!). md5 at the end means that the authentication method to be used is md5.

basically there are only few authentication methods:

• trust
• password
• md5
• ident
• reject

there are also some others, but definitely less used (if you're curious: gss, sspi, kdb5, pam, ldap).

meaning of them is quite simple:

• trust – no checks against password are made. postgresql trusts that you are then one that you're saying you are.
• password/md5 – to connect you have to supply password for this account (again, postgresql account). password method is generally less safe, and shouldn't be use unless you tested that md5 doesn't work. remember that the method (md5/password) has nothing to do with password encoding in database.
• ident – i will describe it later. for now simply assume it's devil incarnate, root of all evil
• reject – simple authentication method which rejects any kind of connections

so, now that we know this, we can read the rest of example pg_hba.conf file:

local   all         all                               trust

this means that any user connecting to any database, but using unix socket, is trusted. remember though about the first rule which was for pgdba user. knowing about how postgresql read the file, we know that this rule effectively means: all users except pgdba, which was mentioned in previous line.

now, this “trust" can be seen as bad, but consider this: in standard situation one using unix socket to connect to pg, has to have the ability to run the process on your machine. so knowing this – it's not much of a problem to give him “trust" when it comes to postgresql connection. (i said standard because dblink/dbilink modules bring another factor to our security issue, but they are definitely not the most used extensions, and i assume anyone using them already knows what i'm writing in here).

host    all         pgdba       0.0.0.0/0             reject

this line effectively forbids any logging to pgdba over tcpip sockets. doesn't matter which ip you are using. if you are using tcpip (thus the “host" at the beginning of the line) – you can't connect as pgdba.

host    all         all         127.0.0.1/32          trust
host    all         all         ::1/128               trust

these 2 lines allow trusted connections from localhost – even using tcp/ip sockets. of course – to all users except pgdba, which was excluded by previous line.

host    all         all         192.168.1.69/32       trust

this line shows that user using computer with ip 192.168.1.69 has special rights and can login using “trust" to any database and using any username (except pgdba of course).

host    all         all         192.168.1.0/24        md5

this line means that any user from 192.168.1.0/24 network (with the exception from previous line) can login to any account (minus pgdba) using md5 authentication.

host    all         all         0.0.0.0/0             reject

the last line forbids any other logins from any other hosts. actually it is not needed as postgresql will automatically reject connection when there is no line for it in pg_hba.conf.

that's all. simple, and very effective.

to give some perspective i usually use pg_hba with this content:

local   all         all                               trust
host    all         all         127.0.0.1/32          trust
host    all         all         ::1/128               trust
host    all         all         0.0.0.0/0             md5

this means that from localhost i can login (using both unix socket and tcpip socket) to any database and any user without giving password. and when i want to connect from another machine – any other machine – i still can connect to any user, but i have to know the password.

what else can be done using pg_hba.conf? for example this:

we have database “xxx" which we allow only one user to connect to: pornadmin. to conenct using pornadmin to xxx database you have to supply password. no other user should be allowed to connect to this database, but when portadmin will want to connect to another database – password will not be needed.

how to do it? it's simple:

host    xxx        pornadmin         0.0.0.0/0             md5
host    xxx        all               0.0.0.0/0             reject
host    all        all               0.0.0.0/0             trust

that's all.

so, this file is very simple and very powerful. but what about the title problem? failed ident authen?

ident authen is very good idea. basically it allows administrator to setup rights in such a way that given *shell* user will be able to connect only using postgresql user that has the same name as shell user.

for example – setting:

local all all ident sameuser

i enforce that shell user “depesz" can connect to database *only* as user depesz.

this has some uses. for for majority of users which just installed postgresql (probably going straight from mysql) it is a major pita.

this shouldn't be a problem. postgresql developers left default pg_hba.conf in a state where is “trust" local connections, and rejects any other.

but the “great" debian guys thought that it's bad. and they package postgresql with modified default pg_hba.conf file which sets “ident sameuser" for all local connections.

effects?

default superuser in postgresql is postgres. to connect to this account i cannot use “depesz" shell account as it is not “postgres". so i have to “su" to user postgres. but user postgres doesn't have password (usually). so i have first to “su" to root, then to postgres, and then i can connect to postgresql database as postgres user. for example to create new user which will be useful for me.

now. i know that ident has its uses. yet i truly believe that leaving it as default for standard postgresql installation does a lot of harm.

why? it's simple. theoretically it's more secure. but:

• if i want security, it means i'm security-conscious admin, and i most probably read the docs, and know how to set it myself
• newbie users have a major problem (which is visible on irc and mailing lists) connecting to postgresql. which looks like if some pro-mysql guy thought about it as a way to scare people off postgresql.
• security is an illusion, when for the simplest tasks (like create new user in postgresql database, create new database) you have to go through your root account!

so, ending this post – if you'll ever see this error (FATAL: Ident authentication failed), please find your pg_hba.conf file, change the setting to sane, and (optionally) write your opinion to postgresql-package maintainers.