Re: Using Postgresql as application server

In this war no one is looking at APEX from oracle. Oracle have implemented
the whole webserver to DBMS gateway and web development and a web based
business solutions in the database it self. They are using pl/sql for this.
Many users are using it and found it stable and scalable. Important is that
web server is external to the database and a mod_pgsql like mod_plsql is
used to connect web server to database. Each page is considered as a stored
procedure in the oracle database. I am not thinking of implementing as it is
in postgresql but we can use plpgsql and pl/python to do some tasks that are
required not only for web server but for daily tasks also such as sending
emails, checking for data files from remote location, using FTP or other
protocols and connecting to other systems.
Web server must be external but most of the application functionality can be
implemented using database stored procs and functions.

Chaitanya Kulkarni

On Wed, Aug 17, 2011 at 4:44 AM, Chris Travers <chris(dot)travers(at)gmail(dot)com>wrote:

> On Tue, Aug 16, 2011 at 3:51 PM, Merlin Moncure <mmoncure(at)gmail(dot)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
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>