Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
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 da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
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.linnakan...@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.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
On Mon, 2009-03-16 at 14:43 -0400, Jonah H. Harris wrote: On Mon, Mar 16, 2009 at 2:34 PM, Tom Lane t...@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.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
On Mon, Mar 16, 2009 at 08:50:36PM -0400, Tom Lane wrote: Heikki Linnakangas heikki.linnakan...@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 2f 73 62 69 6e 2f 64 68 63 6c 69 65 6e 74 00 2d |/sbin/dhclient.-| 0010 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 70 6f 73 74 67 72 65 73 3a 20 77 72 69 74 65 72 |postgres: writer| 0010 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 klep...@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. signature.asc Description: Digital signature
Re: [HACKERS] 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 lt;k...@rice.edugt; napisał(a): On Mon, Mar 16, 2009 at 03:16:07PM +0100, Tomasz Olszak wrote: gt; Greetings to All! gt; gt; I've tried to find solution of my problem on other pg mailing lists but without bigger effect. gt; gt; I have a table A in PG. There is also table A in Oracle. gt; I want to import specific row from oracle to pg, so i create plperlu function gt; gt; CREATE OR REPLACE FUNCTION import.ora_a_row(a_id numeric) gt; RETURNS dok_za AS gt; $BODY$ gt; gt; In IPL: gt; create_connection; gt; select all columns on oracle from table a where id = a_id; gt; returning tuple; gt; gt; $BODY$ gt; LANGUAGE 'plperlu' VOLATILE; gt; gt; then i can use such function in pl/pgsql; gt; gt; DECLARE: gt; var A%ROWTYPE; gt; BEGIN; gt; ... gt; select * into var from import.ora_a_row(100); gt; END;... gt; gt; Like you see it's very, very convenient. gt; gt; And it works, but only when I make select * from import.ora_a_row(100); from psql?? on postgresql server(local client). gt; When I try to make that select in pgadmin or from remote machine I have tns error: gt; gt; TNS:could not resolve the connect identifier specified (DBD ERROR: OCIServerAttach) at line 20 gt; gt; 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). gt; gt; When I used PGADMIN 1.6 on postgresql server and left host editline blank(specifying only a pgport) it worked too. gt; But when I've written localhost as host it didn't work (the same with connecting psql -h localhost -U user database ). gt; gt; Anybody ancounter this kind of problem or maybe it's a bug in plperlu? gt; gt; I'll be grateful for any of Your help. gt; gt; Regards gt; gt; Tomasz gt; 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
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
On Mon, Mar 16, 2009 at 11:09 AM, Tomasz Olszak tols...@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
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
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 Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
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.har...@gmail.comgt; napisał(a): On Mon, Mar 16, 2009 at 11:09 AM, Tomasz Olszak lt;tols...@o2.plgt; 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
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
Jonah H. Harris jonah.har...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
On Mon, Mar 16, 2009 at 1:06 PM, Tom Lane t...@sss.pgh.pa.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
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
On Mon, Mar 16, 2009 at 12:50 PM, Tomasz Olszak tols...@o2.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
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
On Mon, Mar 16, 2009 at 12:36 PM, Alvaro Herrera alvhe...@commandprompt.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
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
All, Probably somebody should resurrect the Oralink project instead. http://pgfoundry.org/projects/oralink/ --Josh -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
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 Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
On Mon, Mar 16, 2009 at 2:04 PM, Alvaro Herrera alvhe...@commandprompt.comwrote: 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
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
Jonah H. Harris wrote: On Mon, Mar 16, 2009 at 2:04 PM, Alvaro Herrera alvhe...@commandprompt.comwrote: 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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
Bruce Momjian br...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
Jonah H. Harris escribió: On Mon, Mar 16, 2009 at 2:04 PM, Alvaro Herrera alvhe...@commandprompt.comwrote: 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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
On Mon, Mar 16, 2009 at 2:26 PM, Jonah H. Harris jonah.har...@gmail.comwrote: On Mon, Mar 16, 2009 at 2:04 PM, Alvaro Herrera alvhe...@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
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
On Mon, Mar 16, 2009 at 2:34 PM, Tom Lane t...@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. -- Jonah H. Harris, Senior DBA myYearbook.com
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
Alvaro Herrera alvhe...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
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.har...@gmail.comwrote: On Mon, Mar 16, 2009 at 2:04 PM, Alvaro Herrera alvhe...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
Jonah H. Harris wrote: On Mon, Mar 16, 2009 at 2:34 PM, Tom Lane t...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
On Mon, Mar 16, 2009 at 3:03 PM, Heikki Linnakangas heikki.linnakan...@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? -- Jonah H. Harris, Senior DBA myYearbook.com
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
Jonah H. Harris wrote: On Mon, Mar 16, 2009 at 3:03 PM, Heikki Linnakangas heikki.linnakan...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
On Mon, Mar 16, 2009 at 3:21 PM, Heikki Linnakangas heikki.linnakan...@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. 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
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
Jonah H. Harris wrote: On Mon, Mar 16, 2009 at 3:21 PM, Heikki Linnakangas heikki.linnakan...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
On Mon, Mar 16, 2009 at 5:22 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.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
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
Heikki Linnakangas heikki.linnakan...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
On Mon, Mar 16, 2009 at 8:50 PM, Tom Lane t...@sss.pgh.pa.us wrote: Heikki Linnakangas heikki.linnakan...@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/p...@instance Just doing u...@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
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
Bruce Momjian br...@momjian.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
Tom Lane wrote: Bruce Momjian br...@momjian.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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
Jonah H. Harris wrote: On Mon, Mar 16, 2009 at 8:50 PM, Tom Lane t...@sss.pgh.pa.us wrote: Heikki Linnakangas heikki.linnakan...@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/p...@instance Just doing u...@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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
Bruce Momjian br...@momjian.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
