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