Thread: Application user login/management
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
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
On Sun, 2004-10-03 at 22:23, 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. The method I worked with was similar to your method 3, of maintaining the info in tables, but more complex, and easier to handle for large numbers of users. 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. The application was give standard libs / includes that allowed for pushing a username up against any group, etc... So that all the yes / no of being allowed somewhere or allowed to do something was kept in the LDAP database. This allowed us to allow owners of given groups to edit them by themselves, i.e. the Director of Marketing could both add other junior admins to the marketing groups, and could edit members of all the marketing groups. Note that these groups / authentication are then accessible to all other applications in the company that are LDAP aware. And there's a lot of stuff that can work with LDAP and / or apache/http auth against 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.
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