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.
it is not necessary to su – root, You can use “sudo su – postgres” command
@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)
Last time I had this problem, I just changed the auth method in the pg_hba file and it helped.
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… 🙁
@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.
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.
@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.
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!
@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.
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.
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.
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.
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.
Thx dude – this post was the only one that catually answered my questions! 🙂
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?
@Victor:
i dont use pgadmin, so i dont know what kind of error you’re getting. without knowing it i can’t help.
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
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…
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.
> 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?
@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).
Thank you so much!!! God bless your kind soul.
Your article have saved me a good deal of working hours.
Please note that the ‘ident sameuser’ default setting, is
no longer a special attribute of the “genius debian guys”, but is
now practiced by the truly wonderful Fedora guys. I just upgraded
to Fedora 9 and got stuck with it for a good couple of days.
They seriously should get back to default of ‘trust’ on local.
thanks man, that solve my problem with postgresql ident authentication.
Thanks for the article. Most other explanations expected a certain amount of knowledge which I don’t have yet! Once thing, you might mention that one has to restart the postgres server for the changes to take effect. That stumped me for a couple of seconds.
Thanks………..Chris
@Chris:
Well, first – restart is not necessary. Reload is sufficient. I should have mention it, though.
Second – if any part of the text is not clear – please say which, I’ll write more detailed explanation.
To be short. Newbies usually don’t like, don’t want and don’t know what to read. I think you should change your post. I wouldn’t use “before i tell you how to fix the problem, let me describe something called pg_hba.conf in postgresql”. Try to use instead, “here’s the solution in 10 seconds” – copy paste stuff like “$sudo vim /etc/postgres(.whatever..)” commands like that. Then you would say – “Want to know why this works?” and then explain. I’m pretty sure most newbies don’t even read your post because it’s long. (I didn’t too, but I had no choice) Anyway, thanks for your post, and thanks for replying the comments and defend your position. I agree more than a 100%.
Make it simple, so that your grandmother can use it!
Thanks for this. I don’t understand why postgres can’t try multiple authentication schemes (e.g use md5 if ident fails).
I ended up with a pg_hba.conf like this:
local all postgres ident sameuser
local all andy ident sameuser
local all all md5
host all all 127.0.0.1/32 md5
host all all ::1/128 md5
NEED HELP!!! SPEND MORE THEN 2 WEEKS AND GOT NO WHERE.
I am totally new to linux, hph and postgresql. i just finished reading dummies Debian, php5 and postgresql toturials. Please bear with me.
WHEN I RUN THIS from php script $connection = pg_connect(“dbmame=tn host=lego port=5432 password=mm user=mm “);
I GOT THIS ERROR. “Fatal error: Call to undefined function pg_connect() in /var/www/apache2-default/test/test.php on line 5″
_______
so this is what i have on my system.
1. debian etch stable……(host name= lego)
2. apache2
3. php5
4. postgresql 7.4………….
______________
when i run ps -au..this is what i got
postgres 2189 0.0 0.2 18476 2268 ? S 13:07 0:00 /usr/lib/postgresql/7.4/bin/postmaster -D /var/lib/postgresql/7.4/main
postgres 2192 0.0 0.0 9276 884 ? S 13:07 0:00 postgres: stats buffer process
postgres 2193 0.0 0.1 8420 1048 ? S 13:07 0:00 postgres: stats collector process
____________
when i run nmap lego.. this is what i got.
22/tcp open ssh
25/tcp open smtp
80/tcp open http
111/tcp open rpcbind
113/tcp open auth
587/tcp open submission
________________________________________________________________
MY /VAR/LIB/POSTGRESQL/7.4/MAIN/pg_hba.conf
local all postgres ident sameuser
local all all ident sameuser
host all all trust (I ADD IT)
host all all 127.0.0.1 255.255.255.255 md5
host all all ::1 ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff md5
________________________________________________________________
MY /VAR/LIB/POSTGRESQL/7.4/MAIN/postgresql.conf
# – Connection Settings –
#tcpip_socket = false
max_connections = 100
# note: increasing max_connections costs about 500 bytes of shared
# memory per connection slot, in addition to costs from shared_buffers
# and max_locks_per_transaction.
#superuser_reserved_connections = 2
port = 5432
unix_socket_directory = ‘/var/run/postgresql’
#unix_socket_group = ”
#unix_socket_permissions = 0777 # octal
#virtual_host = ” # what interface to listen on; defaults to any
#rendezvous_name = ” # defaults to the computer name
# – Security & Authentication –
#authentication_timeout = 60 # 1-600, in seconds
#ssl = false
#password_encryption = true
#krb_server_keyfile = ”
#db_user_namespace = false
_________________________________________
Some this is not right and i am not sure where to look for the answer… please help..
ths.
@Mgmg:
Look, you has not open port 5432 for postgres (you get beter info with netstat -ltp). And in your postgresql.conf:
#tcpip_socket = false
Change it to:
tcpip_socket = true
restart postgres, and it should work.
And one more note, not connected with your problem. In your pg_hba.conf remove line:
host all all trust (I ADD IT)
It’s not correct and potentialy allow everyone from everywhere to log without the password. Use:
local all all trust
it allow to log localy with psql without password, and if you want allow local programs to connect to localhost:
host all all 127.0.0.1/32 trust
P.S. Sorry for my english 🙂
Thanks for your responed Madej. I got some progress but still no go.
lego:/var/www/apache2-default/test# netstat -ltp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 *:submission *:* LISTEN 2472/sendmail: MTA:
tcp 0 0 *:sunrpc *:* LISTEN 1933/portmap
tcp 0 0 *:auth *:* LISTEN 2412/inetd
tcp 0 0 *:46578 *:* LISTEN 2448/rpc.statd
tcp 0 0 *:postgresql *:* LISTEN 2983/postmaster
tcp 0 0 *:smtp *:* LISTEN 2472/sendmail: MTA:
tcp6 0 0 *:www *:* LISTEN 2521/apache2
tcp6 0 0 *:ssh *:* LISTEN 2423/sshd
tcp6 0 0 *:postgresql *:* LISTEN 2983/postmaster
______________________________________
Starting Nmap 4.11 ( http://www.insecure.org/nmap/ ) at 2009-02-12 13:39 PST
Interesting ports on lego.absmc.org (10.25.46.10):
Not shown: 1673 closed ports
PORT STATE SERVICE
22/tcp open ssh
25/tcp open smtp
80/tcp open http
111/tcp open rpcbind
113/tcp open auth
587/tcp open submission
5432/tcp open postgres
___________________________
if i run from command link like this, then i connect to tn database. BUT
test@debian1:~$ psql -d tn -h lego -p 5432 -U mm -W
Password:
Welcome to psql 7.4.23, the PostgreSQL interactive terminal.
Type: copyright for distribution terms
h for help with SQL commands
? for help on internal slash commands
g or terminate with semicolon to execute query
q to quit
tn=>
tn=>d
List of relations
Schema | Name | Type | Owner
——–+———–+——-+——-
public | tech_note | table | mm
(1 row)
tn=>
___________________________________________________
if i run from URL, this is what i got
Fatal error: Call to undefined function pg_connect() in /var/www/apache2-default/test/test.php on line 5
_________________________________________________
this is my script.
_________________________
?php
$connection = pg_connect(“dbmame=tn host=lego port=5432 password=mm user=mm”);
$result = pg_execute ($connection, “select date from tech_note” );
print “$result”;
print “Successfully disconnected from database”;
?>
/body>
/html>
i remove < sign……….
Thanks a lot, GREAT tutorial.
Hi,
This is a very excellent manual. I habe some expierience in debian, though have no experience with postgresql. The authors writing style (eg: “security is an illusion” and “>>great<< debian guys") is just brilliant. Moreover its concise and coherent. I have had great fun reading this. Now i have to locate this pg_hba.conf file in my debian /etc/.. 🙂
Stay Tuned,
Ltune
I’m not getting into arcane arguments, but want to thank the author for saving me more time setting up a postgres DB so I can actually get on with setting up an opennms server on ubuntu. Good work!
Another soul helped. Thanks a lot! 🙂
this page just answered all my questions! thanks a lot!
Thanks, this saved me a lot of frustration. Very well explained. I do agree the default settings are not useful, especially since the errors are not clear where to look to solve this.
I also see the point that this is from a security perspective not the best solution, but it at least now makes me understand where it went wrong, and what to do about it. Also agree that setting the default not secure enough makes for more vulnerable systems. Like Windows…
A BIG thank-you.
Thanks a lot depesz! Your class (yes, a PRO class) saved my day.
A great day for you, too!
Thanks,
I only just installed postgres on Debain and it took me two days to log into it from my default OS account. The pg_hba.conf should by default be set to:
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
Have to log into it from a dedicated OS user account is just stupid and confusing for the beginner (lets face it production environments are going to change these anyway, so set it up for the lowest common dominator)
Oh man. Thanks so much for the explanation of how this works. I thought I did read the installation and configuration directions (installing on CentOS) and was really frustrated with the documentation or lack of explanation around the configuration files. Your post makes sense and lets me have the choice of choosing my comfortable level of risk. Thanks again.
This was very informative article. It cleared things quite a bit. Thanks for writing it! 🙂
Tuomas
Thanks again for this article. I agree, I shouldn’t have to read the entire docs to start using new software. All they need is something in their quickstart guide flagging up what is clearly a common issue. Good to have your angle on it.
many thanks, dude. this really looks perfectly clear.
i’ve met this ‘authentication’ trouble after having the program installed with the package on windows 7. Neither the installation nor the manual on the site has any reference to this troubling thing.
Really descriptive and nice writeup! Thanks.
I got this same error and changing user or changing auth method didn’t solve it. But changing my host to localhost (DATABASE_HOST = ‘localhost’) did the trick.My installation is running on a VPS host, and i probably failed to read the documentation carefully.
Anyway great read. Now i understand alot more of security and postgres configuration!