Re: authentication problems

От:

Stefan Huber <looseleaf@gmx.net>

Дата:

30 сентября 2001 г. в 03:47:32

>Hmmm.  Perhaps the host line working fine for ip connections as well, 
>but something is going wrong in the JDBC-specific ip connections.  Is 
>there a way to make a local ip connection instead of a UNIX socket 
>connection with psql?
As Tom already said, the "host local allow" line has nothing to do with TCP/IP.

But if you want to explicitly specify a hostname, then use "-h " or "-h " as parameter to psql.

So what do the following commands return?
$ psql -h localhost testdb
(should be OK)

$ psql -h 127.0.0.1 testdb
(should be OK)

$ psql -h  testdb
(should be rejected)

Maybe a stupid question, but does MacOS have the same path-format as Un*ces? (You wrote about connecting to your DB via JDBC from a MacOS (on another machine?), or did I misinterpret that?)

And you said that you never had to specify the host you connect to in your JDBC connect statement. Usually this is a "host=" parameter. I've never used JDBC, but ODBC, Pg, ADO (via ODBC), ...

You posted an error message:
>The error message I get is:
>No entry in pg_hba.conf_file for 169.245.10.10 [or whatever ip 
>address I happen to be using at the time] for user: postgres 
>database: testdb

How exactly did you connect to your DB and from which computer?

Stefan

Re: authentication problems

От:

"Chad R. Larson" <chad@eldocomp.com>

Дата:

12 октября 2001 г. в 14:55:43

On Sat, Sep 29, 2001 at 07:34:43PM -0700, Russ McBride wrote:
> I'm going to restate the situation since there was some confusion, 
> judging from a couple of responses.
> 
> The goal-- The goal is to be able to connect to my database from the 
> very machine where the database sits, without worrying about what my 
> ip settings are (I switch them around a bunch for various reasons).

Unless you use a '-h localhost' switch on the command line (to force
a TCP/IP connection) that line isn't the one that will apply.
You'll want something like

    local	all	trust

in your pg_hba.conf file.

         -crl
--
Chad R. Larson (CRL22)    chad@eldocomp.com
  Eldorado Computing, Inc.   602-604-3100
     5353 North 16th Street, Suite 400
       Phoenix, Arizona   85016-3228

Re: authentication problems-- success!

От:

Tom Lane <tgl@sss.pgh.pa.us>

Дата:

1 октября 2001 г. в 09:24:56

Russ McBride  writes:
> Shouldn't specifying 'localhost' always force *my* IP to be broadcast 
> as 127.0.0.1 (or whatever 'localhost' may have been changed to in 
> /etc/hosts) regardless of what other network stuff is going on?

One would think.  Perhaps there is something screwy about your DNS
setup; or maybe this is a plain old bug that needs to be reported to
Apple.

			regards, tom lane

Re: authentication problems

От:

Tom Lane <tgl@sss.pgh.pa.us>

Дата:

29 сентября 2001 г. в 23:02:31

Russ McBride  writes:
> Thanks for the troubleshooting idea Nick.  I can ping localhost which 
> does show up as 127.0.0.1.  The error message I get is:
> No entry in pg_hba.conf_file for 169.245.10.10 [or whatever ip 
> address I happen to be using at the time] for user: postgres 
> database: testdb

Hmph.  How are you connecting exactly?  Do "psql -h localhost" and
"psql -h 127.0.0.1" produce different results?  What do you have in
/etc/hosts and /etc/resolv.conf?

			regards, tom lane

Re: authentication problems

От:

Tom Lane <tgl@sss.pgh.pa.us>

Дата:

29 сентября 2001 г. в 23:29:06

Russ McBride  writes:
> I typed "/usr/local/pgsql/bin/psql -h localhost" and 
> "/usr/local/pgsql/bin/psql -h 127.0.0.1" and they both returned the 
> same (odd) message:

> psql: FATAL 1: database "postgres" does not exist in the system catalog

