Re: Best practice? Web application: single PostgreSQL

On Tue, 13 Jan 2004, Keith G. Murphy wrote:

> John Sidney-Woollett wrote:
>
> > Keith G. Murphy said:
> >
> >>2) have the web server connecting to the database actually using the
> >>user's account (possibly using LDAP authentication against PostgreSQL),
> >>and controlling access to different database entities through GRANT, etc.
> >
> >
> > My experience with java web/app servers indicates that for most setups
> > using a pool of connections is preferable to using a single connection per
> > connected user - it scales much better.
> >
> > What you could consider is one or more pools which map to the "roles" that
> > your (web) app supports. For example, if a user needs "minimal rights"
> > access to db resources, then your cgi (request handler) accesses the data
> > using a connection from the "minimal rights" connection pool. A user
> > needing "greater rights" would have the cgi access the database from the
> > "greater rights" pool.
> >
> That sounds like an excellent compromise. How do you typically handle
> the mechanics of authentication from web server to PostgreSQL on the
> connect, using this scheme?

I create individual databases for unrelated projects (like say, phonebook
and sales_projections and then connect to each database as a different
artificial user often named for the database. Then I usually wrap that in
an include file I just add at the top of each page that connects and has
the password (on systems using password authentication) or that connects
without a password if I'm on a system using trust.

Then, any access by users is handled by ACLs I just build in a table in
that database.

We authenticate with auth_ldap, so we always know the user's name / groups
etc...