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

On Tue, Aug 16, 2011 at 4:04 PM, Chris Travers <chris.travers@gmail.com> wrote:
> On Tue, Aug 16, 2011 at 12:31 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>
>>>>
>>> Who enforces security and how?
>>
>> *) http wrapper (example node.js): check security in the wrapper.
>> presumably your application server would be keeping sessions state
>> independently of database session and would do verification on every
>> call.
>
> But here then you hare having to re-implement the whole security
> system yourself.  Does this give you a net benefit in complexity and
> performance over a standard middleware solution?  But moreover for
> this to work you have to include security information in meaningful
> tuples handed to the database, right?  I don't like this.  It sounds
> like a lot of complexity where a middleware solution for managing
> security would be far better.

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

>> *) stock pgbouncer: there is essentially no strong way of checking
>> security. what we ended up doing was modifying pgbouncer to keep track
>> of the client auth and building a query whitelist.  very simple and
>> effective.  we also added in support for listen/notify.  imagine
>> interacting directly with remote agents inside the psql console and
>> being able to join client provided data to other tables in the
>> database :-).  ad hoc sql obviously can't be allowed from an untrusted
>> source.
>
> Ok, so here you are extending the connection pooler itself to provide
> traditional middleware functions rather than implementing them in the
> database itself, right?  So this important app server function is not
> implemented in the database.

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.

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.

> The thing is that for small to midsize businesses, I think Pg makes a
> great centerpiece for an app server environment.  In the areas I am
> used to working in, even most larger businesses would be unlikely to
> need connection pooling.  It seems that if you get to the point where
> you need to use connection pooling you have to do what you are doing
> and implement your security no further back than the connection
> pooler. This makes the connection pooler, not PostgreSQL, the entry
> point for the application server environment.  It doesn't mean that Pg
> isn't taking on some of the load that middleware normally does at that
> point but it is no longer capable of *being* the middleware.

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.

merlin