Re: User privileges in web database applications

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.