“FATAL: Ident authentication failed”, or how cool ideas get bad usage schemas
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.
October 4th, 2007 at 22:56
it is not necessary to su - root, You can use “sudo su - postgres” command
October 5th, 2007 at 06:50
@Piotr Szwed:
sure, but:
1. you have to have sudo configured (think: ubuntu, not debian)
2. sudo su - postgres also gives you root permissions for a moment. of course you dont get root-shell, but there is nothing there to stop you (yes, i know it can be configured, but so can be configured pg_hba.conf)
October 5th, 2007 at 08:44
Last time I had this problem, I just changed the auth method in the pg_hba file and it helped.
October 5th, 2007 at 13:08
All this would be solved if people simply read the documentation. /usr/share/doc/postgresql-common/README.Debian.gz. Oh well.
BTW, you can use ’sudo -u postgres psql’ … there is no reason to use sudo only to launch su… why do people keep doing that again? Oh yes, they never read the documentation…
October 5th, 2007 at 13:10
@Sam Morris:
i know it is doable. i know it is fixable. i know people should read the docs.
but face the facts - most people dont read it. and shipping defaults which are bad for newbies is a very bad idea.
the advanced guys - they can take care of themselves. newbies need protection.
October 5th, 2007 at 13:42
But time and time again we have seen that if you ship software with insecure defaults, uninformed users will continue to use it oblivious to the security holes that they are leaving themselves vulnerable to.
October 5th, 2007 at 14:35
@Sam Morris:
than make it md5.
on the other hand - do you really belive that letting “trust” on local connections is bad?
if sombody *can* make local connection (using unix socket for example) than i can assume he has shell access. and at this point - protecting postgresql will not do anything.
besides - yes, i believe that default settings should be less safe and more userfriendly - especially in such situations. as i said already - power users will tweak the conf anyway. putting postgresql on default parameters to handle production system is a bad idea regardless of pg_hba.conf - there are simply too many things set too conservative.
your opinion is that “sacrificing 90% of new users to a make some 0.0001% people happy” is valid. for me it is not.
October 5th, 2007 at 15:07
Yes, of course ‘trust’ for local connections is bad, because it bypasses all authentication checks!
I also fail to see why the fact that /bin/bash is running means that ‘protecting postgresql will not do anything’… this is only the case if you set your system up with the braindead pg_hba.conf setup using trust in the first place!
October 5th, 2007 at 15:13
@Sam Morris:
my point was that somebody capable of running /bin/bash can easily launch any kind of attack on the system.
special protecting of postgresql in this case doesn’t really matter.
but - since you’re considering work of postgresql-dev-team as braindead (they did put trust as default!, and i merely support their opinion on this, plus show my general dislike of “ident”) - i think that i will refrain from further responding to you insightful comments.
October 5th, 2007 at 15:39
The postgresql developers presumably intend for system administrators to read the manual before they start using postgresql, and therefore intend for end users to configure their systems correctly!
The only ‘braindead’ thing is the idea of using trust in a production system!
I simply don’t see why you think that being able to run bash means that users can attack the system–unlike Windows, where everyone logs in as an administrator all the time, the Debian system has the concept of separate users and user privileges. If I give someone a login on my server, I don’t have to worry about them being able to compromise unrelated parts of the system.
October 5th, 2007 at 19:35
eh, i was not to reply. but - i didn’t say i’m consistent
sam, your way of configuration means that:
1. while setting postgres “to play with” - you have to edit configuration
2. while setting production server you have to edit it as well (add other hosts to hba.conf, tune settings in postgresql.conf)
in my way:
1. while setting postgres to play with - you just install, and use
2. while setting for production - you have to configure.
so basically - all this “ident in hba” gives us is to:
1. protect newbies
2. make their life more complicated.
i once was a postgresql newbie. and i still remember it. and i know that every obstacle (even as simple to fix as ident issue) is a *big* issue when you just trying to start.
because of this, sorry, but i will *never* say “ok, it’s good” to ident in *defaults*.
because most of installations are not on servers. because seasoned admins have to edit the files anyway. and because i think that scaring newbies away with “i’ll secure it that way that you will have to spend hours just to get it running” attitude.
i know that admins should read docs. but dont treat everybody as admin. linux is fighting for its way to desktop. it has a lot of users which have no admin experience. and making life more difficult to them makes them less likely to adopt the thing.
so - putting “ident sameuser” in default settings makes is effectively great propaganda for mysql. after all - when you install mysql, you can simply: mysql -uroot and conenct. no password needed. no security.
and since i like postgresql, (and not really like mysql) i feel that one of the biggest distributions is making it unfairly difficult to *start* your postgresql experience. and this is what pisses me off.
October 5th, 2007 at 19:36
I have encountered this error message numerous times, but I’ve found that there are at least a couple of things you need to fix as well as editing your hba.conf and ident.conf files.
Firstly, you need to be running an ident daemon. My guess (I haven’t checked recently) is that the Debian package recommends but doesn’t depend on this.
Secondly, for the ident daemon to work you need to load the tcp_diag kernel module.
I have also had problems resulting from the order of entries in the hba.conf file.
But what makes it all really frustrating for me is that the message gives you no clue where the problem lies. Specificaly, it doesn’t distinguish between “failed to connect to ident daemon”, and “user reported by ident daemon is not allowed to connect”. This is something that PostgreSQL could improve.
As for whether Debian’s defaults are sensible: well, I’m happy with them.
October 5th, 2007 at 22:50
The Debian way have much more than this simple good authentication schema. And everything is fine to me.
Since 2001, I knew that Debian is a hard distro, I choose the hard way. And I knew that I need to read the docs before using it well.
Sorry for the newbies that forgot that.
October 7th, 2007 at 23:49
Thx dude - this post was the only one that catually answered my questions!
November 1st, 2007 at 21:44
[...] more… [...]
February 12th, 2008 at 16:38
Whats next when u have set the pg_hba.conf file with
local all all trust
host all all 127.0.0.1/32 trust
host all all ::1/128 trust
and u can login using console but pgAdmin keeps giving u the same error when u try to add server?
February 12th, 2008 at 16:47
@Victor:
i dont use pgadmin, so i dont know what kind of error you’re getting. without knowing it i can’t help.
March 21st, 2008 at 14:25
Victor - are you connecting using PgAdmin III from your server itself or from some other computer.
If some other computer, then you need to add a line like
(this will allow connection by all ips using md5)
host all all 0.0.0.0/0 md5
or more restrictive - only allows certain ips
host all all 192.168.1.0/24 md5
or restrictive lax - trust anyone within a subnet
host all all 192.168.1.0/24 trust
Hope that helps,
Regina
March 21st, 2008 at 17:43
You don’t need an ident daemon to allow “local ident sameuser”.
It’s also ludicrous to say that just because someone can log in, they should be able to connect as any postgres user. Why even have users in that case? I guess that advice makes sense if you’re running a server in your basement, but most real servers have more than one user…
March 21st, 2008 at 17:45
It’s also worth pointing out that the postgres defaults are not necessarily expected to make sense without additional configuration (there’s even a warning in the default pg_hba.conf), whereas the debian defaults are expected to be sensible/secure out of the box.
April 18th, 2008 at 01:16
> pg_hba.conf file [...] is usually located in $PGDATA directory, but not always (hi, “genius, let’s do it differently” debian guys).
For just about any other system service, a sysadmin will look under /etc for the configuration file. If your expectation is that PostgreSQL should put its service configuration files somewhere other than /etc/postgresql/ then that is the “genius let’s do it differently”.
I can’t count the number of times I’ve seen system administrators get tripped up by the fact that this file isn’t stored under /etc/postgresql/. Perhaps PostgreSQL’s “let’s put it somewhere different” should be reevaluated in this case?
April 18th, 2008 at 06:55
@bignose:
sysadmins - most definitelly. but dbadmins, especially postgresql admins, know that conf file is in $PGDATA. and moving it someplace else makes it difficult to work with it.
of course - the ideal situation would be if pg guys started with configs in /etc. but they didn’t, and for many years configs were in datadir, and changing it now is simply brilliant (in a sarcastic way).
May 1st, 2008 at 00:25
[...] err on the side of safety while leaving new users unable to do anything. Read on how to get past FATAL: Ident authentication failed error. I added two lines for my Hyperic database user to be able to connect from localhost using [...]