Create a trigger only for certain users

pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed

Create a trigger only for certain users
4+ messages / 2 participants
[nested] [flat]

* Create a trigger only for certain users
@ 2016-05-11 14:12  Ezequiel Luis Pellettieri <ezequiel.pellettieri@gmail.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Ezequiel Luis Pellettieri @ 2016-05-11 14:12 UTC (permalink / raw)
  To: pgsql-sql

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

--

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Create a trigger only for certain users
@ 2016-05-11 14:22  Adrian Klaver <adrian.klaver@aklaver.com>
  parent: Ezequiel Luis Pellettieri <ezequiel.pellettieri@gmail.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Adrian Klaver @ 2016-05-11 14:22 UTC (permalink / raw)
  To: Ezequiel Luis Pellettieri <ezequiel.pellettieri@gmail.com>; pgsql-sql

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


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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Create a trigger only for certain users
@ 2016-05-14 00:26  Ezequiel Luis Pellettieri <ezequiel.pellettieri@gmail.com>
  parent: Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Ezequiel Luis Pellettieri @ 2016-05-14 00:26 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-sql

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
>



--

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Create a trigger only for certain users
@ 2016-05-16 19:53  Ezequiel Luis Pellettieri <ezequiel.pellettieri@gmail.com>
  parent: Ezequiel Luis Pellettieri <ezequiel.pellettieri@gmail.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Ezequiel Luis Pellettieri @ 2016-05-16 19:53 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-sql

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

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

end of thread, other threads:[~2016-05-16 19:53 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-05-11 14:12 Create a trigger only for certain users Ezequiel Luis Pellettieri <ezequiel.pellettieri@gmail.com>
2016-05-11 14:22 ` Adrian Klaver <adrian.klaver@aklaver.com>
2016-05-14 00:26   ` Ezequiel Luis Pellettieri <ezequiel.pellettieri@gmail.com>
2016-05-16 19:53     ` Ezequiel Luis Pellettieri <ezequiel.pellettieri@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox