[SQL] Create a trigger only for certain users

[Ezequiel Luis Pellettieri]

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

--

[Adrian Klaver]

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...@aklaver.com


--
Sent via pgsql-sql mailing list (pgsq...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[Ezequiel Luis Pellettieri]

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

[Ezequiel Luis Pellettieri]

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