Re: Using Postgresql as application server

On Tue, Aug 16, 2011 at 12:31 PM, Merlin Moncure <mmoncure(at)gmail(dot)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.

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

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.

Best Wishes,
Chris Travers