How does the PG back end treat local connections differently than it
does TCP/IP connections? I'm using dbi-link to connect to a remote
Oracle database. Everything works fine if I connect to the PG database
using 'psql -Umyuser mydb' but if I connect from another client (psql
-Umyuser -h mydbserver mydb) or even from the db server itself (psql
-Umyuser -h localhost mydb) I get the error:

CONTEXT: SQL statement "SELECT dbi_link.cache_connection( 1 )"
ERROR: error from Perl function "remote_select": error from Perl
function "cache_connection": DBI
connect('database=nxxc;host=mydbserver;sid=nxxc',' rpt5124',...) failed:
ORA-06413: Connection not open. (DBD ERROR: OCIServerAttach) at line 137
at line 13.

Google has given me a ton of hits on similar issues but upon further
investigation they are all with people having difficulties getting
dbi-link configured properly to talk to an Oracle database. Mine works
fine, so those hits haven't shone any light on my problem.

I've narrowed the problem down to connecting to PG via TCP/IP versus
connecting via the local port, so it must be the difference between the
two environments that is causing my issues. Apparently the trouble is
not with the Oracle environment, since the error I get is about
connections, not things I've found from Google which are more about
environmentals not being properly set.

Can anyone tell me the difference from the PG back end? Is there a
TCP/IP connection limit that may be causing me trouble? Something else
to check?

TIA,

Barry

Barry Kline <blkline (AT) attglobal (DOT) net> wrote:
Maybe you have to edit yout pg_hba.conf to enable such a connection. Can
you login with plain psql -h <server> ... ?


Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

Am 06.05.2010 15:17, schrieb Barry Kline:
Have a look at your pg_hba.conf, local access is ruled by the local keyword, network
access by host and hostssl keywords.

One difference between the two access methods is that the ident authentification
is only useful on a local connection.

Max

M. Strobel wrote:


Thanks Max & Andreas for your comments. What I guess I'm looking for
is what PG is doing on the back end between the two. I can access the
server without trouble. The problem comes when PG attempts to link back
to the Oracle server. In the case where I connect via the unix domain
socket things work properly. The code fails however when I connect to
the IP socket. So that indicates to me that the two different
environments are slightly different and I was hoping that someone might
shine some light on that.

Regards,

Barry

On 2010-05-07, M. Strobel <sorry_no_mail_here (AT) nowhere (DOT) dee> wrote:
Postgres can use RFC1413 for TCP based ident authentication but this is only
useful between trusted hosts that are running ident servers.



--- news://freenews.netfront.net/ - complaints: news (AT) netfront (DOT) net ---

Jasen Betts wrote:

Hi Jasen.

Are you saying that pg_hba.conf has something to do with connections
between the two database servers?

Here's what I have:


Works: psql -> Local Domain Socket ----->[PG] -----> [Oracle]
^
Fails: psql -> IP Socket -----------------|


I can connect to PG from LDS or IP socket and manipulate the PG database
to which I connected with no trouble. What fails is if I call one of
the dbi-link functions to reference the remote Oracle database.

My understanding of pg_hba is that it controls the connection from the
client to the PG server but not from the PG server to a remote database
server, unless the remote is a PG database, in which case the remote's
pg_hba would have a bearing.

Am I not correct?

Thanks so much for your input!

Barry

Barry Kline <blkline (AT) attglobal (DOT) net> wrote:
I can't see why this should make a difference. The only guess I have is
that something like SELinux or AppArmor might be denying the back-end
permission to connect to the network when the initial connection didn't
need it.

But it seems most likely to be a dbi-link quirk or bug. Doesn't look
like anyone else here has any ideas, so I'd suggest the dbi-link
mailing list at http://lists.pgfoundry.org/pipermail/dbi-link-general/

-M-

Matthew Woodcraft wrote:

That's a good idea and one I checked this morning. SELinux was set for
enforcing but I turned it off (and even rebooted!) with the same result.


Just got myself subscribed to that list and I'm going to move the
problem over to there.

Thanks so much Matthew.

Barry

On 2010-05-08, Barry Kline <blkline (AT) attglobal (DOT) net> wrote:
"host" is a generic term meaning basically a computer or other
networked device.

the pg_hba.conf on host PG file only effects logins to the postgresql
service on that host

Run the query "show all;" through both connections and look for
diferences, a postgres session has quite a lot of "state" and it may
be a part other than the authentication method or type of socket that
is causing trouble.

yes, but oracle probably uses some other system to decide who logs on.


--- news://freenews.netfront.net/ - complaints: news (AT) netfront (DOT) net ---

Jasen Betts wrote:

That was an excellent idea Jasen. I did it to two different output
files, but I don't think it's going to offer any clues:

[root@crpsvsql001 tmp]# diff ip_socket unix_domain_socket
162,164c162,164
< tcp_keepalives_count | 9


The only differences are what I'd expect between the two connection methods.

Regards,

Barry