Thread: BUG #5994: Can't excute DBI->connect to oracle by client site

BUG #5994: Can't excute DBI->connect to oracle by client site

From
"Li Hongbing"
Date:
The following bug has been logged online:

Bug reference:      5994
Logged by:          Li Hongbing
Email address:      lihongbing@intasect.com.cn
PostgreSQL version: PostgreSQL 8.4.
Operating system:   x86_64_unknonwn_linux_gnu
Description:        Can't excute DBI->connect to oracle by client site
Details:

I am trying to create a 64-bit postgresql 8.4 database server which can
retrieve data from Oracle(10gR2) at an other server by DBI-LINK.

DBI-LINK works good on postgresql server site when I connect to linux by
telnet.When I connect to postgresql server by postgresql client just like
pgAdmin or EMS SQL Manager,I got a error like this:
error from Perl function "set_up_connection": DBI
connect('host=192.168.138.173;sid=smmacs;port=1521','rmtrsys',...) failed:
ORA-24327: need explicit attach before authenticating a user (DBD ERROR:
OCISessionBegin) at line 43 at line 35.

so I write test code to confirm DBI connect function

CREATE OR REPLACE FUNCTION dbi_link.test2()
  RETURNS char AS
$BODY$
use DBI;
use DBD::Oracle;


my $dbname = 'xxx';
my $user = 'rmtrsys';
my $passwd = 'rmtrsys';

my $dbh =
DBI->connect("dbi:Oracle:host=192.168.138.173;sid=smmacs;port=1521", $user,
$passwd,{ora_charset => 'UTF8'}) or
die "Database connection not made: $DBI::errstr";

$dbh -> disconnect;

return "OK";

$BODY$
  LANGUAGE 'plperlu' VOLATILE
  COST 100;


When I connect to linux by telnet and excute psql -d dbi_link_test to run,it
works good

dbi_link_test=# select dbi_link.test2();
 test2
-------
 OK
(1 row)

But when I connect by  postgresql client (pgAdmin or EMS SQL Manager) to run
same sql, it returned an error:
NOTICE:  DBI
connect('host=192.168.138.173;sid=smmacs;port=1521','rmtrsys',...) failed:
ORA-24327: need explicit attach before authenticating a user (DBD ERROR:
OCISessionBegin) at line 10


ERROR:  error from Perl function "test2": Database connection not made:
ORA-24327: need explicit attach before authenticating a user (DBD ERROR:
OCISessionBegin) at line 10.

********** エラー **********

ERROR: error from Perl function "test2": Database connection not made:
ORA-24327: need explicit attach before authenticating a user (DBD ERROR:
OCISessionBegin) at line 10.
SQLステート:XX000


when got this error,I can't shutdown the server normally,ths shutdown
command will not terminate unless I cancel it.
[postgres@localhost ~]$ pg_ctl stop -D /usr/local/pgsql/data
waiting for server to shut down...LOG:  received smart shutdown request
.LOG:  autovacuum launcher shutting down
..........................

Above test let me know the DBI-LINK crushed by DBI->connect, I think the
really reason is one of these:
1.postgresql create backend process has a bug,most likely with bad process
environment var
2.Orace OCI functions has a bug
3.DBD:Oracle has a bug

However,I should report this as a postgresql bug ,becourse of run same sql
must get same result anywhere(server or client).

Versions:
Oracle-10.2.0.1.0
DBI-1.616
DBD-Oracle-1.28
YAML-0.72
perl, v5.8.8 built for x86_64-linux-thread-multi


Regards,
Li Hongbing,China

Re: BUG #5994: Can't excute DBI->connect to oracle by client site

