Обсуждение: connecting: unix socket? Yes. TCPIP port? No. -i? Yes.
I'm just getting into PostGreSQL and would ultimately like to
communicate from a java/JDBC applet through the web with PostGreSQL on a
Solaris unix system. I've had a user account created for me. I'm able to
do everything so far from the commandline through unix sockets by
running 'psql' and 'createdb mydb'.
I began experimenting with JDBC and was having problems connecting, so I
put the java application on the same system where PostGreSQL is and was
getting the same 'user authentication failed...' messages. I then tried
altering the jdbc URL to specify the database only without the internet
host name and it worked successfully.
I changed this statement in the code below:
con = DriverManager.getConnection(
"jdbc:postgresql://my-isp.com/mydb","mylogin","mypwd");
to (since I was local):
con = DriverManager.getConnection(
"jdbc:postgresql:mydb","mylogin","mypwd");
and it worked on the local machine.
The error was:
bash$ java pCon
registering postgresql.Driver...
connecting to the PostGreSQL DB...
java.sql.SQLException: User authentication failed
bash$
I noticed in the docs that you can channel psql through TCPIP ports
rather than unix sockets. I tried it (with my real names) and get this:
bash$ psql -h my-isp.com -d mydb
Connection to database 'mydb' failed.
User authentication failedbash$
bash$ psql -h my-isp.com -p 5432 -d mydb
Connection to database 'mydb' failed.
User authentication failedbash$
I also tried setting PGHOST to my-isp.com, which responded the same.
The administrator says this is how postmaster is invoked:
/usr/local/pgsql/bin/postmaster -S -i -D /usr/local/pgsql/data -p 5432
// pCon.java
import java.io.*;
import java.sql.*;
public class pCon {
public pCon() { }
public static void main(String[] args) throws Exception
{
System.out.println("registering postgresql.Driver...");
Connection con;
try {
Class.forName("postgresql.Driver");
} catch (Exception e) {
System.out.println(""+e);
return;
}
System.out.println("connecting to the PostGreSQL DB...");
try {
con = DriverManager.getConnection(
"jdbc:postgresql://my-isp.com/mydb","mylogin","mypwd");
} catch (SQLException se) {
System.out.println(""+se);
return;
}
System.out.println(
"closing connection to the PostGreSQL DB...");
try {
con.close();
} catch (SQLException se) {
System.out.println(""+se);
}
System.out.println(
"connection to the PostGreSQL DB closed successfully");
}
}
--
Bob VonMoss
mailto:bvonmoss@bigfoot.com
from Chicago, IL
On Fri, 5 Feb 1999, Bob VonMoss wrote: > I'm just getting into PostGreSQL and would ultimately like to > communicate from a java/JDBC applet through the web with PostGreSQL on a > Solaris unix system. I've had a user account created for me. I'm able to > do everything so far from the commandline through unix sockets by > running 'psql' and 'createdb mydb'. > I'm new to postgresql as well but I had a similar problem. The pg_hba.conf file by default allows connections from the localhost and unix socks but not tcp connections from other machines. The default USERAUTH is to trust connections from these two sources. I switched to crypt userauth and added a third connection type to the file host all 192.168.1.0 255.255.255.0 crypt and it solved my problems. ->->->->->->->->->->->->->->->->->->---<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-< James Thompson 138 Cardwell Hall Manhattan, Ks 66506 785-532-0561 Kansas State University Department of Mathematics ->->->->->->->->->->->->->->->->->->---<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<
Bob VonMoss <bvonmoss@bigfoot.com> writes:
> [ can connect via unix socket, but not via TCP ]
> The administrator says this is how postmaster is invoked:
> /usr/local/pgsql/bin/postmaster -S -i -D /usr/local/pgsql/data -p 5432
OK, that eliminates the "forgot -i" gotcha.
James Thompson is almost certainly right that the problem is that
Postgres' pg_hba.conf file is not set up to allow connections from
whichever IP address you are connecting from.
We heard about a similar problem recently which turned out to be
due to use of "virtual server" IP addresses. I've forgotten now
whether Postgres saw the connection as coming from the virtual IP
address or the underlying machine's real address, but anyway it wasn't
what the user was expecting :-(.
regards, tom lane
Tom Lane wrote: > Bob VonMoss <bvonmoss@bigfoot.com> writes: > > [ can connect via unix socket, but not via TCP ] > > > The administrator says this is how postmaster is invoked: > > /usr/local/pgsql/bin/postmaster -S -i -D /usr/local/pgsql/data -p 5432 > > OK, that eliminates the "forgot -i" gotcha. > > James Thompson is almost certainly right that the problem is that > Postgres' pg_hba.conf file is not set up to allow connections from > whichever IP address you are connecting from. > > We heard about a similar problem recently which turned out to be > due to use of "virtual server" IP addresses. The pgsql administrator put a line in pg_hba.conf that looks like this, where my_db is substituted for my database name: host my_db 0.0.0.0 0.0.0.0 ident sameuser I'm still getting the same 'User authentication failed' messages. Same thing from JDBC. Do I need access to the 'template1' table also? Here's a transcript of a session from the command line on the same machine (with host, database and login id substituted): bash$ psql my_db Welcome to the POSTGRESQL interactive sql monitor: Please read the file COPYRIGHT for copyright terms of POSTGRESQL type \? for help on slash commands type \q to quit type \g or terminate with semicolon to execute query You are currently connected to the database: my_db ubf99=> \z Couldn't find any tables! ubf99=> \q bash$ psql -h my-isp.com -p 5432 -d my_db -u Username: login-id Password: Connection to database 'my_db' failed. User authentication failedbash$ bash$ bash$ psql -h my-isp.com -d my_db Connection to database 'my_db' failed. User authentication failedbash$ bash$ psql -h my-isp.com -u my_db Username: login-id Password: Connection to database 'my_db' failed. User authentication failedbash$ -- Bob VonMoss mailto:bvonmoss@bigfoot.com from Chicago, IL
On Sun, 7 Feb 1999, Bob VonMoss wrote:
> Tom Lane wrote:
>
> > Bob VonMoss <bvonmoss@bigfoot.com> writes:
> > > [ can connect via unix socket, but not via TCP ]
> >
> > > The administrator says this is how postmaster is invoked:
> > > /usr/local/pgsql/bin/postmaster -S -i -D /usr/local/pgsql/data -p 5432
> >
> > OK, that eliminates the "forgot -i" gotcha.
> >
> > James Thompson is almost certainly right that the problem is that
> > Postgres' pg_hba.conf file is not set up to allow connections from
> > whichever IP address you are connecting from.
> >
> > We heard about a similar problem recently which turned out to be
> > due to use of "virtual server" IP addresses.
>
> The pgsql administrator put a line in pg_hba.conf that looks like this,
> where my_db is substituted for my database name:
>
> host my_db 0.0.0.0 0.0.0.0 ident sameuser
>
> I'm still getting the same 'User authentication failed' messages. Same thing
> from JDBC. Do I need access to the 'template1' table also?
no.
Is the machine you are running the java app or psql on running ident?
> Here's a transcript of a session from the command
--
Peter T Mount peter@retep.org.uk
Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres
Java PDF Generator: http://www.retep.org.uk/pdf
Peter T Mount wrote: > On Sun, 7 Feb 1999, Bob VonMoss wrote: > > > Tom Lane wrote: > > > > > Bob VonMoss <bvonmoss@bigfoot.com> writes: > > > > [ can connect via unix socket, but not via TCP ] > > > > > > > The administrator says this is how postmaster is invoked: > > > > /usr/local/pgsql/bin/postmaster -S -i -D /usr/local/pgsql/data -p 5432 > > > > > > OK, that eliminates the "forgot -i" gotcha. > > > > > > James Thompson is almost certainly right that the problem is that > > > Postgres' pg_hba.conf file is not set up to allow connections from > > > whichever IP address you are connecting from. > > > > > > We heard about a similar problem recently which turned out to be > > > due to use of "virtual server" IP addresses. > > > > The pgsql administrator put a line in pg_hba.conf that looks like this, > > where my_db is substituted for my database name: > > > > host my_db 0.0.0.0 0.0.0.0 ident sameuser > > > > I'm still getting the same 'User authentication failed' messages. Same thing > > from JDBC. > Is the machine you are running the java app or psql on running ident? Not that I know of. I'm requesting this from the administrator: host my_db 0.0.0.0 0.0.0.0 password sameuser -- Bob VonMoss mailto:bvonmoss@bigfoot.com from Chicago, IL
On Mon, 8 Feb 1999, Bob VonMoss wrote:
> Peter T Mount wrote:
>
> > On Sun, 7 Feb 1999, Bob VonMoss wrote:
> >
> > > Tom Lane wrote:
> > >
> > > > Bob VonMoss <bvonmoss@bigfoot.com> writes:
> > > > > [ can connect via unix socket, but not via TCP ]
> > > >
> > > > > The administrator says this is how postmaster is invoked:
> > > > > /usr/local/pgsql/bin/postmaster -S -i -D /usr/local/pgsql/data -p 5432
> > > >
> > > > OK, that eliminates the "forgot -i" gotcha.
> > > >
> > > > James Thompson is almost certainly right that the problem is that
> > > > Postgres' pg_hba.conf file is not set up to allow connections from
> > > > whichever IP address you are connecting from.
> > > >
> > > > We heard about a similar problem recently which turned out to be
> > > > due to use of "virtual server" IP addresses.
> > >
> > > The pgsql administrator put a line in pg_hba.conf that looks like this,
> > > where my_db is substituted for my database name:
> > >
> > > host my_db 0.0.0.0 0.0.0.0 ident sameuser
> > >
> > > I'm still getting the same 'User authentication failed' messages. Same thing
> > > from JDBC.
> > Is the machine you are running the java app or psql on running ident?
>
> Not that I know of. I'm requesting this from the administrator:
>
> host my_db 0.0.0.0 0.0.0.0 password sameuser
Yeah, he's changed it to ident. I'd go back to him, and get him to change
it to either password or crypt.
Peter
--
Peter T Mount peter@retep.org.uk
Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres
Java PDF Generator: http://www.retep.org.uk/pdf
Nothing's working, see latest developments at bottom. Peter T Mount wrote: > On Sun, 7 Feb 1999, Bob VonMoss wrote: > > > Tom Lane wrote: > > > > > Bob VonMoss <bvonmoss@bigfoot.com> writes: > > > > [ can connect via unix socket, but not via TCP ] > > > > > > > The administrator says this is how postmaster is invoked: > > > > /usr/local/pgsql/bin/postmaster -S -i -D /usr/local/pgsql/data -p 5432 > > > > > > OK, that eliminates the "forgot -i" gotcha. > > > > > > James Thompson is almost certainly right that the problem is that > > > Postgres' pg_hba.conf file is not set up to allow connections from > > > whichever IP address you are connecting from. > > > > > > We heard about a similar problem recently which turned out to be > > > due to use of "virtual server" IP addresses. > > > > The pgsql administrator put a line in pg_hba.conf that looks like this, > > where my_db is substituted for my database name: > > > > host my_db 0.0.0.0 0.0.0.0 ident sameuser > > > > I'm still getting the same 'User authentication failed' messages. Same thing > > from JDBC. Do I need access to the 'template1' table also? > > no. > > Is the machine you are running the java app or psql on running ident? We've tried: host my_db 0.0.0.0 0.0.0.0 ident sameuser host my_db 0.0.0.0 0.0.0.0 password sameuser Same messages: "User authentication failed" I'm hoping that it will eventually work through JDBC, but it doesn't work with psql -h my-isp.com -d my_db where my-isp.com and my_db are substitutes for the real things. I'm running psql on their computer when I telnet there. I don't know what ident is, but it's only on their computer. 'password' didn't work either. I don't know what the problem is. I'm exhausting my possibilities here. Yes, the postmaster is launched with -i. I only created 'my_db' with the command 'createdb my_db' from a unix prompt on the host. I don't have to run something like 'createdb -d dezines.com my_db', right? -- Bob VonMoss mailto:bvonmoss@bigfoot.com from Chicago, IL
Bob VonMoss <bvonmoss@bigfoot.com> writes:
> We've tried:
> host my_db 0.0.0.0 0.0.0.0 ident sameuser
As you already found out, that won't work if you're not running an ident
daemon on your connecting machine. (You aren't *really* running with an
0.0.0.0 mask are you? That means anyone anywhere on the net can get
into your database, if they can reach the machine it's on...)
> host my_db 0.0.0.0 0.0.0.0 password sameuser
See the documentation:
# password: Authentication is done by matching a password supplied in clear
# by the host. If AUTH_ARGUMENT is specified then the password
# is compared with the user's entry in that file (in the $PGDATA
# directory). See pg_passwd(1). If it is omitted then the
# password is compared with the user's entry in the pg_shadow
# table.
Unless your admin made a file named "sameuser" to store Postgres
passwords in, this will not work. I think you want to remove "sameuser".
(Also, did he remember to set a Postgres password for you?)
> I'm hoping that it will eventually work through JDBC, but it doesn't
> work with
> psql -h my-isp.com -d my_db
> Same messages: "User authentication failed"
Even if the host system were correctly configured for password
authentication, this psql invocation would fail since you didn't supply
a username and password. Need -u option.
regards, tom lane