Issue connecting to postgresql using pdo on localhost (08006)

Question

I have set up a test server and installed PHP 5.4 (zend Server with Nginx) and also postgresql 9.1.

I am trying to connect to the postgresql database using the following code:

define('DB_TYPE', 'pgsql');
define('DB_HOST', 'localhost');
define('DB_PORT', 5432);
define('DB_NAME', 'rockprofile');
define('DB_USERNAME', 'rockprofile');
define('DB_PASSWORD', 'PASSWORD');

$dsn = DB_TYPE . ':dbname=' . DB_NAME . ';host=' . DB_HOST . ';port=' . DB_PORT;

try {
   $db = new PDO($dsn, DB_USERNAME, DB_PASSWORD);
} catch (PDOException $e) {
   echo $e->getMessage();
   print_r($e->getTrace());
}

However this is generating the following:

SQLSTATE[08006] [7] FATAL: Ident authentication failed for user "rockprofile"

Array (
   [0] => Array (
       [file] => /usr/share/nginx/inc/config.php
       [line] => 24
       [function] => __construct
       [class] => PDO 
       [type] => -> [args] => Array (
            [0] => pgsql:dbname=rockprofile;host=localhost;port=5432
            [1] => rockprofile
            [2] => PASSWORD ) )
   [1] => Array ( [file] => /usr/share/nginx/bands/index.php
       [line] => 2
       [args] => Array (
            [0] => /usr/share/nginx/inc/config.php )
            [function] => include ) )

As you can see I am using localhost. I know the dsn is correct including the username and password because if I substitute localhost with the local 192 IP it works. I suspect this is an issue with the configuration of postgresql and the following is the current content:

local   all             all                                    peer
host    all             all            127.0.0.1/32            ident
host    all             all            ::1/128                 ident
host    all             all             192.168.1.0/24          md5

On searching someone appeared to resolve this by changing ident to trust on the config above but this has not helped in my case.

It is probably something simple I need to add but searches seem to be fruitless at the moment. Can anyone tell me how to correct this.

*-----EDIT-----*

As per Daniel Vérité's response to fix the issue ident needs to be changed to MD5. I personally had to change this for both the IPv4 and IPv6 entries.

local   all             all                                    peer
host    all             all            127.0.0.1/32            md5
host    all             all            ::1/128                 md5
host    all             all             192.168.1.0/24          md5

is this Windows, Linux, or OSX youre working on? For windows you should try using 127.0.0.1 — prodigitalson
– prodigitalson, Commented Jul 18, 2013 at 13:26
sorry should have mentioned. This is a linux machine with the minimal install of cent os 6.4. I did try 127.0.0.1 as well but same issue. — Peter
– Peter, Commented Jul 18, 2013 at 13:38
@prodigitalson This resolved the error I got, do you have some explanation why "localhost" didn't work in my case ? — vdegenne
– vdegenne, Commented Jan 5, 2016 at 19:53
It is not actually the use of local host that caused the issue rather it was the authentication method. The issue is resolved by changing the authentication method from ident to md5 — Peter
– Peter, Commented Jan 5, 2016 at 23:15

Daniel Vérité · Accepted Answer · 2013-07-18 13:36:58Z

3

See http://www.postgresql.org/docs/9.1/static/auth-methods.html:

The ident authentication method works by obtaining the client's operating system user name from an ident server and using it as the allowed database user name (with an optional user name mapping)

The ident authentication method is not really suited for connections from a web server, because the web processes run under a account, typically www-data or some such that has no relationship with the databases account (rockprofile in your case).

Since you want authentication by password, replace this with md5 in pg_hba.conf and reload or restart PostgreSQL.

Also you may change your password because it appears in clear in the trace, negating your substitution effort in the code.

answered Jul 18, 2013 at 13:36

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Peter Over a year ago

Hi Daniel thanks for pointing out the password issue. I forgot to edit. I have edited it now. I will of course change the password tho. Thank you very much for the very clear answer. This has resolved the issue. I had to change it on the ipv6 line but also changed it on the ipv4 line.

Collectives™ on Stack Overflow

Issue connecting to postgresql using pdo on localhost (08006)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related