“FATAL: Ident authentication failed”, or how cool ideas get bad usage schemas

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.

63 thoughts on ““FATAL: Ident authentication failed”, or how cool ideas get bad usage schemas”

  1. Hello,
    Trying since days to get rid of this message, after reading documentation and spending huge time surfing the web to understand, still with local all all trust it says password authentication failed for user …
    Any other idea or hess ?
    Thanks

  2. Hello,

    Trying since days to get rid of this message, after reading documentation and spending huge time surfing the web to understand, still with local all all trust it says password authentication failed for user …
    Any other idea or hess ?
    Thanks

  3. @Guy: if it says: “Password authentication failed” – it means you’re providing bad password.
    And if you’d read this blogpost in full, you’d know how to disable password protection by changing authentication to “trust”.

  4. thank you for your answer. I triple checked the password which is good, the pg_hba.conf is set temporarily to “local all all trust” and “host all all trust” for the testing and same problem.

  5. If you are getting password errors, then your hba is either faulty, not read, or in wrong place. In any way – comments in my blog are not really the best place for debugging problems – there are many things that I don’t know, and asking them one by one doesn’t make sense.

    Just jump on irc, to #postgresql, and there are people that will be willing to help.

  6. I spent far too much time getting postgres up and running on Fedora. Your explanation was the one that worked for me. Many thanks, I really appreciate it.
    And I now have an understanding of some of the things I need to deal with after I have finished playing with the databases.

  7. You forgot mention what Postgres can’t use encryption in ident! In Fedora/CentOS/RHEL install authd which is provide ident service has it enabled (-E option) by default. And it is another reason postgres also will fail until encryption was disabled.

    I spent many time until realize how to fix it.

  8. I had been struggling setting up Postgresql on Fedora 25 and kept getting the ident error. This post helped me setup and even understand what* was happening.

    Thanks #DEPESZ and #PAVEL ALEXEEV 🙂

  9. This article helped to solve the typical issue “Ident authentication failed”.
    Awesome!

Comments are closed.