Thread: Re: Problem with accesing Oracle from plperlu functionwhen using remote pg client.

Dnia 16 marca 2009 15:27 Kenneth Marshall <ktm@rice.edu> napisał(a):

On Mon, Mar 16, 2009 at 03:16:07PM +0100, Tomasz Olszak wrote:
> Greetings to All!
>
> I've tried to find solution of my problem on other pg mailing lists but without bigger effect.
>
> I have a table A in PG. There is also table A in Oracle.
> I want to import specific row from oracle to pg, so i create plperlu function
>
> CREATE OR REPLACE FUNCTION import.ora_a_row(a_id numeric)
> RETURNS dok_za AS
> $BODY$
>
> In IPL:
> create_connection;
> select all columns on oracle from table a where id = a_id;
> returning tuple;
>
> $BODY$
> LANGUAGE 'plperlu' VOLATILE;
>
> then i can use such function in pl/pgsql;
> ....
> DECLARE:
> var A%ROWTYPE;
> BEGIN;
> ...
> select * into var from import.ora_a_row(100);
> END;...
>
> Like you see it's very, very convenient.
>
> And it works, but only when I make "select * from import.ora_a_row(100);" from psql?? on postgresql server(local client).
> When I try to make that select in pgadmin or from remote machine I have tns error:
>
> TNS:could not resolve the connect identifier specified (DBD ERROR: OCIServerAttach) at line 20
>
> I've tried with different postgresql versions and different perls, and different DBI Oracle packages, so i think pg or perl versions are not causes(Of course environment variables are propably set etc.). Oracle Base directory is about 1.6 gigabyte so I think it's full client(not instant).
>
> When I used PGADMIN 1.6 on postgresql server and left host editline blank(specifying only a pgport) it worked too.
> But when I've written "localhost" as host it didn't work (the same with connecting "psql -h localhost -U user database" ).
>
> Anybody ancounter this kind of problem or maybe it's a bug in plperlu?
>
> I'll be grateful for any of Your help.
>
> Regards
>
> Tomasz
>

This looks like an ENVIRONMENT variable problem. The server does not
run with the same set of settings as your psql program. I think that
it will work once you get those issues ironed out.

Good luck,
Ken

Jonah H. Harris escribió:

> On connection, the Oracle client sends the current application name to the
> Oracle server (which is listed in the V$SESSION view); in the case of
> Postgres, the program name is the current backend process name text.
> Because Oracle picks up Postgres' backend text, "postgres: www postgres
> 192.168.1.1(13243)", the (13243) screws up Oracle's TNS parser which
> prevents it from resolving the connection.  This doesn't happen when you're
> connected to PG locally, because the backend text is, "postgres: www
> postgres [local]".

Wow, that's a really idiotic thing for Oracle to do.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Jonah H. Harris wrote:


> Finally, my low-level Oracle knowledge does benefit Postgres :)

:-)

> 
> It's a TNS parsing error due to a combination of Oracle's use of a
> Lispish s-expression-like name-value pair format and Postgres' process
> listing format for remote connections. 
> 
> On connection, the Oracle client sends the current application name to
> the Oracle server (which is listed in the V$SESSION view); in the case
> of Postgres, the program name is the current backend process name text. 
> Because Oracle picks up Postgres' backend text, "postgres: www postgres
> 192.168.1.1(13243)", the (13243) screws up Oracle's TNS parser which
> prevents it from resolving the connection.  This doesn't happen when
> you're connected to PG locally, because the backend text is, "postgres:
> www postgres [local]".
> 
> The solution to this is to change the following line in
> src/backend/postmaster/postmaster.c:
> 
> remote_port[0] == '\0' ? "%s" : "%s(%s)"
> 
> TO
> 
> remote_port[0] == '\0' ? "%s" : "%s[%s]"
> OR
> remote_port[0] == '\0' ? "%s" : "%s:%s"
> 
> Which I would prefer as a nice change to make overall.

Any way to override it in the Oracle client? With an enviroment variable
or something?

Because making that change would probably break a bunch of pg admin
scripts on random systems around the world.. (yes, they should be using
pg_stat_activity, but I'll bet you there are a lot of stuff out there
based on the ps output)

