Re: user-based query white list

Merlin Moncure wrote:
> There is extra safety from using whitelists...
> 
> For one, it's trivial to write a query that consumes unlimited CPU
> resources that accesses no built in tables or functions.  There are
> various other dangerous things that are difficult to lock down like
> temp tables.
> 
> Assuming you can handle paramaterized queries on the client, a
> whitelist is pretty easy and powerful safeguard on top of the normal
> protections.  Your biggest concern is malformed protocol messages or
> parameters and there are extra possible defenses there.
> 
> A whitelist is trivial to implement.  So the question is: is the OP
> suggesting how one could be done and if so, does it make it safe to
> allow ssl connections from $WORLD.
> 
> merlin
> 
> On 12/7/08, Hannu Krosing <hannu@krosing.net> wrote:
>> On Sat, 2008-12-06 at 13:30 -0500, 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).
>> Then grant access to those functions only.
>>
>>> 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?
>> Use statement_timeout GUC to prevent bogging
>>
>> ------------
>> Hannu
>>
>>
>
>>
> 
> 

I think what is missing is a way to deny the execution of queries that 
don't operate on an object (like a table, sequence, role, schema, 
etc...), OR queries not covered by the priv system.  Object-based 
queries can be locked down using the existing priv system.  Not sure if 
denying non-object related queries would work; what happens when you 
call "SELECT NOW()" within an allowed function?

Andrew Chernow
esilo, LLC.