title: " PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: FATAL: password authentication failed for user ..." in Raspberry Pi (Stretch)
body: <p>So sorry to bother everyone, I'm new to <strong>PostgreSQL</strong>, 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 <strong>data insertion/manipulation</strong> from a <strong>Web Form</strong> into the server via <strong>PHP</strong> <code>pg_connect()</code>. The Web Form is located locally in <code>/var/www/html/web_form.html</code> and calls to a PHP script that performs the data insertion,</p>
  
  <pre><code>    &lt;form name="some_name" action="script.php" method="POST"&gt;
  </code></pre>
  
  <p>so the data insertion is done to a local server.</p>
  
  <p>I'm using a <strong>Raspberry Pi 3</strong> to simulate the "server" (as so not to "disturb" the true running server during such an early development). The Raspberry Pi is running a <strong>Raspbian Stretch</strong> distro. Well, it was originally Jessie, but then I decided to involve the Stretch repo from</p>
  
  <pre><code>    deb http://mirrordirector.raspbian.org/raspbian/ stretch main contrib non-free rpi
  </code></pre>
  
  <p>with a priority of <code>100</code>. It also has <code>postgresql-9.6.3</code> (from Stretch), <code>php7.0.19-1</code> (also from Stretch), and <code>apache2.4.25</code> (from official Raspbian). Please do note that the Stretch repo do replaces <strong>A LOT</strong> of Jessie's official stable packages, and also known to be <strong>"not quite stable"</strong> itself, so it might also be the source of the problem.</p>
  
  <p>I can access the database in the server from <strong>remote computers</strong>, whether using direct <code>psql</code>, Python (<code>psycopg2</code>), or even the exact same Web Form via PHP (tried from remote computer), so it will mostly won't be because of <strong>PDO problems</strong> (I've checked <code>phpinfo()</code> , but <strong>feel free</strong> to give advice on this, since I <strong>don't really understand PDO myself</strong>). On local, I can access the database via <code>psql</code>, but not local access via PHP (<code>pg_connect</code>).</p>
  
  <p>I've meddled with this problem for hours now in vain. Please help me.</p>
  
  <p>Here's my Raspberry Pi's configurations:</p>
  
  <p>1.
  A snippet of the PHP code:</p>
  
  <pre><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();
      }
  </code></pre>
  
  <p>Please mind that this same code <strong>has perfectly worked</strong> on another computer. I've <strong>succeeded</strong> 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 <strong>particular</strong> problem server. When running in browser, it only shows:</p>
  
  <pre><code>    Error 0:
  </code></pre>
  
  <p>the result when running in console:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I also have tried to change <code>host=localhost</code> to <code>host=127.0.0.1</code> or <code>host=0.0.0.0</code>. 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 <code>Error X:</code> number.</p>
  
  <p>2.
  The PostgreSQL configurations:</p>
  
  <p>For the <code>pg_hba.conf</code> content :</p>
  
  <pre><code>    # 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
  </code></pre>
  
  <p>I already tried to comment/uncomment LINE 7, or change both LINE 7 and 8 to either <code>md5</code> , <code>trust</code> , or <code>password</code> . I also have tried to remove <code>127.0.0.1/32</code> from LINE 7, or change it to <code>127.0.0.0/24</code> or <code>0.0.0.0/0</code> . And, yes, I did performed</p>
  
  <pre><code>    sudo /etc/init.d/postgresql reload
      sudo /etc/init.d/postgresql restart
  </code></pre>
  
  <p>(even <code>sudo reboot</code>) for every change I've made.</p>
  
  <p>For the <code>postgresql.conf</code> content :</p>
  
  <p>I already set <code>listen_addresses = '*'</code> and <code>password_encryption = on</code> , with the rest remained unchanged.</p>
  
  <p>3.
  Firewall :</p>
  
  <p><code>sudo iptables -L -n</code> doesn't show any entries : </p>
  
  <pre><code>    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  
  </code></pre>
  
  <p>What is wrong with my configurations?
  Are Stretch the cause?
  Or is it because the implementation of PostgreSQL-9.6?</p>
  
  <p>I've already googled here and there, but most of the solutions only advice to change the <code>pg_hba.conf</code> to <code>trust</code> , or assume that the <code>user</code> name is not exist.</p>
  
  <p>I'm desperate, please help.</p>
  
  <p>(Please do mind that <strong>I don't know anything</strong> regarding PHP, PostgreSQL, Apache, or server, so please don't expect that I really know what I've done so far. <strong>Please do analyze everything</strong>.... Also, English is not my native, so I might mixed around some "jargons" here and there (if any).... Sorry for that... )</p>