(if we do change it, the colon notification seems to be the reasonable
one given that's how we usually write ip/port pairs)

//Magnus

On Mon, Mar 16, 2009 at 11:09 AM, Tomasz Olszak <tolszak@o2.pl> wrote:

So it looks like that plperlu function is executing from remote and local clients with the same set of environment variable.

It has nothing to do with the environment variables.

So I don't have a clue how can I iron out this issue.

Finally, my low-level Oracle knowledge does benefit Postgres :)

It's a TNS parsing error due to a combination of Oracle's use of a Lispish s-expression-like name-value pair format and Postgres' process listing format for remote connections. 

On connection, the Oracle client sends the current application name to the Oracle server (which is listed in the V$SESSION view); in the case of Postgres, the program name is the current backend process name text.  Because Oracle picks up Postgres' backend text, "postgres: www postgres 192.168.1.1(13243)", the (13243) screws up Oracle's TNS parser which prevents it from resolving the connection.  This doesn't happen when you're connected to PG locally, because the backend text is, "postgres: www postgres [local]".

The solution to this is to change the following line in src/backend/postmaster/postmaster.c:

remote_port[0] == '\0' ? "%s" : "%s(%s)"

TO

remote_port[0] == '\0' ? "%s" : "%s[%s]"
OR
remote_port[0] == '\0' ? "%s" : "%s:%s"

Which I would prefer as a nice change to make overall.

--
Jonah H. Harris, Senior DBA
myYearbook.com

"Jonah H. Harris" <jonah.harris@gmail.com> writes:
> The solution to this is to change the following line in
> src/backend/postmaster/postmaster.c:

We're not going to break a bunch of other applications in order to make
some undocumented, unsupported Oracle thingie work (until they change
it...).  Got another solution?
        regards, tom lane

<div class="gmail_quote">On Mon, Mar 16, 2009 at 12:36 PM, Alvaro Herrera <span dir="ltr"><<a
href="mailto:alvherre@commandprompt.com">alvherre@commandprompt.com</a>></span>wrote:<br /><blockquote
class="gmail_quote"style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
JonahH. Harris escribió:<br /><div class="im"></div><br /></blockquote><blockquote class="gmail_quote"
style="border-left:1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div
class="im"></div>Wow,that's a really idiotic thing for Oracle to do.</blockquote></div><br />Well, being able to find
outwhat applications are connected to the database is nice.  But, it would also be nice if they stopped parsing the
programname if/when it encounters a left/right parenthesis.<br /><br />-- <br /> Jonah H. Harris, Senior DBA<br
/>myYearbook.com<br/><br /> 

All,

Probably somebody should resurrect the Oralink project instead.
http://pgfoundry.org/projects/oralink/


--Josh

Jonah H. Harris escribió:

> I first encountered it while working on database links from PG to Oracle at
> EnterpriseDB, and the reason Tomasz couldn't find the answer to this online
> is because it's such a rare problem that Oracle has no reason to change it.
> Really, how many people have parenthesis in their program names?  Similarly,
> the problem has always existed when connecting to Oracle from Postgres using
> DBI-Link or the oralink contrib module, there's just so few PG people
> connecting to Oracle that it hasn't really come up before.

I have seen a bunch of reports of people not being able to connect to
Oracle via DBI-Link on the spanish list, so this definitely has bitten
some people.  I'm not sure that it's all that rare.

> As for alternate solutions, the only thing I can think of is a config
> parameter to disable rewrite of the ps line.

We already have one; it's called update_process_title.

Maybe DBI-Link could set the title to something else before attempting
the connection. (And perhaps locally set update_process_title to off.)

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Jonah H. Harris wrote:
> On Mon, Mar 16, 2009 at 2:04 PM, Alvaro Herrera
> <alvherre@commandprompt.com>wrote:
> 
> > We already have one; it's called update_process_title.
> 
> 
> I have it turned off, and I still see the remote IP/port in the process
> list.

I am thinking the title doesn't update _after_ you turn it off, but it
was updated when the session started.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <bruce@momjian.us> writes:
> Jonah H. Harris wrote:
>> I have it turned off, and I still see the remote IP/port in the process
>> list.

