Re: [GENERAL] cutting out the middleperl
Or SQL-on-rails http://www.sqlonrails.org/ LOL! merlin Heh heh...insufficiently AJAX-y ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] cutting out the middleperl
On Mar 27, 10:34 am, [EMAIL PROTECTED] (Aidan Van Dyk) wrote: Sounds something like mod_libpq: http://asmith.id.au/mod_libpq.html Thanks, I appreciate the link...also looks promising. Kev ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] cutting out the middleperl
On 3/27/07, Aidan Van Dyk [EMAIL PROTECTED] wrote: 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. Sounds something like mod_libpq: http://asmith.id.au/mod_libpq.html brilliant. highest possible marks! i mean, wow! :-) merlin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] cutting out the middleperl
On 22 Mar 2007 14:58:15 -0700, Kev [EMAIL PROTECTED] 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. 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. 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? 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. IMO, I think 'thin middleware' approach is a great way to design applications...so you are right on the money. The web server. IMO, should be mostly concerned about rendering html. I don't think eliminating the middleware is really practical. While you could use a thick-client javascript framework like GWT and write your queries in javascript (getting data back via json), I don't think it's really possible to secure this properly without killing the 'ease of implementation' factor. Then again, it's no worse then your typical old school visual basic or delphi in-house application so common in the 90's. I really miss the simplicity of Delphi. merlin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] cutting out the middleperl
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] cutting out the middleperl
Kenneth == Kenneth Downs [EMAIL PROTECTED] writes: Kenneth This in effect makes the web server a proxy to the database, which Kenneth sounds like what you are after. The P portion for us is PHP, not Kenneth Perl, and it is small though non-zero. It has only two jobs really. Kenneth In the one direction it converts HTTP requests into SQL, and in the Kenneth other it converts SQL results into HTML. How do you control trust? I presume you're not accepting raw SQL queries (or even snippets) over the wire, so you have to have enough server-side mapping code to map domain objects into database objects and domain verbs into queries, and then authenticate and authorize that this verb is permitted by the incoming user. That can't be just a trivial amount of code. That's usually a serious pile of code. And please don't tell me you do all of that client-side. :) -- Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095 merlyn@stonehenge.com URL:http://www.stonehenge.com/merlyn/ Perl/Unix/security consulting, Technical writing, Comedy, etc. etc. See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] cutting out the middleperl
On 3/27/07, Randal L. Schwartz merlyn@stonehenge.com wrote: Kenneth == Kenneth Downs [EMAIL PROTECTED] writes: Kenneth This in effect makes the web server a proxy to the database, which Kenneth sounds like what you are after. The P portion for us is PHP, not Kenneth Perl, and it is small though non-zero. It has only two jobs really. Kenneth In the one direction it converts HTTP requests into SQL, and in the Kenneth other it converts SQL results into HTML. How do you control trust? I presume you're not accepting raw SQL queries (or even snippets) over the wire, so you have to have enough server-side mapping code to map domain objects into database objects and domain verbs into queries, and then authenticate and authorize that this verb is permitted by the incoming user. That can't be just a trivial amount of code. That's usually a serious pile of code. And please don't tell me you do all of that client-side. :) looking at his project, it looks like you create tables and forms using simple rule based system. very elegant imo, although I would greatly prefer to to have the rules be in tables them selves, so I can manipulate with sql, or self hosting dialogs. very interesting project i must sasy, it feels like alphora but with web spin on it. merlin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] cutting out the middleperl
Randal L. Schwartz wrote:
Kenneth == Kenneth Downs [EMAIL PROTECTED] writes:
Kenneth This in effect makes the web server a proxy to the database, which
Kenneth sounds like what you are after. The P portion for us is PHP, not
Kenneth Perl, and it is small though non-zero. It has only two jobs really.
Kenneth In the one direction it converts HTTP requests into SQL, and in the
Kenneth other it converts SQL results into HTML.
How do you control trust? I presume you're not accepting raw SQL queries (or
even snippets) over the wire, so you have to have enough server-side mapping
code to map domain objects into database objects and domain verbs into
queries, and then authenticate and authorize that this verb is permitted by
the incoming user. That can't be just a trivial amount of code. That's
usually a serious pile of code.
In a proxy or quasi-proxy situation the simplest scenario is direct
table access, all other scenarios are more complicated and reduce to
table access in the end. So because the problem must be considered in
terms of table access we ask what is required to pull that off, and the
answer is:
a) the database is implementing security
b) users are using real accounts instead of connecting as a superuser
and having the client do the security
When this is the case, there are only two implementation issues. The
first is how to manage trust (or authentication), and the second is the
mundane issue of how to encode the queries.
Just a couple of weeks ago we discussed the trust issue, it comes down
to the known design tradeoffs off HTTPS, sessions, dongles, user habits
and so forth. 'nuf said on that.
As for the mundane question of how to encode the queries, the KISS
principle says they will come over looking like HTML FORM actions (post
or get). So you'll have a list of input values with some hidden
variables that control the action.
You need precious little code to translate these into SQL if you have a
description of the database, we use the old-fashioned term data
dictionary for this. Our data dictionary lists the column names, types
and sizes for each table (among other things). Since all simple SQL
commands are lists of column names and values, the SQL generation is
child's play. Our typical code might look like this:
if(gp('gp_mode')=='ins') { // gp() retrieves a get/post variable
$rowvalues=aFromGP(txt_); // convert group of post vars into an
associative array
$table=gp('gp_table'); // fetch the table name from the stream
SQLX_insert($table,$rowvalues); // this routine generates an insert
statement
}
The server will throw an error for constraint violations or security
violations, the web layer doesn't concern itself with these things
except to report them.
The only thing the web layer need do is handle the escaping of quotes to
prevent SQL injection, but again, this is only to prevent the user from
shooting himself in the foot, anything he injects we'd be happy to
execute for him, since it all runs at his security level!
The shocking conclusion from points a) and b) at the top of this reply
is this: there is absolutely no difference, from a security
perspective, between these this HTTP request:
index.php?gp_table=examplegp_mode=instxt_colname=valuetxt_colname=value
and this one:
index.php?gp_sql=insert+into+example+(column1,column2)+values+(value1,value2)
Amazing! The simple fact is the user is either authorized to execute
the query or he isn't. If you connect to the database using his
credentials then let him inject all the SQL he wants, if that's his idea
of fun.
And please don't tell me you do all of that client-side. :)
Well, since you said please, and since we don't do it, I won't say it.
Re: [GENERAL] cutting out the middleperl
Merlin Moncure wrote: On 3/27/07, Randal L. Schwartz merlyn@stonehenge.com wrote: Kenneth == Kenneth Downs [EMAIL PROTECTED] writes: Kenneth This in effect makes the web server a proxy to the database, which Kenneth sounds like what you are after. The P portion for us is PHP, not Kenneth Perl, and it is small though non-zero. It has only two jobs really. Kenneth In the one direction it converts HTTP requests into SQL, and in the Kenneth other it converts SQL results into HTML. How do you control trust? I presume you're not accepting raw SQL queries (or even snippets) over the wire, so you have to have enough server-side mapping code to map domain objects into database objects and domain verbs into queries, and then authenticate and authorize that this verb is permitted by the incoming user. That can't be just a trivial amount of code. That's usually a serious pile of code. And please don't tell me you do all of that client-side. :) looking at his project, it looks like you create tables and forms using simple rule based system. very elegant imo, although I would greatly prefer to to have the rules be in tables them selves, so I can manipulate with sql, or self hosting dialogs. very interesting project i must sasy, it feels like alphora but with web spin on it. Actually we do put the rules in the tables and you can execute SQL directly, something that I so much take for granted now that I sometimes have to remind myself that most of the world cannot do this! If the website is not giving that impression, I'll have to correct that, ouch! Can you tell me what gave you the impression we were just about web forms? Thanks for the comments, elegant, now that's something I'll have to forward to Mom :) merlin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] cutting out the middleperl
On 22 Mar 2007 14:58:15 -0700, Kev [EMAIL PROTECTED] 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. 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. 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? 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. IMO, I think 'thin middleware' approach is a great way to design applications...so you are right on the money. The web server. IMO, should be mostly concerned about rendering html. I don't think eliminating the middleware is really practical. While you could use a thick-client javascript framework like GWT and write your queries in javascript (getting data back via json), I don't think it's really possible to secure this properly without killing the 'ease of implementation' factor. Then again, it's no worse then your typical old school visual basic or delphi in-house application so common in the 90's. I really miss the simplicity of Delphi. merlin Hi Merlin, Thanks for your reply, these are helpful comments. Just wondering about the security factor, though--is there something specific that would be impossible to lock down? I would think (but I'm no expert to be sure!) that a whitelist-only filter, I mean, if there weren't any buffer overflow vulnerabilities or anything like that, would be tough to sneak malicious SQL functions or other code through. Or did you mean in some other way? I don't think I would pass straight SQL queries anyway, if it makes a difference, but rather just the function name and the parameters separately, so that there's always an SQL function involved, but so that that's not obvious from the javascript end. All that's obvious is the thin-perlness of it: my perl script could be calling another script by the name we pass it, or a perl subroutine, or something else. Heh...Delphi was fun, except when I wanted to do some fancier things with it, even in high school...although our low-budget high school didn't exactly have the latest major version. Thanks, Kev ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] cutting out the middleperl
Hi Kenneth, This is wonderful news. I will definitely be checking into it as soon as I have a moment. Thanks! Kev 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] cutting out the middleperl
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. Sounds something like mod_libpq: http://asmith.id.au/mod_libpq.html ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] cutting out the middleperl
On Mar 27, 2007, at 7:34 AM, Aidan Van Dyk wrote: 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. Sounds something like mod_libpq: http://asmith.id.au/mod_libpq.html Or SQL-on-rails http://www.sqlonrails.org/ Cheers, Steve ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] cutting out the middleperl
On 3/27/07, Steve Atkins [EMAIL PROTECTED] wrote: Or SQL-on-rails http://www.sqlonrails.org/ LOL! merlin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] cutting out the middleperl
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. 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. 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? 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