From
Robert Haas
Date:
On Tue, Apr 26, 2011 at 5:36 AM, Li Hongbing <lihongbing@intasect.com.cn> w=
rote:
> I am trying to create a 64-bit postgresql 8.4 database server which can
> retrieve data from Oracle(10gR2) at an other server by DBI-LINK.
>
> DBI-LINK works good on postgresql server site when I connect to linux by
> telnet.When I connect to postgresql server by postgresql client just like
> pgAdmin or EMS SQL Manager,I got a error like this:
> error from Perl function "set_up_connection": DBI
> connect('host=3D192.168.138.173;sid=3Dsmmacs;port=3D1521','rmtrsys',...) =
failed:
> ORA-24327: need explicit attach before authenticating a user (DBD ERROR:
> OCISessionBegin) at line 43 at line 35.
>
> so I write test code to confirm DBI connect function
>
> CREATE OR REPLACE FUNCTION dbi_link.test2()
>  RETURNS char AS
> $BODY$
> use DBI;
> use DBD::Oracle;
>
>
> my $dbname =3D 'xxx';
> my $user =3D 'rmtrsys';
> my $passwd =3D 'rmtrsys';
>
> my $dbh =3D
> DBI->connect("dbi:Oracle:host=3D192.168.138.173;sid=3Dsmmacs;port=3D1521"=
, $user,
> $passwd,{ora_charset =3D> 'UTF8'}) or
> die "Database connection not made: $DBI::errstr";
>
> $dbh -> disconnect;
>
> return "OK";
>
> $BODY$
>  LANGUAGE 'plperlu' VOLATILE
>  COST 100;
>
>
> When I connect to linux by telnet and excute psql -d dbi_link_test to run=
,it
> works good
>
> dbi_link_test=3D# select dbi_link.test2();
>  test2
> -------
>  OK
> (1 row)
>
> But when I connect by  postgresql client (pgAdmin or EMS SQL Manager) to =
run
> same sql, it returned an error:
> NOTICE:  DBI
> connect('host=3D192.168.138.173;sid=3Dsmmacs;port=3D1521','rmtrsys',...) =
failed:
> ORA-24327: need explicit attach before authenticating a user (DBD ERROR:
> OCISessionBegin) at line 10
>
>
> ERROR:  error from Perl function "test2": Database connection not made:
> ORA-24327: need explicit attach before authenticating a user (DBD ERROR:
> OCISessionBegin) at line 10.
>
> ********** =A5=A8=A5=E9=A9` **********
>
> ERROR: error from Perl function "test2": Database connection not made:
> ORA-24327: need explicit attach before authenticating a user (DBD ERROR:
> OCISessionBegin) at line 10.
> SQL=A5=B9=A5=C6=A9`=A5=C8:XX000
>
>
> when got this error,I can't shutdown the server normally,ths shutdown
> command will not terminate unless I cancel it.
> [postgres@localhost ~]$ pg_ctl stop -D /usr/local/pgsql/data
> waiting for server to shut down...LOG:  received smart shutdown request
> .LOG:  autovacuum launcher shutting down
> ..........................
>
> Above test let me know the DBI-LINK crushed by DBI->connect, I think the
> really reason is one of these:
> 1.postgresql create backend process has a bug,most likely with bad process
> environment var
> 2.Orace OCI functions has a bug
> 3.DBD:Oracle has a bug
>
> However,I should report this as a postgresql bug ,becourse of run same sql
> must get same result anywhere(server or client).

My guess is that, depending on how you connect, something in your
environment is different, and that is what is making the Oracle
connection succeed or fail.

--=20
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: BUG #5994: Can't excute DBI->connect to oracle by client site

From
李\xBCt兵
Date:
I run my test program with 2 ways in the same login environment.One succeed and one fail.

1) psql -d dbi_link_test
-->connect succeed
2) psql -h HOSTNAME -d dbi_link_test
-->connect fail

The different is connection options(Unix Domain Sockets/TCP Sockets).

----- Original Message -----
From: "Robert Haas" <robertmhaas@gmail.com>
To: "Li Hongbing" <lihongbing@intasect.com.cn>
Cc: <pgsql-bugs@postgresql.org>
Sent: Tuesday, May 10, 2011 10:40 AM
Subject: Re: [BUGS] BUG #5994: Can't excute DBI->connect to oracle by client site


