Postgres SQLSTATE[08006] [7] timeout expired
I have three servers behind a load balancer and a fourth server solely for
Postgres 10 database that is not behind the load balancer. All four are behind
the same firewall, with port 5432 open.
I have a simple browser-based html form to submit email addresses, usernames
and passwords; the form submits through a jQuery program that calls a php
program.
I can navigate the browser to the IP address of the server that hosts the
Postgres database and submit records to the database with no problems. When I
navigate to the site's URL (which goes through the load balancer), I always get
"SQLSTATE[08006] [7] timeout expired" after a short delay -- in other words, no
connection.
The php program is very simple:
'xxx.xx.xx.xx',
'port' => '5432',
'dbname' => '[DBNAME]',
'user' => '[USERNAME]',
'password' => '[PASSWORD]',
]);
echo 'HTTP Referer ' . $_SERVER['HTTP_REFERER'];
echo PHP_EOL;
echo 'IP Address ' . $_SERVER["REMOTE_ADDR"];
echo PHP_EOL;
try{
// create a PostgreSQL database connection
echo "I'm here now";
echo PHP_EOL;
//ini_set("default_socket_timeout", 300);
$pdo = new PDO($dsn);
$pdo->setAttribute(PDO::ATTR_TIMEOUT, 300);
// display a message if connected to the PostgreSQL successfully
if($pdo){
echo "Connected to the database successfully!";
echo PHP_EOL;
}
}catch (PDOException $e){
// report error message
echo $e->getMessage();
exit();
}
$pdo = null;
?>
The echo messages are simply debugging progress messages for the browser's dev
console.
The pg_hba.conf has these lines enabled:
pg_hba.conf:
hostall [username] 0.0.0.0/0 trust
hostall all 0.0.0.0/0 md5
hostall all ::/0 md5
hostall all allmd5
The $_SERVER["REMOTE_ADDR"]; line shown in my PHP program above always comes
back with the address of the load balancer.
I'm new to Postgres, so any help with this connection problem will be very
appreciated. Thanks a lot.
Re: Postgres SQLSTATE[08006] [7] timeout expired
I have enabled logging. I chose the highest level of recording so I would have
the best chance of finding out what's wrong. My error log is written to a .csv
file. I initiated a browser-based transaction to insert records into a
database. That transaction produced 1,299 records, with what appear to be
repeating blocks. I don't see anything in here that specifically tells me why
the client is not able to connect.
The last 26 lines look like representative of the transaction blocks:
StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGR,
xid/subid/cid: 0/1/0
pg_statistic: vac: 0 (threshold 129), anl: 0 (threshold 89)
pg_type: vac: 0 (threshold 125), anl: 0 (threshold 88)
pg_authid: vac: 0 (threshold 51), anl: 0 (threshold 51)
pg_attribute: vac: 0 (threshold 566), anl: 0 (threshold 308)
pg_proc: vac: 0 (threshold 629), anl: 0 (threshold 339)
pg_class: vac: 0 (threshold 118), anl: 0 (threshold 84)
pg_index: vac: 0 (threshold 77), anl: 0 (threshold 63)
pg_opclass: vac: 0 (threshold 77), anl: 0 (threshold 63)
pg_am: vac: 0 (threshold 51), anl: 0 (threshold 51)
pg_amop: vac: 0 (threshold 192), anl: 0 (threshold 121)
pg_amproc: vac: 0 (threshold 132), anl: 0 (threshold 91)
pg_cast: vac: 0 (threshold 94), anl: 0 (threshold 72)
pg_namespace: vac: 0 (threshold 51), anl: 0 (threshold 51)
pg_database: vac: 0 (threshold 50), anl: 0 (threshold 50)
pg_tablespace: vac: 0 (threshold 50), anl: 0 (threshold 50)
CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGR,
xid/subid/cid: 0/1/0
shmem_exit(0): 1 before_shmem_exit callbacks to make
shmem_exit(0): 7 on_shmem_exit callbacks to make
proc_exit(0): 2 callbacks to make
exit(0)
shmem_exit(-1): 0 before_shmem_exit callbacks to make
shmem_exit(-1): 0 on_shmem_exit callbacks to make
proc_exit(-1): 0 callbacks to make
reaping dead processes
server process (PID 15930) exited with exit code 0
My problem now is I don't know what to look for to find out why the client is
not able to connect within the timeout period. The dev console (Chrome) always
ends with a display of the echo messages up to the lines:
$pdo = new PDO($dsn);
$pdo->setAttribute(PDO::ATTR_TIMEOUT, 300);
And the final line is the Chrome dev console is SQLSTATE[08006] [7] timeout
expired.
The load balancer is not the issue; if I point the browser to the IP address of
one of the servers behind the load balancer (bypassing the load balancer), the
same thing happens. Only when I go to the IP address of the server with the
Postgres database will the transaction complete, and it happens quickly.
Thanks for any tips on what to look for in the csv log file to explain why the
browser-based PHP connection does not complete
Howard
‐‐‐ Original Message ‐‐‐
On Sunday, August 25, 2019 9:36 AM, Condor wrote:
> On 25-08-2019 02:54, Adrian Klaver wrote:
>
> > On 8/24/19 3:42 PM, Howard Wells wrote:
> >
> > > I have three servers behind a load balancer and a fourth server solely
> > > for Postgres 10 database that is not behind the load balancer. All
> > > four are behind the same firewall, with port 5432 open.
> > > I have a simple browser-based html form to submit email addresses,
> > > usernames and passwords; the form submits through a jQuery program
> > > that calls a php program.
> > > I can navigate the browser to the IP address of the server that hosts
> > > the Postgres database and submit records to the database with no
> > > problems. When I navigate to the site's URL (which goes through the
> > > load balancer), I always get "SQLSTATE[08006] [7] timeout expired"
> > > after a short delay -- in other words, no connection.
> > > The php program is very simple:
> > > > > echo 'Hello ' . htmlspecialchars($_POST["firstname"]) . '!' ;
> > > echo PHP_EOL;
> > > $dsn = vsprintf('pgsql:host=%s;port=%s;dbname=%s;user=%s;password=%s',
> > > [
> > > 'host' => 'xxx.xx.xx.xx',
> > > 'port' => '5432',
> > > 'dbname' => '[DBNAME]',
> > > 'user' => '[USERNAME]',
> > > 'password' => '[PASSWORD]',
> > > ]);
> > > echo 'HTTP Referer ' . $_SERVER['HTTP_REFERER'];
> > > echo PHP_EOL;
> > > echo 'IP Address ' . $_SERVER["REMOTE_ADDR"];
> > > echo PHP_EOL;
> > > try{
> > > // create a PostgreSQL database connection
> > > echo "I'm here now";
> > > echo PHP_EOL;
> > > //ini_set("default_socket_timeout", 300);
> > > $pdo = new PDO($dsn);
> > > $pdo->setAttribute(PDO::AT
Re: Postgres SQLSTATE[08006] [7] timeout expired
Thanks for the replies. I am getting the information requested by Adrian Klaver. Rob Sargent, I am going to temporary enable full trust because the password authentication could be the issue. Then I'll write back. Howard ‐‐‐ Original Message ‐‐‐ On Sunday, August 25, 2019 12:20 PM, Rob Sargent wrote: > > > > > > > > > console. > > > > > > The pg_hba.conf has these lines enabled: > > > > > > pg_hba.conf: > > > > > > host all [username] 0.0.0.0/0 trust > > > > > > host all all 0.0.0.0/0 md5 > > > > > > host all all ::/0 md5 > > > > > > host all all all > > Who is encrypting the password? > > >
Re: Postgres SQLSTATE[08006] [7] timeout expired
I enabled "host all all 0.0.0.0/0 trust" in pg_hba.conf and restarted Postgres, but I still get the same behavior. It looks like password authentication is not the issue. I enabled MD5, but I didn't know I would have to do anything else; from this test, that doesn't seem to be the problem. I'm getting the load balancer info. Thanks again. Howard ‐‐‐ Original Message ‐‐‐ On Sunday, August 25, 2019 12:48 PM, Howard Wells wrote: > Thanks for the replies. I am getting the information requested by Adrian > Klaver. Rob Sargent, I am going to temporary enable full trust because the > password authentication could be the issue. Then I'll write back. > > Howard > > ‐‐‐ Original Message ‐‐‐ > On Sunday, August 25, 2019 12:20 PM, Rob Sargent robjsarg...@gmail.com wrote: > > > > > > > > > > > > > > > > > console. > > > > > > > The pg_hba.conf has these lines enabled: > > > > > > > pg_hba.conf: > > > > > > > host all [username] 0.0.0.0/0 trust > > > > > > > host all all 0.0.0.0/0 md5 > > > > > > > host all all ::/0 md5 > > > > > > > host all all all > > > > Who is encrypting the password? > > > > >
Re: Postgres SQLSTATE[08006] [7] timeout expired
I solved this problem. All four servers are behind a firewall, but port 5432 was not open on the firewall. When I opened 5432, the problem disappeared. Thanks to Rob Sargent, Adrian Klaver and Condor for your help. Howard ‐‐‐ Original Message ‐‐‐ On Sunday, August 25, 2019 12:20 PM, Rob Sargent wrote: > > > > > > > > > console. > > > > > > The pg_hba.conf has these lines enabled: > > > > > > pg_hba.conf: > > > > > > host all [username] 0.0.0.0/0 trust > > > > > > host all all 0.0.0.0/0 md5 > > > > > > host all all ::/0 md5 > > > > > > host all all all > > Who is encrypting the password? > > >
Re: Postgres SQLSTATE[08006] [7] timeout expired
On my first post, I asked the person in charge of the firewall if 5432 was open and he told me it was. When I checked it myself later, I found out that it wasn't. Before I post to this list again, I'll check all assumptions myself. "Only when I go to the IP address of the server with the Postgres database will the transaction complete, and it happens quickly." My best guess is that when the IP address is also the address of the Postgres server, the firewall does not interfere. Howard ‐‐‐ Original Message ‐‐‐ On Sunday, August 25, 2019 4:55 PM, Adrian Klaver wrote: > On 8/25/19 2:59 PM, Howard Wells wrote: > > > I solved this problem. All four servers are behind a firewall, but port > > 5432 was not open on the firewall. When I opened 5432, the problem > > disappeared. > > Glad you solved. Confused as to how: > > 1. From first post: > "All four are behind the same firewall, with port 5432 open." > > 2. A subsequent post: > > "Only when I go to the IP address of the server with the Postgres > database will the transaction complete, and it happens quickly." > > > > Thanks to Rob Sargent, Adrian Klaver and Condor for your help. > > Howard > > ‐‐‐ Original Message ‐‐‐ > > On Sunday, August 25, 2019 12:20 PM, Rob Sargent robjsarg...@gmail.com > > wrote: > > > > > > > > > > > > > > > > > > > > > console. > > > > > > > > The pg_hba.conf has these lines enabled: > > > > > > > > pg_hba.conf: > > > > > > > > host all [username] 0.0.0.0/0 trust > > > > > > > > host all all 0.0.0.0/0 md5 > > > > > > > > host all all ::/0 md5 > > > > > > > > host all all all > > > > > > Who is encrypting the password? > > > > > > > > > -- > > Adrian Klaver > adrian.kla...@aklaver.com
How to access Postgres .pgpass file from php?
I have my php files in the web root, and the Postgres 10 logon credentials are
in the php file. I want to put them outside the web root for security, because
a malicious robot could easily read the credentials.
After research, I found the .pgpass file. That looks like the ideal solution,
but after even more research, I haven't found how to use that file from a php
logon script.
Here is the section from my php script:
$dsn = vsprintf('pgsql:host=%s;port=%s;dbname=%s;user=%s;password=%s', [
'host' => '000.00.00.00',
'port' => '5432',
'dbname' => '[dbname]',
'user' => '[username]',
'password' => '[password]',
]);
Currently I store the real dbname, user and password in the php. My questions
are:
1. How can I access it from the .pgpass file instead?
2. Where is .phpass loccated in Apache2 Ubuntu 18.04?
Thanks for any help with this.
Howard
