Re: user-based query white list

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
>
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>