On Tue, Apr 26, 2011 at 5:36 AM, Li Hongbing <lihongbing@intasect.com.cn> wrote:
> I am trying to create a 64-bit postgresql 8.4 database server which can
> retrieve data from Oracle(10gR2) at an other server by DBI-LINK.
>
> DBI-LINK works good on postgresql server site when I connect to linux by
> telnet.When I connect to postgresql server by postgresql client just like
> pgAdmin or EMS SQL Manager,I got a error like this:
> error from Perl function "set_up_connection": DBI
> connect('host=192.168.138.173;sid=smmacs;port=1521','rmtrsys',...) failed:
> ORA-24327: need explicit attach before authenticating a user (DBD ERROR:
> OCISessionBegin) at line 43 at line 35.
>
> so I write test code to confirm DBI connect function
>
> CREATE OR REPLACE FUNCTION dbi_link.test2()
>  RETURNS char AS
> $BODY$
> use DBI;
> use DBD::Oracle;
>
>
> my $dbname = 'xxx';
> my $user = 'rmtrsys';
> my $passwd = 'rmtrsys';
>
> my $dbh =
> DBI->connect("dbi:Oracle:host=192.168.138.173;sid=smmacs;port=1521", $user,
> $passwd,{ora_charset => 'UTF8'}) or
> die "Database connection not made: $DBI::errstr";
>
> $dbh -> disconnect;
>
> return "OK";
>
> $BODY$
>  LANGUAGE 'plperlu' VOLATILE
>  COST 100;
>
>
> When I connect to linux by telnet and excute psql -d dbi_link_test to run,it
> works good
>
> dbi_link_test=# select dbi_link.test2();
>  test2
> -------
>  OK
> (1 row)
>
> But when I connect by  postgresql client (pgAdmin or EMS SQL Manager) to run
> same sql, it returned an error:
> NOTICE:  DBI
> connect('host=192.168.138.173;sid=smmacs;port=1521','rmtrsys',...) failed:
> ORA-24327: need explicit attach before authenticating a user (DBD ERROR:
> OCISessionBegin) at line 10
>
>
> ERROR:  error from Perl function "test2": Database connection not made:
> ORA-24327: need explicit attach before authenticating a user (DBD ERROR:
> OCISessionBegin) at line 10.
>
> ********** エラ�` **********
>
> ERROR: error from Perl function "test2": Database connection not made:
> ORA-24327: need explicit attach before authenticating a user (DBD ERROR:
> OCISessionBegin) at line 10.
> SQLステ�`ト:XX000
>
>
> when got this error,I can't shutdown the server normally,ths shutdown
> command will not terminate unless I cancel it.
> [postgres@localhost ~]$ pg_ctl stop -D /usr/local/pgsql/data
> waiting for server to shut down...LOG:  received smart shutdown request
> .LOG:  autovacuum launcher shutting down
> ..........................
>
> Above test let me know the DBI-LINK crushed by DBI->connect, I think the
> really reason is one of these:
> 1.postgresql create backend process has a bug,most likely with bad process
> environment var
> 2.Orace OCI functions has a bug
> 3.DBD:Oracle has a bug
>
> However,I should report this as a postgresql bug ,becourse of run same sql
> must get same result anywhere(server or client).

My guess is that, depending on how you connect, something in your
environment is different, and that is what is making the Oracle
connection succeed or fail.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs





Re: BUG #5994: Can't excute DBI->connect to oracle by client site

From
John R Pierce
Date:
On 05/10/11 8:09 PM, =E6=9D=8E=E7=B4=85=E5=85=B5 wrote:
> I run my test program with 2 ways in the same login environment.One=20
> succeed and one fail.
>
> 1) psql -d dbi_link_test
> -->connect succeed
> 2) psql -h HOSTNAME -d dbi_link_test
> -->connect fail
>
> The different is connection options(Unix Domain Sockets/TCP Sockets).=20

is your server listen_addresses =3D '*' ? if not, its not listening on the=
=20
interface that will respond to connections to 'HOSTNAME'

and, is pg_hba.conf configured for an appropriate authentication method=20
for `host` connections ?

Re: BUG #5994: Can't excute DBI->connect to oracle by client site

From
李紅兵
Date:
Perhaps you misunderstanding what I mean.
I can connect to postgreSQL server  correctly,
but When I connect postgreSQL with TCP Sockets, it return error to connect to oracle server by DBI->connect from perlu
script.

