Re: A mechanism securing web applications in DBMS

Stephen,

> As an FYI- we generally prefer inline responses rather than top-posting on the PostgreSQL mailing lists.  Thanks.

Sorry for that.

> > - Try to make our mechanism as simple as possible.
> > Web application developers have all kinds of backgrounds. If the
> > security mechanism is too alien to them, they wouldn't use it.
> I'm surprised to hear this and a suggestion to used stored procedures in
> the same email- SPs are generally considered 'foreign' to the web
> developers that I've talked to. :)  That said, I'll grant that there are
> generally two camps: those who expect a database to only have BerkleyDB
> level key/value capabilities, and those who know what they're doing and
> what relational databases and SQL are all about.  The latter (and clear
> minority) group will take advantage of these capabilites, certainly,
> regardless of how they are expressed and are likely already comfortable
> using stored procedures and database-level roles.

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.

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.

* 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.

> 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.

> > (3) CREATE AUTHENTICATION FUNCTION
> > In our mechanism, we ask web application developers provide an
> > authentication function which normally takes user id and password as
> > inputs and returns a row containing all the identifiers (attributes)
> > of the corresponding application-level user. Let us call the place
> > storing the current application-level user's identifiers as
> > "identifier store".
> I would *strongly* advocate *against* passing the password to the
> database in any (non-hashed) form.  You are much better off using a
> one-way hash as early as possible in the stack (ideally, in whatever
> system initially receives the password on the server side) and then
> comparing that one-way hash.  Of course, passwords in general are not
> considered secure and one-time passwords, hardware tokens, or PIV /
> HSPD12 / CAC cards with client-side certificates.

You are absolutely right. I should've explained it better. I just
wanted to show how authentication works and skipped all the hashing
part.

Thanks,
Zhaomo

On Fri, Sep 5, 2014 at 5:52 PM, Stephen Frost <sfrost@snowman.net> wrote:
> Zhaomo,
>
>   As an FYI- we generally prefer inline responses rather than
>   top-posting on the PostgreSQL mailing lists.  Thanks.
>
> * Zhaomo Yang (zhy001@cs.ucsd.edu) wrote:
>> (1) Two philosophies important to our design
>> - Try to not force web application developers to make too many changes
>> to their apps if they wa.
>
> That's certainly fair.
>
>> - Try to make our mechanism as simple as possible.
>> Web application developers have all kinds of backgrounds. If the
>> security mechanism is too alien to them, they wouldn't use it.
>
> I'm surprised to hear this and a suggestion to used stored procedures in
> the same email- SPs are generally considered 'foreign' to the web
> developers that I've talked to. :)  That said, I'll grant that there are
> generally two camps: those who expect a database to only have BerkleyDB
> level key/value capabilities, and those who know what they're doing and
> what relational databases and SQL are all about.  The latter (and clear
> minority) group will take advantage of these capabilites, certainly,
> regardless of how they are expressed and are likely already comfortable
> using stored procedures and database-level roles.
>
>> (2) Why we need to cache application-level users' identifiers
>> We want to differentiate application-level users in DBMS, but not by
>> creating a DB user (or role in PG's terminology ) for every
>> application-level user, otherwise there will be all sorts of problems
>> when the number of application-level users is greater than a threshold
>> (e.g. catalog, as you mentioned).
>
> While I agree that this can be an issue when things scale up, you *can*
> address it by sharding the database based on user.  Even so though, I
> agree that PG would do well to improve the situation around this.
>
>> Instead, we still use one DB user
>> representing all the application-level users, just as how web apps
>> work now. Besides the identifiers (attributes) of a application-level
>> user are stored in some private place of the corresponding session
>> (e.g. temp table) when the application-level user authenticates so
>> that the DBMS can differentiate application-level users. (Connection
>> pooling should be fine as long as an application session doesn't
>> return its connection until it finishes. )
>
> Fair enough, and the RLS capabilities which are being added to PG will
> support this approach.  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.
>
>> Normally, a web application authenticates an application-level user by
>> making a SELECT query with the user provided user id and password on
>> the password table to see if there is a match (Of course this is an
>> over simplified version of how authentication works. ).  Using our
>> mechanism, the web application instead calls the authentication
>> function, which does a SELECT on the table first, and store the
>> identifiers of that application-level user somewhere if a match found.
>> The identifiers of the current application-level user are referenced
>> by the policies so that fine-grained access control can be enforced.
>
> That 'somewhere' is certainly something that PG could improve upon- we
> don't have SQL-level variable capability today and this means that temp
> tables have to be used, which is certainly unfortunate.  I'd love to see
> work done to improve this situation.
>
>> (3) CREATE AUTHENTICATION FUNCTION
>> In our mechanism, we ask web application developers provide an
>> authentication function which normally takes user id and password as
>> inputs and returns a row containing all the identifiers (attributes)
>> of the corresponding application-level user. Let us call the place
>> storing the current application-level user's identifiers as
>> "identifier store".
>
> I would *strongly* advocate *against* passing the password to the
> database in any (non-hashed) form.  You are much better off using a
> one-way hash as early as possible in the stack (ideally, in whatever
> system initially receives the password on the server side) and then
> comparing that one-way hash.  Of course, passwords in general are not
> considered secure and one-time passwords, hardware tokens, or PIV /
> HSPD12 / CAC cards with client-side certificates.
>
>> The whole point of this CREATE AUTHENTICATION FUNCTION syntax is to
>> reduce developers' work.  By giving developers very specific
>> instructions on how to write an authentication function, we hope they
>> would find it easy to write one. Admittedly, however, what CREATE
>> AUTHENTICATION FUNCTION does can be achieved by CREATE FUNCTION.
>
> I don't see how this is particularly better than simply providing a
> function-creating-function (if there is really a concern that creating
> two functions instead of just the one is a serious complication..) or,
> better yet, creating an extension which creates all the functions,
> tables, etc necessary for this system.
>
>         Thanks!
>
>                 Stephen