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

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.

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.

> Saying 'that shouldn't be allowed to happen' is simply not the reality
> in many enterprise environments.  I don't see what's so complicated
> about storing who a person is and what they are allowed to do, and
> checking the permission just before that 'what' is about to get done.

No, I think the opposite.  If the RDBMS enforces security then any
client can connect and you don't have to worry about this problem at
all.  However if you push this into the RDBMS and use native
facilities, then you can't use connection pooling.
>

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

>
> sure. that is a distinction I guess you could make.  but the real
> point is all the zillions of LOC that just stupidly bounce data around
> -- kill em with fire, i say :-D.
>
We can agree with that part.

 Best Wishes,
Chris Travers