php connection failure
Readers,
Background: http://news-web.php.net/php.general/327600
Since further configuration, remain unable to connect to the php database.
Software system is debian, xfce, postgresql, php. Database created on local machine with directory of web server content in extant normal user account. Then created another normal user:
--ingroup [extant normal user] --no-create-home --disabled-login [new normal user of same name as postgresql new role created for this database]
Please what is the next investigation to make?
On 8/7/21 11:46 AM, ourdiaspora wrote:
Readers,
Background: http://news-web.php.net/php.general/327600
Since further configuration, remain unable to connect to the php database.
Further configuration of what?
After the configuration change did you restart the server?
Is the pg_hba.conf file the correct one for the database instance?
What does:
pg_lsclusters
show?
Software system is debian, xfce, postgresql, php. Database created on local machine with directory of web server content in extant normal user account. Then created another normal user:
--ingroup [extant normal user] --no-create-home --disabled-login [new normal user of same name as postgresql new role created for this database]
Please what is the next investigation to make?
--
Adrian Klaver
adrian.klaver@aklaver.com
On Saturday, August 7th, 2021 at 8:23 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
Since further configuration, remain unable to connect to the php database.
Further configuration of what?
The postgresql configuration file:
SHOW hba_file;
/etc/postgresql/9.6/main/pg_hba.conf
SHOW config_file;
/etc/postgresql/9.6/main/postgresql.conf
After the configuration change did you restart the server?
Yes; the php web page recognised changes, for example removal of the comment instruction:
"
<html>
<head>
Generic CPAC
</head>
<body>
<?php
//echo '<p>Hello World</p>'
/*successful test of configuration of apache http server*/
phpinfo();
?>
</body>
</html>
"
Is the pg_hba.conf file the correct one for the database instance?
Changes were made to the function 'listen_addressess' to the configuration file shown in above.
What does:
pg_lsclusters
show?
pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
9.6 main 5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log
On 8/7/21 1:09 PM, ourdiaspora wrote:
On Saturday, August 7th, 2021 at 8:23 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
Since further configuration, remain unable to connect to the php database.
Further configuration of what?
The postgresql configuration file:
SHOW hba_file;
/etc/postgresql/9.6/main/pg_hba.conf
SHOW config_file;
/etc/postgresql/9.6/main/postgresql.confAfter the configuration change did you restart the server?
Yes; the php web page recognised changes, for example removal of the comment instruction:
"
<html>
<head>
Generic CPAC
</head>
<body>
<?php
//echo '<p>Hello World</p>'
/*successful test of configuration of apache http server*/
phpinfo();
?>
</body>
</html>
"
That is the Apache server restarting. I was asking about restarting the
Postgres server. Was that done?
Is the pg_hba.conf file the correct one for the database instance?
Changes were made to the function 'listen_addressess' to the configuration file shown in above.
The connection was reaching the database so the 'listen_addresses' is
correct. The issue is with the settings in pg_hba.conf and the
connection being attempted. There have been similar problems with
various OSes and their handling of localhost/120.0.0.2/::1.
So for the machine that is running the Postgres server:
1) OS and version?
2) The contents of /etc/hosts
3) What happens if you do?:
psql -d cpacweb -h 127.0.0.1 -U cpaca
What does:
pg_lsclusters
show?
pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
9.6 main 5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log
--
Adrian Klaver
adrian.klaver@aklaver.com
On 8/7/21 1:09 PM, ourdiaspora wrote:
On Saturday, August 7th, 2021 at 8:23 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
Changes were made to the function 'listen_addressess' to the configuration file shown in above.
I should have asked in previous post, what was 'listen_addresses'
changed to?
--
Adrian Klaver
adrian.klaver@aklaver.com
On Saturday, August 7th, 2021 at 10:17 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 8/7/21 1:09 PM, ourdiaspora wrote:
On Saturday, August 7th, 2021 at 8:23 PM, Adrian Klaver adrian.klaver@aklaver.com wrote:
Changes were made to the function 'listen_addressess' to the configuration file shown in above.
I should have asked in previous post, what was 'listen_addresses'
changed to?
The postgresql server was restarted, successfully.
listen_addresses = 'localhost'
On 8/7/21 3:15 PM, ourdiaspora wrote:
On Saturday, August 7th, 2021 at 10:17 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 8/7/21 1:09 PM, ourdiaspora wrote:
On Saturday, August 7th, 2021 at 8:23 PM, Adrian Klaver adrian.klaver@aklaver.com wrote:
Changes were made to the function 'listen_addressess' to the configuration file shown in above.
I should have asked in previous post, what was 'listen_addresses'
changed to?
The postgresql server was restarted, successfully.
listen_addresses = 'localhost'
And the answers to these previous questions:
So for the machine that is running the Postgres server:
1) OS and version?
2) The contents of /etc/hosts
3) What happens if you do?:
psql -d cpacweb -h 127.0.0.1 -U cpaca
--
Adrian Klaver
adrian.klaver@aklaver.com
ourdiaspora <ourdiaspora@protonmail.com> writes:
The postgresql server was restarted, successfully.
listen_addresses = 'localhost'
I believe on most machines, that would result in the postmaster
only listening on 127.0.0.1, and ::1 if you're IPv6-enabled.
If php is trying to connect to something else, like say the
machine's external IP address, that could be your issue.
regards, tom lane
On Saturday, August 7th, 2021 at 11:20 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
1. OS and version?
Same local machine as described
2. The contents of /etc/hosts
127.0.0.1 localhost
::1 localhost ip6-localhost ip6-loopback
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
3. What happens if you do?:
psql -d cpacweb -h 127.0.0.1 -U cpaca
psql -d cpacweb -h 127.0.0.1 -U cpaca
psql: could not connect to server: Connection refused
Is the server running on host "127.0.0.1" and accepting
TCP/IP connections on port 5432?
sudo /etc/init.d/apache2 status
[ ok ] apache2 is running.
sudo service postgresql status
9.6/main (port 5432): online
On Sunday, August 8th, 2021 at 12:11 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
If php is trying to connect to something else, like say the
machine's external IP address, that could be your issue.
Excuse the ignorance, but if the instruction in the php file is:
"
...$dbconn = pg_connect("dbname=cpacweb user=cpaca host=localhost")...
"
why should php try to connect to anything except 'localhost', as instructed?
ourdiaspora <ourdiaspora@protonmail.com> writes:
psql -d cpacweb -h 127.0.0.1 -U cpaca
psql: could not connect to server: Connection refused
Is the server running on host "127.0.0.1" and accepting
TCP/IP connections on port 5432?
Hm --- this suggests that you've got a kernel packet filter (i.e.
software firewall) that is not passing traffic for 5432.
It's not very clear how it worked before if that were in place
previously, but since you've said zero about what the working
configuration looked like, that's the best I've got.
regards, tom lane
On Saturday, August 7, 2021, ourdiaspora <ourdiaspora@protonmail.com> wrote:
SHOW hba_file;
/etc/postgresql/9.6/main/pg_hba.conf
Please post the contents of this file. The one shown at the link is
invalid.
David J.
On Saturday, August 7, 2021, Tom Lane <tgl@sss.pgh.pa.us> wrote:
ourdiaspora <ourdiaspora@protonmail.com> writes:
psql -d cpacweb -h 127.0.0.1 -U cpaca
psql: could not connect to server: Connection refused
Is the server running on host "127.0.0.1" and accepting
TCP/IP connections on port 5432?Hm --- this suggests that you've got a kernel packet filter (i.e.
software firewall) that is not passing traffic for 5432.
Agreed.
It's not very clear how it worked before if that were in place
previously, but since you've said zero about what the working
configuration looked like, that's the best I've got.
I don’t see where it ever did. Only local connections ever were shown to
have worked and nothing has been said the non-local connections stopped
working, just that none of the changes to config files got them to work
(which makes sense as it doesn’t seem our configuration is at fault, at
least not exclusively).
David J.
On Sunday, August 8th, 2021 at 12:26 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Hm --- this suggests that you've got a kernel packet filter (i.e.
software firewall) that is not passing traffic for 5432.
Don't know anything about that, so perhaps it can be assumed unchanged from whatever the default system configuration is...
On Sunday, August 8th, 2021 at 12:50 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Saturday, August 7, 2021, ourdiaspora <ourdiaspora@protonmail.com> wrote:
SHOW hba_file;
/etc/postgresql/9.6/main/pg_hba.conf
Please post the contents of this file. The one shown at the link is invalid.
local all postgres peer
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
# 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
local cpacweb cpaca trust
On Saturday, August 7, 2021, ourdiaspora <ourdiaspora@protonmail.com> wrote:
On Sunday, August 8th, 2021 at 12:50 AM, David G. Johnston <
david.g.johnston@gmail.com> wrote:On Saturday, August 7, 2021, ourdiaspora <ourdiaspora@protonmail.com>
wrote:
SHOW hba_file;
/etc/postgresql/9.6/main/pg_hba.conf
Please post the contents of this file. The one shown at the link is
invalid.
local all postgres peer
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
# 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
local cpacweb cpaca trust
Ok. The first line is just redundant but I see that the last line has been
made valid.
The reported error: FATAL: no pg_hba.conf entry for host "::1", user
"cpaca", database "cpacweb", SSL off
When put against that pg_hba.conf file doesn’t make sense. That was the
crux of the original post - everything here has just caused things to be
worse off since now we are seeing not listening errors
David J.
On 8/7/21 4:11 PM, ourdiaspora wrote:
On Saturday, August 7th, 2021 at 11:20 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
psql -d cpacweb -h 127.0.0.1 -U cpaca
psql: could not connect to server: Connection refused
Is the server running on host "127.0.0.1" and accepting
TCP/IP connections on port 5432?
What did you change listen_addresses from?
What if you do?:
psql -d cpacweb -U cpaca
sudo /etc/init.d/apache2 status
[ ok ] apache2 is running.sudo service postgresql status
9.6/main (port 5432): online
--
Adrian Klaver
adrian.klaver@aklaver.com
On Sunday, August 8th, 2021 at 1:02 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
What did you change listen_addresses from?
New line; same line had hash (#) to comment out.
What if you do?:
psql -d cpacweb -U cpaca
psql -d cpacweb -U cpaca
psql: FATAL: Peer authentication failed for user "cpaca"
On 8/7/21 5:36 PM, ourdiaspora wrote:
On Sunday, August 8th, 2021 at 1:02 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
What did you change listen_addresses from?
New line; same line had hash (#) to comment out.
What if you do?:
psql -d cpacweb -U cpaca
psql -d cpacweb -U cpaca
psql: FATAL: Peer authentication failed for user "cpaca"
So from a previous post of yours I have pasted the pg_hba.conf file
below my instructions:
1) Get rid of the first local line .
2) Then for the remaining local line below change peer to trust and
restart Postgres.
3) Then repeat:
psql -d cpacweb -U cpaca
local all postgres peer
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
# 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
local cpacweb cpaca trust
--
Adrian Klaver
adrian.klaver@aklaver.com
ourdiaspora <ourdiaspora@protonmail.com> writes:
psql -d cpacweb -U cpaca
psql: FATAL: Peer authentication failed for user "cpaca"
The reason that's able to make a connection to the PG server
is that it's not going through TCP at all, but a Unix socket.
I'd guess at this point that your configuration never worked
for TCP connections --- either localhost or not --- because
your kernel packet filter isn't permitting it. It's a gold
plated certainty that the default packet filter configuration
wouldn't allow traffic to port 5432 from off-machine. Whether
it would allow such traffic from localhost is less certain,
but the symptoms you're showing sure look like yours doesn't.
Saying you haven't touched that configuration doesn't excuse
you from investigating it.
regards, tom lane
