Re: Create a trigger only for certain users

Hi! taking the doc example this is what I have done to log only changes
done by postgres user:

CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS
$emp_audit$
BEGIN
--
-- Create a row in emp_audit to reflect the operation performed on
emp,
-- make use of the special variable TG_OP to work out the operation.
--
IF (current_user) = 'postgres' THEN
IF (TG_OP = 'DELETE') THEN
INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;
RETURN NEW;
END IF;
END IF;
RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$emp_audit$ LANGUAGE plpgsql;

CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW EXECUTE PROCEDURE process_emp_audit();

Thank you very much for your help Adrian.
Cheers

Pelle.-

2016-05-13 21:26 GMT-03:00 Ezequiel Luis Pellettieri <
ezequiel(dot)pellettieri(at)gmail(dot)com>:

> Thank you so very much Adrian for your answer. I'm still in 9.2 but I'll
> give it a try on 1 and 2 and let you know how it goes.
> The main goal for this trigger is to audit activity in certain tables for
> only 2 users in the database.
> Cheers
> Pelle.-
>
> 2016-05-11 11:22 GMT-03:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>:
>
>> On 05/11/2016 07:12 AM, Ezequiel Luis Pellettieri wrote:
>>
>>> Hi guys, I'm going to build a trigger on update for a few tables, but I
>>> have to ignore updates for certain users. Is there a way to accomplish
>>> this task?
>>>
>>
>>
>> A thought, assuming plpgsql:
>>
>> 1) Use session_user/current_user:
>> http://www.postgresql.org/docs/9.5/interactive/functions-info.html
>>
>> 2) In BEFORE trigger code:
>> http://www.postgresql.org/docs/9.5/interactive/plpgsql-trigger.html
>>
>> test for the user and return NULL if there is a match:
>>
>> "Row-level triggers fired BEFORE can return null to signal the trigger
>> manager to skip the rest of the operation for this row (i.e., subsequent
>> triggers are not fired, and the INSERT/UPDATE/DELETE does not occur for
>> this row)."
>>
>> Another thought, assuming Postgres 9.5+:
>>
>> Use Row Level Security:
>>
>> http://www.postgresql.org/docs/9.5/static/sql-createpolicy.html
>>
>>
>>> Thanks in advance.
>>>
>>> Regards.
>>> Pelle.-
>>>
>>> --
>>> *
>>> *
>>> *
>>> *
>>> *
>>> *
>>>
>>
>>
>> --
>> Adrian Klaver
>> adrian(dot)klaver(at)aklaver(dot)com
>>
>
>
>
> --
>
>