Hi, Most web database applications I've seen use a system separate from the rdbms's user database for managing user privileges. For example, there may be a "users" table, or there may be external authentication with, say, LDAP. Or, for example, in MoinMoin (an extensible wiki, where apps can be written as MoinMoin plugins), you can use MoinMoin's authentication system. The application does the authentication, and it also implements permissions checking.
But I think that checking user privileges at the database level is better. I think it's simpler and more secure, and if later you also want to create nonweb apps, you won't have any more authentication/privilege headaches. For this reason, in a web app I've made, the app connects to the database as user postgres, and after authenticating (receives user's password, checks with pg_shadow, and uses session cookie) uses "set session authorization" in order to lower its privileges. I've even written triggers to implement row-level permissions checking. The benefit of this solution is that I avoid reconnecting to the database on each request, having instead a persistent connection as user "postgres". One disadvantage, however, is that, since I use identd to allow the web server user (www-data) to connect as postgres, a web server compromise shall mean a compromise of the database as user postgres. I'm considering developing applications with MoinMoin, and I'd like to have a unified user database used both by the wiki and by the database. I can probably hack MoinMoin to use postgresql for authenticating. But how may I have persistent database connections without the security risk I described? Another issue is that I may have thousands of users, as is common in open web-accessible databases; could this be a problem for PostgreSQL? I'd also like your general opinion or pointer on the issue of authentication and privilege checking of web db apps; all I can find on Google is tutorials that tell you how to create a "users" table and do all checking at the application level. Thanks! ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org