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(at)krosing(dot)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.