Re: [GENERAL] Unable to connect to Postgresql

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...

> ------------------------------------------
> [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
>
> # "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 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?

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development