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/