Re: Application user login/management - Mailing list pgsql-general
From | Jason Sheets |
---|---|
Subject | Re: Application user login/management |
Date | |
Msg-id | 4160D747.8010006@idahoimageworks.com Whole thread Raw |
In response to | Application user login/management (Michael Glaesemann <grzm@myrealbox.com>) |
List | pgsql-general |
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
pgsql-general by date: