Обсуждение: User privileges in web database applications
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!
Antonis Christofides wrote: > 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. What stops the user code from issuing a "RESET SESSION AUTHORIZATION" command, say from a SQL injection, thus regaining superuser privileges? -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On Jun 29, 2006, at 3:37 AM, Antonis Christofides wrote: > The benefit of this solution is that > I avoid reconnecting to the database on each request, having instead a > persistent connection as user "postgres". But it is also a resource liability. How do you know if the user will make another request or when they are "finished" using your site? You can certainly time out the connection but for a busy site you will potentially be holding open a large number of connections that will never be used again. I agree with your reasons for wanting to do this, but it may not be feasible for a busy web site. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL
I'm coming in a bit late to this conversation, so forgive me if I've missed something. Isn't this problem the reason that connection pools were created? In a connection pool, connections are only associated with a particular user for the duration of a transaction. Once the transaction is complete, the connection goes back to the pool. Pool sizes are usually configurable, and the number of actual connections in the pool can be less than the configured amount. The pool is responsible for allocating blocks of connection - up to MAX - as necessary. The pool is also responsible for guaranteeing that a connection is valid when it's requested from the pool. This may involve periodic 'pinging' of unused connections, or simply testing, and recreating if nessecary, a connection before it's reserved for use. I'm pretty sure there is a pgpool project somewhere to provide exactly this service. Tim ___ Sent with SnapperMail www.snappermail.com ...... Original Message ....... On Thu, 29 Jun 2006 10:19:34 -0400 "John DeSoi" <desoi@pgedit.com> wrote: > >On Jun 29, 2006, at 3:37 AM, Antonis Christofides wrote: > >> The benefit of this solution is that >> I avoid reconnecting to the database on each request, having instead a >> persistent connection as user "postgres". > >But it is also a resource liability. How do you know if the user will >make another request or when they are "finished" using your site? You >can certainly time out the connection but for a busy site you will >potentially be holding open a large number of connections that will >never be used again. > >I agree with your reasons for wanting to do this, but it may not be >feasible for a busy web site.
On Jun 29, 2006, at 5:58 PM, Tim Hart wrote: > I'm coming in a bit late to this conversation, so forgive me if > I've missed > something. Isn't this problem the reason that connection pools were > created? > > In a connection pool, connections are only associated with a > particular > user for the duration of a transaction. Once the transaction is > complete, > the connection goes back to the pool. Right, this is standard operating procedure. But the original poster mentioned tying the connection to a particular web user/session. In other words, one connection per user. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL
> Right, this is standard operating procedure. But the original poster > mentioned tying the connection to a particular web user/session. In > other words, one connection per user. Maybe I didn't phrase the question correctly, but I think that the answer to my question is, indeed, connection pooling, probably pgpool. Thanks all. -- Antonis Christofides +30-2107722840 (work) +30-2106521785 (home) +30-6979924665 (mobile)
Both connection pooling and using the superuser with SET SESSION AUTHORIZATION both have their uses. You might have an application that processes some type of transaction and inserts data into a users schema or table, but where there are no user credentials available. Then you might have a web interface for users to access that data where user credentials are available. We have this type of setup and we use a superuser with SET SESSION AUTHORIZATION for the incoming transactions. But we also have extensive security requirements that demand we do things most people don't do. Full security/code audits every quarter, peer review and full testing for any new code, hardware encryption for sensitive data and keys stored on tokens, client certificate authentication for all web access, restrictive firewall, etc.. Bottom line is that I'm paranoid about using SET SESSION AUTHORIZATION, but it does have it's uses and can be used safely.
Antonis Christofides wrote: >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. > Couldn't agree more. But consider this reasoning as perhaps more fundamental. For a database app all security resolves to the basic permissions of a single user being allowed to insert, update, delete or select any particular row from any particular table. Every security system that is implemented on some other basis will have to be resolved down to this. So why not just implement this in the first place? Our own approach was to build security directly into the specification. Our table definitions include security definitions, which groups can do what do the table. The generator builds the security commands the same way it builds the CREATE TABLE commands. >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 considered this. How is it working out in real life? We connect using real user credentials, and where necessary elevate to super-user, which I think is probably marginally safer but more expensive. >I've even written triggers to implement >row-level permissions checking. > Yeah, this is cool. What kind of features have you implemented here? We've just done the very basics, not much to brag about.