Thread: connecting: unix socket? Yes. TCPIP port? No. -i? Yes.

connecting: unix socket? Yes. TCPIP port? No. -i? Yes.

From
Bob VonMoss
Date:
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



Re: [INTERFACES] connecting: unix socket? Yes. TCPIP port? No. -i? Yes.

From
James Thompson
Date:
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
->->->->->->->->->->->->->->->->->->---<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<



Re: [INTERFACES] connecting: unix socket? Yes. TCPIP port? No. -i? Yes.

From
Tom Lane
Date:
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

Re: connecting: unix socket? Yes. TCPIP port? No. -i? Yes.

From
Bob VonMoss
Date:
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



Re: [INTERFACES] Re: connecting: unix socket? Yes. TCPIP port? No. -i? Yes.

From
Peter T Mount
Date:
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


Re: [INTERFACES] Re: connecting: unix socket? Yes. TCPIP port? No.-i? Yes.

From
Bob VonMoss
Date:
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



Re: [INTERFACES] Re: connecting: unix socket? Yes. TCPIP port? No.-i? Yes.

From
Peter T Mount
Date:
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


Re: [INTERFACES] Re: connecting: unix socket? Yes. TCPIP port? No.-i? Yes.

From
Bob VonMoss
Date:
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



Re: [INTERFACES] Re: connecting: unix socket? Yes. TCPIP port? No.-i? Yes.

From
Tom Lane
Date:
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