Re: DB Authentication Design

It really depends on what you are doing, what your security model is, what your concurrency constraints are, etc.  What you are describing is a fairly typical approach and it sacrifices some real security possibilities for connection pooling possibilities.  The fundamental question is whether the security of your application's user should be tied to the database connection. 

The other option is to have a different user for each user, and assign permissions to the user instead of to the application.  You can use roles to manage groups for each application.

For the latter (per-user permissions), your advantages are:

1.  You can push security responsibility back to the database which is probably better tested than your code will ever be.

2.  You can re-use access across applications, allowing for common security enforcement.

3.  You don't have to trust your applications from your database.

4.  It significantly mitigates the impact of sql injection attacks because top-level attacks don't bypass permission barriers (this is not necessarily true with any functions running as security definer though).

The above are why we use the per-user approach in LedgerSMB.

For per-application approaches, the pros are different:

1.  Since security is not tied to the database connection, you can pool a connection over several users (which is what you are talking about).

2.   You can still have some mitigation of sql injection even if you can't have the same degree, by assigning rights per application instead of per user.

3.  In a web application environment, you have a lot more freedom over authentication in every hop.   If you go with per-user permissions, for web apps you are talking about, effectively, HTTP basic with MD5 or other password auth between the web and db servers, or Kerberos auth on every link, or some more complex scenarios where auth is handled by a different connection and/or role.  But if you go with per-application permissions, you can use HTTP digest or other through between the browser and the server, and anything you want between the web and db servers.  This is because you have less of a need to re-use credentials in that setup.

For the kind of work *I* do, the per-user approach works better, but without knowing a lot about your project I can't offer real recommendations.