On Tue, Nov 4, 2008 at 10:59 AM, Steve Atkins <steve@blighty.com> wrote:
>
> On Nov 4, 2008, at 9:21 AM, Webb Sprague wrote:
>
>> Hi all,
>>
>> I am writing an application that allows users to analyze demographic
>> and economic data, and I would like the users to be able to pick
>> columns, transform columns with functions (economists take the
>> logarithm of everything), and write customized WHERE and GROUP-BY
>> clauses. This is kind of like passing through a query to the DB in a
>> library catalog.
>>
>> Has anybody found a good way to do this, especially inside the
>> database from a plpgsql function (select * from custom_query('table1',
>> 'col1 > 100')) ? I don't want to just concatenate a user supplied
>> WHERE clause, at least without somehow checking the resulting
>> statement for (1) only one statement, (2) no data modification
>> clauses, and (3) only one "level" in the tree.
>>
>>
>> It seems like if I could interact with an SQL parser through a script,
>> I could accomplish this relatively easily. Perhaps SPI can help me
>> (give me hints!), though I don't really want to write any C. Perhaps
>> I am wrong about the possibility of this at all.
>>
>> I realize that roles and permissions can help protect the system, but
>> I still feel nervous.
>>
>> Has anybody done a similar thing, or tried? The problem is that if we
>> try to parameterize everything, then we don't really allow the kind of
>> data exploration that we are shooting for and these guys / gals are
>> smart enough to deal with a little syntax.
>
> If they're that smart, they're smart enough to deal with SQL, and
> likely to be frustrated by a like-sql-but-not command language or
> a GUI query designer.
>
> Instead, create a user that only has enough access to read data (and
> maybe create temporary tables) and use that user to give them
> a sql commandline.
>
> It'll be drastically less development effort for you, and the end result
> is less likely to frustrate your users.
>
> When I've done this I've also provided some useful plpgsql and sql
> functions for users to use, to wrap commonly needed transformations,
> and some views to hide parts of the data model they didn't need
> to know about.
This... Also, look into setting up replicant slave dbs for users to
hammer on so the main one doesn't get killed by a rogue query.
