Thread: why is there no TRIGGER ON SELECT ?
Other than "It's currently not available", can anyone provide a logical explanation of why triggers cannot be implemented for SELECT statements, or rules for SELECT must be DO INSTEAD SELECT? PostgreSQL was derived from Ingres, and Ingres had a nice auditing feature that also handled SELECT. It would be simple enough to write a RULE or TRIGGER on a SELECT to just log access, but for some unexplainable reason (at least to my knowledge) this has been greatly restricted in PostgreSQL. I am sure many DBA's and developers would greatly appreciate the addition of a TRIGGER or RULE on SELECT, and it should be simple enough to change the code, so I am curious as to why this has never been done. Thanks in advance. Melvin Davidson |
Other than "It's currently not available", can anyone provide a logical explanation of why triggers cannot be implemented for SELECT statements, or rules for SELECT must be DO INSTEAD SELECT?
PostgreSQL was derived from Ingres, and Ingres had a nice auditing feature that also handled SELECT. It would be simple enough to write a RULE or TRIGGER on a SELECT to just log access, but for some unexplainable reason (at least to my knowledge) this has been greatly restricted in PostgreSQL. I am sure many DBA's and developers would greatly appreciate the addition of a TRIGGER or RULE on SELECT, and it should be simple enough to change the code, so I am curious as to why this has never been done.
Thanks in advance.
logging (auditing) code in it ?
--
// Dmitriy.
| Dmitriy >Why not use function which returns table and wrap the >logging (auditing) code in it ? Because to use a trigger function, you need a trigger, and as previously stated, you cannot have a trigger on select. The same applies for a rule. Melvin Davidson |
Dmitriy Because to use a trigger function, you need a trigger, and as previously stated, you cannot have a trigger on select. The same applies for a rule.
>Why not use function which returns table and wrap the
>logging (auditing) code in it ?
Why not select via regular function ?
CREATE OR REPLACE FUNCTION public.test_select()
RETURNS TABLE(id integer, name text)
LANGUAGE sql
SECURITY DEFINER -- note here!
AS $function$
SELECT 1, 'dima' UNION ALL
SELECT 2, 'melvin'; -- just for example I use simple union query
$function$
You can revoke SELECT privileges on table and
give to some users privileges on function (to
prevent them from selecting from table directly).
--
// Dmitriy.
Melvin: The proposal is to do something of the form
SELECT * FROM selecting_function()
And have selecting_function() perform any necessary auditing.
I guess this work fairly well – as long as you remember to remove “SELECT” privileges on the wrapped table from everyone and setup SECURITY DEFINER on the function itself.
For a normal “SELECT FROM table WHERE” construct the query planner is able to use indexes on “table” based upon the where clause. If you do “SELECT FROM func() WHERE” does the entire table always get scanned/returned or are indexes applied in this case as well? These (performance concerns) are the main reason that using a wrapping function is not intuitive. It would also require a slightly different paradigm for the end-user and would require rewriting to make it work in an existing system that was directly accessing tables before (I assume if access is being done via VIEWs that incorporating the function calls into the views would work just fine).
David J
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Melvin Davidson
Sent: Tuesday, February 22, 2011 8:47 AM
To: Dmitriy Igrishin
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] why is there no TRIGGER ON SELECT ?
Dmitriy Melvin Davidson |
| Dave and Dmitriy, I know a function can be used, but the point is to log a table whenever "someone else" does a SELECT on it. It cannot be depended on that a user will include that (or any specific function in a SELECT. iow, when any user does "SELECT ... FROM tablex;" then logging should occur. That cannot happen unless there a trigger of the form: CREATE TRIGGER tg_log_table AFTER SELECT ON tablex FOR EACH STATEMENT EXECUTE PROCEDURE log_table(); And yes, I know access the to table is restricted, but that is also not the point. Suppose an application requires that a user/client be charged for each time they access certain data? The point, and question is, not how to work around the problem, but rather, why cannot this functionality be implemented in triggerts and rules. Melvin Davidson
|
Melvin Davidson, 22.02.2011 15:42: > I know a function can be used, but the point is to log a table > whenever "someone else" does a SELECT on it. > > It cannot be depended on that a user will include that (or any > specific function in a SELECT. iow, when any user does "SELECT ... > FROM tablex;" then logging should occur. You can force users to use the function. Remove the SELECT privilege on the table for the user, create a view that uses the function and then grant select on theview to the users. Thus they won't even notice they are going through a function and you can still audit the SELECT. The function needs to be created with SECURITY DEFINER though. The downside of this is, that this only works if the result set isn't too large. Because all rows that are returned by thefunction will be first buffered on the the server before they are returned to the client. Regards Thomas
> -----Original Message----- > From: Melvin Davidson [mailto:melvin6925@yahoo.com] > Sent: Tuesday, February 22, 2011 9:43 AM > To: 'Dmitriy Igrishin'; David Johnston > Cc: pgsql-general@postgresql.org > Subject: Re: why is there no TRIGGER ON SELECT ? > > Dave and Dmitriy, > > I know a function can be used, but the point is to log a > table whenever "someone else" does a SELECT on it. > > It cannot be depended on that a user will include that (or > any specific function in a SELECT. iow, when any user does > "SELECT ... FROM tablex;" then logging should occur. > > That cannot happen unless there a trigger of the form: > CREATE TRIGGER tg_log_table AFTER SELECT ON tablex FOR EACH > STATEMENT EXECUTE PROCEDURE log_table(); > > And yes, I know access the to table is restricted, but that > is also not the point. Suppose an application requires that a > user/client be charged for each time they access certain data? > > The point, and question is, not how to work around the > problem, but rather, why cannot this functionality be > implemented in triggerts and rules. > > Melvin Davidson > > > Dmitriy > > >Why not use function which returns table and wrap the logging > >(auditing) code in it ? > > Because to use a trigger function, you need a trigger, and as > previously stated, you cannot have a trigger on select. The > same applies for a rule. > > Melvin Davidson > > Somewhat OT (but, m.b. related?): With every view there is: RULE "_RETURN" AS ON SELECT TO <view_name>... DO INSTEAD ... Which PG creates automatically (behind the scene), when view is created. Is there a way, or did anyone try to modify this Rule? It is after all "DO INSTEAD" Rule. Regards, Igor Neyman