Re: mod_ruby and postgresql (long)

On Fri, 4 Oct 2002 03:46, Jeremy Henty wrote:
> ???  I use mod_ruby and ruby-postgres to create database-backed web
> pages and I never had any such problem.  But I call ruby-postgres
> directly without going through DBI.  Maybe this is part of the
> problem?  It might help if you posted details of the code and the
> errors.  I have been considering migrating to DBI instead of raw
> postgres and I would like to know more about any pitfalls.

This is a long post, because I want to give enough detail to explain the 
things I've been trying and the success (or lack thereof) I've had.

I would have posted some of the code, except that, as with any real project, 
there's a lot of infrastructure, which would only serve to hide the details.

It would appear that the problem had to do with the fact that I was reading 
the URLs for the data sources from an XML file.  I added an "untaint" to the 
URL, login and password and that got me through.

I can understand why that makes sense, but the specific line where I was 
getting the error message was a require based on a hard-coded string (inside 
one of the DBI modules), that didn't make any reference to variables.  Hence, 
my confusion.  I tried the untaints because it seemed logical that they were 
probably needed.

This is the first time I've used Postgres or mod_ruby in any serious way, so 
I'm not really surprised I'm having a few problems.  I guess it's a little 
more frustrating simply because my expectations of how easy things *should* 
be have been amplified by writing so much Ruby code in the last few months 
:-).

The problem I have now is probably more to do with the database access. Rather 
than explaining the difficulties I'm having, maybe it would make more sense 
to ask how other people normally set things up when using Postgres (via DBI) 
with mod_ruby.

I create the database as myself (harryo), but of course the mod_ruby code is 
running as apache.  I would have thought that, so long as I connected using 
"harryo" as the login and the appropriate password, all would be well.

However, I found that didn't work.  I got an error saying

   initialize: FATAL 1:  IDENT authentication failed for user "harryo"

This happens if I run equivalent code just from the command line, rather than 
via mod_ruby.  If I run precisely the same code, logged in as "harryo", it 
works fine.

Obviously, this has something to do with how Postgres does the authentication, 
but I would have thought that the login and password would be what defined 
whether access was granted, rather than the actual user ID.

If someone can tell me the correct way to set up the database and to access it 
from my mod_ruby code, I'd appreciate it.  However, it feels a little off 
topic, so I'm going to try to find a Postgres newsgroup to ask about it.

When I use "apache" as the login (having created that user in Postgres), I can 
now make the connection successfully, but there's still some kind of 
authentication issue, because when I try to execute a "select", I now get an 
error saying

    "execute": ERROR: test1: permission denied

(test1 is the name of the database table).  Again, I can do the same select 
from a piece of code external to mod_ruby, logged in as "apache" with no 
problems at all.

So, I'm guessing this is actually again something to do with safe levels.  I 
did originally have an obvious problem, until I added an "untaint" on the 
select string.

Harry O.