Re: [GENERAL] Unable to connect to Postgresql - Mailing list pgsql-general

On Sunday 09 April 2017 20:01:32 Adrian Klaver wrote:
> 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.htm
> l "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?

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 ?

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?

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