When I connect postgreSQL with Unix Domain Sockets,it works well.

My conf file:
listen_addresses = '*'  # what IP address(es) to listen on;
host    all         all         0.0.0.0/0          password



----- Original Message -----
From: "John R Pierce" <pierce@hogranch.com>
To: <pgsql-bugs@postgresql.org>
Sent: Wednesday, May 11, 2011 11:40 AM
Subject: Re: [BUGS] BUG #5994: Can't excute DBI->connect to oracle by client site


On 05/10/11 8:09 PM, 李紅兵 wrote:
> I run my test program with 2 ways in the same login environment.One succeed and one fail.
>
> 1) psql -d dbi_link_test
> -->connect succeed
> 2) psql -h HOSTNAME -d dbi_link_test
> -->connect fail
>
> The different is connection options(Unix Domain Sockets/TCP Sockets).

is your server listen_addresses = '*' ? if not, its not listening on the
interface that will respond to connections to 'HOSTNAME'

and, is pg_hba.conf configured for an appropriate authentication method
for `host` connections ?




--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs





Re: BUG #5994: Can't excute DBI->connect to oracle by client site

From
Craig Ringer
Date:
On 11/05/11 13:53, 李紅兵 wrote:
> Perhaps you misunderstanding what I mean.
> I can connect to postgreSQL server  correctly,
> but When I connect postgreSQL with TCP Sockets, it return error to
> connect to oracle server by DBI->connect from perlu script.
>
> When I connect postgreSQL with Unix Domain Sockets,it works well.

Are you _certain_ that you're connecting to the same database on the
same machine when you use TCP vs unix sockets? The simplest explanation
for this problem would be that you're running different test perlu code
or running it on different machines/DBs when connecting via TCP than
when connecting via unix socket.

If that's not the case, about the only thing I can imagine is some weird
kernel security module getting in the way. Maybe something that tries to
isolate processes started as a result of tcp/ip connections so they
can't make new connections themselves, or something crazy like that. You
wouldn't happen to be on a box with SELinux/Apparmor/TOMOYO/SMACK/etc,
would you?

--
Craig Ringer

Re: BUG #5994: Can't excute DBI->connect to oracle by client site

From
李紅兵
Date:
yes,I am sure that I connect to the same database and same machine.
because I run my test code at the linux server with telnet.

As you said,it likes some security modules  inhibit to make a new connection.
My server is Redhat Linux Enterprice version with typical installation.and I don't know about
SELinux/Apparmor/TOMOYO/SMACK


----- Original Message -----
From: "Craig Ringer" <craig@postnewspapers.com.au>
To: "李紅兵" <lihongbing@intasect.com.cn>
Cc: "John R Pierce" <pierce@hogranch.com>; <pgsql-bugs@postgresql.org>
Sent: Wednesday, May 11, 2011 2:17 PM
Subject: Re: [BUGS] BUG #5994: Can't excute DBI->connect to oracle by client site


> On 11/05/11 13:53, 李紅兵 wrote:
>> Perhaps you misunderstanding what I mean.
>> I can connect to postgreSQL server  correctly,
>> but When I connect postgreSQL with TCP Sockets, it return error to
>> connect to oracle server by DBI->connect from perlu script.
>>
>> When I connect postgreSQL with Unix Domain Sockets,it works well.
>
> Are you _certain_ that you're connecting to the same database on the
> same machine when you use TCP vs unix sockets? The simplest explanation
> for this problem would be that you're running different test perlu code
> or running it on different machines/DBs when connecting via TCP than
> when connecting via unix socket.
>
> If that's not the case, about the only thing I can imagine is some weird
> kernel security module getting in the way. Maybe something that tries to
> isolate processes started as a result of tcp/ip connections so they
> can't make new connections themselves, or something crazy like that. You
> wouldn't happen to be on a box with SELinux/Apparmor/TOMOYO/SMACK/etc,
> would you?
>
> --
> Craig Ringer
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>
>




Re: BUG #5994: Can't excute DBI->connect to oracle by client site

