Re: [GENERAL] Unable to connect to Postgresql

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

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
>


--
Adrian Klaver
adrian.klaver@aklaver.com