SOLVED: Problem with connecting to postgres using JDBC with JSP, but not JDBC with Java

Hi folks!

Great news. I have solved my problem regarding the Subject line (originally
posted by myself on December 24th under the subject Problem with connecting to
postgres using jdbc7.0-1.2.jar). I have been wacking my head against my
monitor for the whole week and I managed to get it to work. It's 3:30 am local
time in Toronto and I should be exhausted, but it started working for me 10
minutes ago and I am on a real high. I may not even sleep tonight!!! Feel
free to skip the rest of this note as I'm not including any technical stuff
other than for what I did to fix my problem.

I was unable to execute a servlet that would connect to the database and do
something silly such as select the count(*) of a table. At the same time I was
able to do this with a non-servlet/non-jsp java program. I could not figure it
out and I was constantly getting the following error when trying to run the
servlet:

Apache Tomcat/4.0.1 - HTTP Status 500 - Internal Server Error
type Exception report
message Internal Server Error
description The server encountered an internal error (Internal Server
Error) that prevented it from fulfilling this request.
exception
java.lang.NullPointerException

My problem: I had installed Tomcat 4 from a binary distribution and this has
placed some startup and shutdown wrapper scripts in the $TOMCAT_HOME/bin
directory. Well these scripts reference the file catalina.sh which is itself a
wrapper script. Anyway, to make a long story short, the classpath that is used
in the TOMCAT processes when TOMCAT is started using the startup.sh /
catalina.sh scripts does not append to the $CLASSPATH that you define in your
.bashrc file. So when TOMCAT starts, the jdbc7.0-1.2.jar file (which I had
included in my .bashrc $CLASSPATH variable) is not in the TOMCAT classpath and
hence I was getting the error when trying to connect to the postgresql
database. I would not see this file when running the stand alone java program
becuase the .bashrc classpath variable was being used and this did include the
reference to the driver.

To fix it, I have very slightly modified the catalina.sh startup file so that
the classpath that it defines contains the jdbc7.0-1.2.jar driver reference.

Many thanks to everyone for all your support and valuable suggestions
especially Rene Piljman, Jens Carlberg, Ari Krupnikov, Nick Fankhauser, Tony,
Mihai Gheorghiu, Dave Cramer and anyone I may have missed. I have read your
suggestions, but because I am still a rookie with much of this, I will be
trying to internalize everything over the next few days (as I catch up on my
sleep). I hope you won't mind if I email you again in the next little while to
inquire about some of the details that are unclear to me at this time.

One thing I'll say right now (even though this is not the forum for this) is
that I still don't really understand how you are supposed to maintain a secure
/ consistent system when installing a binary distribution of an application.
Since you untar and unzip it as root all the internal files take on the
ownership and group of root. Once you move the directory to where you want it,
how can you run any of it under a different user such as www or tomcat (in the
case of TOMCAT) if everything is owned by root (unless if you change the
necessary files to a different owner)? There does not seem to be good
documentation on what files should be owned by who and I've never seen the
installation process (for any application) documented down to that level.
Maybe I'm just overly concerned about this. It seems like RPM packages are
much more friendly to install /uninstall and they hanle file permissions
& dependencies for you (as well as creating any additional users that are
needed to run the application), but these are not always available and when
they are, the options they come with are not very customizable (not to mention
that they leave out certain things that are provided when installing a binary
distribution or installing from source). I guess that installing from source
has the same pitfalls regarding not knowing what to set the file permissions to
as installing binary distributions (with the added worry of running accross
some error that you seem to get and no one else in the world has seen before).
I experienced this earlier tonight when I tried to compile the mod_webapp
module for Apache 1.3 and seeing the famous apxs error. If anyone has
suggestions or tips or knows where I could go to get a good reference on how
best to install source and binary distributions while maintaining the file
permissions that were intended by the original application developers (without
just blindly setting everything to root - unless if this is exactly the way you
are supposed to do of course), I would really appreciate it.

Happy rest of the holidays everyone and a happy new year. And once again,
thank you for offering so much support. I hope I will one day be able to
contribute back to the board as much as you all have to me.

Nick Fankhauser wrote:

> Peter-
>
> A couple of thoughts-
>
> Apparently the database connection is failing. You want to know why, so I'd
> suggest replacing "catch (SQLException e) {}" with "catch (SQLException e)
> {e.printStackTrace();}".
>
> Since you're running Tomcat, the stack trace won't appear on the console,
> but you should be able to find it in the tomcat log files. In my case, I'm
> on Debian Linux, so to look at the tomcat stdout log, I use "tail -50
> /var/log/tomcat/stdout.log". The location probably varies slightly by OS.
> (Get 50 lines on the tail because the stack trace will be long <grin>.)
>
> My first guess as to what you'll find in the trace is that it's an
> authentication problem. You mention that tomcat is running as su (root?).
> Take a look at the pg_hba.conf file for postgresql- It's pretty standard for
> it to be set up as "peer sameuser" by default for localhost connections. If
> this is the case, Tomcat will be running as root, but your servlet tries to
> connect as "peter" & fails the sameuser test for authentication, or even if
> the auth method is "trust", root may not be a postgres user. I suppose you
> could test this quickly by creating a root postgres user. (I'm not sure what
> the longer-term security implications of this would be, but I assume we're
> in "make it work" mode right now.)
>
> BTW, tomcat usually runs as www-data by default in most installations (same
> as apache), so In my case, I just make sure there is a www-data user in
> postgresql & grant that user the privs I need it to have on each object.
>
> I'm running Tomcat 3.2, so your mileage may vary, but it looks to me like
> you've licked the Tomcat issues & you're back to postgres now.
>
> Hope this helps-
>
> -Nick
>