Re: [GENERAL] Unable to connect to Postgresql

On Sunday 09 April 2017 15:38:10 Adrian Klaver wrote:
> On 04/09/2017 12:37 PM, John Iliffe wrote:
> > On Sunday 09 April 2017 14:34:01 Joe Conway wrote:
> >> On 04/09/2017 11:33 AM, John Iliffe wrote:
> >>> On Saturday 08 April 2017 18:10:35 Joe Conway wrote:
> >>>> On 04/08/2017 01:23 PM, John Iliffe wrote:
> >>>>> On Saturday 08 April 2017 09:38:07 Adrian Klaver wrote:
> >>>>>> So what if you change the connection to use -h localhost?
> >>>>>
> >>>>> Can you please expand on that request?  I'm not sure where you
> >>>>> want me to put that directive.  I'm using the mod_php module in
> >>>>> Apache.
> >>>>
> >>>> See the second example here:
> >>>>
> >>>> http://php.net/manual/en/function.pg-connect.php
> >>>>
> >>>> 8<-------------
> >>>> $dbconn2 = pg_connect("host=localhost port=5432 dbname=mary");
> >>>> // connect to a database named "mary" on "localhost" at port "5432"
> >>>> 8<-------------
> >>>>
> >>>> That will try to use a tcp connection on localhost instead of a
> >>>> unix socket.
> >>>
> >>> 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');
> >>> ---------------------------
> >>>
> >>> Even though "localhost" is in the /etc/hosts file the lookup failed
> >>> to
> >>
> >>> resolve so I provided the full IP address.  The error from Apache is:
> >> You have some very odd issues with your machine...
> >
> > No, Fedora/Red Hat has made a mess!  I just installed everything
> > (APache, Postgresql, PHP, OpenSSL from source as I always have over a
> > period of more than 15 years.  This is the first time I have run into
> > this sort of problem and it seems to be related to systemctl's unit
> > files.
> >
> > 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. Very nice on theory but nothing on the details or "What is
> > required?/How do I do it?"
> >
> > In a small company like mine, we are all multi-tasked and having a
> > specialist for everything, like a system programmer skilled in Linux,
> > is just not on.  Things have to work right out of the box.  Server
> > administration, system operations, etc, is just not my core skill,
> > and I depend heavily on the open source community for help (like you
> > at the moment).
> >
> >>> ------------------------------------------
> >>> [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
> >>> -------------------------------------------
> >>>
> >>> PHP does not show anything in its log.
> >>>
> >>>> Another question I don't believe has been asked is, what does your
> >>>> pg_hba.conf look like?
> >>>
> >>> Note here that I have deleted a number of production users and the
> >>> associated databases from the file shown below for security reasons.
> >>> The user marked "XXXXXXX" has a real name but isn't the one we are
> >>> using to connect to the database, so the active line should be the
> >>> "local all all password" line.  The UID being used to connect IS in
> >>> the password list and PSQL can still connect OK.  The yrarc database
> >>> does exist and contains several tables.
> >>>
> >>> --------------------------------------
> >>> # TYPE  DATABASE        USER            ADDRESS
> >>> METHOD
> >
> > i> >
> >
> >>> # "local" is for Unix domain socket connections only
> >>> #local   all             all                                     md5
> >>>
> >>> local   yrarc          XXXXXXX
> >>> trust local   all             all
> >>> password #local   all             all
> >>>
> >>>     trust # IPv4 local connections:
> >>> host    all             all             127.0.0.1/32
> >>> trust ---------------------------------------------
> >>
> >> 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.  I did try it though
> > and set it to "trust" to avoid any problems with permissions in
> > Postgresql.   I then stopped and restarted both Postgres and Apache
> > and still get the same error in the log from pg_connect asking
> > whether the socket is available.
>
> Remember host != local
>
> host is for IP connections
>
> local is for socket connections

Yes, I had forgotten that for the moment.  I have the following line in the
'local' section of the pg_hba.conf file:

local   all             all                                     password

and this in the 'host' section

host    all             all             127.0.0.1/32            password

and at the moment I can connect using this:

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

but NOT using this:

$db_handle = pg_connect('dbname=yrarc user=yrcro password=yrreadonly');

so I have a problem with the domain sockets.

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
>
> So if you set up a host line pointing to 127.0.0.1 and connected without
> using host in the connection string the 127.0.0.1 host line will not be
> used, instead the first matching local line will. Furthermore in your
> examples when you did connect using host= you used an IP that was not
> 127.0.0.1, so the connection would not use the 127.0.0.1 line anyway.
> That is why I made the suggestion to use host=localhost or if you want
> host=127.0.0.1 .
>
> >> I just noticed in the message above "client 192.168.1.10". I thought
> >> you had said earlier that PHP was running on the same box as
> >> Postgres? So that box uses both 192.168.1.6 and 192.168.1.10 on two
> >> different interfaces?
> >
> > 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.
> >
> >> Joe