Re: [GENERAL] Unable to connect to Postgresql
On Monday 10 April 2017 19:37:43 Adrian Klaver wrote: > On 04/10/2017 01:23 PM, John Iliffe wrote: > > On Monday 10 April 2017 11:53:35 Daniel Verite wrote: > >>John Iliffe wrote: > >>> So, the problem is resolved, although I have no idea why it was > >>> necessary. > >> > >> The key seems to be the PrivateTmp=true in the systemd service. > >> Apache is not chrooted as demonstrated upthread, but that > >> setting alone makes the normal, system-wide /tmp inaccessible > >> to its processes, including the postgres Unix domain socket opened > >> there. > >> > >> I suspect that your attempt to set PrivateTmp=false as a test was > >> missed by systemd for some reason. You could probably insist > >> in that direction and eventually make it work, but I don't see > >> how it's better than the alternative /var/pgsql or localhost through > >> TCP. > >> > >>> Also, I now have several hundred programmes to update to add the > >>> host path and none of them will now be portable. > >> > >> Given that you set two directories: /tmp and /var/pgsql, > >> I would think you can let the other apps use /tmp as before > >> and have only Apache use /var/pgsql ? > > > > Yes, I will do that, but there are several hundred PHP web page > > scripts to be updated. Presumably if one script opens two different > > databases then both of the pg_connect() instances will need to be > > updated. > > Out of curiosity where did you install Postgres from? > > The reason I ask it that I was trying to figure why Fedora would invoke > private /tmp's for services and then have the Postgres socket be only in > the public /tmp. So I spun up a Fedora 25 instance and did an install of > Postgres from the Fedora repos and then from the PGDG repos. In either > case I got a postgresql.conf that had: > > unix_socket_directories = '/var/run/postgresql', '/tmp' > > Upstream you showed your default as: > > unix_socket_directories = '/tmp' > > which is what I see when I do a source install. > > So did you do a source install or did you copy a postgresql.conf from > somewhere else? I downloaded the source from https://www.postgresql.org/ftp/source/v9.6.2/ and installed according ot the notes included there. > > >> Best regards, -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On 04/10/2017 01:23 PM, John Iliffe wrote: On Monday 10 April 2017 11:53:35 Daniel Verite wrote: John Iliffe wrote: So, the problem is resolved, although I have no idea why it was necessary. The key seems to be the PrivateTmp=true in the systemd service. Apache is not chrooted as demonstrated upthread, but that setting alone makes the normal, system-wide /tmp inaccessible to its processes, including the postgres Unix domain socket opened there. I suspect that your attempt to set PrivateTmp=false as a test was missed by systemd for some reason. You could probably insist in that direction and eventually make it work, but I don't see how it's better than the alternative /var/pgsql or localhost through TCP. Also, I now have several hundred programmes to update to add the host path and none of them will now be portable. Given that you set two directories: /tmp and /var/pgsql, I would think you can let the other apps use /tmp as before and have only Apache use /var/pgsql ? Yes, I will do that, but there are several hundred PHP web page scripts to be updated. Presumably if one script opens two different databases then both of the pg_connect() instances will need to be updated. Seems I missed a message: " I just installed everything (APache, Postgresql, PHP, OpenSSL from source as I always have over a period of more than 15 years." Best regards, -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On 04/10/2017 01:23 PM, John Iliffe wrote: On Monday 10 April 2017 11:53:35 Daniel Verite wrote: John Iliffe wrote: So, the problem is resolved, although I have no idea why it was necessary. The key seems to be the PrivateTmp=true in the systemd service. Apache is not chrooted as demonstrated upthread, but that setting alone makes the normal, system-wide /tmp inaccessible to its processes, including the postgres Unix domain socket opened there. I suspect that your attempt to set PrivateTmp=false as a test was missed by systemd for some reason. You could probably insist in that direction and eventually make it work, but I don't see how it's better than the alternative /var/pgsql or localhost through TCP. Also, I now have several hundred programmes to update to add the host path and none of them will now be portable. Given that you set two directories: /tmp and /var/pgsql, I would think you can let the other apps use /tmp as before and have only Apache use /var/pgsql ? Yes, I will do that, but there are several hundred PHP web page scripts to be updated. Presumably if one script opens two different databases then both of the pg_connect() instances will need to be updated. Out of curiosity where did you install Postgres from? The reason I ask it that I was trying to figure why Fedora would invoke private /tmp's for services and then have the Postgres socket be only in the public /tmp. So I spun up a Fedora 25 instance and did an install of Postgres from the Fedora repos and then from the PGDG repos. In either case I got a postgresql.conf that had: unix_socket_directories = '/var/run/postgresql', '/tmp' Upstream you showed your default as: unix_socket_directories = '/tmp' which is what I see when I do a source install. So did you do a source install or did you copy a postgresql.conf from somewhere else? Best regards, -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
Thanks for the idea Rick. It hadn't occurred to me; might have at about the 200th page :-( John = On Monday 10 April 2017 16:46:14 Rick Widmer wrote: > On 4/10/2017 2:23 PM, John Iliffe wrote: > > On Monday 10 April 2017 11:53:35 Daniel Verite wrote: > >>John Iliffe wrote: > >> Given that you set two directories: /tmp and /var/pgsql, > >> I would think you can let the other apps use /tmp as before > >> and have only Apache use /var/pgsql ? > > > > Yes, I will do that, but there are several hundred PHP web page > > scripts to be updated. Presumably if one script opens two different > > databases then both of the pg_connect() instances will need to be > > updated. > > How about creating a php file with the connect code, once, then > including that file from all of the hundreds of web pages. It won't > help much with the first set of changes, but it will help with > portability later. > > > Rick -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
Hello John, On Mon, 2017-04-10 at 16:23 -0400, John Iliffe wrote: > On Monday 10 April 2017 11:53:35 Daniel Verite wrote: > > John Iliffe wrote: > > > So, the problem is resolved, although I have no idea why it was > > > necessary. > > > Good to hear. > > > > Yes, I will do that, but there are several hundred PHP web page > scripts to > be updated. Presumably if one script opens two different databases > then > both of the pg_connect() instances will need to be updated. > Are you saying that every single PHP script has its own separate routine to connect to the database? We have just a single class to do connection stuff and the parameters are held in pg_service.conf. That file has to be readable by Apache. You just pass in an argument that represents the database to which you want to connect, and it grabs host, port, etc. out of the service file. All you have to do is keep the service file up-to-date. Cheers, Rob -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
John Iliffe wrote: > Yes, I will do that, but there are several hundred PHP web page scripts to > be updated. Presumably if one script opens two different databases then > both of the pg_connect() instances will need to be updated. If you have many calls to pg_connect() without a host in the connect string, meaning it defaults to /tmp, instead of changing them you may inject a PGHOST variable into the Apache environment with /var/pgsql as the value. This will make it the default host, so basically everything that was going implicitly to /tmp will go to /var/pgsql instead. I think in RedHat the definition should go into /etc/sysconfig/httpd Generally the environment variables described for libpq here: https://www.postgresql.org/docs/9.6/static/libpq-envars.html apply to php's pg_connect() as well, since it's really just a thin wrapper on top of libpq's PQconnectdb(). Best regards, -- Daniel Vérité PostgreSQL-powered mailer: http://www.manitou-mail.org Twitter: @DanielVerite -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On 04/10/2017 01:23 PM, John Iliffe wrote: On Monday 10 April 2017 11:53:35 Daniel Verite wrote: John Iliffe wrote: So, the problem is resolved, although I have no idea why it was necessary. The key seems to be the PrivateTmp=true in the systemd service. Apache is not chrooted as demonstrated upthread, but that setting alone makes the normal, system-wide /tmp inaccessible to its processes, including the postgres Unix domain socket opened there. I suspect that your attempt to set PrivateTmp=false as a test was missed by systemd for some reason. You could probably insist in that direction and eventually make it work, but I don't see how it's better than the alternative /var/pgsql or localhost through TCP. Also, I now have several hundred programmes to update to add the host path and none of them will now be portable. Given that you set two directories: /tmp and /var/pgsql, I would think you can let the other apps use /tmp as before and have only Apache use /var/pgsql ? Yes, I will do that, but there are several hundred PHP web page scripts to be updated. Presumably if one script opens two different databases then both of the pg_connect() instances will need to be updated. Seems to me that it would be easier to try: PrivateTmp=false in the httpd systemd file. Best regards, -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On 4/10/2017 2:23 PM, John Iliffe wrote: On Monday 10 April 2017 11:53:35 Daniel Verite wrote: John Iliffe wrote: Given that you set two directories: /tmp and /var/pgsql, I would think you can let the other apps use /tmp as before and have only Apache use /var/pgsql ? Yes, I will do that, but there are several hundred PHP web page scripts to be updated. Presumably if one script opens two different databases then both of the pg_connect() instances will need to be updated. How about creating a php file with the connect code, once, then including that file from all of the hundreds of web pages. It won't help much with the first set of changes, but it will help with portability later. Rick -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On Monday 10 April 2017 11:53:35 Daniel Verite wrote: > John Iliffe wrote: > > So, the problem is resolved, although I have no idea why it was > > necessary. > > The key seems to be the PrivateTmp=true in the systemd service. > Apache is not chrooted as demonstrated upthread, but that > setting alone makes the normal, system-wide /tmp inaccessible > to its processes, including the postgres Unix domain socket opened > there. > > I suspect that your attempt to set PrivateTmp=false as a test was > missed by systemd for some reason. You could probably insist > in that direction and eventually make it work, but I don't see > how it's better than the alternative /var/pgsql or localhost through > TCP. > > > Also, I now have several hundred programmes to update to add the host > > path and none of them will now be portable. > > Given that you set two directories: /tmp and /var/pgsql, > I would think you can let the other apps use /tmp as before > and have only Apache use /var/pgsql ? Yes, I will do that, but there are several hundred PHP web page scripts to be updated. Presumably if one script opens two different databases then both of the pg_connect() instances will need to be updated. > > > Best regards, -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
John Iliffe wrote: > So, the problem is resolved, although I have no idea why it was necessary. The key seems to be the PrivateTmp=true in the systemd service. Apache is not chrooted as demonstrated upthread, but that setting alone makes the normal, system-wide /tmp inaccessible to its processes, including the postgres Unix domain socket opened there. I suspect that your attempt to set PrivateTmp=false as a test was missed by systemd for some reason. You could probably insist in that direction and eventually make it work, but I don't see how it's better than the alternative /var/pgsql or localhost through TCP. > Also, I now have several hundred programmes to update to add the host path > and none of them will now be portable. Given that you set two directories: /tmp and /var/pgsql, I would think you can let the other apps use /tmp as before and have only Apache use /var/pgsql ? Best regards, -- Daniel Vérité PostgreSQL-powered mailer: http://www.manitou-mail.org Twitter: @DanielVerite -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On Monday 10 April 2017 09:46:54 Daniel Verite wrote:
> John Iliffe wrote:
> > 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?
>
> pg_connect("host=/var/pgsql [...other parameters...]")
>
> The fact that the value for host starts with a slash indicates
> without ambiguity that it's a path on disk rather than the name
> or address of a host on the network.
>
YUP IT WORKS!
to borrow Apache's happy-message when you first install it.
So, the problem is resolved, although I have no idea why it was necessary.
Also, I now have several hundred programmes to update to add the host path
and none of them will now be portable.
Somehow this seems a step backwards, but my sincere thanks to everyone on
this list who helped me. The whole thing was way beyond my detailed
understanding of Linux.
I provided some further information in a previous memo about the details of
the unit file which may provide some information for those who understand
such things. It might be a good idea to provide some details of the
systemctl process in the Postgresql documentation for people who are first
encountering it (like me) when they have been used to the old init.d file
approach.
I will bring the same thing to Apache's attention on their list too.
Regards,
John
> However if apache runs in a chroot jail, it should no more see
> /var/pgsql than it sees /tmp
> Given an apache process id, on Linux you should be able
> to check the current filesystem-root of that process with
> # ls -ld /proc/$PID/root
> It should show a soft link to the directory that corresponds to
> the root from the point of view of the $PID process.
>
> But again, most people would use host=localhost in this setup.
>
> Best regards,
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On Monday 10 April 2017 09:46:54 Daniel Verite wrote:
> John Iliffe wrote:
> > 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?
>
> pg_connect("host=/var/pgsql [...other parameters...]")
>
> The fact that the value for host starts with a slash indicates
> without ambiguity that it's a path on disk rather than the name
> or address of a host on the network.
>
> However if apache runs in a chroot jail, it should no more see
> /var/pgsql than it sees /tmp
> Given an apache process id, on Linux you should be able
> to check the current filesystem-root of that process with
> # ls -ld /proc/$PID/root
> It should show a soft link to the directory that corresponds to
> the root from the point of view of the $PID process.
>
You are exceeding my level of understanding of Linux but the following
result suggests to me that Apache is using the common root directory (/) as
its base.
-
[root@prod04 John]# ls -ld /proc/27753/root
lrwxrwxrwx. 1 root root 0 Apr 10 10:52 /proc/27753/root -> /
--
Other than the rather odd requirement in the systemctl unit file that httpd
has a private /tmp (see arrow below) there should be no reason why Apache
can't see the /tmp directory. One of the earlier references in this series
mentioned I should check for that, which is the reason for the secondary
socket on /var/pgsql/ . I don't know how to make mod_php, or for that
matter php-fpm, look for the Postgresql socket on /var/pgsql/ though.
There is no conf file for mod_php and no directive that I can find in the
php-fpm configuration file.
[root@prod04 John]# cat /etc/systemd/system/httpd.service
[Unit]
Description=The Apache HTTP Server
[Service]
Type=forking
EnvironmentFile=/usr/apache-2.4.25/bin/envvars
PIDFile=/var/run/httpd.pid
ExecStart=/usr/apache-2.4.25/bin/apachectl -k start
ExecReload=/usr/apache-2.4.25/bin/apachectl -k graceful
ExecStop=/usr/apache-2.4.25/bin/apachectl -k stop
KillSignal=SIGCONT
PrivateTmp=true<---
[Install]
WantedBy=multi-user.target
FYI, changing the PrivateTmp directive to false doesn't fix the problem and
since I don't know why it is there I don't ant to touch it.
> But again, most people would use host=localhost in this setup.
>
> Best regards,
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On Sunday 09 April 2017 23:21:58 Adrian Klaver wrote: > On 04/09/2017 05:30 PM, John Iliffe wrote: > > On Sunday 09 April 2017 20:01:32 Adrian Klaver wrote: > >> 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 ? > > Yes, Apache is connecting to Postgres on the same machine so localhost > should be sufficient for IP purposes. > > Not sure that it applies here, but what does ifconfig show? > --- root@prod04 John]# ifconfig -a enp0s20f0u10: flags=4163 mtu 1500 inet 192.168.1.7 netmask 255.255.255.0 broadcast 192.168.1.255 inet6 fe80::84a:4eb9:f4a4:98a6 prefixlen 64 scopeid 0x20 ether 54:b8:0a:ef:c7:0f txqueuelen 1000 (Ethernet) RX packets 210126 bytes 238984885 (227.9 MiB) RX errors 0 dropped 0 overruns 0 frame 0 TX packets 120884 bytes 14119037 (13.4 MiB) TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0 enp0s31f6: flags=4163 mtu 1500 inet 192.168.1.6 netmask 255.255.255.255 broadcast 192.168.1.6 inet6 fe80::62d4:f478:8bbb:34a1 prefixlen 64 scopeid 0x20 ether 38:d5:47:19:0d:cb txqueuelen 1000 (Ethernet) RX packets 4589 bytes 1072227 (1.0 MiB) RX errors 0 dropped 0 overruns 0 frame 0 TX packets 1003 bytes 71483 (69.8 KiB) TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0 device interrupt 16 memory 0xf700-f702 lo: flags=73 mtu 65536 inet 127.0.0.1 netmask 255.0.0.0 inet6 ::1 prefixlen 128 scopeid 0x10 loop txqueuelen 1000 (Local Loopback) RX packets 106993 bytes 22736948 (21.6 MiB) RX errors 0 dropped 0 overruns 0 frame 0 TX packets 106993 bytes 22736948 (21.6 MiB) TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0 --- The actual connection being used should be on device "lo" which is on the "localhost" address of 127.0.0.1. > > 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? > > Correct. The issue is then why psql and stand alone PHP can see the > domain socket while PHP through Apache does not. Not sure why that is at > the moment. Hmm, had a thought. What user is Apache running as and does > that user have permissions on the socket file(s)? > Looks like anybody can connect to either of the Postgresql domain sockets. socket on /tmp srwxrwxrwx. 1 postgres postgres system_u:object_r:initrc_tmp_t:s00 Apr 10 10:35 .s.PGSQL.5432 second socket on /var/pgsql -- srwxrwxrwx. 1 postgres postgres system_u:object_r:httpd_var_run_t:s0 0 Apr 10 10:35 .s.PGSQL.5432 -- Apache is running under userid apache as expected --- [root@prod04 John]# ps -ef | grep httpd root 420 356 0 10:39 pts/000:00:00 grep --color=auto httpd root 27753 1 0 Apr09 ?00:00:00 /usr/apache-2.4.25/bin/httpd -k start apache 27754 27753 0 Apr09 ?00:00:03 /usr/apache-2.4.25/bin/httpd -k start apache 27755 27753 0 Apr09 ?00:00:04 /usr/apache-2.4.25/bin/httpd -k start apache 27756 27753 0 Apr09 ?00:00:04 /usr/apache-2.4.25/bin/httpd -k start with the following SELinux context info: --- ps -eZ | grep httpd system_u:system_r:unconfined_service_t:s0 27753 ? 00:00:00 httpd system_u:system_r:unconfined_service_t:s0 27754 ? 00:00:03 httpd system_u:system_r:unconfined_service_t:s0 27755 ? 00:00:04 httpd system_u:system_r:unconfined_service_t:s0 27756 ? 00:00:04 httpd -- > > 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 som
Re: [GENERAL] Unable to connect to Postgresql
On Sunday 09 April 2017 22:07:12 rob stone wrote: > On Sun, 2017-04-09 at 20:09 -0400, John Iliffe wrote: > > > > > > > > > You have Apache, PHP, and Postgres all running on your LAN at > > > 192.168.1.6. > > > > > > > > > > > > > > > You are on 192.168.1.10. > > > > > > > > > > > > Your NOT doing "php -f testfcgi.php", so how does Apache "know" to > > > > run > > > > > that script? > > > > testfcgi.php is in the document root of the default named virtual > > server. > > O.K. > > So in sites-available, your conf file just tells Apache to run > testfcgi.php and nothing else? Apache just runs the programme whose name follows the / in the URL. Nothing fancy here at all. testfcgi.php is a php script that is run by mod_php in Apache and uses the php command pg_connect() to try and reach Postgresql. That works if Postgresql is reached via a TCP connection, as suggested by Adrian yesterday, but not if the connection is using a Unix domain socket, which is the usual way to do that. Despite the name, testfcgi.php is running as a mod_php script, not a cgi. That is the next step to get working! Regards, John > There is no resolution required by obtaining the IP address from > /etc/hosts. > > Are you able to put some trigger_error messages into testfcgi.php in > order to make sure Apache is running the correct program? > > Cheers, > Rob -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
John Iliffe wrote:
> 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?
pg_connect("host=/var/pgsql [...other parameters...]")
The fact that the value for host starts with a slash indicates
without ambiguity that it's a path on disk rather than the name
or address of a host on the network.
However if apache runs in a chroot jail, it should no more see
/var/pgsql than it sees /tmp
Given an apache process id, on Linux you should be able
to check the current filesystem-root of that process with
# ls -ld /proc/$PID/root
It should show a soft link to the directory that corresponds to
the root from the point of view of the $PID process.
But again, most people would use host=localhost in this setup.
Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
John Iliffe wrote: > > > > > Basically, nothing. > > If I include it in an Apache script exactly as suggested, then the script > puts out a blank screen, no error messages. So from the point of view of the Apache process, that file does not exist. That's consistent with the error you reported when trying to connect through the Unix domain socket: Unable to connect to PostgreSQL server: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"? If apache is chrooted, or secured by other means with the similar result that it cannot access the full filesystem, that's expected. In this case, connecting to localhost with TCP/IP rather than a Unix domain socket looks like the simpler solution. Best regards, -- Daniel Vérité PostgreSQL-powered mailer: http://www.manitou-mail.org Twitter: @DanielVerite -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On 04/09/2017 05:30 PM, John Iliffe wrote: On Sunday 09 April 2017 20:01:32 Adrian Klaver wrote: 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 ? Yes, Apache is connecting to Postgres on the same machine so localhost should be sufficient for IP purposes. Not sure that it applies here, but what does ifconfig show? 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? Correct. The issue is then why psql and stand alone PHP can see the domain socket while PHP through Apache does not. Not sure why that is at the moment. Hmm, had a thought. What user is Apache running as and does that user have permissions on the socket file(s)? 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 -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On Sun, 2017-04-09 at 20:09 -0400, John Iliffe wrote: > > > > > You have Apache, PHP, and Postgres all running on your LAN at > > 192.168.1.6. > > > > > > You are on 192.168.1.10. > > > > Your NOT doing "php -f testfcgi.php", so how does Apache "know" to > run > > that script? > > testfcgi.php is in the document root of the default named virtual > server. > O.K. So in sites-available, your conf file just tells Apache to run testfcgi.php and nothing else? There is no resolution required by obtaining the IP address from /etc/hosts. Are you able to put some trigger_error messages into testfcgi.php in order to make sure Apache is running the correct program? Cheers, Rob -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On Sunday 09 April 2017 20:07:01 Adrian Klaver wrote: > On 04/09/2017 03:27 PM, rob stone wrote: > > Hello John, > > > > Just saw this message. > > > >> 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 > >> # > > > > Your set-up has Apache, PHP and Postgres all running from the same > > machine. > > So as far as running from there goes, it is "localhost". > > The issue is not localhost, it is the case when John is trying to > connect without a host specifier and therefore is trying to reach the > Unix socket. > > > There is no requirement to traverse a network. It is all on the same > > physical machine. > > > > Alter your postgresql.conf file and remove the hash so that:- > > > > listen_addresses = 'localhost' > > > > is explicitly defined. Alter pg_hba.conf so that localhost is declared > > and let's see what happens. > > It will be the same effect, the commented line is just showing that the > default is 'localhost'. Though, John if you do decide to do this > remember to restart the server to have the change take effect. Yes, I learned that lesson years ago! I have been restarting both Apache and Postgresql and closed the browser on every change, and have rebooted a few times too, to ensure that all buffers are not serving stale images. It only takes a minute and keeps a lot of egg off my face! > > > Cheers, > > Rob -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
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
> >>>
> >>> hostall 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
> >>> l
Re: [GENERAL] Unable to connect to Postgresql
On Sunday 09 April 2017 18:27:49 rob stone wrote: Hi Rob: Thanks for chiming in. > Hello John, > > Just saw this message. > > > 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 > > # > > Your set-up has Apache, PHP and Postgres all running from the same > machine. > So as far as running from there goes, it is "localhost". > There is no requirement to traverse a network. It is all on the same > physical machine. > > Alter your postgresql.conf file and remove the hash so that:- > > listen_addresses = 'localhost' > > is explicitly defined. Alter pg_hba.conf so that localhost is declared > and let's see what happens. Same as before; the TCP connection works as expected but the Unix domain socket reports --- Sun Apr 09 20:07:02.915744 2017] [php7:warn] [pid 27755:tid 140325136111360] [client 192.168.1.10:36749] PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server: No such file or directory\n\tIs the server running locally and accepting\n\tconnections on Unix domain socket "/tmp/.s.PGSQL.5432"? in /httpd/iliffe/testfcgi.php on line 133 --- > > Cheers, > Rob -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On 04/09/2017 03:27 PM, rob stone wrote: Hello John, Just saw this message. 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 # Your set-up has Apache, PHP and Postgres all running from the same machine. So as far as running from there goes, it is "localhost". The issue is not localhost, it is the case when John is trying to connect without a host specifier and therefore is trying to reach the Unix socket. There is no requirement to traverse a network. It is all on the same physical machine. Alter your postgresql.conf file and remove the hash so that:- listen_addresses = 'localhost' is explicitly defined. Alter pg_hba.conf so that localhost is declared and let's see what happens. It will be the same effect, the commented line is just showing that the default is 'localhost'. Though, John if you do decide to do this remember to restart the server to have the change take effect. Cheers, Rob -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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
hostall 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.kla...@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On Sunday 09 April 2017 17:39:30 rob stone wrote: > Hello John, > > > > > > > > > > > PHP and Postgresql are both running on same box. It does have > > > > two > > > > > > interfaces, 192.168.1.6 and 192.168.1.7, and the test programme > > > > is > > > > > > available on either. The reference to 192.168.1.10 is the client > > > > machine, in this case my workstation, which is 192.168.1.10. > > > > > > > > > > > > Here is the error message you said came from the box running PHP: > > > >> > -- > > > >> > [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 > > > >> > --- > > > > > > > > This is a tad confusing to me. > > You have Apache, PHP, and Postgres all running on your LAN at > 192.168.1.6. > > > You are on 192.168.1.10. > > Your NOT doing "php -f testfcgi.php", so how does Apache "know" to run > that script? testfcgi.php is in the document root of the default named virtual server. > > Cheers, > Rob -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
Hello John, Just saw this message. > > 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 > # > > Your set-up has Apache, PHP and Postgres all running from the same machine. So as far as running from there goes, it is "localhost". There is no requirement to traverse a network. It is all on the same physical machine. Alter your postgresql.conf file and remove the hash so that:- listen_addresses = 'localhost' is explicitly defined. Alter pg_hba.conf so that localhost is declared and let's see what happens. Cheers, Rob -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
Hello John, > > > > > PHP and Postgresql are both running on same box. It does have > two > > > interfaces, 192.168.1.6 and 192.168.1.7, and the test programme > is > > > available on either. The reference to 192.168.1.10 is the client > > > machine, in this case my workstation, which is 192.168.1.10. > > > > Here is the error message you said came from the box running PHP: > > >> > -- > > >> > [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 > > >> > --- > > > This is a tad confusing to me. You have Apache, PHP, and Postgres all running on your LAN at 192.168.1.6. You are on 192.168.1.10. Your NOT doing "php -f testfcgi.php", so how does Apache "know" to run that script? Cheers, Rob -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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
> >
> > hostall 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
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
hostall all 127.0.0.1/32password
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?
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'
3) Using a standalone PHP script:
$db_handle = pg_connect('dbname=yrarc user=yrcro password=yrreadonly')
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.kla...@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On Sunday 09 April 2017 15:38:10 Adrian Klaver wrote:
> On 04/09/2017 12:37 PM, John Iliffe wrote:
> > On Sunday 09 April 2017 14:34:01 Joe Conway wrote:
> >> 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= password=xx');
> >>> ---
> >>>
> >>> 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...
> >
> > No, Fedora/Red Hat has made a mess! I just installed everything
> > (APache, Postgresql, PHP, OpenSSL from source as I always have over a
> > period of more than 15 years. This is the first time I have run into
> > this sort of problem and it seems to be related to systemctl's unit
> > files.
> >
> > If I may be permitted a rant at this point, the Fedora documentation
> > is almost useless for SELinux and much of the underlying operating
> > system. Very nice on theory but nothing on the details or "What is
> > required?/How do I do it?"
> >
> > In a small company like mine, we are all multi-tasked and having a
> > specialist for everything, like a system programmer skilled in Linux,
> > is just not on. Things have to work right out of the box. Server
> > administration, system operations, etc, is just not my core skill,
> > and I depend heavily on the open source community for help (like you
> > at the moment).
> >
> >>> --
> >>> [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 "XXX" 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 DATABASEUSERADDRESS
> >>> METHOD
> >
> > i> >
> >
> >>> # "local" is for Unix domain socket connections only
> >>> #local all all md5
> >>>
> >>> local yrarc XXX
> >>> trust local all all
> >>> password #local all all
> >>>
> >>> trust # IPv4 local connections:
> >>> hostall 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
> >> hostall all 192.168.1.6/32md5
> >>
> >> - or maybe -
> >>
> >> # only allow connections from same subnet using tcp
> >> hostall all 192.168.1.0/24md5
> >
> > I don't think I should need that since httpd/mod_php is on the same
> > machine so should be 127.0.0.1 should cover it. I did try it though
> > and set it to "trust" to avoid any problems with permissions in
> > Postgresql. I then stopped and restarted both Postgres and Apache
> > and still get the same err
Re: [GENERAL] Unable to connect to Postgresql
On Sunday 09 April 2017 15:41:12 Joe Conway wrote:
> On 04/09/2017 12:37 PM, John Iliffe wrote:
> >> > 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= password=xx');
> >
> > If I may be permitted a rant at this point, the Fedora documentation
> > is almost useless for SELinux and much of the underlying operating
> > system.
>
> Maybe so, but as I said earlier if you are in permissive, then blaming
> any of these issues on selinux is wrong -- selinux does no enforcement
> in permissive.
>
> >> 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
> >> hostall all 192.168.1.6/32md5
> >>
> >> - or maybe -
> >>
> >> # only allow connections from same subnet using tcp
> >> hostall all 192.168.1.0/24md5
> >
> > I don't think I should need that since httpd/mod_php is on the same
> > machine so should be 127.0.0.1 should cover it.
>
> No, not if your connection string is
>
> 'dbname=yrarc host=192.168.1.6 port=5432 user= password=xx'
I'm not sure why "localhost" didn't work on the first pass; I just rebooted,
changed the connection string to:
$db_handle = pg_connect('dbname=yrarc host=localhost port=5432
user= password=xxx');
and got a connection. So, it looks like I can connect using TCP on the
loopback. Still doesn't work on the Unix domain socket though.
>
> as you said it was. In this case you must have one of the two pg_hba
> lines above or something more broad. With that connection string you are
> not connecting to localhost, you are connecting to 192.168.1.6 and there
> MUST be a pg_hba line to support it.
>
> > "trust" to avoid any problems with permissions in Postgresql.
>
> Trust has nothing whatsoever to do with the problems you have shown us
> so far.
No, I agree with you, but I just didn't want to open another can of worms.
One of the problems here is too many changes and too many variables. Now
that I can connect I changed it back to "password" and everything still
works.
>
> > PHP and Postgresql are both running on same box. It does have two
> > interfaces, 192.168.1.6 and 192.168.1.7, and the test programme is
> > available on either. The reference to 192.168.1.10 is the client
> > machine, in this case my workstation, which is 192.168.1.10.
>
> Here is the error message you said came from the box running PHP:
> >> > --
> >> > [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
> >> > ---
>
No, the workstation doesn't have PHP installed on it.
This message is coming from Apache's error log on 192.168.1.6. Apache logs
the source address of the client (ie my workstation at 192.168.1.10) and
then follows with the PHP error message "PHP Warning: pg_connect(): Unable
to connect to PostgreSQL server: could not connect to server..."
> Now, if your workstation is 192.168.1.10, then I can only conclude that
> you are running PHP on your workstation and *not* on the box with
> Postgres which you say uses 192.168.1.6 and 192.168.1.7.
>
> Joe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On 04/09/2017 12:37 PM, John Iliffe wrote:
>> > 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= password=xx');
> If I may be permitted a rant at this point, the Fedora documentation is
> almost useless for SELinux and much of the underlying operating system.
Maybe so, but as I said earlier if you are in permissive, then blaming
any of these issues on selinux is wrong -- selinux does no enforcement
in permissive.
>> 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
>> hostall all 192.168.1.6/32md5
>>
>> - or maybe -
>>
>> # only allow connections from same subnet using tcp
>> hostall all 192.168.1.0/24md5
>>
> I don't think I should need that since httpd/mod_php is on the same machine
> so should be 127.0.0.1 should cover it.
No, not if your connection string is
'dbname=yrarc host=192.168.1.6 port=5432 user= password=xx'
as you said it was. In this case you must have one of the two pg_hba
lines above or something more broad. With that connection string you are
not connecting to localhost, you are connecting to 192.168.1.6 and there
MUST be a pg_hba line to support it.
> "trust" to avoid any problems with permissions in Postgresql.
Trust has nothing whatsoever to do with the problems you have shown us
so far.
> PHP and Postgresql are both running on same box. It does have two
> interfaces, 192.168.1.6 and 192.168.1.7, and the test programme is
> available on either. The reference to 192.168.1.10 is the client machine,
> in this case my workstation, which is 192.168.1.10.
Here is the error message you said came from the box running PHP:
>> > --
>> > [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
>> > ---
Now, if your workstation is 192.168.1.10, then I can only conclude that
you are running PHP on your workstation and *not* on the box with
Postgres which you say uses 192.168.1.6 and 192.168.1.7.
Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development
signature.asc
Description: OpenPGP digital signature
Re: [GENERAL] Unable to connect to Postgresql
On 04/09/2017 12:37 PM, John Iliffe wrote:
On Sunday 09 April 2017 14:34:01 Joe Conway wrote:
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= password=xx');
---
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...
No, Fedora/Red Hat has made a mess! I just installed everything (APache,
Postgresql, PHP, OpenSSL from source as I always have over a period of more
than 15 years. This is the first time I have run into this sort of problem
and it seems to be related to systemctl's unit files.
If I may be permitted a rant at this point, the Fedora documentation is
almost useless for SELinux and much of the underlying operating system.
Very nice on theory but nothing on the details or "What is required?/How do
I do it?"
In a small company like mine, we are all multi-tasked and having a
specialist for everything, like a system programmer skilled in Linux, is
just not on. Things have to work right out of the box. Server
administration, system operations, etc, is just not my core skill, and I
depend heavily on the open source community for help (like you at the
moment).
--
[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 "XXX" 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 DATABASEUSERADDRESS METHOD
i> >
# "local" is for Unix domain socket connections only
#local all all md5
local yrarc XXX trust
local all all
password #local all all
trust # IPv4 local connections:
hostall all 127.0.0.1/32trust
-
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
hostall all 192.168.1.6/32md5
- or maybe -
# only allow connections from same subnet using tcp
hostall all 192.168.1.0/24md5
I don't think I should need that since httpd/mod_php is on the same machine
so should be 127.0.0.1 should cover it. I did try it though and set it to
"trust" to avoid any problems with permissions in Postgresql. I then
stopped and restarted both Postgres and Apache and still get the same error
in the log from pg_connect asking whether the socket is available.
Remember host != local
host is for IP connections
local is for socket connections
So if you set up a host line pointing to 127.0.0.1 and connected without
using host in the connection string the 127.0.0.1 host line will not be
used, instead the first matching local line will. Furthermore in your
examples when you did connect using host= you used an IP that was not
127.0.0.1, so the connection would not use the 127.0.0.1 line anyway.
That is why I made the suggestion to use host=localhost or if you want
host=127.0.0.1 .
I just noticed in the message above "client 192.168.1.1
Re: [GENERAL] Unable to connect to Postgresql
On Sunday 09 April 2017 15:05:18 Adrian Klaver wrote:
> 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= password=xx');
> > ---
>
> So is this on the machine that has the Postgres server?
Yes, the machine has two addresses, 192.168.1.6 and 192.168.1.7
>
> If not change to that machine and use host=localhost, otherwise just
> change to host=localhost.
>
> > 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:
> Can you show us the /etc/host file?
--
127.0.0.1 localhost localhost.localdomain localhost4
localhost4.localdomain4
::1 localhost localhost.localdomain localhost6
localhost6.localdomain6
192.168.1.1 default
--
I added the blank lines above because the mail client had run them
together. The real file doesn't have any blank lines in it between the host
definitions.
192.168.1.1 is the default Internet gateway. This file was generated by the
Fedora installation programme.
John
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On Sunday 09 April 2017 14:34:01 Joe Conway wrote:
> 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= password=xx');
> > ---
> >
> > 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...
>
No, Fedora/Red Hat has made a mess! I just installed everything (APache,
Postgresql, PHP, OpenSSL from source as I always have over a period of more
than 15 years. This is the first time I have run into this sort of problem
and it seems to be related to systemctl's unit files.
If I may be permitted a rant at this point, the Fedora documentation is
almost useless for SELinux and much of the underlying operating system.
Very nice on theory but nothing on the details or "What is required?/How do
I do it?"
In a small company like mine, we are all multi-tasked and having a
specialist for everything, like a system programmer skilled in Linux, is
just not on. Things have to work right out of the box. Server
administration, system operations, etc, is just not my core skill, and I
depend heavily on the open source community for help (like you at the
moment).
> > --
> > [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 "XXX" 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 DATABASEUSERADDRESS METHOD
i> >
> > # "local" is for Unix domain socket connections only
> > #local all all md5
> >
> > local yrarc XXX trust
> > local all all
> > password #local all all
> > trust # IPv4 local connections:
> > hostall all 127.0.0.1/32trust
> > -
>
> 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
> hostall all 192.168.1.6/32md5
>
> - or maybe -
>
> # only allow connections from same subnet using tcp
> hostall all 192.168.1.0/24md5
>
I don't think I should need that since httpd/mod_php is on the same machine
so should be 127.0.0.1 should cover it. I did try it though and set it to
"trust" to avoid any problems with permissions in Postgresql. I then
stopped and restarted both Postgres and Apache and still get the same error
in the log from pg_connect asking whether the socket is available.
> 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?
PHP and Postgresql
Re: [GENERAL] Unable to connect to Postgresql
On Saturday 08 April 2017 11:20:29 Joe Conway wrote: > On 04/08/2017 06:31 AM, John Iliffe wrote: > > On Saturday 08 April 2017 00:10:14 Adrian Klaver wrote: > >> On 04/07/2017 07:45 PM, Joe Conway wrote: > >> > On 04/07/2017 05:35 PM, Adrian Klaver wrote: > >> >> On 04/07/2017 05:03 PM, John Iliffe wrote: > >> > Running on Fedora 25 with SELinux in PERMISSIVE mode. The > >> > audit log shows no hits on Postgresql. > >> >>> > >> >>> My going in position was/still is, that this is a SELinux > >> >>> security problem > >> >>> but I am finding SELinux to be the most opaque and badly > >> >>> documented software > >> >>> that I have ever had to deal with, which is why it is running in > >> >>> permissive > >> >>> mode at the moment. > >> >> > >> >> Well what I know about SELinux would fit in the navel of a > >> >> flea(tip of the hat to David Niven), so I can not be of much help > >> >> there. The reason I am returned this thread to the list, there > >> >> are folks that do understand it. > >> > > >> > If SELinux is running in permissive I don't see how it could be at > >> > fault for your issue. Did you verify that (getenforce)? > >> > > >> >>> -- > >> >>> [Fri Apr 07 17:03:28.597101 2017] [php7:warn] [pid 1797:tid > >> >>> 140599445419776] [client 192.168.1.10:45127] PHP Warning: > >> >>> pg_connect(): Unable to connect to PostgreSQL server: could not > >> >>> connect to server: No such file or directory\n\tIs the server > >> >>> running locally and > >> >>> accepting\n\tconnections on Unix domain socket > >> >>> "/tmp/.s.PGSQL.5432"? in /httpd/iliffe/testfcgi.php on > >> >>> line 121 > >> > > >> > This might be a silly question, but is PHP running on the same > >> > server as Postgres? > >> > >> To add to this, previously you mentioned: > >> > >> "Also, using the on board firewall (firewalld) to provide a secondary > >> domain where the actual business processes run. " > >> > >> What exactly does that mean? > > > > There is something rather odd here. > > > > getenforce shows the mode as permissive, which is what I think it is. > > If getenforce shows you are in permissive, then selinux is not your > problem, full stop. > > > BUT, this morning's logwatch report shows: > > *** Denials *** > > > > system_u system_u (tcp_socket): 1 times > > selinux will continue to log denials in permissive -- this is useful to > determine what would have been blocked by selinux had it been in > enforcing, which in turn gives you a chance to fix those issues before > turning on enforcing. > > For more detail on the selinux logs look in /var/log/audit/audit.log > > You definitely have something odd going on though. As you said > elsewhere, using a Unix domain socket connection the firewall should > not get involved either. > > Seems like the issue is related to PHP somehow. For example, see: > http://serverfault.com/questions/641329/cannot-connect-to-postgresql-uni > x-domain-socket In a way, probably yes, but I still can't figure it out. The systemctl unit file DOES have the line PrivateTmp=true and changing it to 'false' didn't accomplish anything. So I did the whole routine, created the /var/pgsql directory, changed the postgresql.conf Unix domain socket line to create two sockets, restarted and checked that both sockets do exist (yes) and then retried the connection. Even stopped and restarted Apache in case something there was required but still no luck. I checked in the PHP directories and there doesn't seem to be any config file that applies to mod_php and in the php-fpm config file there is no reference to postgres. That suggests to me that there is no way to point the connection request to the new socket file location. So, any ideas as to where to go next? FYI, psql still works OK. And, thanks again for your patience! John > > Joe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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= password=xx');
---
So is this on the machine that has the Postgres server?
If not change to that machine and use host=localhost, otherwise just
change to host=localhost.
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:
Can you show us the /etc/host file?
--
Adrian Klaver
adrian.kla...@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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= password=xx');
> ---
>
> 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 "XXX" 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 DATABASEUSERADDRESS METHOD
>
> # "local" is for Unix domain socket connections only
> #local all all md5
>
> local yrarc XXX trust
> local all all password
> #local all all trust
> # IPv4 local connections:
> hostall all 127.0.0.1/32trust
> -
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
hostall all 192.168.1.6/32md5
- or maybe -
# only allow connections from same subnet using tcp
hostall all 192.168.1.0/24md5
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
signature.asc
Description: OpenPGP digital signature
Re: [GENERAL] Unable to connect to Postgresql
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= password=xx');
---
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:
--
[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 "XXX" 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 DATABASEUSERADDRESS METHOD
# "local" is for Unix domain socket connections only
#local all all md5
local yrarc XXX trust
local all all password
#local all all trust
# IPv4 local connections:
hostall all 127.0.0.1/32trust
-
>
> Joe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
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.
Another question I don't believe has been asked is, what does your
pg_hba.conf look like?
Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development
signature.asc
Description: OpenPGP digital signature
Re: [GENERAL] Unable to connect to Postgresql
On Saturday 08 April 2017 09:38:07 Adrian Klaver wrote: > On 04/08/2017 06:26 AM, John Iliffe wrote: > > On Saturday 08 April 2017 00:10:14 Adrian Klaver wrote: > >> On 04/07/2017 07:45 PM, Joe Conway wrote: > >>> On 04/07/2017 05:35 PM, Adrian Klaver wrote: > On 04/07/2017 05:03 PM, John Iliffe wrote: > >>> Running on Fedora 25 with SELinux in PERMISSIVE mode. The audit > >>> log shows no hits on Postgresql. > > > > My going in position was/still is, that this is a SELinux security > > problem > > but I am finding SELinux to be the most opaque and badly > > documented software > > that I have ever had to deal with, which is why it is running in > > permissive > > mode at the moment. > > Well what I know about SELinux would fit in the navel of a flea(tip > of the hat to David Niven), so I can not be of much help there. The > reason I am returned this thread to the list, there are folks that > do understand it. > >>> > >>> If SELinux is running in permissive I don't see how it could be at > >>> fault for your issue. Did you verify that (getenforce)? > >>> > > -- > > [Fri Apr 07 17:03:28.597101 2017] [php7:warn] [pid 1797:tid > > 140599445419776] [client 192.168.1.10:45127] PHP Warning: > > pg_connect(): Unable to connect to PostgreSQL server: could not > > connect to server: No such file or directory\n\tIs the server > > running locally and > > accepting\n\tconnections on Unix domain socket > > "/tmp/.s.PGSQL.5432"? in /httpd/iliffe/testfcgi.php on > > line 121 > >>> > >>> This might be a silly question, but is PHP running on the same > >>> server as Postgres? > >> > >> To add to this, previously you mentioned: > >> > >> "Also, using the on board firewall (firewalld) to provide a secondary > >> domain where the actual business processes run. " > >> > >> What exactly does that mean? > > > > I'm trying/planning to use firewalld to keep certain remote addresses > > from connecting to the mail server. Since I have it anyway, I want > > to strengthen the security by moving non-Internet connections > > internal of that firewall so only Apache is exposed to the Internet > > and the databases, etc, are internal. > > > > This is a Unix domain socket connection so I don't think the firewall > > should get involved. > > 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. > > > Since you raised the question, I added port 5432 to the open list in > > firewalld but it didn't make any difference, still not connecting. > > > >>> HTH, > >>> > >>> Joe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On Saturday 08 April 2017 09:20:46 Daniel Verite wrote: > John Iliffe wrote: > > The proper socket does exist: > > > > - > > ls -al /tmp | grep PGSQL > > srwxrwxrwx. 1 postgres postgres0 Apr 7 16:53 .s.PGSQL.5432 > > -rw---. 1 postgres postgres 49 Apr 7 16:53 .s.PGSQL.5432.lock > > Maybe the httpd service lives in another namespace, > e.g. it's chrooted. What if you try: > > Basically, nothing. If I include it in an Apache script exactly as suggested, then the script puts out a blank screen, no error messages. I used the result in an 'if' statement and it apparently returns false, but that could be an artefact of nothing being returned by file_exists(). It is possible that since the "file" is really a socket, and not a file or directory as required by the documentation, it is not found by however file_exists() looks for files. There is no error log entry from either Apache or PHP. > Best regards, -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On 04/08/2017 06:31 AM, John Iliffe wrote: > On Saturday 08 April 2017 00:10:14 Adrian Klaver wrote: >> On 04/07/2017 07:45 PM, Joe Conway wrote: >> > On 04/07/2017 05:35 PM, Adrian Klaver wrote: >> >> On 04/07/2017 05:03 PM, John Iliffe wrote: >> > Running on Fedora 25 with SELinux in PERMISSIVE mode. The audit >> > log shows no hits on Postgresql. >> >>> >> >>> My going in position was/still is, that this is a SELinux security >> >>> problem >> >>> but I am finding SELinux to be the most opaque and badly documented >> >>> software >> >>> that I have ever had to deal with, which is why it is running in >> >>> permissive >> >>> mode at the moment. >> >> >> >> Well what I know about SELinux would fit in the navel of a flea(tip >> >> of the hat to David Niven), so I can not be of much help there. The >> >> reason I am returned this thread to the list, there are folks that >> >> do understand it. >> > >> > If SELinux is running in permissive I don't see how it could be at >> > fault for your issue. Did you verify that (getenforce)? >> > >> >>> -- >> >>> [Fri Apr 07 17:03:28.597101 2017] [php7:warn] [pid 1797:tid >> >>> 140599445419776] [client 192.168.1.10:45127] PHP Warning: >> >>> pg_connect(): Unable to connect to PostgreSQL server: could not >> >>> connect to server: No such file or directory\n\tIs the server >> >>> running locally and >> >>> accepting\n\tconnections on Unix domain socket >> >>> "/tmp/.s.PGSQL.5432"? in /httpd/iliffe/testfcgi.php on >> >>> line 121 >> > >> > This might be a silly question, but is PHP running on the same server >> > as Postgres? >> >> To add to this, previously you mentioned: >> >> "Also, using the on board firewall (firewalld) to provide a secondary >> domain where the actual business processes run. " >> >> What exactly does that mean? >> > There is something rather odd here. > > getenforce shows the mode as permissive, which is what I think it is. If getenforce shows you are in permissive, then selinux is not your problem, full stop. > BUT, this morning's logwatch report shows: > > *** Denials *** > system_u system_u (tcp_socket): 1 times selinux will continue to log denials in permissive -- this is useful to determine what would have been blocked by selinux had it been in enforcing, which in turn gives you a chance to fix those issues before turning on enforcing. For more detail on the selinux logs look in /var/log/audit/audit.log You definitely have something odd going on though. As you said elsewhere, using a Unix domain socket connection the firewall should not get involved either. Seems like the issue is related to PHP somehow. For example, see: http://serverfault.com/questions/641329/cannot-connect-to-postgresql-unix-domain-socket Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development signature.asc Description: OpenPGP digital signature
Re: [GENERAL] Unable to connect to Postgresql
On 04/08/2017 06:26 AM, John Iliffe wrote: On Saturday 08 April 2017 00:10:14 Adrian Klaver wrote: On 04/07/2017 07:45 PM, Joe Conway wrote: On 04/07/2017 05:35 PM, Adrian Klaver wrote: On 04/07/2017 05:03 PM, John Iliffe wrote: Running on Fedora 25 with SELinux in PERMISSIVE mode. The audit log shows no hits on Postgresql. My going in position was/still is, that this is a SELinux security problem but I am finding SELinux to be the most opaque and badly documented software that I have ever had to deal with, which is why it is running in permissive mode at the moment. Well what I know about SELinux would fit in the navel of a flea(tip of the hat to David Niven), so I can not be of much help there. The reason I am returned this thread to the list, there are folks that do understand it. If SELinux is running in permissive I don't see how it could be at fault for your issue. Did you verify that (getenforce)? -- [Fri Apr 07 17:03:28.597101 2017] [php7:warn] [pid 1797:tid 140599445419776] [client 192.168.1.10:45127] PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server: No such file or directory\n\tIs the server running locally and accepting\n\tconnections on Unix domain socket "/tmp/.s.PGSQL.5432"? in /httpd/iliffe/testfcgi.php on line 121 This might be a silly question, but is PHP running on the same server as Postgres? To add to this, previously you mentioned: "Also, using the on board firewall (firewalld) to provide a secondary domain where the actual business processes run. " What exactly does that mean? I'm trying/planning to use firewalld to keep certain remote addresses from connecting to the mail server. Since I have it anyway, I want to strengthen the security by moving non-Internet connections internal of that firewall so only Apache is exposed to the Internet and the databases, etc, are internal. This is a Unix domain socket connection so I don't think the firewall should get involved. So what if you change the connection to use -h localhost? Since you raised the question, I added port 5432 to the open list in firewalld but it didn't make any difference, still not connecting. HTH, Joe -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On Saturday 08 April 2017 00:10:14 Adrian Klaver wrote: > On 04/07/2017 07:45 PM, Joe Conway wrote: > > On 04/07/2017 05:35 PM, Adrian Klaver wrote: > >> On 04/07/2017 05:03 PM, John Iliffe wrote: > > Running on Fedora 25 with SELinux in PERMISSIVE mode. The audit > > log shows no hits on Postgresql. > >>> > >>> My going in position was/still is, that this is a SELinux security > >>> problem > >>> but I am finding SELinux to be the most opaque and badly documented > >>> software > >>> that I have ever had to deal with, which is why it is running in > >>> permissive > >>> mode at the moment. > >> > >> Well what I know about SELinux would fit in the navel of a flea(tip > >> of the hat to David Niven), so I can not be of much help there. The > >> reason I am returned this thread to the list, there are folks that > >> do understand it. > > > > If SELinux is running in permissive I don't see how it could be at > > fault for your issue. Did you verify that (getenforce)? > > > >>> -- > >>> [Fri Apr 07 17:03:28.597101 2017] [php7:warn] [pid 1797:tid > >>> 140599445419776] [client 192.168.1.10:45127] PHP Warning: > >>> pg_connect(): Unable to connect to PostgreSQL server: could not > >>> connect to server: No such file or directory\n\tIs the server > >>> running locally and > >>> accepting\n\tconnections on Unix domain socket > >>> "/tmp/.s.PGSQL.5432"? in /httpd/iliffe/testfcgi.php on > >>> line 121 > > > > This might be a silly question, but is PHP running on the same server > > as Postgres? > > To add to this, previously you mentioned: > > "Also, using the on board firewall (firewalld) to provide a secondary > domain where the actual business processes run. " > > What exactly does that mean? > There is something rather odd here. getenforce shows the mode as permissive, which is what I think it is. BUT, this morning's logwatch report shows: *** Denials *** system_u system_u (tcp_socket): 1 times Unfortunately, it doesn't say WHICH stream socket. I'll check that and see if I can find the actual socket that got denied, and if it was actually let through or not. > > HTH, > > > > Joe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
John Iliffe wrote: > The proper socket does exist: > > - > ls -al /tmp | grep PGSQL > srwxrwxrwx. 1 postgres postgres0 Apr 7 16:53 .s.PGSQL.5432 > -rw---. 1 postgres postgres 49 Apr 7 16:53 .s.PGSQL.5432.lock Maybe the httpd service lives in another namespace, e.g. it's chrooted. What if you try: Best regards, -- Daniel Vérité PostgreSQL-powered mailer: http://www.manitou-mail.org Twitter: @DanielVerite -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On Saturday 08 April 2017 00:10:14 Adrian Klaver wrote: > On 04/07/2017 07:45 PM, Joe Conway wrote: > > On 04/07/2017 05:35 PM, Adrian Klaver wrote: > >> On 04/07/2017 05:03 PM, John Iliffe wrote: > > Running on Fedora 25 with SELinux in PERMISSIVE mode. The audit > > log shows no hits on Postgresql. > >>> > >>> My going in position was/still is, that this is a SELinux security > >>> problem > >>> but I am finding SELinux to be the most opaque and badly documented > >>> software > >>> that I have ever had to deal with, which is why it is running in > >>> permissive > >>> mode at the moment. > >> > >> Well what I know about SELinux would fit in the navel of a flea(tip > >> of the hat to David Niven), so I can not be of much help there. The > >> reason I am returned this thread to the list, there are folks that > >> do understand it. > > > > If SELinux is running in permissive I don't see how it could be at > > fault for your issue. Did you verify that (getenforce)? > > > >>> -- > >>> [Fri Apr 07 17:03:28.597101 2017] [php7:warn] [pid 1797:tid > >>> 140599445419776] [client 192.168.1.10:45127] PHP Warning: > >>> pg_connect(): Unable to connect to PostgreSQL server: could not > >>> connect to server: No such file or directory\n\tIs the server > >>> running locally and > >>> accepting\n\tconnections on Unix domain socket > >>> "/tmp/.s.PGSQL.5432"? in /httpd/iliffe/testfcgi.php on > >>> line 121 > > > > This might be a silly question, but is PHP running on the same server > > as Postgres? > > To add to this, previously you mentioned: > > "Also, using the on board firewall (firewalld) to provide a secondary > domain where the actual business processes run. " > > What exactly does that mean? I'm trying/planning to use firewalld to keep certain remote addresses from connecting to the mail server. Since I have it anyway, I want to strengthen the security by moving non-Internet connections internal of that firewall so only Apache is exposed to the Internet and the databases, etc, are internal. This is a Unix domain socket connection so I don't think the firewall should get involved. Since you raised the question, I added port 5432 to the open list in firewalld but it didn't make any difference, still not connecting. > > > HTH, > > > > Joe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On Friday 07 April 2017 22:45:16 Joe Conway wrote: > On 04/07/2017 05:35 PM, Adrian Klaver wrote: > > On 04/07/2017 05:03 PM, John Iliffe wrote: > Running on Fedora 25 with SELinux in PERMISSIVE mode. The audit > log shows no hits on Postgresql. > >> > >> My going in position was/still is, that this is a SELinux security > >> problem > >> but I am finding SELinux to be the most opaque and badly documented > >> software > >> that I have ever had to deal with, which is why it is running in > >> permissive > >> mode at the moment. > > > > Well what I know about SELinux would fit in the navel of a flea(tip of > > the hat to David Niven), so I can not be of much help there. The > > reason I am returned this thread to the list, there are folks that do > > understand it. > > If SELinux is running in permissive I don't see how it could be at fault > for your issue. Did you verify that (getenforce)? One would think so. But I'm out of ideas otherwise. I've been chasing this around for several days. > > >> -- > >> [Fri Apr 07 17:03:28.597101 2017] [php7:warn] [pid 1797:tid > >> 140599445419776] [client 192.168.1.10:45127] PHP Warning: > >> pg_connect(): Unable to connect to PostgreSQL server: could not > >> connect to server: No such file or directory\n\tIs the server running > >> locally and > >> accepting\n\tconnections on Unix domain socket > >> "/tmp/.s.PGSQL.5432"? in /httpd/iliffe/testfcgi.php on line > >> 121 > > This might be a silly question, but is PHP running on the same server as > Postgres? No question is silly if you don't know the answer :-) Yes, they are both on the same server. > > HTH, > > Joe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On 04/07/2017 07:45 PM, Joe Conway wrote: On 04/07/2017 05:35 PM, Adrian Klaver wrote: On 04/07/2017 05:03 PM, John Iliffe wrote: Running on Fedora 25 with SELinux in PERMISSIVE mode. The audit log shows no hits on Postgresql. My going in position was/still is, that this is a SELinux security problem but I am finding SELinux to be the most opaque and badly documented software that I have ever had to deal with, which is why it is running in permissive mode at the moment. Well what I know about SELinux would fit in the navel of a flea(tip of the hat to David Niven), so I can not be of much help there. The reason I am returned this thread to the list, there are folks that do understand it. If SELinux is running in permissive I don't see how it could be at fault for your issue. Did you verify that (getenforce)? -- [Fri Apr 07 17:03:28.597101 2017] [php7:warn] [pid 1797:tid 140599445419776] [client 192.168.1.10:45127] PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server: No such file or directory\n\tIs the server running locally and accepting\n\tconnections on Unix domain socket "/tmp/.s.PGSQL.5432"? in /httpd/iliffe/testfcgi.php on line 121 This might be a silly question, but is PHP running on the same server as Postgres? To add to this, previously you mentioned: "Also, using the on board firewall (firewalld) to provide a secondary domain where the actual business processes run. " What exactly does that mean? HTH, Joe -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On 04/07/2017 05:35 PM, Adrian Klaver wrote: > On 04/07/2017 05:03 PM, John Iliffe wrote: Running on Fedora 25 with SELinux in PERMISSIVE mode. The audit log shows no hits on Postgresql. >> My going in position was/still is, that this is a SELinux security >> problem >> but I am finding SELinux to be the most opaque and badly documented >> software >> that I have ever had to deal with, which is why it is running in >> permissive >> mode at the moment. > > Well what I know about SELinux would fit in the navel of a flea(tip of > the hat to David Niven), so I can not be of much help there. The reason > I am returned this thread to the list, there are folks that do > understand it. If SELinux is running in permissive I don't see how it could be at fault for your issue. Did you verify that (getenforce)? >> -- >> [Fri Apr 07 17:03:28.597101 2017] [php7:warn] [pid 1797:tid >> 140599445419776] [client 192.168.1.10:45127] PHP Warning: >> pg_connect(): Unable to connect to PostgreSQL server: could not >> connect to server: No such file or directory\n\tIs the server running >> locally and >> accepting\n\tconnections on Unix domain socket >> "/tmp/.s.PGSQL.5432"? in /httpd/iliffe/testfcgi.php on line >> 121 This might be a silly question, but is PHP running on the same server as Postgres? HTH, Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development signature.asc Description: OpenPGP digital signature
Re: [GENERAL] Unable to connect to Postgresql
On Friday 07 April 2017 20:39:55 Adrian Klaver wrote: > On 04/07/2017 05:10 PM, John Iliffe wrote: > Actually Ccing list this time > > > On Friday 07 April 2017 19:51:57 you wrote: > >> On 04/07/2017 04:57 PM, John Iliffe wrote: > >>> Hi Adrian: > > Well, it ain't that simple! I am trying to take advantage of having a > > new server that doesn't have to be in production until month end to > > update everything to the latest and greatest. Everything runs > > properly on the existing server which is on Postgresql 9.2.1, using > > mod_php to connect. > > > > Changes that I have made are: update Postgresql, PHP, and Apache, > > change to fcgi from mod_php (which should not get involved here, but > > I backed out that change and still doesn't work) and the addition of > > SELinux for security (none on present server). > > Aah, so a lot changed. > > Do you have a way of trying to connect using PHP that does not involve > going through Apache? Yes, running in command line mode under root; the output from one of the cron jobs that hits the database seems to be as expected. It uses a database that hasn't been loaded yet and the error message from the postgresql log says that. (actually it says the role doesn't exist but that is the correct response) The point is, it does connect because it tries to log in. > > > Also, using the on board firewall (firewalld) to provide a secondary > > domain where the actual business processes run. > > > > So, I guess the answer is that the current arrangement has never run > > correctly. > > > >>> Regards, > >>> > >>> John -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On Friday 07 April 2017 20:35:40 Adrian Klaver wrote: > On 04/07/2017 05:03 PM, John Iliffe wrote: > > Please reply to list also Yes, sorry about that. > Ccing list. > > > On Friday 07 April 2017 18:58:15 you wrote: > >> On 04/07/2017 02:38 PM, John Iliffe wrote: > >>> When I attempt to run any web application php cannot open a database > >>> because of failure to connect. (Please disregard the programme > >>> name, it is running in mod_php, not as an fcgi module). The (php) > >>> message is: > >>> > >>> -- > >>> [Fri Apr 07 17:03:28.597101 2017] [php7:warn] [pid 1797:tid > >>> 140599445419776] [client 192.168.1.10:45127] PHP Warning: > >>> pg_connect(): Unable to connect to PostgreSQL server: could not > >>> connect to server: No such file or directory\n\tIs the server > >>> running locally and > >>> accepting\n\tconnections on Unix domain socket > >>> "/tmp/.s.PGSQL.5432"? in /httpd/iliffe/testfcgi.php on > >>> line 121 > >>> > >>> The proper socket does exist: > >>> > >>> - > >>> ls -al /tmp | grep PGSQL > >>> srwxrwxrwx. 1 postgres postgres0 Apr 7 16:53 .s.PGSQL.5432 > >>> -rw---. 1 postgres postgres 49 Apr 7 16:53 > >>> .s.PGSQL.5432.lock > >>> > >>> ss -a | grep 5432 > >>> u_str LISTEN 0 128/tmp/.s.PGSQL.5432 30480 > >>> > >>> * 0 - > >>> > >>> Running on Fedora 25 with SELinux in PERMISSIVE mode. The audit log > >>> shows no hits on Postgresql. > >>> > >>> Postgresql version number is 9.6.2 > >>> > >>> As expected, the postgresql log shows nothing since the last start > >>> up. > >> > >> Meant to add to previous post: > >> > >> What happens if you try to connect to the database using psql? > > > > Works just as I would expect. > > > > In fact, I was able to load the one of the databases from the pg_dump > > backup using pg_restore without any problems either, and I checked the > > results by running some in-stream transactions in psql. Everything > > went fine at that point, until I tried to start Apache and couldn't > > connect. > > To be precise PHP could not connect, correct? Yes. The "unable to connect" message is being issued by PHP. But PHP seems to know what is required (Unix domain socket number and where to find it are both correct as far as I can see. > > > My going in position was/still is, that this is a SELinux security > > problem but I am finding SELinux to be the most opaque and badly > > documented software that I have ever had to deal with, which is why > > it is running in permissive mode at the moment. > > Well what I know about SELinux would fit in the navel of a flea(tip of > the hat to David Niven), so I can not be of much help there. The reason > I am returned this thread to the list, there are folks that do > understand it. > > > Regards, > > > > John > > > >>> Thanks in advance. > >>> > >>> John > >>> = -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On 04/07/2017 05:10 PM, John Iliffe wrote: Actually Ccing list this time On Friday 07 April 2017 19:51:57 you wrote: On 04/07/2017 04:57 PM, John Iliffe wrote: Hi Adrian: Well, it ain't that simple! I am trying to take advantage of having a new server that doesn't have to be in production until month end to update everything to the latest and greatest. Everything runs properly on the existing server which is on Postgresql 9.2.1, using mod_php to connect. Changes that I have made are: update Postgresql, PHP, and Apache, change to fcgi from mod_php (which should not get involved here, but I backed out that change and still doesn't work) and the addition of SELinux for security (none on present server). Aah, so a lot changed. Do you have a way of trying to connect using PHP that does not involve going through Apache? Also, using the on board firewall (firewalld) to provide a secondary domain where the actual business processes run. So, I guess the answer is that the current arrangement has never run correctly. Regards, John -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On 04/07/2017 05:03 PM, John Iliffe wrote: Please reply to list also Ccing list. On Friday 07 April 2017 18:58:15 you wrote: On 04/07/2017 02:38 PM, John Iliffe wrote: When I attempt to run any web application php cannot open a database because of failure to connect. (Please disregard the programme name, it is running in mod_php, not as an fcgi module). The (php) message is: -- [Fri Apr 07 17:03:28.597101 2017] [php7:warn] [pid 1797:tid 140599445419776] [client 192.168.1.10:45127] PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server: No such file or directory\n\tIs the server running locally and accepting\n\tconnections on Unix domain socket "/tmp/.s.PGSQL.5432"? in /httpd/iliffe/testfcgi.php on line 121 The proper socket does exist: - ls -al /tmp | grep PGSQL srwxrwxrwx. 1 postgres postgres0 Apr 7 16:53 .s.PGSQL.5432 -rw---. 1 postgres postgres 49 Apr 7 16:53 .s.PGSQL.5432.lock ss -a | grep 5432 u_str LISTEN 0 128/tmp/.s.PGSQL.5432 30480 * 0 - Running on Fedora 25 with SELinux in PERMISSIVE mode. The audit log shows no hits on Postgresql. Postgresql version number is 9.6.2 As expected, the postgresql log shows nothing since the last start up. Meant to add to previous post: What happens if you try to connect to the database using psql? Works just as I would expect. In fact, I was able to load the one of the databases from the pg_dump backup using pg_restore without any problems either, and I checked the results by running some in-stream transactions in psql. Everything went fine at that point, until I tried to start Apache and couldn't connect. To be precise PHP could not connect, correct? My going in position was/still is, that this is a SELinux security problem but I am finding SELinux to be the most opaque and badly documented software that I have ever had to deal with, which is why it is running in permissive mode at the moment. Well what I know about SELinux would fit in the navel of a flea(tip of the hat to David Niven), so I can not be of much help there. The reason I am returned this thread to the list, there are folks that do understand it. Regards, John Thanks in advance. John = -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On 04/07/2017 04:57 PM, John Iliffe wrote: Hi Adrian: Not the same problem. Last time I couldn't get postgresql running at all. This time it is running and I can't connect to it. I did check for something else holding the socket, but as far as I can see nothing else has it. So when was the last time you could connect and has anything of note happened since then? Regards, John === On Friday 07 April 2017 18:51:33 Adrian Klaver wrote: On 04/07/2017 02:38 PM, John Iliffe wrote: When I attempt to run any web application php cannot open a database because of failure to connect. (Please disregard the programme name, it is running in mod_php, not as an fcgi module). The (php) message is: -- [Fri Apr 07 17:03:28.597101 2017] [php7:warn] [pid 1797:tid 140599445419776] [client 192.168.1.10:45127] PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server: No such file or directory\n\tIs the server running locally and accepting\n\tconnections on Unix domain socket "/tmp/.s.PGSQL.5432"? in /httpd/iliffe/testfcgi.php on line 121 The proper socket does exist: - ls -al /tmp | grep PGSQL srwxrwxrwx. 1 postgres postgres0 Apr 7 16:53 .s.PGSQL.5432 -rw---. 1 postgres postgres 49 Apr 7 16:53 .s.PGSQL.5432.lock ss -a | grep 5432 u_str LISTEN 0 128/tmp/.s.PGSQL.5432 30480 * 0 - Running on Fedora 25 with SELinux in PERMISSIVE mode. The audit log shows no hits on Postgresql. Postgresql version number is 9.6.2 As expected, the postgresql log shows nothing since the last start up. Well the last time this happened the answer was this: https://www.postgresql.org/message-id/25543.1489081789%40sss.pgh.pa.us Thanks in advance. John = -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
Hi Adrian: Not the same problem. Last time I couldn't get postgresql running at all. This time it is running and I can't connect to it. I did check for something else holding the socket, but as far as I can see nothing else has it. Regards, John === On Friday 07 April 2017 18:51:33 Adrian Klaver wrote: > On 04/07/2017 02:38 PM, John Iliffe wrote: > > When I attempt to run any web application php cannot open a database > > because of failure to connect. (Please disregard the programme name, > > it is running in mod_php, not as an fcgi module). The (php) message > > is: > > > > -- > > [Fri Apr 07 17:03:28.597101 2017] [php7:warn] [pid 1797:tid > > 140599445419776] [client 192.168.1.10:45127] PHP Warning: > > pg_connect(): Unable to connect to PostgreSQL server: could not > > connect to server: No such file or directory\n\tIs the server running > > locally and > > accepting\n\tconnections on Unix domain socket > > "/tmp/.s.PGSQL.5432"? in /httpd/iliffe/testfcgi.php on line > > 121 > > > > The proper socket does exist: > > > > - > > ls -al /tmp | grep PGSQL > > srwxrwxrwx. 1 postgres postgres0 Apr 7 16:53 .s.PGSQL.5432 > > -rw---. 1 postgres postgres 49 Apr 7 16:53 .s.PGSQL.5432.lock > > > > ss -a | grep 5432 > > u_str LISTEN 0 128/tmp/.s.PGSQL.5432 30480 > > * 0 - > > > > Running on Fedora 25 with SELinux in PERMISSIVE mode. The audit log > > shows no hits on Postgresql. > > > > Postgresql version number is 9.6.2 > > > > As expected, the postgresql log shows nothing since the last start up. > > Well the last time this happened the answer was this: > > https://www.postgresql.org/message-id/25543.1489081789%40sss.pgh.pa.us > > > Thanks in advance. > > > > John > > = -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On 04/07/2017 02:38 PM, John Iliffe wrote: When I attempt to run any web application php cannot open a database because of failure to connect. (Please disregard the programme name, it is running in mod_php, not as an fcgi module). The (php) message is: -- [Fri Apr 07 17:03:28.597101 2017] [php7:warn] [pid 1797:tid 140599445419776] [client 192.168.1.10:45127] PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server: No such file or directory\n\tIs the server running locally and accepting\n\tconnections on Unix domain socket "/tmp/.s.PGSQL.5432"? in /httpd/iliffe/testfcgi.php on line 121 The proper socket does exist: - ls -al /tmp | grep PGSQL srwxrwxrwx. 1 postgres postgres0 Apr 7 16:53 .s.PGSQL.5432 -rw---. 1 postgres postgres 49 Apr 7 16:53 .s.PGSQL.5432.lock ss -a | grep 5432 u_str LISTEN 0 128/tmp/.s.PGSQL.5432 30480* 0 - Running on Fedora 25 with SELinux in PERMISSIVE mode. The audit log shows no hits on Postgresql. Postgresql version number is 9.6.2 As expected, the postgresql log shows nothing since the last start up. Meant to add to previous post: What happens if you try to connect to the database using psql? Thanks in advance. John = -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On 04/07/2017 02:38 PM, John Iliffe wrote: When I attempt to run any web application php cannot open a database because of failure to connect. (Please disregard the programme name, it is running in mod_php, not as an fcgi module). The (php) message is: -- [Fri Apr 07 17:03:28.597101 2017] [php7:warn] [pid 1797:tid 140599445419776] [client 192.168.1.10:45127] PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server: No such file or directory\n\tIs the server running locally and accepting\n\tconnections on Unix domain socket "/tmp/.s.PGSQL.5432"? in /httpd/iliffe/testfcgi.php on line 121 The proper socket does exist: - ls -al /tmp | grep PGSQL srwxrwxrwx. 1 postgres postgres0 Apr 7 16:53 .s.PGSQL.5432 -rw---. 1 postgres postgres 49 Apr 7 16:53 .s.PGSQL.5432.lock ss -a | grep 5432 u_str LISTEN 0 128/tmp/.s.PGSQL.5432 30480* 0 - Running on Fedora 25 with SELinux in PERMISSIVE mode. The audit log shows no hits on Postgresql. Postgresql version number is 9.6.2 As expected, the postgresql log shows nothing since the last start up. Well the last time this happened the answer was this: https://www.postgresql.org/message-id/25543.1489081789%40sss.pgh.pa.us Thanks in advance. John = -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to PostgreSQL server : Could not get socket error status
On 12/22/11 1:31 AM, Shankar Palaniappan wrote: I am getting the following error when I try to connect from PHP with PostgreSQL server. Operating System is HP-UX and I was able to connect through PostgreSQL server from psql, what postgres connection parameters are you passing to the php connection function? if you specified host=localhost, or some such, then on psql use -h localhost to simulate this. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to PostgreSQL server via PHP
It was SELinux denying apache permission to make TCP connections!
I thought I had SELinux turned off but it wasn't. To be sure it is do
/usr/sbin/sestatus | grep SELinux
and if it comes back with anything other than SELinux status: disabled it's
still running.
While I was talking to the php board I discovered you can configure php to
run from the command line (mine was by default). So I wrote this bit of php
(obviously change the database name and you may need a user and passwd)...
" ;
printf("%s", pg_errormessage( $dbconn ) );
exit(); }
else {echo "connected", "\n";}
?>
saved it as test.php and at a shell prompt ran...
[john@ tmp]$ php test.php
connected
If you get 'connected' you know php and postgres are talking via tcp and
some secondary process (firewall/SELinux) is preventing the apache
connection.
Thanks for the help.
From: "John Coulthard" <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Unable to connect to PostgreSQL server via PHP
Date: Mon, 13 Aug 2007 15:34:19 +
From: Tom Lane <[EMAIL PROTECTED]>
To: "John Coulthard" <[EMAIL PROTECTED]>
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Unable to connect to PostgreSQL server via PHP
Date: Mon, 13 Aug 2007 10:09:15 -0400
"John Coulthard" <[EMAIL PROTECTED]> writes:
> That's not my problem though this is "could not connect to server:
> Permission denied" If it's denying permission I must have the
permissions
> set wrong but where to I start looking for them?
"Permission denied" is a pretty strange error for a TCP connect failure,
as that is not a filesystem operation.
Are you able to connect with psql, or some other non-php client?
Use "psql -h localhost" to make sure it tries a TCP connection not
a Unix-socket connection.
Thanks. You mean like this? This connects without an error.
[EMAIL PROTECTED] john]# su webuser
[EMAIL PROTECTED] john]$ psql -h localhost lumbribase
Welcome to psql 8.0.8, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
lumbribase=>
_
FREE pop-up blocking with the new MSN Toolbar - get it now!
http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
_
Don't just search. Find. Check out the new MSN Search!
http://search.msn.click-url.com/go/onm00200636ave/direct/01/
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Unable to connect to PostgreSQL server via PHP
From: Tom Lane <[EMAIL PROTECTED]> To: "John Coulthard" <[EMAIL PROTECTED]> CC: pgsql-general@postgresql.org Subject: Re: [GENERAL] Unable to connect to PostgreSQL server via PHP Date: Mon, 13 Aug 2007 10:09:15 -0400 "John Coulthard" <[EMAIL PROTECTED]> writes: > That's not my problem though this is "could not connect to server: > Permission denied" If it's denying permission I must have the permissions > set wrong but where to I start looking for them? "Permission denied" is a pretty strange error for a TCP connect failure, as that is not a filesystem operation. Are you able to connect with psql, or some other non-php client? Use "psql -h localhost" to make sure it tries a TCP connection not a Unix-socket connection. Thanks. You mean like this? This connects without an error. [EMAIL PROTECTED] john]# su webuser [EMAIL PROTECTED] john]$ psql -h localhost lumbribase Welcome to psql 8.0.8, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit lumbribase=> _ FREE pop-up blocking with the new MSN Toolbar - get it now! http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Unable to connect to PostgreSQL server via PHP
"John Coulthard" <[EMAIL PROTECTED]> writes: > That's not my problem though this is "could not connect to server: > Permission denied" If it's denying permission I must have the permissions > set wrong but where to I start looking for them? "Permission denied" is a pretty strange error for a TCP connect failure, as that is not a filesystem operation. Are you able to connect with psql, or some other non-php client? Use "psql -h localhost" to make sure it tries a TCP connection not a Unix-socket connection. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Unable to connect to PostgreSQL server via PHP
From: "Lim Berger" <[EMAIL PROTECTED]>
To: "John Coulthard" <[EMAIL PROTECTED]>
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Unable to connect to PostgreSQL server via PHP
Date: Mon, 13 Aug 2007 18:51:37 +0800
On 8/13/07, John Coulthard <[EMAIL PROTECTED]> wrote:
> The part of the php code for the connection is
>
>
> $dbconn=pg_connect( "dbname=lumbribase host=localhost port=5432
> user=postgres password=$PG_PASS" );
> if ( ! $dbconn ) {
> echo "Error connecting to the database ! " ;
> printf("%s", pg_errormessage( $dbconn ) );
> exit(); }
>
> This code works on zeldia
> http://zeldia.cap.ed.ac.uk/Lumbribase/search_id.php
> but not on xyala http://xyala.cap.ed.ac.uk/Lumbribase/search_id.php
>
> So it's something to do with the way xyala is set up. I'm sure I'm
missing
> something obvious but what..?
>
> It's not a firewall issue because it persists when the iptabes are off.
>
> What does "could not connect to server: Permission denied" mean? Have I
> done some something as root or me that I should have done as
> user postgres?
Hi John,
Had the same issue. Try the following steps:
1. PGHBA.CONF
This is an important file. Mine (on Linux CentOS 4) is located at
"/var/lib/pgsql/data/pghba.conf". Make sure it looks like the
following.
local all all md5
hostall all 127.0.0.1 255.255.255.255 md5
2. POSTGRESQL.CONF
listen_addresses = 'localhost,*'
#port = 5432
other settings
3. PHP CODE
link = pg_connect("host=localhost dbname=MYDB user=MYUSER
password=MYPASS");
--
That is all you need. Don't specify anything else in the connection
string. Let me know how it goes.
LB
---(end of broadcast)---
TIP 6: explain analyze is your friend
Thanks for the sugestions but they don''t solve the problem. I do notice
that if I set listen_addresses='localhost,*' then I get the following when I
start postgres...
bash-3.00$ ps -ef | grep postgres
root 9669 8757 0 13:34 pts/500:00:00 su postgres
postgres 9670 9669 0 13:34 pts/500:00:00 bash
postgres 9673 9670 0 13:34 pts/500:00:00 ps -ef
postgres 9674 9670 0 13:34 pts/500:00:00 grep postgres
bash-3.00$ /usr/bin/pg_ctl -D /var/lib/pgsql/data start
postmaster starting
bash-3.00$ LOG: could not bind IPv4 socket: Address already in use
HINT: Is another postmaster already running on port 5432? If not, wait a
few seconds and retry.
bash-3.00$
it does start though. But if I just have localhost or * as the
listen_addresses it starts without error.
That's not my problem though this is "could not connect to server:
Permission denied" If it's denying permission I must have the permissions
set wrong but where to I start looking for them? All sub dirs in
/var/lib/pgsql are owned by postgres and seem to have the same permissions
on both systems.
Cheers
_
Find a local pizza place, movie theater, and more
.then map the best route!
http://maps.live.com/default.aspx?v=2&ss=yp.bars~yp.pizza~yp.movie%20theater&cp=42.358996~-71.056691&style=r&lvl=13&tilt=-90&dir=0&alt=-1000&scene=950607&encType=1&FORM=MGAC01
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Unable to connect to PostgreSQL server via PHP
The part of the php code for the connection is
$dbconn=pg_connect( "dbname=lumbribase host=localhost port=5432
user=postgres password=$PG_PASS" );
if ( ! $dbconn ) {
echo "Error connecting to the database ! " ;
printf("%s", pg_errormessage( $dbconn ) );
exit(); }
This code works on zeldia
http://zeldia.cap.ed.ac.uk/Lumbribase/search_id.php
but not on xyala http://xyala.cap.ed.ac.uk/Lumbribase/search_id.php
So it's something to do with the way xyala is set up. I'm sure I'm missing
something obvious but what..?
It's not a firewall issue because it persists when the iptabes are off.
What does "could not connect to server: Permission denied" mean? Have I
done some something as root or me that I should have done as user postgres?
Thanks
From: Julio Cesar Sánchez González <[EMAIL PROTECTED]>
To: John Coulthard <[EMAIL PROTECTED]>
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Unable to connect to PostgreSQL server via PHP
Date: Fri, 10 Aug 2007 22:50:47 -0500
El jue, 09-08-2007 a las 14:51 +, John Coulthard escribió:
> Hi
>
> I'm trying to set up a new webserver running php and pgsql. PHP was
> connecting to postgres but I needed to install the php-gd module and now
I
> get the error...
>
> "PHP Warning: pg_connect() [> href='function.pg-connect'>function.pg-connect]: Unable to connect
to
> PostgreSQL server: could not connect to server: Permission denied\n\tIs
the
> server running on host "localhost" and accepting\n\tTCP/IP
> connections on port 5432?"
>
> and I'm at a loss can anyone tell me why it's not connecting?
>
> Thanks
>
> This bit's I know are...
> http://xyala.cap.ed.ac.uk/php_info.php say's php's configured for pgsql
>
> [EMAIL PROTECTED] telnet localhost 5432
> Trying 127.0.0.1...
> Connected to localhost.localdomain (127.0.0.1).
> Escape character is '^]'.
> Connection closed by foreign host.
> [EMAIL PROTECTED]
>
>
> [EMAIL PROTECTED] less /var/lib/pgsql/data/pg_hba.conf
> # TYPE DATABASEUSERCIDR-ADDRESS METHOD
>
> # "local" is for Unix domain socket connections only
> #local all all ident sameuser
> local all all trust
> # IPv4 local connections:
> #hostall all 127.0.0.1/32 ident sameuser
> hostall all 127.0.0.1/32 trust
> # IPv6 local connections:
> #hostall all ::1/128 ident sameuser
> hostall all ::1/128 trust
>
> [EMAIL PROTECTED] grep 'listen' /var/lib/pgsql/data/postgresql.conf
> # "pg_ctl reload". Some settings, such as listen_address, require
> #listen_addresses = 'localhost' # what IP interface(s) to listen on;
> listen_addresses = '*'
> [EMAIL PROTECTED]
>
> [EMAIL PROTECTED] less /etc/php.d/pgsql.ini
> ; Enable pgsql extension module
> extension=pgsql.so
>
> the server I'm going to replace is running the same versions of PHP and
> postgres http://zeldia.cap.ed.ac.uk/php_info.php
> The /etc/php.ini files on the two machines are the same and the
> /var/lib/pgsql/data/postgresql.conf files are only different because
I've
> set listen_addresses = '*' on the new server (xyala) to see if I can
make it
> work.
>
> _
> Express yourself instantly with MSN Messenger! Download today it's FREE!
> http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
>
>
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
show your php source code for help you.
--
Regards,
Julio Cesar Sánchez González
www.sistemasyconectividad.com.mx
blog: http://darkavngr.blogspot.com
---
Ahora me he convertido en la muerte, destructora de mundos.
Soy la Muerte que se lleva todo, la fuente de las cosas que vendran.
_
Messenger Café open for fun 24/7. Hot games, cool activities served daily.
Visit now. http://cafemessenger.com?ocid=TXT_TAGHM_AugHMtagline
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Unable to connect to PostgreSQL server via PHP
On 8/13/07, John Coulthard <[EMAIL PROTECTED]> wrote:
> The part of the php code for the connection is
>
>
> $dbconn=pg_connect( "dbname=lumbribase host=localhost port=5432
> user=postgres password=$PG_PASS" );
> if ( ! $dbconn ) {
> echo "Error connecting to the database ! " ;
> printf("%s", pg_errormessage( $dbconn ) );
> exit(); }
>
> This code works on zeldia
> http://zeldia.cap.ed.ac.uk/Lumbribase/search_id.php
> but not on xyala http://xyala.cap.ed.ac.uk/Lumbribase/search_id.php
>
> So it's something to do with the way xyala is set up. I'm sure I'm missing
> something obvious but what..?
>
> It's not a firewall issue because it persists when the iptabes are off.
>
> What does "could not connect to server: Permission denied" mean? Have I
> done some something as root or me that I should have done as
> user postgres?
Hi John,
Had the same issue. Try the following steps:
1. PGHBA.CONF
This is an important file. Mine (on Linux CentOS 4) is located at
"/var/lib/pgsql/data/pghba.conf". Make sure it looks like the
following.
local all all md5
hostall all 127.0.0.1 255.255.255.255 md5
2. POSTGRESQL.CONF
listen_addresses = 'localhost,*'
#port = 5432
other settings
3. PHP CODE
link = pg_connect("host=localhost dbname=MYDB user=MYUSER password=MYPASS");
--
That is all you need. Don't specify anything else in the connection
string. Let me know how it goes.
LB
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Unable to connect to PostgreSQL server via PHP
The part of the php code for the connection is
$dbconn=pg_connect( "dbname=lumbribase host=localhost port=5432
user=postgres password=$PG_PASS" );
if ( ! $dbconn ) {
echo "Error connecting to the database ! " ;
printf("%s", pg_errormessage( $dbconn ) );
exit(); }
This code works on zeldia
http://zeldia.cap.ed.ac.uk/Lumbribase/search_id.php
but not on xyala http://xyala.cap.ed.ac.uk/Lumbribase/search_id.php
So it's something to do with the way xyala is set up. I'm sure I'm missing
something obvious but what..?
It's not a firewall issue because it persists when the iptabes are off.
What does "could not connect to server: Permission denied" mean? Have I
done some something as root or me that I should have done as user postgres?
Thanks
From: Julio Cesar Sánchez González <[EMAIL PROTECTED]>
To: John Coulthard <[EMAIL PROTECTED]>
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Unable to connect to PostgreSQL server via PHP
Date: Fri, 10 Aug 2007 22:50:47 -0500
El jue, 09-08-2007 a las 14:51 +, John Coulthard escribió:
> Hi
>
> I'm trying to set up a new webserver running php and pgsql. PHP was
> connecting to postgres but I needed to install the php-gd module and now
I
> get the error...
>
> "PHP Warning: pg_connect() [> href='function.pg-connect'>function.pg-connect]: Unable to connect
to
> PostgreSQL server: could not connect to server: Permission denied\n\tIs
the
> server running on host "localhost" and accepting\n\tTCP/IP
> connections on port 5432?"
>
> and I'm at a loss can anyone tell me why it's not connecting?
>
> Thanks
>
> This bit's I know are...
> http://xyala.cap.ed.ac.uk/php_info.php say's php's configured for pgsql
>
> [EMAIL PROTECTED] telnet localhost 5432
> Trying 127.0.0.1...
> Connected to localhost.localdomain (127.0.0.1).
> Escape character is '^]'.
> Connection closed by foreign host.
> [EMAIL PROTECTED]
>
>
> [EMAIL PROTECTED] less /var/lib/pgsql/data/pg_hba.conf
> # TYPE DATABASEUSERCIDR-ADDRESS METHOD
>
> # "local" is for Unix domain socket connections only
> #local all all ident sameuser
> local all all trust
> # IPv4 local connections:
> #hostall all 127.0.0.1/32 ident sameuser
> hostall all 127.0.0.1/32 trust
> # IPv6 local connections:
> #hostall all ::1/128 ident sameuser
> hostall all ::1/128 trust
>
> [EMAIL PROTECTED] grep 'listen' /var/lib/pgsql/data/postgresql.conf
> # "pg_ctl reload". Some settings, such as listen_address, require
> #listen_addresses = 'localhost' # what IP interface(s) to listen on;
> listen_addresses = '*'
> [EMAIL PROTECTED]
>
> [EMAIL PROTECTED] less /etc/php.d/pgsql.ini
> ; Enable pgsql extension module
> extension=pgsql.so
>
> the server I'm going to replace is running the same versions of PHP and
> postgres http://zeldia.cap.ed.ac.uk/php_info.php
> The /etc/php.ini files on the two machines are the same and the
> /var/lib/pgsql/data/postgresql.conf files are only different because
I've
> set listen_addresses = '*' on the new server (xyala) to see if I can
make it
> work.
>
> _
> Express yourself instantly with MSN Messenger! Download today it's FREE!
> http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
>
>
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
show your php source code for help you.
--
Regards,
Julio Cesar Sánchez González
www.sistemasyconectividad.com.mx
blog: http://darkavngr.blogspot.com
---
Ahora me he convertido en la muerte, destructora de mundos.
Soy la Muerte que se lleva todo, la fuente de las cosas que vendran.
_
Express yourself instantly with MSN Messenger! Download today it's FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Unable to connect to PostgreSQL server via PHP
El jue, 09-08-2007 a las 14:51 +, John Coulthard escribió: > Hi > > I'm trying to set up a new webserver running php and pgsql. PHP was > connecting to postgres but I needed to install the php-gd module and now I > get the error... > > "PHP Warning: pg_connect() [ href='function.pg-connect'>function.pg-connect]: Unable to connect to > PostgreSQL server: could not connect to server: Permission denied\n\tIs the > server running on host "localhost" and accepting\n\tTCP/IP > connections on port 5432?" > > and I'm at a loss can anyone tell me why it's not connecting? > > Thanks > > This bit's I know are... > http://xyala.cap.ed.ac.uk/php_info.php say's php's configured for pgsql > > [EMAIL PROTECTED] telnet localhost 5432 > Trying 127.0.0.1... > Connected to localhost.localdomain (127.0.0.1). > Escape character is '^]'. > Connection closed by foreign host. > [EMAIL PROTECTED] > > > [EMAIL PROTECTED] less /var/lib/pgsql/data/pg_hba.conf > # TYPE DATABASEUSERCIDR-ADDRESS METHOD > > # "local" is for Unix domain socket connections only > #local all all ident sameuser > local all all trust > # IPv4 local connections: > #hostall all 127.0.0.1/32 ident sameuser > hostall all 127.0.0.1/32 trust > # IPv6 local connections: > #hostall all ::1/128 ident sameuser > hostall all ::1/128 trust > > [EMAIL PROTECTED] grep 'listen' /var/lib/pgsql/data/postgresql.conf > # "pg_ctl reload". Some settings, such as listen_address, require > #listen_addresses = 'localhost' # what IP interface(s) to listen on; > listen_addresses = '*' > [EMAIL PROTECTED] > > [EMAIL PROTECTED] less /etc/php.d/pgsql.ini > ; Enable pgsql extension module > extension=pgsql.so > > the server I'm going to replace is running the same versions of PHP and > postgres http://zeldia.cap.ed.ac.uk/php_info.php > The /etc/php.ini files on the two machines are the same and the > /var/lib/pgsql/data/postgresql.conf files are only different because I've > set listen_addresses = '*' on the new server (xyala) to see if I can make it > work. > > _ > Express yourself instantly with MSN Messenger! Download today it's FREE! > http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ > > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings show your php source code for help you. -- Regards, Julio Cesar Sánchez González www.sistemasyconectividad.com.mx blog: http://darkavngr.blogspot.com --- Ahora me he convertido en la muerte, destructora de mundos. Soy la Muerte que se lleva todo, la fuente de las cosas que vendran. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Unable to connect to PostgreSQL server
You may also want to look into your php.ini settings. pgsql.max_persistent pgsql.max_links pgsql.auto_reset_persistent and PGSQL_CONNECT_FORCE_NEW are options you would want to check into. > Look in data/postgres.conf > > You will find a line there such as max_connections = 50 you will also need to > check that shared_buffers is at least twice the max_connections. > > You can also start Postmaster with -B nBuffers -N maxbackends > > > On 20/4/2006 17:41, "Martin Kuria" <[EMAIL PROTECTED]> wrote: > >> hi, >> >> I have a problem with my postgresql database it always gives me an error: >> >> Warning: pg_connect() [function.pg-connect]: Unable to connect to PostgreSQL >> server: FATAL: sorry, too many clients already in ~/includes/DbConnector.php >> on line 27 >> >> Please advice how I can manage my database to handle enough connections >> please advice. >> >> +-+ >> | Martin W. Kuria (Mr.) [EMAIL PROTECTED] >> ++ >> >> _ >> Express yourself instantly with MSN Messenger! Download today it's FREE! >> http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ >> >> >> ---(end of broadcast)--- >> TIP 6: explain analyze is your friend >> > ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Unable to connect to PostgreSQL server
Look in data/postgres.conf You will find a line there such as max_connections = 50 you will also need to check that shared_buffers is at least twice the max_connections. You can also start Postmaster with -B nBuffers -N maxbackends On 20/4/2006 17:41, "Martin Kuria" <[EMAIL PROTECTED]> wrote: > hi, > > I have a problem with my postgresql database it always gives me an error: > > Warning: pg_connect() [function.pg-connect]: Unable to connect to PostgreSQL > server: FATAL: sorry, too many clients already in ~/includes/DbConnector.php > on line 27 > > Please advice how I can manage my database to handle enough connections > please advice. > > +-+ > | Martin W. Kuria (Mr.) [EMAIL PROTECTED] > ++ > > _ > Express yourself instantly with MSN Messenger! Download today it's FREE! > http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ > > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend > ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Unable to connect to PostgreSQL server
Martin,please check out the server configuration documentation athttp://www.postgresql.org/docs/8.1/interactive/runtime-config-connection.html#RUNTIME-CONFIG-CONNECTION-SETTINGS your configuration file of choice is postgresql.conf within the data directory.(btw: because the default is rather big, it is very likely that there is some "not reusing connections" or "not closing connection when ready with usage" bug in your application) best wishes,HaraldOn 4/20/06, Martin Kuria <[EMAIL PROTECTED]> wrote: hi,I have a problem with my postgresql database it always gives me an error:Warning: pg_connect() [function.pg-connect]: Unable to connect to PostgreSQLserver: FATAL: sorry, too many clients already in ~/includes/DbConnector.php on line 27Please advice how I can manage my database to handle enough connectionsplease advice.+-+| Martin W. Kuria (Mr.) [EMAIL PROTECTED]++_Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/---(end of broadcast)--- TIP 6: explain analyze is your friend-- GHUM Harald Massapersuadere et programmareHarald Armin MassaReinsburgstraße 202b70197 Stuttgart0173/9409607 -PostgreSQL - supported by a community that does not put you on hold
