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
>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. |