Re: [GENERAL] Unable to connect to Postgresql

On Sunday 09 April 2017 23:21:58 Adrian Klaver wrote:
> On 04/09/2017 05:30 PM, John Iliffe wrote:
> > On Sunday 09 April 2017 20:01:32 Adrian Klaver wrote:
> >> So the issue is in PHP via Apache using the socket, because if I
> >> remember right you used localhost in the Apache/PHP combination and
> >> it worked, correct?
> >
> > Yes.
> >
> > I think there is some confusion here, might be on my part, I don't
> > know.
> >
> > There is a network connection from 192.168.1.10 to 192.168.1.6 to
> > Apache and then there should be a connection from Apache on using
> > localhost (or 127.0.0.1) to Postgresql.  So shouldn't that be
> > sufficient?  Other than the original error on my part, coding the
> > server's external address (192.168.1.6) in the db_connect() call
> > which is now fixed, shouldn't the pg_hba host address  line be
> > 127.0.0.1/32 ?
>
> Yes, Apache is connecting to Postgres on the same machine so localhost
> should be sufficient for IP purposes.
>
> Not sure that it applies here, but what does ifconfig show?
>
-------------------------------------------------------
root@prod04 John]# ifconfig -a
enp0s20f0u10: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.1.7  netmask 255.255.255.0  broadcast 192.168.1.255
        inet6 fe80::84a:4eb9:f4a4:98a6  prefixlen 64  scopeid 0x20<link>
        ether 54:b8:0a:ef:c7:0f  txqueuelen 1000  (Ethernet)
        RX packets 210126  bytes 238984885 (227.9 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 120884  bytes 14119037 (13.4 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

enp0s31f6: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.1.6  netmask 255.255.255.255  broadcast 192.168.1.6
        inet6 fe80::62d4:f478:8bbb:34a1  prefixlen 64  scopeid 0x20<link>
        ether 38:d5:47:19:0d:cb  txqueuelen 1000  (Ethernet)
        RX packets 4589  bytes 1072227 (1.0 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 1003  bytes 71483 (69.8 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0
        device interrupt 16  memory 0xf7000000-f7020000

lo: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10<host>
        loop  txqueuelen 1000  (Local Loopback)
        RX packets 106993  bytes 22736948 (21.6 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 106993  bytes 22736948 (21.6 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0
-------------------------------------------

The actual connection being used should be on device "lo" which is on the
"localhost" address of 127.0.0.1.

> > Anyhow, that is working properly now.  The domain socket doesn't have
> > an explicit address (for 'local') as it is on the current machine as
> > far as I understand.
> >
> > Am I correct?
>
> Correct. The issue is then why psql and stand alone PHP can see the
> domain socket while PHP through Apache does not. Not sure why that is at
> the moment. Hmm, had a thought. What user is Apache running as and does
> that user have permissions on the socket file(s)?
>
Looks like anybody can connect to either of the Postgresql domain sockets.

socket on /tmp
----------------------------------------
srwxrwxrwx.  1 postgres postgres system_u:object_r:initrc_tmp_t:s0    0 Apr
10 10:35 .s.PGSQL.5432
----------------------------------------

second socket on /var/pgsql
----------------------
srwxrwxrwx.  1 postgres postgres system_u:object_r:httpd_var_run_t:s0
0 Apr 10 10:35 .s.PGSQL.5432
----------------------

Apache is running under userid apache as expected
-----------------------------------
[root@prod04 John]# ps -ef | grep httpd
root       420   356  0 10:39 pts/0    00:00:00 grep --color=auto httpd

root     27753     1  0 Apr09 ?        00:00:00
/usr/apache-2.4.25/bin/httpd -k start

apache   27754 27753  0 Apr09 ?        00:00:03
/usr/apache-2.4.25/bin/httpd -k start

apache   27755 27753  0 Apr09 ?        00:00:04
/usr/apache-2.4.25/bin/httpd -k start

apache   27756 27753  0 Apr09 ?        00:00:04
/usr/apache-2.4.25/bin/httpd -k start
------------------------------------

with the following SELinux context info:
---------------------------------------
ps -eZ | grep httpd
system_u:system_r:unconfined_service_t:s0 27753 ? 00:00:00 httpd
system_u:system_r:unconfined_service_t:s0 27754 ? 00:00:03 httpd
system_u:system_r:unconfined_service_t:s0 27755 ? 00:00:04 httpd
system_u:system_r:unconfined_service_t:s0 27756 ? 00:00:04 httpd

------------------------------------------

> > John
> >
> >>>> Report back.
> >>>>
> >>>>> Based on the reference that Joe sent earlier, I do have a second
> >>>>> domain socket on /var/pgsql but the problem is how do I get PHP to
> >>>>> look there? There isn't any config file for mod_php and php-fpm
> >>>>> has one but the location of the domain socket is the default -
> >>>>> /tmp/.s.......
> >>>>>
> >>>>> I don't think this is the problem if this list unless someone
> >>>>> happens to know the solution.  If not, then thank you for all the
> >>>>> work, and especially for the promptness of the responses.   I'm
> >>>>> not at all sure that I could have figured this out by myself.
> >>>>>
> >>>>> John