Application user login/management - Mailing list pgsql-general

Hello all,

Recently I've been thinking about different methods of managing users
that log into a PostgreSQL-backed application. The users I'm thinking
of are not necessarily DBAs: they're application users that really
shouldn't even be aware that they are being served by the world's most
advanced open source database server. I appreciate any thoughts or
feedback people may have, as I'm trying to decide which is the most
appropriate way to move forward.

Method 1: Use PostgreSQL users and groups.
All application users will (unknowingly) be PostgreSQL users as well.
Restrict access for these users to prevent them from logging into the
PostgreSQL server directly, and limit their access to the DB using the
built-in PostgreSQL access privilege mechanism. Updates occur through
functions; selects are against views or using set returning functions.
This method leverages built-in functionality. Drawbacks I see are that
PostgreSQL users are unique to a cluster, rather that the db. This
means that once a user exists in one db, they exist in all of the dbs.
There might be users with the same name in other dbs, so that name is
no longer available (though of course this can also occur in a single
db as well). Also, it may be desirable to let usernames be retired for
one person, but the user is not deleted, for example if their data is
still required even though they are no longer active. One might want to
allow a new user to be able to use this username, i.e., active
usernames would be unique, rather than usernames in general.

Method 2: Store username/password information as data in tables, using
pgcrypto for authentication
In this scenario, middleware passes username/password combinations to
PostgreSQL and functions within the database use contrib/pgcrypto to
handle authentication. This allows a username to be 'retired' for one
person and assigned to another. Another advantage is that using
PostgreSQL functions for authentication mean that this doesn't need to
be duplicated in middleware. A possible disadvantage is that it
requires pgcrypto, though I don't know how much of a disadvantage this
is, as it is a contrib library that ships with the standard PostgreSQL
package.

Method 3: Store username/password information as data in tables, and
use middleware for authentication
This seems to be the most popular method from what I've seen of open
source packages. One reason for this may be that the middleware is
designed to work with a number of different dbms backends, and
different dbms' have different capabilities with respect to user
management: it's just easier to take care of it in the middleware.

I lean towards the first and second methods, as I like to keep as much
in the server as possible, and portability wrt the database server
isn't as important to me as being able to develop different middleware
against the same data.

Another thing on my mind is security. Any thoughts on the relative
security of the three methods I've outlined above?

Thank you for any and all thoughts on this. I appreciate hearing
other's views.

Regards,

Michael