Re: user-based query white list

Grzegorz Jaskiewicz wrote:
> 
> On 2008-12-06, at 18:30, Andrew Chernow wrote:
> 
>> Grzegorz Jaskiewicz wrote:
>>> On 2008-12-06, at 18:21, Andrew Chernow wrote:
>>>> Looking for a way to limited a user to a specific set of queries.  I 
>>>> don't think this can be done right now ... or can it?  Has this 
>>>> feature request surfaced in the past?
>>>>
>>>> I currently need this as an extra security measure for a libpq 
>>>> client app (want to block arbitrary queries from malicious 
>>>> attackers).  The easiest way I found was to add some query_string 
>>>> checks into backend/tcop/postgres.c for the 'Q' and 'P' commands in 
>>>> PostgresMain().  Seems to work just fine.  If it doesn't match, I 
>>>> issue an ereport FATAL since that is seen as a "malicious query 
>>>> execution attempt".
>>>>
>>>> I think it is something rather simple to design/implement (probably 
>>>> use a table of user allowed queries, support regex matches, etc.. 
>>>> loaded at session startup and SIGHUP).
>>> Can it be done with views, and adjusting permissions so user is only 
>>> allowed to use few views ??
>>
>> Not sure.  The client I am working on only calls functions, small API 
>> to interact with (no knowledge of views or tables).  Even if that were 
>> not the case, would views stop a client from sending in other queries, 
>> like "SELECT 1+1" or something that could bog down the server?
> 
> 
> I use views to simplify code. Say you have a simple join, with one 
> WHERE. You omit the WHERE in view, and leave it like that. Than just 
> select foo1, foo2 from VIEW WHERE boo1=foo1 and foo3 <> '123';
> Postgresql is smart enough, to run it as one query (as oppose to mysql), 
> so the code is simpler, everybody's happy.
> 
> If you want to continue on that discussion, I suggest we move it to 
> pg-general.
> 
> 

I don't think view-based security solves my problem.  I need to limit a user to 
20 fixed queries, for example.  That means the user cannot execute "SELECT 
NOW()" or "SELECT 'hello world'".  The user can only execute a pre-defined list 
of queries.

-- 
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/