Re: When use triggers?

[Berend Tober <btober@xxxxxxxxxxxx>]

Michael Stephenson wrote:

On Wed, May 16, 2018 at 6:36 PM, Adrian Klaver
<adrian.klaver@xxxxxxxxxxx> wrote:

On 05/16/2018 03:19 PM, hmidi slim wrote:

HI,

I'm working on a microservice application and I avoid using triggers
because they will not be easy to maintain and need an experimented person in
database administration to manage them. ...

Two benefits triggers and their associated functions offer, with the
understanding these are general statements:

1) Are in the database so tasks that always need happen do not need to be
replicated across the various interfaces that touch the database.

2) Also since they run on the server the data does not necessarily cross a
network, so less network bandwidth and better performance.

An example of a use case is table auditing. If you need to track what is
being INSERTed/UPDATEd/DELETEd in a table stick an audit trigger/function on
the table and push the information to an audit table.

As to managing, they are like any other code. I keep my schema code in
scripts under version control and deploy them from there. I use
Sqitch(http://sqitch.org/) which allows me to deploy and revert changes. I
use a dev database to test and troubleshoot triggers and functions.

The only appropriate use for triggers that I've ever found was for
auditing changes to tables. ...

A great use case for triggers and stored functions is data validation or cleaning. Sure, this could be done in the end-user application ... or, that is, in *all* end-user applications if you don't mind duplicating algorithmic implementations and you trust the applications to do it properly and the same way every time and non-maliciously.

Triggering a before-insert function allows for a single implementation to be used consistently for all inserts.

Also, while appropriately limiting access privileges for end-users, triggers can invoke functions that do stuff at a higher privilege level safely, such as, for example, creating data base roles for new users.

Here's a complicated but cool example:

First, there is a publicly-exposed but non-data-leaking view that allows unprivileged users to initiate inserts for account creation (and by unprivileged I mean really unprivileged, that is, end users that don't even have an associated database login role when they do the insert):


CREATE OR REPLACE VIEW public.fairian AS
 SELECT NULL::name AS fairian_name,
    NULL::name AS passwd,
    NULL::name AS email_address;

GRANT USAGE ON SCHEMA public TO public;
GRANT SELECT, INSERT ON TABLE public.fairian TO public;


A rule redirects inserts on the public dummy view to a protected intermediate view (i.e., a view contained in non-publicly-visible schema) on which new and unprivileged users do not have read, write, or update privileges, but since relations that are used due to rules get checked against the privileges of the rule owner, not the user invoking the rule, this actually works:

REVOKE ALL ON schema protected  FROM public;

CREATE OR REPLACE RULE fairian_iir AS
    ON INSERT TO public.fairian DO INSTEAD 
    INSERT INTO protected.fairian (fairian_name, passwd, email_address)
    VALUES (new.fairian_name, new.passwd, new.email_address);


The redirected insert triggers a function that drills the insert down one level further to the actual table that is visible only to the data base owner:


CREATE TRIGGER fairian_iit
  INSTEAD OF INSERT
  ON protected.fairian
  FOR EACH ROW
  EXECUTE PROCEDURE protected.fairian_iit();

CREATE OR REPLACE FUNCTION protected.fairian_iit()
  RETURNS trigger AS
$BODY$
BEGIN
    -- Note, the password is not actually stored but there is some
    -- validation ... look at the private trigger function

    INSERT INTO private.fairian (fairian_name, passwd, email_address)
        VALUES (new.fairian_name, new.passwd, new.email_address);

  RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER;


When the insert is finally pushed all the way down to the actual table in the (non-publicly visible) private schema, there's a trigger function which prevents account name collisions by serializing inserts, that ensures the credentials meet certain requirements such as containing no white space and starting with a character, and (although omitted below) sanitizes against SQL injection:


REVOKE ALL ON schema private  FROM public;

CREATE TRIGGER fairian_bit
  BEFORE INSERT
  ON private.fairian
  FOR EACH ROW
  EXECUTE PROCEDURE private.fairian_bit();


CREATE OR REPLACE FUNCTION private.fairian_bit()
  RETURNS trigger AS
$BODY$
    DECLARE
      _fairwinds private.fairwinds;
    BEGIN
      -- Serializes account creation
      select * into _fairwinds from private.fairwinds for update;

    -- Deny white space characters
   
    if position(' ' in new.fairian_name) > 0 then
        raise exception 'Fairian name may not contain spaces.';
    end if;
    if position(' ' in new.passwd) > 0 then
        raise exception 'Password may not contain spaces.';
    end if;
    if position(' ' in new.email_address) > 0 then
        raise exception 'E-mail address may not contain spaces.';
    end if;

    if not (select new.fairian_name similar to '[a-zA-Z]%') then
        RAISE EXCEPTION 'Fairian name must begin with a letter.';
    end if;

    --
    -- Anti-SQL-injection cleaning code omitted here
    -- 
      
    EXECUTE 'CREATE ROLE ' || new.fairian_name || ' WITH
        LOGIN
        INHERIT
        NOSUPERUSER
        NOCREATEDB
        NOCREATEROLE
        ENCRYPTED
        PASSWORD ' || quote_literal(new.passwd) || ' IN GROUP fairwinds';

    EXECUTE 'ALTER USER ' || new.fairian_name || ' SET search_path = privileged,public,pg_temp;';

    -- Do not store the plaintext password
   
    NEW.passwd = NULL;
        RETURN NEW;
    END;
    $BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER;


Whew!