Re: Using Postgresql as application server - Mailing list pgsql-general

On Tue, Aug 16, 2011 at 6:14 PM, Chris Travers <chris.travers@gmail.com> wrote:
> On Tue, Aug 16, 2011 at 3:51 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>
>>
>> /shrug.  pretty much every project I've ever worked on application
>> security has been ad hoc, database driven, not very complicated, and
>> not a performance bottleneck.  By the way, I think the opposite of
>> you: security information relating to application roles and actions
>> *should* be stored in the database (it is, after all, data) even if it
>> is enforced by a classic middleware.  What happens when some other
>> application, written by another team, connects to the database?
>
> Not understanding my perspective.  Ideally you'd use the RDBMS's
> functionality directly to enforce security via GRANT and REVOKE
> statements.  Whether it is stored in the database or not is for the
> RDBMS to decide.

GRANT/REVOKE only constrain read/write privileges to a database.
Application level security is typically much finer grained than that.
Also, I using SQL roles for actual user roles is not typically done
for various reasons.  Generally, SQL roles are used to define 'what'
is logging in, no necessarily 'who'.  If you allow and SQL through
from the application then table level security becomes very
important...otherwise not so much.

> What I am saying is that the further back you enforce the security the
> more you can guarantee consistent enforcement across applications.
> Connection pooling makes this much harder because you can't enforce it
> within the db using the normal methods and end up having to implement
> it all over.  Instead you have to implement security before the data
> hits the database.  That's a big difference and it has HUGE
> ramifications for security exposure vs utility of an application.

That is totally incorrect.  pgbouncer maintains separate pools for
each role and only intermingles queries for like roles.  Any
intelligent connection pooler would do the same. You lose access to
database session features but database level security features are
still enforced.  Whether you connection pool or not really doesn't
play into this from my point of view.

Recall that in our hypothetical 'database as middleware' database, the
main tables and there data are not actually in the database -- the
only tables available to query would be session state, etc.  Most
operations would funnel through back to the main database through
procedures and application level security would be checked there.
Now, if you want your system to be simple, tight, and fast, you could
combine those two databases but would have to figure out how to manage
security to a libpq speaking application.   Like I said, in my case I
did this with a whitelist, but it's not the only way.

>> well, not exactly.  it is a concession to security.  allowing
>> untrusted entities to send ad hoc sql to a database is obviously not
>> going to fly so it must be dealt with appropriately.  note pgbouncer
>> (or node.js etc) is not defining or handling session auth, just
>> playing a small role enforcement.  an auth'd application service
>> requests are essentially protocol noise and I see no problem letting
>> the protocol handler bounce them out.  also, whatever you happen to
>> wrap your 'middleware' database is still part of the middleware.
>
> Well, what you are actually doing here is enforcing security on a
> level of abstraction away from the database.  This means that you
> can't allow ad hoc queries because you can't guarantee safety.  I
> don't know what you get by doing this instead of providing
> interface-level security in the part of your middleware.  In fact
> that's essentially what you have to do, is it not?

If you expose (as I did) your middleware api as a 100% sql function
interface, then yes ad hoc sql is not allowed.   If you wrap your
middleware with a http server than ad hoc sql would not be allowed.  I
doubt the day will come where the browser will be sending ad hoc SQL
queries directly to a database.

The reason to use a database backend to handle middleware functions is
based on the strength of the SQL language, supported by the various PL
extensions you may want to include, to manage various everyday
programming tasks.  The security discussion is a bit of a sideshow
because it is generally a tiny fraction of the coding that typically
happens at this level.  An individual's personal appraisal of this
idea will largely depend on certain personal factors that will vary
from developer to developer.  An unbiased analysis would probably
conclude that it is an interesting, but unproven approach with a lot
of potential.

>> also I think most people would not go the libpq route even though
>> listen/notify allows you to create beautifully interactive systems --
>> mainly due to weirdness of the whole thing and the amount of work you
>> have to do to get it safe.
>
> Why?  Each listener has to have its own connection, right?  Otherwise
> there is nowhere to send the notifications to.  That connection has to
> be attached to a db role.  The DB role has to have permission to some
> portions of the database but not others, right?  I don't see why that
> is hard to make safe.

It's hard to make safe because allowing applications to speak libpq
means you have to be worried about various nasty things untrusted SQL
can do to your database server.  In the end, I think the only
reasonable way to do this is a whitelist.

merlin