Nothing odd about that: the database name is defaulting to your PG user
name, evidently postgres.  Try "psql -h localhost databasename" for
some valid database name.  The important thing about this message is
that you are getting past the pg_hba.conf check, so this *is* forward
progress.

			regards, tom lane

Re: authentication problems

От:

Tom Lane <tgl@sss.pgh.pa.us>

Дата:

29 сентября 2001 г. в 23:56:19

Russ McBride  writes:
> Typing "/usr/local/pgsql/bin/psql -h localhost testdb" starts up my 
> database in psql quite nicely, which seems to indicate to me that the 
> pg_hba.conf is being read and the line:

> local  all                        trust
> is working as expected (I assume that this is the line that allows 
> all local UNIX socket connections).

You assume wrong: "-h localhost" triggers a connection over IP, not
over UNIX sockets.  Specifically, it's going to match the pg_hba.conf
line that mentions 127.0.0.1.

At this point I think your problem is that your JDBC setup is trying
to connect to your machine by name, not by the "localhost" alias,
and this results in a connection via your current network IP address
rather than the 127.0.0.1 loopback address.  Since you have an entry
in pg_hba.conf for 127.0.0.1 and not the other address, only the
localhost address will work.

			regards, tom lane

Re: authentication problems

От:

"Nick Fankhauser" <nickf@ontko.com>

Дата:

30 сентября 2001 г. в 16:16:53

Russ-

> > Typing "/usr/local/pgsql/bin/psql -h localhost testdb" starts up my
> > database in psql quite nicely

Here's a suggestion which certainly doesn't run down the cause
satisfactorily, but might meet your goals and add a clue...

When you specify the database URL in your java code, try explicitly naming
either localhost or 127.0.0.1.

Supposedly, JDBC always defaults to localhost if a host is not specified,
but from what we've learned so far, psql seems to have no problem if you
explicitly point it to localhost using an IP connection, whereas JDBC
doesn't connect when left to take the implicit default.

So try either "jdbc:postgresql://localhost/testdb" or
"jdbc:postgresql://127.0.0.1/testdb" & see what happens.

If either works, you have at least achieved your goal of a stable local JDBC
connection that doesn't depend on the actual IP address of the local
machine. You'll also have one more clue to ponder if you can't sleep nights
without knowing *why* the default isn't working as expected.

You mentioned at the start of this thread that it works fine for you an a
different machine- is the working machine also running Mac OS 10.1?

-Nick
---------------------------------------------------------------------
Nick Fankhauser

    nickf@doxpop.com  Phone 1.765.965.7363  Fax 1.765.962.9788
doxpop - Court records at your fingertips - http://www.doxpop.com/

Re: authentication problems

От:

"Nick Fankhauser" <nickf@ontko.com>

Дата:

29 сентября 2001 г. в 17:11:57

Russ-

You've got a real poser there... our system works fine with the same lines
in pg_hba. I think that means you may find your answer in the operating
system environment instead of the postgres setup.

> local     all                                   trust
> host      all   127.0.0.1    255.255.255.255    trust

