Re: [GENERAL] Unable to connect to Postgresql

On Sunday 09 April 2017 15:41:12 Joe Conway wrote:
> On 04/09/2017 12:37 PM, John Iliffe wrote:
> >> > Thanks Joe.  I Changed the pg_connect line in the script to:
> >> >   $db_handle = pg_connect('dbname=yrarc host=192.168.1.6 port=5432
> >> >
> >> > user=xxxx password=xxxxxx');
> >
> > If I may be permitted a rant at this point, the Fedora documentation
> > is almost useless for SELinux and much of the underlying operating
> > system.
>
> Maybe so, but as I said earlier if you are in permissive, then blaming
> any of these issues on selinux is wrong -- selinux does no enforcement
> in permissive.
>
> >> You have no pg_hba.conf rule for host=192.168.1.6 so it is not
> >> surprising that cannot connect. You need something like:
> >>
> >> # only allow connections from one host using tcp
> >> host    all             all             192.168.1.6/32            md5
> >>
> >> - or maybe -
> >>
> >> # only allow connections from same subnet using tcp
> >> host    all             all             192.168.1.0/24            md5
> >
> > I don't think I should need that since httpd/mod_php is on the same
> > machine so should be 127.0.0.1 should cover it.
>
> No, not if your connection string is
>
>  'dbname=yrarc host=192.168.1.6 port=5432 user=xxxx password=xxxxxx'

I'm not sure why "localhost" didn't work on the first pass; I just rebooted,
changed the connection string to:

    $db_handle = pg_connect('dbname=yrarc host=localhost port=5432
user=xxxx password=xxxxxxx');

and got a connection.  So, it looks like I can connect using TCP on the
loopback.   Still doesn't work on the Unix domain socket though.

>
> as you said it was. In this case you must have one of the two pg_hba
> lines above or something more broad. With that connection string you are
> not connecting to localhost, you are connecting to 192.168.1.6 and there
> MUST be a pg_hba line to support it.
>
> > "trust" to avoid any problems with permissions in Postgresql.
>
> Trust has nothing whatsoever to do with the problems you have shown us
> so far.

No, I agree with you, but I just didn't want to open another can of worms.
One of the problems here is too many changes and too many variables.  Now
that I can connect I changed it back to "password" and everything still
works.
>
> > PHP and Postgresql are both running on same box.  It does have two
> > interfaces, 192.168.1.6 and 192.168.1.7, and the test programme is
> > available on either.  The reference to 192.168.1.10 is the client
> > machine, in this case my workstation, which is 192.168.1.10.
>
> Here is the error message you said came from the box running PHP:
> >> > ------------------------------------------
> >> > [Sun Apr 09 14:08:16.178126 2017] [php7:warn] [pid 24917:tid
> >> > 139671464015616] [client 192.168.1.10:59260] PHP Warning:
> >> > pg_connect(): Unable to connect to PostgreSQL server: could not
> >> > connect to server: Connection refused\n\tIs the server running on
> >> > host "192.168.1.6" and accepting\n\tTCP/IP connections on
> >> > port 5432? in
> >> > /httpd/iliffe/testfcgi.php on line 132
> >> > -------------------------------------------
>
No, the workstation doesn't have PHP installed on it.

This message is coming from Apache's error log on 192.168.1.6.  Apache logs
the source address of the client (ie my workstation at 192.168.1.10) and
then follows with the PHP error message "PHP Warning: pg_connect(): Unable
to connect to PostgreSQL server: could not connect to server..."

> Now, if your workstation is 192.168.1.10, then I can only conclude that
> you are running PHP on your workstation and *not* on the box with
> Postgres which you say uses 192.168.1.6 and 192.168.1.7.
>
> Joe