Re: Best practice? Web application: single PostgreSQL

Quoting Chris Travers <chris@travelamericas.com>:

> > I do #1- most connections are not persistent though I have done those
> > before as well.  Security-wise I'm been reviewing the pros and cons
> > of this and so far I really can't make much of an argument for #2.
> > Just the opposite in fact.  The one thing I will be doing though,
> > for more security, is create a special webuser for that client.
> >
>
> My concern with connection pooling is that the application itself must run
> with permission to do anything that any user may do in the application.  If
> you have a way of using postgresql native accounts, you can actually force
> the application to have no rights to the database at all unless the
> credentials are supplied by a user.  The application then has NO rights to
> anything that the user doesn't, and this may allow for better security.

I think we might be talking about two different things- unless you are equating
persistant connections to connection pooling.  Perhaps that is correct but let
me example a little more what I was talking about.  It might be a little
off-topic but I want to be clear in the archives.

My particular environment is Linux, Apache, mod_perl and PostgreSQL.  The user
and group that the web server runs as has no permissions to anything.  When a
client's app need a DB, we create an account in PG for them and grant
permissions to the web user for the objects it needs access to.

In the mod_perl script, if I do persistent, the first thing that is done is a
check for a previous connection to **that** client's database (via a global
variable).  If not (or if that connection is "busy") a new one is created.  The
maximum number of connections would be equal to the maximum number of Apache
listeners.  That's assuming you don't use other application logic to control
it.  For those who aren't familiar with mod_perl, the benefit is that not
only does your script run as an object in the Apache server but the database
connection objects will persist.  When you don't do the persistent connections,
the difference is that your application object in Apache will have to open the
database connection everytime.  For local connections to PostgreSQL, I really
haven't seen that much of a difference but in my "test" case, I'm
pretty sure I wasn't implementing the persistent-ness of the script correctly
and the application was not being hit with any significant load.

I thought connection pooling was more generic- any connection from the web
server/application business logic could be reused for any db connection.  Please
correct me if I'm wrong here.

Depending on the application, the
authorization/authentication would be wrapped in an SSL transport (HTTPS instead
of HTTP).  After that each connection is "tracked" (via the Apache
cookie-tracking and and pgAuth module I wrote) by always checking the validity
of the client's browser cookie.  The authorization pairs for the client's
application are stored in a user table in their database and the cookie gets
linked to that and is valid only for that browser session (i.e. as long as the
browser instance is open and you did not "log out").  No direct information
about the database is revealed to the client interface (web browser) at all.

> The real drawback for multiple accounts is that each account needs to be
> maintained.  In environments where this is an issue, I usually create
> functions to do this so that my information does not get out of sync.
> However, I can imagine this being a concern, security wise.

Its the pros and cons of PG having its own accounts.  Definitely a security
benefit, which generally means a headache somewhere else- in this case double
account unless, like you said, have way to keep things in sync.  For something
secure, I don't mind the headache...  Advil is my friend  :)

> Best Wishes,
> Chris Travers
>


--
Keith C. Perry, MS E.E.
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com

____________________________________
This email account is being host by:
VCSN, Inc : http://vcsn.com