From: | Grzegorz Jaskiewicz <gj(at)pointblue(dot)com(dot)pl> |
---|---|
To: | Andrew Chernow <ac(at)esilo(dot)com> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: user-based query white list |
Date: | 2008-12-06 18:41:26 |
Message-ID: | F0CE3C62-D0E1-4993-AC03-3108399FCAF0@pointblue.com.pl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Stark | 2008-12-06 18:48:31 | Re: Optimizing DISTINCT with LIMIT |
Previous Message | Andrew Chernow | 2008-12-06 18:30:59 | Re: user-based query white list |