> I am thinking the title doesn't update _after_ you turn it off, but it
> was updated when the session started.

Yeah, we intentionally set the title during backend startup, else you
could not tell backends apart from the postmaster let alone each other.
The GUC is only meant to eliminate the performance-hogging behavior of
changing the title for every command.

I'm finding it hard to believe that there is no way to override what
Oracle's client library does --- there are *plenty* of situations where
you don't really want a client command line exposed to the whole world.
        regards, tom lane

Jonah H. Harris escribió:
> On Mon, Mar 16, 2009 at 2:04 PM, Alvaro Herrera
> <alvherre@commandprompt.com>wrote:
> 
> > We already have one; it's called update_process_title.
> 
> I have it turned off, and I still see the remote IP/port in the process
> list.

Yeah, apparently init_ps_display changes the title inconditionally.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Maybe DBI-Link could set the title to something else before attempting
> the connection. (And perhaps locally set update_process_title to off.)

Making the (unwarranted?) assumption that Oracle's library only captures
the title during connect, it seems like DBI-Link could be made to just
transiently override the title while connecting.  There should be no
need to break user-visible behavior for this.

The whole thing seems mighty bizarre though.  Given the number of
different ways we have to set the process title, I rather wonder whether
what we do will always determine what Oracle sees, on every platform.
        regards, tom lane

On Mon, Mar 16, 2009 at 02:30:28PM -0400, Jonah H. Harris wrote:
> On Mon, Mar 16, 2009 at 2:26 PM, Jonah H. Harris <jonah.harris@gmail.com>wrote:
> 
> > On Mon, Mar 16, 2009 at 2:04 PM, Alvaro Herrera <
> > alvherre@commandprompt.com> wrote:
> >
> >>  We already have one; it's called update_process_title.
> >
> >
> > I have it turned off, and I still see the remote IP/port in the process
> > list.
> >
> 
> Ahh, this is why:
> 
> init_ps_display():set_ps_display(initial_str, true);
> 
> Perhaps it should obey the configuration setting as well?
> 
> -- 
> Jonah H. Harris, Senior DBA
> myYearbook.com

What about have the GUC support off, on, and a format string to use
to fix this problem.

Ken

Jonah H. Harris wrote:
> On Mon, Mar 16, 2009 at 2:34 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 
>> I'm finding it hard to believe that there is no way to override what
>> Oracle's client library does --- there are *plenty* of situations where
>> you don't really want a client command line exposed to the whole world.
> 
> AFAIK, there is no way to override that.  It's very low-level in their
> client stack, is operating-system specific, and has been there forever.

Someone should raise a support request / whatever they call them with 
Oracle to get this fixed on their side..

--   Heikki Linnakangas  EnterpriseDB   http://www.enterprisedb.com

Jonah H. Harris wrote:
> On Mon, Mar 16, 2009 at 3:03 PM, Heikki Linnakangas <
> heikki.linnakangas@enterprisedb.com> wrote:
> 
>> Someone should raise a support request / whatever they call them with
>> Oracle to get this fixed on their side..
> 
> Heh.  Why would they fix it when it's only a problem for < 1% of their users
> in odd corner cases?

Because that's what a respectable business does when a customer runs 
into a bug with software they sell.

Whether or not they actually will fix it, I don't know, but they surely 
won't if no-one complains them about it.

--   Heikki Linnakangas  EnterpriseDB   http://www.enterprisedb.com

Jonah H. Harris wrote:
> On Mon, Mar 16, 2009 at 3:21 PM, Heikki Linnakangas <
> heikki.linnakangas@enterprisedb.com> wrote:
> 
>> Because that's what a respectable business does when a customer runs into a
>> bug with software they sell.
> 
> It's not a bug, it's expected behavior.

You really call it expected that a process with a parenthesis in the 
process title can't use OCI, but gets an obscure error message instead? 
Sure, it's a corner case, and in most cases it can be worked around, but 
it's still a bug.

Hmm, I wonder if you could do something malicious with it. Like, run a 
query along the lines of "SELECT $$ (HOST=10.0.0.123) $$, connect()... " 
to divert the connection to another server.

