why is there no TRIGGER ON SELECT ?

Hey Melvin,

2011/2/22 Melvin Davidson <melvin6925@yahoo.com>

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.

Why not use function which returns table and wrap the
logging (auditing) code in it ?

Melvin Davidson

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

2011/2/22 Melvin Davidson <melvin6925@yahoo.com>

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.

Yes, you can't. But why do you need a *trigger* function and trigger ?
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).

Melvin Davidson

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

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

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  

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

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