> Also, I don't understand what's special about the above numbers
> (127.0.0.1 and it's mask) such that they are used to allow any ips to
> come in from the same machine.

The explanation may be a clue to the problem. 127.0.0.1 is universally
reserved and recognized to mean "this machine", but this is not hard-coded
in the networking programs, so most systems will have lines in the IP
configuration files equating localhost to 127.0.0.1. For instance, your
etc/hosts file should have an entry that says:

127.0.0.1   localhost

I wonder if this line, or something like it in your system's IP
configuration is missing & hence postgres can't use localhost to reach the
the local machine. One way to test this would be to try to ping localhost &
see if it responds by telling you it is attempting to ping 127.0.0.1

If you can't ping localhost, or it doesn't resolve to 127.0.0.1, then you
know the problem is at the operating system/network level & postgres and
JDBC are probably just fine.

-Nick

---------------------------------------------------------------------
Nick Fankhauser

Business:
nickf@doxpop.com  Phone 1.765.965.7363  Fax 1.765.962.9788
doxpop  - Court records at your fingertips - http://www.doxpop.com/

Personal:
nick@fankhausers.com   http://www.fankhausers.com

Re: authentication problems

От:

Russ McBride <Russ@psyex.com>

Дата:

29 сентября 2001 г. в 23:25:15

I typed "/usr/local/pgsql/bin/psql -h localhost" and 
"/usr/local/pgsql/bin/psql -h 127.0.0.1" and they both returned the 
same (odd) message:

psql: FATAL 1: database "postgres" does not exist in the system catalog


I'm connecting through JDBC using 7.1.3 on Darwin (Mac) OS 10.1.

Stefan Huber suggested that I tell JDBC to connect through the 
"lo-network" device so that the 127.0.0.1 line in pg_hba.conf works.

thanks for thinking about this problem,


Russ




>Russ McBride  writes:
> > Thanks for the troubleshooting idea Nick.  I can ping localhost which
> > does show up as 127.0.0.1.  The error message I get is:
> > No entry in pg_hba.conf_file for 169.245.10.10 [or whatever ip
> > address I happen to be using at the time] for user: postgres
> > database: testdb
>
>Hmph.  How are you connecting exactly?  Do "psql -h localhost" and
>"psql -h 127.0.0.1" produce different results?  What do you have in
>/etc/hosts and /etc/resolv.conf?
>
>			regards, tom lane

Re: authentication problems

От:

Russ McBride <Russ@psyex.com>

Дата:

29 сентября 2001 г. в 22:32:26

Hi,

I'm going to restate the situation since there was some confusion, 
judging from a couple of responses.

The goal-- The goal is to be able to connect to my database from the 
very machine where the database sits, without worrying about what my 
ip settings are (I switch them around a bunch for various reasons).

The normal solution-- set the following line in the pg_hba.conf file, 
which should be there by default:
host       all       127.0.0.1      255.255.255.255         trust
This should allow the localhost to connect regardless of its ip 
address setting.

The problem-- the above setting doesn't work as it should.  Even with 
the above line I can only connect if I manually type in the exact ip 
address of the machine (which is both the client machine and the 
posgresql server).

Nick said:
>
>If you can't ping localhost, or it doesn't resolve to 127.0.0.1, then you
>know the problem is at the operating system/network level & postgres and
>JDBC are probably just fine.
>
>-Nick
>

Thanks for the troubleshooting idea Nick.  I can ping localhost which 
does show up as 127.0.0.1.  The error message I get is:
No entry in pg_hba.conf_file for 169.245.10.10 [or whatever ip 
address I happen to be using at the time] for user: postgres 
database: testdb

I'm baffled.  Any remotely feasible other troubleshooting ideas are 
appreciated.

 
Russ

Re: authentication problems

От:

Russ McBride <Russ@psyex.com>

Дата:

29 сентября 2001 г. в 23:50:33

Typing "/usr/local/pgsql/bin/psql -h localhost testdb" starts up my 
database in psql quite nicely, which seems to indicate to me that the 
pg_hba.conf is being read and the line:

local  all                        trust
is working as expected (I assume that this is the line that allows 
all local UNIX socket connections).

Hmmm.  Perhaps the host line working fine for ip connections as well, 
but something is going wrong in the JDBC-specific ip connections.  Is 
there a way to make a local ip connection instead of a UNIX socket 
connection with psql?

best,

Russ





>Russ McBride  writes:
> > I typed "/usr/local/pgsql/bin/psql -h localhost" and
> > "/usr/local/pgsql/bin/psql -h 127.0.0.1" and they both returned the
> > same (odd) message:
>
> > psql: FATAL 1: database "postgres" does not exist in the system catalog
>
>Nothing odd about that: the database name is defaulting to your PG user
>name, evidently postgres.  Try "psql -h localhost databasename" for
>some valid database name.  The important thing about this message is
>that you are getting past the pg_hba.conf check, so this *is* forward
>progress.
>
>			regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org

Re: authentication problems-- success!

От:

Russ McBride <Russ@psyex.com>

Дата:

1 октября 2001 г. в 02:49:40

Hi guys,

>When you specify the database URL in your java code, try explicitly naming
>either localhost or 127.0.0.1.

Thanks for all the time you guys spent thinking about this 
authentication problem and writing me.  I changed the URL in the java 
code from 'localhost' to '127.0.0.1' and it worked fine (duh).

I also discovered the following interesting fact further showing that 
the problem is OS-specific and has nothing to do with the way 
postgreSQL is functioning.  On my Mac 10.03-os machine, it makes no 
difference what my network settings are, whether my ports are active, 
etc.  When I connect to a pg database locally, it goes fine.  On my 
Mac 10.1-os machine, however, when I try to make a connection to 
localhost it advertises my IP to postgresql as the first of whatever 
active port settings I happen to have active at that time.  Only if I 
switch off all ports does my IP get advertised as '127.0.0.1'. 
Shouldn't specifying 'localhost' always force *my* IP to be broadcast 
as 127.0.0.1 (or whatever 'localhost' may have been changed to in 
/etc/hosts) regardless of what other network stuff is going on?  This 
problem still remains a mystery to me, but at least I know what my 
new os is doing, if not why, or how to fix what it's doing.

What started off as a "quick" check to make sure that pg worked on 
10.1 as well as 10.0.x versions before completing my installation 
documentation turned into a full on adventure, ending with this 
authentication challenge.  I'm looking forward to getting some of the 
actual work done now.

If anyone needs help with their Mac OS X installation send me an 
e-mail.  I think we've got it pretty well figured out now . . .

Best,

Russ


>
>
>Supposedly, JDBC always defaults to localhost if a host is not specified,
>but from what we've learned so far, psql seems to have no problem if you
>explicitly point it to localhost using an IP connection, whereas JDBC
>doesn't connect when left to take the implicit default.
>
>So try either "jdbc:postgresql://localhost/testdb" or
>"jdbc:postgresql://127.0.0.1/testdb" & see what happens.
>
>If either works, you have at least achieved your goal of a stable local JDBC
>connection that doesn't depend on the actual IP address of the local
>machine. You'll also have one more clue to ponder if you can't sleep nights
>without knowing *why* the default isn't working as expected.
>
>You mentioned at the start of this thread that it works fine for you an a
>different machine- is the working machine also running Mac OS 10.1?
>
>-Nick
>---------------------------------------------------------------------
>Nick Fankhauser
>
>    nickf@doxpop.com  Phone 1.765.965.7363  Fax 1.765.962.9788
>doxpop - Court records at your fingertips - http://www.doxpop.com/
>
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/users-lounge/docs/faq.html

authentication problems

От:

Russ McBride <Russ@psyex.com>

Дата:

29 сентября 2001 г. в 05:05:14


Alright all you pg_hba.conf experts . . .

I can't for the life of me figure out why I'm getting connection 
error messages which mention the pg_hba.conf file.  I was trying to 
use the default settings:

local     all                                   trust
host      all   127.0.0.1    255.255.255.255    trust

As I understand it, the first line should let any user on the same 
machine as the database connect through the regular UNIX sockets 
while the second line should let any user on the same machine connect 
through tcp/ip (jdbc, for example).  These default settings are 
working great on another machine I have that's also hosting a 
database.  A request can come in from any ip address on the 
database's machine, and the connection goes through without a hitch.

But on my problematic machine postmaster won't let me connect 
(through tcp/ip using jdbc) unless I explicitly type an additional 
line into the pg_hba.conf file with the machine's actual ip address 
and subnet mask.  Why???  It should let me in regardless of the 
machine's ip.

Also, I don't understand what's special about the above numbers 
(127.0.0.1 and it's mask) such that they are used to allow any ips to 
come in from the same machine.

I've tried other variations beside what the docs suggest, like the 
following line that was recommended:

host    all   127.0.0.1     255.0.0.0    trust

but this didn't help

Any suggestions are welcome . . . or will I forever be cursed to 
modify the pg_hba.conf file every time I change my machine's ip 
address.



Russ