Re: Unable to connect to Postgresql

On 04/09/2017 02:35 PM, John Iliffe wrote:
> On Sunday 09 April 2017 17:02:47 Adrian Klaver wrote:
>> On 04/09/2017 02:00 PM, John Iliffe wrote:
>>> On Sunday 09 April 2017 15:38:10 Adrian Klaver wrote:
>>>> 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.
>>
>> I don't think it has been asked and for the sake of completeness, what
>> do you have listen_addresses set to in postgresql.conf?
>
> Still set to the default:
>
> #listen_addresses = 'localhost' # what IP address(es) to listen on;
> # comma-separated list of

Well that would explain why connecting via 192.1.168.x would not work,
Postgres is only listening on the loopback interface:

https://www.postgresql.org/docs/9.6/static/runtime-config-connection.html
"listen_addresses (string)

Specifies the TCP/IP address(es) on which the server is to listen
for connections from client applications. The value takes the form of a
comma-separated list of host names and/or numeric IP addresses. The
special entry * corresponds to all available IP interfaces. The entry
0.0.0.0 allows listening for all IPv4 addresses and :: allows listening
for all IPv6 addresses. If the list is empty, the server does not listen
on any IP interface at all, in which case only Unix-domain sockets can
be used to connect to it. The default value is localhost, which allows
only local TCP/IP "loopback" connections to be made. While client
authentication (Chapter 20) allows fine-grained control over who can
access the server, listen_addresses controls which interfaces accept
connection attempts, which can help prevent repeated malicious
connection requests on insecure network interfaces. This parameter can
only be set at server start.
"

> addresses;
> # defaults to 'localhost'; use '*'
> for all
> # (change requires restart)
> #port = 5432 # (change requires restart)
>
> I did change the Unix domain socket directories:
>
> #unix_socket_directories = '/tmp' # comma-separated list of
> directories
> unix_socket_directories = '/tmp,/var/pgsql' # *****changed from default
> #
>
>>
>> To solve this is going to require starting as close to the Postgres
>> server as possible and use a consistent connection string between psql
>> and your PHP code. For the time being I would leave the Apache server
>> out of the loop as well as your workstation(as much as possible).
>>
>> So:
>>
>> 1) Log into the machine with the Postgres server.
>>
>> 2) Using psql:
>>
>> psql 'dbname=yrarc user=yrcro password=yrreadonly'
> worked, no problem. Connected to the database and allowed me to select
> anything as expected.
>
>>
>> 3) Using a standalone PHP script:
>>
>> $db_handle = pg_connect('dbname=yrarc user=yrcro password=yrreadonly')
>>
> Worked perfectly as a standalone PHP programme. Connected and retrieved a
> record from the database.

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?

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

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com