Обсуждение: authentication problems
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
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
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
Russ McBride <Russ@psyex.com> 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
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 <Russ@psyex.com> 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
Russ McBride <Russ@psyex.com> 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
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 <Russ@psyex.com> 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
Russ McBride <Russ@psyex.com> 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
>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 <machinename>" or "-h <ip-adress>" 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 <ip-adress-of-machine> 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 JDBCfrom 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
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/
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
Russ McBride <Russ@psyex.com> 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
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
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