Thread: psql connection issue

psql connection issue

From
Stephen Davies
Date:
I am in the process of migrating a bunch of databases and associated CGI
scripts from 9.1.4 to 9.3 (and from 32-bit to 64-bit).

The database migration has been successful but I have an issue with psql
connections from CGI scripts.

I can connect to the 9.3 server locally with psql from the command line, with
psql from other boxes on the LAN via TCP, via JDBC from programs and servlets
but cannot connect locally via CGI.

If I run any of the CGI scripts from the command line they work but when
invoked by Apache, they fail with the usual question as to whether anything is
listening on socket /tmp/.s.PGSQL.5432.

Running netstat -an shows:
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN
tcp6       0      0 :::5432                 :::*                    LISTEN
unix  2      [ ACC ]     STREAM     LISTENING     29773945 /tmp/.s.PGSQL.5432
unix  3      [ ]         STREAM     CONNECTED     30139402 /tmp/.s.PGSQL.5432

which I believe confirms that the socket is there and is used by local command
line psql connections.

Why would CGI connections fail?

Cheers and thanks,
Stephen
--
=============================================================================
Stephen Davies Consulting P/L                             Phone: 08-8177 1595
Adelaide, South Australia.                                Mobile:040 304 0583
Records & Collections Management.


Re: psql connection issue

From
Tom Lane
Date:
Stephen Davies <sdavies@sdc.com.au> writes:
> I am in the process of migrating a bunch of databases and associated CGI
> scripts from 9.1.4 to 9.3 (and from 32-bit to 64-bit).

> The database migration has been successful but I have an issue with psql
> connections from CGI scripts.

> I can connect to the 9.3 server locally with psql from the command line, with
> psql from other boxes on the LAN via TCP, via JDBC from programs and servlets
> but cannot connect locally via CGI.

> If I run any of the CGI scripts from the command line they work but when
> invoked by Apache, they fail with the usual question as to whether anything is
> listening on socket /tmp/.s.PGSQL.5432.

Some Linux variants think it improves security to run daemons like apache
in a context where what the daemon sees as /tmp has been mapped somewhere
else.

If you're running one of these platforms, the Postgres server and libpq
distributed by the vendor will have been hacked to cope, typically by
agreeing that the socket location is something like /var/run/postgresql/
rather than /tmp.  I'm guessing your 9.3 installation was self-built
and hasn't been configured that way.

            regards, tom lane


Re: psql connection issue

From
Ian Barwick
Date:
On 14/10/08 12:51, Stephen Davies wrote:
> I am in the process of migrating a bunch of databases and associated CGI scripts from
>  9.1.4 to 9.3 (and from 32-bit to 64-bit).
>
> The database migration has been successful but I have an issue with psql
> connections from CGI scripts.
>
> I can connect to the 9.3 server locally with psql from the command line,
> with psql from other boxes on the LAN via TCP, via JDBC from programs and
> servlets but cannot connect locally via CGI.
>
> If I run any of the CGI scripts from the command line they work but when
> invoked by Apache, they fail with the usual question as to whether anything is
>  listening on socket /tmp/.s.PGSQL.5432.
>
> Running netstat -an shows:
> tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN
> tcp6       0      0 :::5432                 :::*                    LISTEN
> unix  2      [ ACC ]     STREAM     LISTENING     29773945 /tmp/.s.PGSQL.5432
> unix  3      [ ]         STREAM     CONNECTED     30139402 /tmp/.s.PGSQL.5432
>
> which I believe confirms that the socket is there and is used by local command
> line psql connections.
>
> Why would CGI connections fail?

It's possible that whatever driver the CGI scripts use is expecting to find
the socket in another directory, e.g. /var/run/postgresql/.


Regards

Ian Barwick

--
 Ian Barwick                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


Re: psql connection issue

From
Stephen Davies
Date:
The permissions on the socket are 777 owner/group postgres.

I installed the 9.3 onto the Centos 7 server using the repo at postgresql.org.

