Re: Best practice? Web application: single PostgreSQL

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.

Normally, I place the database functions/tables/objects into different
logical schemas, then I create one or more specific users (for the web/app
server only) which equates to a logical role, and I grant specific rights
on the different schema objects to those users.

Your mileage may vary.

John Sidney-Woollett