Re: user-based query white list

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