(http://yum.postgresql.org/9.3/redhat/rhel-$releasever-$basearch)

There is no /var/run/postgresql and find cannot find another socket anywhere else.

Cheers and thanks,
Stephen

On 08/10/14 14:32, Tom Lane wrote:
> Stephen Davies <sdavies@sdc.com.au> writes:
>> I am in the process of migrating a bunch of databases and associated CGI
>> scripts from 9.1.4 to 9.3 (and from 32-bit to 64-bit).
>
>> The database migration has been successful but I have an issue with psql
>> connections from CGI scripts.
>
>> I can connect to the 9.3 server locally with psql from the command line, with
>> psql from other boxes on the LAN via TCP, via JDBC from programs and servlets
>> but cannot connect locally via CGI.
>
>> If I run any of the CGI scripts from the command line they work but when
>> invoked by Apache, they fail with the usual question as to whether anything is
>> listening on socket /tmp/.s.PGSQL.5432.
>
> Some Linux variants think it improves security to run daemons like apache
> in a context where what the daemon sees as /tmp has been mapped somewhere
> else.
>
> If you're running one of these platforms, the Postgres server and libpq
> distributed by the vendor will have been hacked to cope, typically by
> agreeing that the socket location is something like /var/run/postgresql/
> rather than /tmp.  I'm guessing your 9.3 installation was self-built
> and hasn't been configured that way.
>
>             regards, tom lane
>


--
=============================================================================
Stephen Davies Consulting P/L                             Phone: 08-8177 1595
Adelaide, South Australia.                                Mobile:040 304 0583
Records & Collections Management.


Re: psql connection issue

From
Adrian Klaver
Date:
On 10/07/2014 09:10 PM, Stephen Davies wrote:
> The permissions on the socket are 777 owner/group postgres.
>
> I installed the 9.3 onto the Centos 7 server using the repo at
> postgresql.org.
>
> (http://yum.postgresql.org/9.3/redhat/rhel-$releasever-$basearch)
>
> There is no /var/run/postgresql and find cannot find another socket
> anywhere else.

Sounds similar to this:

Long version:


http://serverfault.com/questions/609947/database-connection-to-postgresql-refused-for-flask-app-under-mod-wsgi-when-start

Short version:

Disable SELinux


>
> Cheers and thanks,
> Stephen
>
> On 08/10/14 14:32, Tom Lane wrote:
>> Stephen Davies <sdavies@sdc.com.au> writes:
>>> I am in the process of migrating a bunch of databases and associated CGI
>>> scripts from 9.1.4 to 9.3 (and from 32-bit to 64-bit).
>>
>>> The database migration has been successful but I have an issue with psql
>>> connections from CGI scripts.
>>
>>> I can connect to the 9.3 server locally with psql from the command
>>> line, with
>>> psql from other boxes on the LAN via TCP, via JDBC from programs and
>>> servlets
>>> but cannot connect locally via CGI.
>>
>>> If I run any of the CGI scripts from the command line they work but when
>>> invoked by Apache, they fail with the usual question as to whether
>>> anything is
>>> listening on socket /tmp/.s.PGSQL.5432.
>>
>> Some Linux variants think it improves security to run daemons like apache
>> in a context where what the daemon sees as /tmp has been mapped somewhere
>> else.
>>
>> If you're running one of these platforms, the Postgres server and libpq
>> distributed by the vendor will have been hacked to cope, typically by
>> agreeing that the socket location is something like /var/run/postgresql/
>> rather than /tmp.  I'm guessing your 9.3 installation was self-built
>> and hasn't been configured that way.
>>
>>             regards, tom lane
>>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: psql connection issue

From
Stephen Davies
Date:
This is not the same issue.
However, I had already disabled SELinux for other reasons.

The actual cause of my issue was the "new" private tmp facility in systemd
startup of httpd. This makes the PostgreSQL socket invisible to CGI scripts.

We have survived for many years without this before migrating to CentOS 7 so I
simply disabled this too and all came good.

Cheers and thanks,
Stephen

On 08/10/14 23:49, Adrian Klaver wrote:
> On 10/07/2014 09:10 PM, Stephen Davies wrote:
>> The permissions on the socket are 777 owner/group postgres.
>>
>> I installed the 9.3 onto the Centos 7 server using the repo at
>> postgresql.org.
>>
>> (http://yum.postgresql.org/9.3/redhat/rhel-$releasever-$basearch)
>>
>> There is no /var/run/postgresql and find cannot find another socket
>> anywhere else.
>
> Sounds similar to this:
>
> Long version:
>
>
http://serverfault.com/questions/609947/database-connection-to-postgresql-refused-for-flask-app-under-mod-wsgi-when-start
>
>
> Short version:
>
> Disable SELinux
>
>
>>
>> Cheers and thanks,
>> Stephen
>>
>> On 08/10/14 14:32, Tom Lane wrote:
>>> Stephen Davies <sdavies@sdc.com.au> writes:
>>>> I am in the process of migrating a bunch of databases and associated CGI
>>>> scripts from 9.1.4 to 9.3 (and from 32-bit to 64-bit).
>>>
>>>> The database migration has been successful but I have an issue with psql
>>>> connections from CGI scripts.
>>>
>>>> I can connect to the 9.3 server locally with psql from the command
>>>> line, with
>>>> psql from other boxes on the LAN via TCP, via JDBC from programs and
>>>> servlets
>>>> but cannot connect locally via CGI.
>>>
>>>> If I run any of the CGI scripts from the command line they work but when
>>>> invoked by Apache, they fail with the usual question as to whether
>>>> anything is
>>>> listening on socket /tmp/.s.PGSQL.5432.
>>>
>>> Some Linux variants think it improves security to run daemons like apache
>>> in a context where what the daemon sees as /tmp has been mapped somewhere
>>> else.
>>>
>>> If you're running one of these platforms, the Postgres server and libpq
>>> distributed by the vendor will have been hacked to cope, typically by
>>> agreeing that the socket location is something like /var/run/postgresql/
>>> rather than /tmp.  I'm guessing your 9.3 installation was self-built
>>> and hasn't been configured that way.
>>>
>>>             regards, tom lane
>>>
>>
>>
>
>


--
=============================================================================
Stephen Davies Consulting P/L                             Phone: 08-8177 1595
Adelaide, South Australia.                                Mobile:040 304 0583
Records & Collections Management.


Re: psql connection issue

From
Jim Nasby
Date:
FWIW, you could also use an IP connection to Postgres instead of the local socket.

On 10/8/14, 6:34 PM, Stephen Davies wrote:
> This is not the same issue.
> However, I had already disabled SELinux for other reasons.
>
> The actual cause of my issue was the "new" private tmp facility in systemd startup of httpd. This makes the
PostgreSQLsocket invisible to CGI scripts. 
>
> We have survived for many years without this before migrating to CentOS 7 so I simply disabled this too and all came
good.
>
> Cheers and thanks,
> Stephen
>
> On 08/10/14 23:49, Adrian Klaver wrote:
>> On 10/07/2014 09:10 PM, Stephen Davies wrote:
>>> The permissions on the socket are 777 owner/group postgres.
>>>
>>> I installed the 9.3 onto the Centos 7 server using the repo at
>>> postgresql.org.
>>>
>>> (http://yum.postgresql.org/9.3/redhat/rhel-$releasever-$basearch)
>>>
>>> There is no /var/run/postgresql and find cannot find another socket
>>> anywhere else.
>>
>> Sounds similar to this:
>>
>> Long version:
>>
>>
http://serverfault.com/questions/609947/database-connection-to-postgresql-refused-for-flask-app-under-mod-wsgi-when-start
>>
>>
>> Short version:
>>
>> Disable SELinux
>>
>>
>>>
>>> Cheers and thanks,
>>> Stephen
>>>
>>> On 08/10/14 14:32, Tom Lane wrote:
>>>> Stephen Davies <sdavies@sdc.com.au> writes:
>>>>> I am in the process of migrating a bunch of databases and associated CGI
>>>>> scripts from 9.1.4 to 9.3 (and from 32-bit to 64-bit).
>>>>
>>>>> The database migration has been successful but I have an issue with psql
>>>>> connections from CGI scripts.
>>>>
>>>>> I can connect to the 9.3 server locally with psql from the command
>>>>> line, with
>>>>> psql from other boxes on the LAN via TCP, via JDBC from programs and
>>>>> servlets
>>>>> but cannot connect locally via CGI.
>>>>
>>>>> If I run any of the CGI scripts from the command line they work but when
>>>>> invoked by Apache, they fail with the usual question as to whether
>>>>> anything is
>>>>> listening on socket /tmp/.s.PGSQL.5432.
>>>>
>>>> Some Linux variants think it improves security to run daemons like apache
>>>> in a context where what the daemon sees as /tmp has been mapped somewhere
>>>> else.
>>>>
>>>> If you're running one of these platforms, the Postgres server and libpq
>>>> distributed by the vendor will have been hacked to cope, typically by
>>>> agreeing that the socket location is something like /var/run/postgresql/
>>>> rather than /tmp.  I'm guessing your 9.3 installation was self-built
>>>> and hasn't been configured that way.
>>>>
>>>>             regards, tom lane
>>>>
>>>
>>>
>>
>>
>
>

--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com


Re: psql connection issue

From
Stephen Davies
Date:
Yes but that would have involved changing hundreds of CGI scripts. Dropping
the private tmp was easier.

On 11/10/14 07:01, Jim Nasby wrote:
> FWIW, you could also use an IP connection to Postgres instead of the local
> socket.
>
> On 10/8/14, 6:34 PM, Stephen Davies wrote:
>> This is not the same issue.
>> However, I had already disabled SELinux for other reasons.
>>
>> The actual cause of my issue was the "new" private tmp facility in systemd
>> startup of httpd. This makes the PostgreSQL socket invisible to CGI scripts.
>>
>> We have survived for many years without this before migrating to CentOS 7 so
>> I simply disabled this too and all came good.
>>
>> Cheers and thanks,
>> Stephen
>>
>> On 08/10/14 23:49, Adrian Klaver wrote:
>>> On 10/07/2014 09:10 PM, Stephen Davies wrote:
>>>> The permissions on the socket are 777 owner/group postgres.
>>>>
>>>> I installed the 9.3 onto the Centos 7 server using the repo at
>>>> postgresql.org.
>>>>
>>>> (http://yum.postgresql.org/9.3/redhat/rhel-$releasever-$basearch)
>>>>
>>>> There is no /var/run/postgresql and find cannot find another socket
>>>> anywhere else.
>>>
>>> Sounds similar to this:
>>>
>>> Long version:
>>>
>>>
http://serverfault.com/questions/609947/database-connection-to-postgresql-refused-for-flask-app-under-mod-wsgi-when-start
>>>
>>>
>>>
>>> Short version:
>>>
>>> Disable SELinux
>>>
>>>
>>>>
>>>> Cheers and thanks,
>>>> Stephen
>>>>
>>>> On 08/10/14 14:32, Tom Lane wrote:
>>>>> Stephen Davies <sdavies@sdc.com.au> writes:
>>>>>> I am in the process of migrating a bunch of databases and associated CGI
>>>>>> scripts from 9.1.4 to 9.3 (and from 32-bit to 64-bit).
>>>>>
>>>>>> The database migration has been successful but I have an issue with psql
>>>>>> connections from CGI scripts.
>>>>>
>>>>>> I can connect to the 9.3 server locally with psql from the command
>>>>>> line, with
>>>>>> psql from other boxes on the LAN via TCP, via JDBC from programs and
>>>>>> servlets
>>>>>> but cannot connect locally via CGI.
>>>>>
>>>>>> If I run any of the CGI scripts from the command line they work but when
>>>>>> invoked by Apache, they fail with the usual question as to whether
>>>>>> anything is
>>>>>> listening on socket /tmp/.s.PGSQL.5432.
>>>>>
>>>>> Some Linux variants think it improves security to run daemons like apache
>>>>> in a context where what the daemon sees as /tmp has been mapped somewhere
>>>>> else.
>>>>>
>>>>> If you're running one of these platforms, the Postgres server and libpq
>>>>> distributed by the vendor will have been hacked to cope, typically by
>>>>> agreeing that the socket location is something like /var/run/postgresql/
>>>>> rather than /tmp.  I'm guessing your 9.3 installation was self-built
>>>>> and hasn't been configured that way.
>>>>>
>>>>>             regards, tom lane
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>


--
=============================================================================
Stephen Davies Consulting P/L                             Phone: 08-8177 1595
Adelaide, South Australia.                                Mobile:040 304 0583
Records & Collections Management.