From
李紅兵
Date:
addendum ,I can run my test code on a windows postgreSQL(8.4) with tcp/ip connection.

----- Original Message -----
From: "Craig Ringer" <craig@postnewspapers.com.au>
To: "李紅兵" <lihongbing@intasect.com.cn>
Cc: "John R Pierce" <pierce@hogranch.com>; <pgsql-bugs@postgresql.org>
Sent: Wednesday, May 11, 2011 2:17 PM
Subject: Re: [BUGS] BUG #5994: Can't excute DBI->connect to oracle by client site


> On 11/05/11 13:53, 李紅兵 wrote:
>> Perhaps you misunderstanding what I mean.
>> I can connect to postgreSQL server  correctly,
>> but When I connect postgreSQL with TCP Sockets, it return error to
>> connect to oracle server by DBI->connect from perlu script.
>>
>> When I connect postgreSQL with Unix Domain Sockets,it works well.
>
> Are you _certain_ that you're connecting to the same database on the
> same machine when you use TCP vs unix sockets? The simplest explanation
> for this problem would be that you're running different test perlu code
> or running it on different machines/DBs when connecting via TCP than
> when connecting via unix socket.
>
> If that's not the case, about the only thing I can imagine is some weird
> kernel security module getting in the way. Maybe something that tries to
> isolate processes started as a result of tcp/ip connections so they
> can't make new connections themselves, or something crazy like that. You
> wouldn't happen to be on a box with SELinux/Apparmor/TOMOYO/SMACK/etc,
> would you?
>
> --
> Craig Ringer
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>
>




Re: BUG #5994: Can't excute DBI->connect to oracle by client site

From
Craig Ringer
Date:
On 05/11/2011 03:13 PM, 李紅兵 wrote:
> yes,I am sure that I connect to the same database and same machine.
> because I run my test code at the linux server with telnet.
>
> As you said,it likes some security modules inhibit to make a new
> connection.
> My server is Redhat Linux Enterprice version with typical
> installation.and I don't know about
> SELinux/Apparmor/TOMOYO/SMACK

If you're running on RHEL, you're using SELinux.

For a test, you could set SELinux policy to audit mode (ie: not
enforcing) and see if you still have the problem. If the problem goes
away that way, you know you need to tweak your SELinux policies or set
an SELinux boolean.

See the SELinux documentation to learn more.

--
Craig Ringer

Re: BUG #5994: Can't excute DBI->connect to oracle by client site

From
李紅兵
Date:
I found this problem two weeks ago in a project,and then go to other team.Environment and machine has been changed
after.
so I cannot test now.Colleague told me this problem has been resolved by other pepole.

I read the config of selinux.It changed as you said.
SELINUX=enforcing
changed to
SELINUX=disabled

Have no test,but I think this is the reason why I cannot execute DBI-connect in my program.

Thank you very mach.

regards,
Li Hongbing
----- Original Message -----
From: "Craig Ringer" <craig@postnewspapers.com.au>
To: "李紅兵" <lihongbing@intasect.com.cn>
Cc: "John R Pierce" <pierce@hogranch.com>; <pgsql-bugs@postgresql.org>
Sent: Wednesday, May 11, 2011 5:18 PM
Subject: Re: [BUGS] BUG #5994: Can't excute DBI->connect to oracle by client site


> On 05/11/2011 03:13 PM, 李紅兵 wrote:
>> yes,I am sure that I connect to the same database and same machine.
>> because I run my test code at the linux server with telnet.
>>
>> As you said,it likes some security modules inhibit to make a new
>> connection.
>> My server is Redhat Linux Enterprice version with typical
>> installation.and I don't know about
>> SELinux/Apparmor/TOMOYO/SMACK
>
> If you're running on RHEL, you're using SELinux.
>
> For a test, you could set SELinux policy to audit mode (ie: not enforcing) and see if you still have the problem. If
theproblem  
> goes away that way, you know you need to tweak your SELinux policies or set an SELinux boolean.
>
> See the SELinux documentation to learn more.
>
> --
> Craig Ringer
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>
>