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.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
