1

I would like to configure PostgreSQL 9.4 to allow connections from database users with passwords from PHP on both the command line and through Apache on a CentOS 6.6 box. I installed PostgreSQL with yum and the only configuration changes I have made are in the pg_hba.conf file:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                               md5
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
# IPv6 local connections:
host    all         all         ::1/128               md5

I have a test database called test and a user named psql with the password of psql. Logging in on the command line works fine:

psql -U psql -W test  
Password for user psql:   
psql (9.4.5) Type "help" for help.

test=>

The privileges are:

enter image description here

My PHP code is:

<?php
$user = 'psql';
$pass = 'psql';
try {
    $dbh = new PDO('pgsql:host=127.0.0.1;dbname=test', $user, $pass);
    foreach($dbh->query('SELECT * from test') as $row) {
        print_r($row);
    }
    $dbh = null;
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . PHP_EOL;
    die();
}

Yields the error:

Error!: SQLSTATE[08006] [7] FATAL: Ident authentication failed for user "psql"

I tried using trust as well, but that didn't work either, and I would like to know how to allow access for database users with passwords. I would like to avoid using ident.

Could you tell me what's wrong?

  • 1
    On the command line test, you are connecting to the socket, whereas the PHP code tries to connect via tcp. Does the PHP app reside on the DB server? The error message suggests a different state of the ph_hba.conf from what you show above. – dezso Oct 16 '15 at 5:00
  • When you made the changes to the pg_hba.conf, did you run service postgresql-9.4 reload to actually apply the changes? – Kassandry Oct 16 '15 at 5:56
  • @deszo - Thank you for the tip. The issue was that I was modifying /var/lib/pgsql/data/pg_hba.conf, instead of /var/lib/pgsql/9.4/data/pg_hba.conf. – user2182349 Oct 16 '15 at 11:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.