Re: Application user login/management

Michael Glaesemann wrote:

> 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.

I've seen this method used successfully in some applications, I prefer
to avoid using it as you must also create a PostgreSQL user for each
application user. Instead I use either method 2 or 3 for user
authentication and then use method 1 to restrict the middleware's access
to the database itself (don't give the application more access than it
requires).

>
> 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.
>
If you are confident that (a.) you will either run the database server
or (b.) have the authority to require that pgcrypto be installed on the
database for all installations this may be a good solution. Keep in
mind you are limited to the encryption types supported by pgcrypto and
moving to another database solution may be difficult. I also can't
comment on the availability of pgcrypto on Win32 but with PostgreSQL 8
just around the corner the desire might be there to run the DB on
Windows at some point. libmcrypt is currently available in win32 but
I've occasionally seen behavior differences with it on win32 v.s. Unix.

Also keep in mind that if you are not using encrypted database
connections (using PostgreSQL's built in SSL support or SSH tunneling or
another technique) you may be sending user's passwords across the
network in plain text for the database to use. I would either insure
that all connections will be encrypted or preferably at hash the
password with at least SHA-1 on the application side and pass that as
the password to the back-end, SHA-1 is available in almost all languages
these days; this technique may also remove the requirement of using
pgcrypto on the back-end.

If you are going to use multiple interfaces to the application this may
be the best choice as you don't have to re-implement the security system
for each client application.

> 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.
>
This is the technique I've used pretty often, it gives me very powerful
application integration and allows me to more easily support different
back-ends if the customer so chooses (I currently go with PostgreSQL and
SQLite). The biggest drawback you've already touched is the system is
implemented in the middleware so other interfaces to the application
must also implement the security system.

> Another thing on my mind is security. Any thoughts on the relative
> security of the three methods I've outlined above?
>
Don't store passwords in plain text, don't pass anything sensitive over
the wire unencrypted (preferably nothing at all but sometimes that isn't
feasible). Like I mentioned above, I don't see method one as an
application security method but rather as a way to augment the security
of methods two or three. YMMV and some good planning now might help you
avoid re-implementing your system later.

> Thank you for any and all thoughts on this. I appreciate hearing
> other's views.
>
> Regards,
>
> Michael
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend