Subject: Re: Create a trigger only for certain users
To: Ezequiel Luis Pellettieri <ezequiel.pellettieri@gmail.com>,
 pgsql-sql@postgresql.org
References: 
 <CAGsySmgA1hEa_viRUZGbgJp7ZfGDQWXmcSSjUtyyCA7uvED01w@mail.gmail.com>
From: Adrian Klaver <adrian.klaver@aklaver.com>
Message-ID: <e2c2bbac-4279-f680-f4d6-e093de9ae611@aklaver.com>
Date: Wed, 11 May 2016 07:22:15 -0700
User-Agent: Mozilla/5.0 (X11; Linux i686; rv:45.0) Gecko/20100101
 Thunderbird/45.0
MIME-Version: 1.0
In-Reply-To: 
 <CAGsySmgA1hEa_viRUZGbgJp7ZfGDQWXmcSSjUtyyCA7uvED01w@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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