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

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

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 ?

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

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

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

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 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

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