Re: user-based query white list

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?

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