Kev, we have a GPL'd product targeting Postgres that has significant
overlap with what you want, though in other areas we went in another
direction. The site is www.andromeda-project.org, and I've put some
comments below:
Kev wrote:
Hi everyone,
I'm still in the design phase of a project. I was just wondering if
anyone has any thoughts or experience on the idea of cutting the P out
of the LAMP (or in my case, WAMP for now) stack. What I mean is
having
everything encapsulated into sql (or plpgsql or plperl where needed)
functions stored in the pgsql server, and have Apache communicate with
pgsql via a tiny C program that pretty much just checks whether the
incoming function is on the allowed list and has the proper data
types,
then passes it straight in. Any errors are logged as potential
security
breaches.
Andromeda's goal is to implement all biz rules: constraints, automations
and security, in the server.
This in effect makes the web server a proxy to the database, which
sounds like what you are after. The "P" portion for us is PHP, not
Perl, and it is small though non-zero. It has only two jobs really. In
the one direction it converts HTTP requests into SQL, and in the other
it converts SQL results into HTML.
In terms of experience, I sat down to write the first code 33 months
ago, and it began to pay my bills about six months later. All of the
commercial bragging stuff is on the company website: http://www.secdat.com.
I'm really new to mod_perl too, so another question would be if this
would be much faster than a simple perl script that did the same
thing.
Can't say there. My personal preference is for PHP because I can't
understand Perl five minutes after I've written it.
I ask this because I realize I need to carefully check data coming
into
pgsql functions as well as at the client end. Why maintain a bunch of
scripts with names similar to the functions they're calling and all
performing similar checks anyway?
Well actually we tackled that problem by decided to *preserve* direct
table access through SQL as the standard API, which I realize is not the
standard, but for the life of me I can't understand why, since it is
such an amazingly simpler way to get what everyone says they are after.
Here's what I mean. We write out a database spec in a plaintext file
that includes security, constraints, and automations. A "builder"
program then generates the DDL, encodes the biz logic in triggers, and
assigns table sel/ins/upd/del permissions to the tables.
No messy API to remember or manage. Just specify the tables and
columns, who can do what, and what the formulas are, and its all automatic.
A huge benefit to this is the basic ability to manipulate user's
databases through direct SQL.
It's also IMHO the only way to ensure that you can accomplish the task
of having the web server be a proxy. Its easy to convert HTTP into
simple SQL insert/update etc., much harder to make it try to learn an API.
I was kinda salivating at the thought of how fast things would be if
you
cut out the A as well, by using a Flash applet to give socket access
to
JavaScript. But then I guess you have to make your pgsql server
itself
publicly accessible on some port. Is that just asking for trouble?
I appreciate any comments or thoughts anyone might have on this.
Thanks,
Kev
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
