Re: [GENERAL] Unable to connect to Postgresql

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

> 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