Thread: 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

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