Thread: Create a trigger only for certain users
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?
Thanks in advance.
Regards.
Pelle.-
--
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.klaver@aklaver.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.klaver@aklaver.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.klaver@aklaver.com
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.pellettieri@gmail.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.CheersPelle.---2016-05-11 11:22 GMT-03:00 Adrian Klaver <adrian.klaver@aklaver.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.klaver@aklaver.com