" PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: FATAL: password authentication failed for user ..." in Raspberry Pi (Stretch)

Question

So sorry to bother everyone, I'm new to PostgreSQL, but my project requires me to build an automation system that connects to a server running PostgreSQL server. Long story short, let's just say I am required to perform a data insertion/manipulation from a Web Form into the server via PHP pg_connect(). The Web Form is located locally in /var/www/html/web_form.html and calls to a PHP script that performs the data insertion,

    <form name="some_name" action="script.php" method="POST">

so the data insertion is done to a local server.

I'm using a Raspberry Pi 3 to simulate the "server" (as so not to "disturb" the true running server during such an early development). The Raspberry Pi is running a Raspbian Stretch distro. Well, it was originally Jessie, but then I decided to involve the Stretch repo from

    deb http://mirrordirector.raspbian.org/raspbian/ stretch main contrib non-free rpi

with a priority of 100. It also has postgresql-9.6.3 (from Stretch), php7.0.19-1 (also from Stretch), and apache2.4.25 (from official Raspbian). Please do note that the Stretch repo do replaces A LOT of Jessie's official stable packages, and also known to be "not quite stable" itself, so it might also be the source of the problem.

I can access the database in the server from remote computers, whether using direct psql, Python (psycopg2), or even the exact same Web Form via PHP (tried from remote computer), so it will mostly won't be because of PDO problems (I've checked phpinfo() , but feel free to give advice on this, since I don't really understand PDO myself). On local, I can access the database via psql, but not local access via PHP (pg_connect).

I've meddled with this problem for hours now in vain. Please help me.

Here's my Raspberry Pi's configurations:

1. A snippet of the PHP code:

    $conn_str="host=localhost port=5432 dbname=my_db_name user=my_user_name password=my_password";
    $db = pg_connect($conn_str);
    if(!$db){
        $errormessage=pg_last_error();
        echo "Error 0: " . $errormessage;
        exit();
    }

Please mind that this same code has perfectly worked on another computer. I've succeeded to perform data insertion when the Web Form is located in a remote computer accessing the said server, or when the Web Form is accessing the remote computer's own local PostgreSQL server, but not when it is done in this particular problem server. When running in browser, it only shows:

    Error 0:

the result when running in console:

PHP Warning:  pg_connect(): Unable to connect to PostgreSQL server: FATAL:  password authentication failed for user "raspiserver"

FATAL:  password authentication failed for user "raspiserver" in php shell code on line 1

I also have tried to change host=localhost to host=127.0.0.1 or host=0.0.0.0. I'm sure that the error came from that block of code, since that block is already on the first lines in my PHP code, while the other error reporting codes I put, each has distinct Error X: number.

2. The PostgreSQL configurations:

For the pg_hba.conf content :

    # Database administrative login by Unix domain socket
    local   all             postgres                                peer

    # TYPE  DATABASE        USER            ADDRESS                 METHOD

    # "local" is for Unix domain socket connections only
    local   my_db_name     my_user_name     127.0.0.1/32            md5
    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
    # Allow replication connections from localhost, by a user with the
    # replication privilege.
    # local   replication     postgres                                peer
    #host    replication     postgres        127.0.0.1/32            md5
    #host    replication     postgres        ::1/128                 md5

    #host   all             all             0.0.0.0/0               md5
    host    another_db_name      my_user_name             192.168.52.0/24         trust
    host    my_db_name     my_user_name             192.168.52.0/24         trust

I already tried to comment/uncomment LINE 7, or change both LINE 7 and 8 to either md5 , trust , or password . I also have tried to remove 127.0.0.1/32 from LINE 7, or change it to 127.0.0.0/24 or 0.0.0.0/0 . And, yes, I did performed

    sudo /etc/init.d/postgresql reload
    sudo /etc/init.d/postgresql restart

(even sudo reboot) for every change I've made.

For the postgresql.conf content :

I already set listen_addresses = '*' and password_encryption = on , with the rest remained unchanged.

3. Firewall :

sudo iptables -L -n doesn't show any entries :

    Chain INPUT (policy ACCEPT)
    target     prot opt source               destination         

    Chain FORWARD (policy ACCEPT)
    target     prot opt source               destination         

    Chain OUTPUT (policy ACCEPT)
    target     prot opt source               destination  

What is wrong with my configurations? Are Stretch the cause? Or is it because the implementation of PostgreSQL-9.6?

I've already googled here and there, but most of the solutions only advice to change the pg_hba.conf to trust , or assume that the user name is not exist.

I'm desperate, please help.

(Please do mind that I don't know anything regarding PHP, PostgreSQL, Apache, or server, so please don't expect that I really know what I've done so far. Please do analyze everything.... Also, English is not my native, so I might mixed around some "jargons" here and there (if any).... Sorry for that... )

Answer 1

This particular declaration in pg_hba.conf is incorrect:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   my_db_name     my_user_name     127.0.0.1/32            md5

because when the TYPE field is local, the line must have 4 fields, not 5: there is no ADDRESS field because it doesn't apply to Unix domain sockets (which is what local really means), only to TCP connections (which are declared with TYPE being host or hostssl)

When you reload PostgreSQL with a pg_hba.conf with this line, it will fail with

LOG:  invalid authentication method "127.0.0.1/32"

but you will see that only if looking in the server log. As a result of the failure, it will ignore your new version of pg_hba.conf.

Anyway this other line already in your file

host    all             all             127.0.0.1/32            md5

probably does what you want. In this case just remove the offending line, reload again, and check the server log for a message indicating the success of the reload.