Re: why is there no TRIGGER ON SELECT ?

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 the view 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 the function will be first buffered on the the server before they are returned to the client.

Regards
Thomas