Re: Best practice? Web application: single PostgreSQL user vs. multiple users

Hi Keith and others,

Personally I am rather humble regarding my ability to write unassailable
programs from a security perspective, so I tend to use individual database
accounts for individual users.  I know that under certain environments this
may not scale well and connection pooling may be required, but generally I
am not comfortable with that approach.

Basically my logic is that the most important security is where the
information is stored.  For this reason I try to "push security back"
towards the database manager as far as possible.  Then everythign else can
be given as few permissions as possible (for example, the web application
itself has no access to the database apart from the user).  Usernames and
passwords can be stored separately in order to reduce the cost of compromise
(f. ex. HERMES stores the username in a cookie but the password as a session
variable), etc.  These strategies are simply not possible under the
connection pooling scenario.

For me it comes down to the following question:  What is the cost of
enforcing security yourself?  If there is a security flaw in PostgreSQL,
there is NOTHING that will keep you safe, but why multiply single points of
security failure?

However you have another problem in the scenario you describe-- that is that
the actual authentication occurs via LDAP.  This changes the assumptions and
security environment a bit.  If I had complete control over such an
environment, I would do one of the following things:

1:  Use Kerberos to authenticate and LDAP to store profiles.  LDAP and
PostgreSQL authentication are now handled by Kerberos and you can pass the
authentication token via the web app in mod_perl (not sure how to do it in
PHP though).  For many browsers, this would allow for single signon and
transparent logins.

2:  Have multiple accounts for different roles and store these in the LDAP
user profiles.  This creates a large number of headaches (are the role
fields properly secured, for example).

3:  Finally you could have a user profiles table which contained the allowed
accounts to use.

Best Wishes,
Chris Travers