* Zhaomo Yang (zhy001@cs.ucsd.edu) wrote:
> I am surprised to hear this too. :) We haven't talked to many web
> developers yet and this is one of the things we need to do in the
> future.
Certainly an excellent idea to talk to your target audience. :)
> The goal of this mechanism is to add another layer of protection
> inside DBMS so that even if the application server is compromised the
> users' data is under protection*. This requires DBMS to be able to
> authenticate application-level users (know which application-level
> user it is communicating with). That it, we need to move the
> authentication logic of application-level users into DBMS. For this
> purpose, using store procedures (or something similar) is a must. I
> think even if a security mechanism is designed to be easy to use, it
> will still require some expertise.
I agree that good security does require expertise to get right.
> * this mechanism can't help if the attackers control the app server
> completely and the users are not aware of that and keep using the app.
> In that case the attackers will be able to collect all the credentials
> of the users who log in while they are in charge.
This is really the crux of the problem you're trying to solve- what is
the attack vector? Based on the discussion so far, I imagine you're
considering the "attacker can submit arbitrary SQL" situation, where the
attacker doesn't have full access to the application server. That's
certainly a worthwhile thing to consider, but I'm not entirely sure the
approach you've outlined will work out well..
> > If a temp table is being used then dynamic SQL may be required and therefore
> > a plpgsql function will be involved to handle looking up the current user, as you
> > won't be using PG roles.
>
> This is why I'd like to have global temp table in PG. With that we can
> probably get around of dynamic SQL.
Have you considered just using a regular, but unlogged, table? That
would also avoid any risk that the application manages to drop or shadow
the temp table somehow with a "fake" table that changes who is currently
authenticated, and avoids having to figure out how to deal with the temp
table vanishing due to the connections going away.
> You are absolutely right. I should've explained it better. I just
> wanted to show how authentication works and skipped all the hashing
> part.
Ah, ok.
Thanks,
Stephen