--   Heikki Linnakangas  EnterpriseDB   http://www.enterprisedb.com

Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
> Hmm, I wonder if you could do something malicious with it.

There are any number of scenarios where exposing the client command-line
contents to other database users represents a security hole, quite
independently of whether anything falls over depending on the line
contents.  (I wonder whether there are any Oracle clients that accept
a password on the command line, for instance.)

The only reason this complaint is directed to us, and not Oracle,
is that the complainant knows how far he's likely to get complaining
to Oracle :-(
        regards, tom lane

On Tue, Mar 17, 2009 at 1:16 AM, Jonah H. Harris <jonah.harris@gmail.com> wrote:
> If we want to fix it long-term, we'd have to look at one of the previously
> discussed alternatives to using (port).

That's still just a work-around, not a long-term fix. What happens if
the user has parentheses or something else which confuses Oracle in
their database name or username?

-- 
greg

That said, I don't see the problem switching to hostname:port. That
seems like a more standard format anyways. It's not a complete
solution to this problem but it does seem like it's less likely to
confuse people and programs, Oracle included. We're getting awfully
conservative about pretty minor changes these days.

-- 
greg

Jonah H. Harris wrote:
> On Mon, Mar 16, 2009 at 8:50 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 
> > Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
> > > Hmm, I wonder if you could do something malicious with it.
> >
> > There are any number of scenarios where exposing the client command-line
> > contents to other database users represents a security hole, quite
> > independently of whether anything falls over depending on the line
> > contents.  (I wonder whether there are any Oracle clients that accept
> > a password on the command line, for instance.)
> 
> 
> Sure they let you pass the password on the command line, but they don't
> recommend it.  Most of the utilities accept the syntax:
> 
> utility user/pass@instance
> 
> Just doing user@instance will generally prompt for a password.
> 
> Ahh, the number of passwords I've recovered from shell history files as a
> consultant... good times :)
> 
> The only reason this complaint is directed to us, and not Oracle,
> > is that the complainant knows how far he's likely to get complaining
> > to Oracle :-(
> 
> 
> I don't doubt that.  But, like I said, it's really a matter of the
> application name.  In our case, Postgres falls into that corner case and we
> either choose to do something about it or we don't.  I put the temporary
> solution out there for anyone that has the problem.  If we want to fix it
> long-term, we'd have to look at one of the previously discussed alternatives
> to using (port).  I don't particularly care one way or another, but if we
> were to change the ps line format, I just wanted to say that I preferred
> host:port rather than host(port).

I think I was the one who originally added the port in paretheses, and I
agree that a colon would have made more sense, but I never thought of
it.
postgres test 127.0.0.1(57966) idle

vs.
postgres test 127.0.0.1:57966 idle

In fact my old BSD ps looks like:
postgres test 127.0.0.1(58013) idle (postmaster)

The old argv[0] is in parentheses.

I think any serious tools are now using pg_stat_activity.  I saw we make
the change in 8.4 and just document it.  I wouldn't make the change for
Oracle but rather for clarity.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +

On Mon, 2009-03-16 at 21:21 +0200, Heikki Linnakangas wrote:
> Jonah H. Harris wrote:
> > On Mon, Mar 16, 2009 at 3:03 PM, Heikki Linnakangas <
> > heikki.linnakangas@enterprisedb.com> wrote:
> > 
> >> Someone should raise a support request / whatever they call them with
> >> Oracle to get this fixed on their side..
> > 
> > Heh.  Why would they fix it when it's only a problem for < 1% of their users
> > in odd corner cases?
> 
> Because that's what a respectable business does when a customer runs 
> into a bug with software they sell.

You just made me snort out my coffee damn you.

All I would say is that your noble expectation seems unlikely to be met
in practice.

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Training, Services and Support

On Mon, 2009-03-16 at 14:43 -0400, Jonah H. Harris wrote:
> On Mon, Mar 16, 2009 at 2:34 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>         I'm finding it hard to believe that there is no way to
>         override what
>         Oracle's client library does --- there are *plenty* of
>         situations where
>         you don't really want a client command line exposed to the
>         whole world.
> 
> AFAIK, there is no way to override that.  It's very low-level in their
> client stack, is operating-system specific, and has been there
> forever.

But we can easily change what we do, for this specific situation only.

Write a C function to update the process title to something that does
work correctly, then use with update_process_title = off. That way you
can do a specific workaround for this situation without touching the
postmaster code.

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Training, Services and Support

Tom Lane wrote:
> > The solution to this is to change the following line in
> > src/backend/postmaster/postmaster.c:
>
> We're not going to break a bunch of other applications in order to make
> some undocumented, unsupported Oracle thingie work (until they change
> it...).  Got another solution?

Yes :^)
Upgrade to a recent Oracle Patch Set.

The problem is not a corner case, as it also affected user and machine
names (though I have no idea who would have parentheses or equality signs
in these) as well as programs run from directories with "bad characters"
in the name.

The problem is tracked as bug 3807408 by oracle and has been fixed in
9.2.0.8, 10.2.0.3 and 11.1.0.6.
If you don't want to upgrade, you can also apply the
One-Off Patch 3807408 which has been issued for 10.2.0.1 and 9.2.0.7
for most UNIX platforms.

I tested it with a C program named "parens (5432)" on Linux
with Oracle 10.2.0.4, and it works fine.

Yours,
Laurenz Albe

On Mon, Mar 16, 2009 at 08:50:36PM -0400, Tom Lane wrote:
> Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
> > Hmm, I wonder if you could do something malicious with it.
>
> There are any number of scenarios where exposing the client command-line
> contents to other database users represents a security hole, quite
> independently of whether anything falls over depending on the line
> contents.  (I wonder whether there are any Oracle clients that accept
> a password on the command line, for instance.)

Note that you're talking about the whole command line, whereas oracle
apparently talks about the "program name" (argv[0]). Normally the
commandline in memory has NUL characters between the arguments, with
the part to the first NUL being the program name, like so:

# cat /proc/3793/cmdline |hexdump -C
00000000  2f 73 62 69 6e 2f 64 68  63 6c 69 65 6e 74 00 2d  |/sbin/dhclient.-|
00000010  31 00 2d 6c 66 00 2f 76  61 72 2f 6c 69 62 2f 64  |1.-lf./var/lib/d|            ^^          ^^

Whereas postgresql, in munging it's command line uses *spaces* between
each bit, meaning that anyone looking for the "program name" (argv[0])
is going to get the whole line. Example:

# cat /proc/4472/cmdline |hexdump -C
00000000  70 6f 73 74 67 72 65 73  3a 20 77 72 69 74 65 72  |postgres: writer|
00000010  20 70 72 6f 63 65 73 73  20 20 20 00 00 00 00 00  | process   .....|         ^^                       ^^ ^^

Maybe someone could check if replacing the first space with a NUL
works. It shouldn't effect the ps output.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

On Tue, Mar 17, 2009 at 11:27:54AM +0100, Albe Laurenz wrote:
> Tom Lane wrote:
> > > The solution to this is to change the following line in
> > > src/backend/postmaster/postmaster.c:
> > 
> > We're not going to break a bunch of other applications in order to make
> > some undocumented, unsupported Oracle thingie work (until they change
> > it...).  Got another solution?
> 
> Yes :^)
> Upgrade to a recent Oracle Patch Set.

OK, I'm not taking on responsibility for a *fixed* Oracle bug, so
that's what I've put in the README.Oracle :)

Cheers,
David.
> 
> The problem is not a corner case, as it also affected user and machine
> names (though I have no idea who would have parentheses or equality signs
> in these) as well as programs run from directories with "bad characters"
> in the name.
> 
> The problem is tracked as bug 3807408 by oracle and has been fixed in
> 9.2.0.8, 10.2.0.3 and 11.1.0.6.
> If you don't want to upgrade, you can also apply the
> One-Off Patch 3807408 which has been issued for 10.2.0.1 and 9.2.0.7
> for most UNIX platforms.
> 
> I tested it with a C program named "parens (5432)" on Linux
> with Oracle 10.2.0.4, and it works fine.
> 
> Yours,
> Laurenz Albe
> 
> -- 
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate