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

Thank you for quick answer.
I understand, but when I print from plperlu function notices with result of `env` they're the same in both cases (from remote and local client).
So it looks like that plperlu function is executing from remote and local clients with the same set of environment variable.
So I don't have a clue how can I iron out this issue.
Dnia 16 marca 2009 15:27 Kenneth Marshall <ktm(at)rice(dot)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

On Mon, Mar 16, 2009 at 11:09 AM, Tomasz Olszak <tolszak(at)o2(dot)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 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

Jonah, you're the man :). 
Thank you very much, I tried to solve it for about 2 weeks. I know that few people in the net have the same problem too.
I simply chanche that line, recompile postgresql and wait for some better solution.
I know that a lot of people uses DBI-LINK. It simply doesn't work when you envoking functions(for example make_ancessor or somethink like that) from remote client like pgAdmin :).
Regards to all
Thank you one more time Jonah.
Dnia 16 marca 2009 17:26 "Jonah H. Harris" &lt;jonah(dot)harris(at)gmail(dot)com&gt; napisał(a):
On Mon, Mar 16, 2009 at 11:09 AM, Tomasz Olszak &lt;tolszak(at)o2(dot)pl&gt; 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(dot)harris(at)gmail(dot)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

On Mon, Mar 16, 2009 at 1:06 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

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

Unfortunately, that's the way Oracle has done it since before the existence
of POSTGRES.

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.

As for alternate solutions, the only thing I can think of is a config
parameter to disable rewrite of the ps line. Frankly, I don't recall ever
seeing a script that looked for (port) in the process list, but there are
probably some home-grown ones out there. As for me, I'd prefer to separate
the host and port via a colon, just as everything else does, but that isn't
backward compatible.

I would expect this to become more of an issue when we start getting SQL/MED
more closely integrated with the server and people can more easily connect
to other databases.

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

On Mon, Mar 16, 2009 at 12:50 PM, Tomasz Olszak <tolszak(at)o2(dot)pl> wrote:

> Thank you very much, I tried to solve it for about 2 weeks. I know that few
> people in the net have the same problem too.

No problem :)

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

On Mon, Mar 16, 2009 at 12:36 PM, Alvaro Herrera <alvherre(at)commandprompt(dot)com
> wrote:

> Jonah H. Harris escribió:
>
> Wow, that's a really idiotic thing for Oracle to do.

Well, being able to find out what applications are connected to the database
is nice. But, it would also be nice if they stopped parsing the program
name if/when it encounters a left/right parenthesis.

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

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

On Mon, Mar 16, 2009 at 2:04 PM, Alvaro Herrera
<alvherre(at)commandprompt(dot)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.

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

Jonah H. Harris wrote:
> On Mon, Mar 16, 2009 at 2:04 PM, Alvaro Herrera
> <alvherre(at)commandprompt(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Mon, Mar 16, 2009 at 2:26 PM, Jonah H. Harris <jonah(dot)harris(at)gmail(dot)com>wrote:

> On Mon, Mar 16, 2009 at 2:04 PM, Alvaro Herrera <
> alvherre(at)commandprompt(dot)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

Bruce Momjian <bruce(at)momjian(dot)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(at)commandprompt(dot)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.

On Mon, Mar 16, 2009 at 2:34 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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.

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

Alvaro Herrera <alvherre(at)commandprompt(dot)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(dot)harris(at)gmail(dot)com>wrote:
>
> > On Mon, Mar 16, 2009 at 2:04 PM, Alvaro Herrera <
> > alvherre(at)commandprompt(dot)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(at)sss(dot)pgh(dot)pa(dot)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

On Mon, Mar 16, 2009 at 3:03 PM, Heikki Linnakangas <
heikki(dot)linnakangas(at)enterprisedb(dot)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?

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

Jonah H. Harris wrote:
> On Mon, Mar 16, 2009 at 3:03 PM, Heikki Linnakangas <
> heikki(dot)linnakangas(at)enterprisedb(dot)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

On Mon, Mar 16, 2009 at 3:21 PM, Heikki Linnakangas <
heikki(dot)linnakangas(at)enterprisedb(dot)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. Not that I think it couldn't be
better handled.

I'm not trying to dig at this, but looking at it in terms of flexibility,
rather than us change the way we display a port in the ps-line because it
may break a couple hundred scripts, you seem to think it's more reasonable
for a company with a product utilized by millions of users, installed in
countless governments, and deployed in mission-critical areas, to risk
changing a fairly mature and well-tested behavior because it affects fewer
than 1% of its users per year; specifically, users who are trying to
interoperate with a competing database? If it were my business, it doesn't
seem like something I would put much effort into :)

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

Wouldn't hurt :)

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

Jonah H. Harris wrote:
> On Mon, Mar 16, 2009 at 3:21 PM, Heikki Linnakangas <
> heikki(dot)linnakangas(at)enterprisedb(dot)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

On Mon, Mar 16, 2009 at 5:22 PM, Heikki Linnakangas <
heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:

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

Not any more malicious than a connection string in and of itself. It's
only used as a hierarchical name-value pair string, nothing is executed from
it.

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

Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)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 Mon, Mar 16, 2009 at 8:50 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)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(at)instance

Just doing user(at)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).

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

On Tue, Mar 17, 2009 at 1:16 AM, Jonah H. Harris <jonah(dot)harris(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> > Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)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(at)instance
>
> Just doing user(at)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> 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.

I think this is a non-solution, because it fails to guarantee that the
process title contains nothing that will confuse Oracle. As somebody
already pointed out, it's possible to have parens in a user or database
name ... and for that matter, how sure are we that parens are the only
trouble case? You'll be potentially breaking scripts that work now,
in order to reduce but not eliminate the problem with Oracle.

IMHO the right fix is the one that was touched on upthread: have
DBI-Link temporarily install a plain-vanilla, punctuation-free process
title (eg, just "postgres") while starting an Oracle connection.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > 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.
>
> I think this is a non-solution, because it fails to guarantee that the
> process title contains nothing that will confuse Oracle. As somebody
> already pointed out, it's possible to have parens in a user or database
> name ... and for that matter, how sure are we that parens are the only
> trouble case? You'll be potentially breaking scripts that work now,
> in order to reduce but not eliminate the problem with Oracle.
>
> IMHO the right fix is the one that was touched on upthread: have
> DBI-Link temporarily install a plain-vanilla, punctuation-free process
> title (eg, just "postgres") while starting an Oracle connection.

I was suggesting the colon rather to be clearer, not as an Oracle
fix.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> I was suggesting the colon rather to be clearer, not as an Oracle
> fix.

Well, we could certainly debate the change on its own merits, but
I'm not seeing that it's enough nicer to justify a risk of breaking
ps-watching scripts.

regards, tom lane

I wrote:
> Well, we could certainly debate the change on its own merits, but
> I'm not seeing that it's enough nicer to justify a risk of breaking
> ps-watching scripts.

Also, on second thought: what about IPv6 addresses? Colon doesn't
look like a very good idea at all if you suppose that what's in
front of it is IPv6.

regards, tom lane

On Tuesday 17 March 2009 05:29:48 Tom Lane wrote:
> I wrote:
> > Well, we could certainly debate the change on its own merits, but
> > I'm not seeing that it's enough nicer to justify a risk of breaking
> > ps-watching scripts.
>
> Also, on second thought: what about IPv6 addresses? Colon doesn't
> look like a very good idea at all if you suppose that what's in
> front of it is IPv6.

In that case standard URL formats put [] around the IP address. People will
forever hate the IPv6 designers for that, but it's standard and widespread
now.

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(dot)linnakangas(at)enterprisedb(dot)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.com
PostgreSQL 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(at)sss(dot)pgh(dot)pa(dot)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.com
PostgreSQL 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(dot)linnakangas(at)enterprisedb(dot)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(at)svana(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

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

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