Re: Application user login/management - Mailing list pgsql-general

Thank you, both Scott and Jason, for your responses. You both brought
up things I hadn't thought about. I've included snippets of their posts
below.

> On Sun, 2004-10-03 at 22:23, Michael Glaesemann wrote:
>> 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.


On Oct 4, 2004, at 1:48 PM, Scott Marlowe wrote:

> We built an OpenLDAP server and wrote some scripts to maintain that and
> allow for group editing.  This structure existed completely outside of
> the either the database or application.  Then, apache handled all the
> authentication through ldap authentication.
<snip />
> This allows you to scale your authentication and group management
> independently of any scaling issues with your application servers.
> Since single master / multi slave OpenLDAP is a pretty easy thing to
> implement, the only applications that need to access the master can be
> set to the ldap editing applications (group editor, update scripts,
> etc...) while standard old authentication can be pointed at one or more
> slaves.


>> Method 2: Store username/password information as data in tables,
>> using pgcrypto for authentication

On Oct 4, 2004, at 1:53 PM, Jason Sheets wrote:

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

As with many things, there are tradeoffs. As I'm going to be running
the database server, I think I'm going to push the pgcrypto solution as
far as it will go. Thanks again, to both of you, for your comments.
Much appreciated!

Michael