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:
<snip />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.
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
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
