Re: A mechanism securing web applications in DBMS

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