Thread: Postgresql, Perl and DBI connect problem

Postgresql, Perl and DBI connect problem

From
Bernard Miville
Date:
Hi,

I am just trying to connect to a Postgresql DB using a Perl cgi script
from a web page from my own computer on a remote network.

I am using Postgresql 8.1.4 on Fedora Core 5 with a Linux firewall that
allows all traffic.

Postmaster is running with -i and I tried different -h (including *),
right now it is set to 127.0.0.1

In postgresql.conf, I have

listen_addresses='*'
port = 5432.

In pg_hba.conf I have:

local  dbname
user                                                            md5
host   dbname user          xxx.xx.xx.x    255.255.255.255  md5
host   dbname user         127.0.0.1/32  255.255.255.255  md5
host   dbname user         ::1/128            255.255.255.255  md5

In the real file, dbname, user and xxx.xx.xx.x are replaced by the
actual value (just in case!!). I also tried all all and trust. For user
there is a password set, and it works locally when I use psql to connect.

I tried different combination (e.g. only 127.0.0.1), like removing the
mask (255.255.255.255) etc. but nothing seems to work.

In the perl cgi script I have:

use DBI;
my $conn =
DBI->connect("DBI:Pg:dbname=dbname;host=127.0.0.1;port=5432;", "user",
"password",{ RaiseError => 1, AutoCommit => 0})|| die "Database
connection not made: $DBI::errstr";;

The error I get is:

DBI connect('dbname=dbname;host=127.0.0.1;port=5432;','user',...) failed: could not create socket: Permission denied

If I remove the host and port from the DBI->connect (which should not be done!) I get this error:

DBI connect('dbname=dbname;','user',...) failed: could not connect to server: Permission denied
    Is the server running locally and accepting
    connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

From another Linux box I am able to remotely connect to the database using:

psql -hxxx.xx.xx.x dbname user

That works fine. It also works locally using psql from the command line, but from the web cgi Perl script it does not
connect?

Needless to say, I searched the web and a lot of forums the past 2 days and found a lot of solutions that do not work
formy case or  
are for Postgresql 7.4 (e.g. tcpip_socket = true does not exist in 8.1, need to use listen_addresses instead).

Any help would be really appreciated.

Thank you,

Bernard

--

_____________________________________________________________________
Bernard Miville
IODP Data Manager

IODP-MI Sapporo Office
CRIS Bldg., Room 05-103, Hokkaido University, N21, W10
Sapporo, Japan 001-0021
Voice:  +81 (0)11 738-1072
Fax:    +81 (0)11 738-3520
E-Mail: bmiville@iodp-mi-sapporo.org
WWW: http://www.iodp.org
SIP: +1747-6068953 (6068953@proxy01.sipphone.com)
ENUM: +878107472468937
Skype: mivilleb
_____________________________________________________________________
INTEGRATED OCEAN DRILLING PROGRAM - MANAGEMENT INTERNATIONAL
Washington D.C. & Sapporo
_____________________________________________________________________


Re: Postgresql, Perl and DBI connect problem

From
Tom Lane
Date:
Bernard Miville <bmiville@iodp-mi-sapporo.org> writes:
> I am just trying to connect to a Postgresql DB using a Perl cgi script
> from a web page from my own computer on a remote network.
> I am using Postgresql 8.1.4 on Fedora Core 5 with a Linux firewall that
> allows all traffic.

Given that it's a "permission denied" error, I'm wondering about SELinux.
If you do "/usr/sbin/setenforce 0" as root, does it start working?

I don't suggest turning off SELinux as a permanent solution, but we
need to narrow down where the problem is.

            regards, tom lane

Re: Postgresql, Perl and DBI connect problem

From
Bernard Miville
Date:
Hi Tom,

It now works!

I hate SELinux, it always cause me problems.

Any suggestion on how to make SELinux happy without having to turn it off.

Thanks,

Bernard

Tom Lane wrote:
> Bernard Miville <bmiville@iodp-mi-sapporo.org> writes:
>
>> I am just trying to connect to a Postgresql DB using a Perl cgi script
>> from a web page from my own computer on a remote network.
>> I am using Postgresql 8.1.4 on Fedora Core 5 with a Linux firewall that
>> allows all traffic.
>>
>
> Given that it's a "permission denied" error, I'm wondering about SELinux.
> If you do "/usr/sbin/setenforce 0" as root, does it start working?
>
> I don't suggest turning off SELinux as a permanent solution, but we
> need to narrow down where the problem is.
>
>             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
>
>


--

_____________________________________________________________________
Bernard Miville
IODP Data Manager

IODP-MI Sapporo Office
CRIS Bldg., Room 05-103, Hokkaido University, N21, W10
Sapporo, Japan 001-0021
Voice:  +81 (0)11 738-1072
Fax:    +81 (0)11 738-3520
E-Mail: bmiville@iodp-mi-sapporo.org
WWW: http://www.iodp.org
SIP: +1747-6068953 (6068953@proxy01.sipphone.com)
ENUM: +878107472468937
Skype: mivilleb
_____________________________________________________________________
INTEGRATED OCEAN DRILLING PROGRAM - MANAGEMENT INTERNATIONAL
Washington D.C. & Sapporo
_____________________________________________________________________


Re: Postgresql, Perl and DBI connect problem

From
Tom Lane
Date:
Bernard Miville <bmiville@iodp-mi-sapporo.org> writes:
> It now works!
> I hate SELinux, it always cause me problems.

It's still got a lot of rough edges, for sure, but I think the idea is
sound.  (And I'm not just saying that because I work for Red Hat ...
just look at all the fun Windows users are having because Microsoft
hasn't even thought of comparable security measures.)  Please try to
fix it, or at least get the attention of people who can fix it.

The first thing is to make sure you are up2date on the selinux policy
module (selinux-policy and selinux-policy-targeted RPMs, if you took
the Fedora installation defaults).  If that doesn't help, please file
a bug report against selinux-policy-targeted at bugzilla.redhat.com.
My guess is that this has nothing to do with Postgres per se, but is
an overly enthusiastic restriction on what the apache daemon can do.

            regards, tom lane