Skip to content

AuditColumns

Jump to bottom Edit New page
Joe Abbate edited this page Jul 8, 2013 · 16 revisions

Augmenter will be able to generate various types of audit columns that are maintained automatically and cannot be altered by the session user.

  • Created By User
  • Created Timestamp
  • Created By IP Address
  • Modified By User
  • Modified Timestamp
  • Modified By IP Address

The Created columns will be set automatically on INSERT, and will be set to the OLD value on an update so they cannot be altered by the session user.

The Modified columns will be set automatically on INSERT and UPDATE so they cannot be set by the session user.

The User columns will use a function to get the name of the user to insert. By default this function will return SESSION_USER, but the developer can override this function (or use a different function) as required; for example to return the user's application login for a web application that uses the a single database user to connect to the database.

The IP Address columns will use a function to get the IP Address to insert. By default this function will return inet_client_addr(), but the developer can override this function (or use a different function) as required; for example to return the end users' IP address as reported by the web server for a web application behind a reverse proxy.

The input yaml file will optionally contain one or more audit config sections specifying what audit columns are required and what their names are.

A table can be marked as audited in the yaml file: if the yaml file contains no audit configuration then a default configuration will be generated; if the yaml file contains a single audit configuration then that configuration will be used; if the yaml file contains multiple configurations then the configuration to be used must be specified, or an exception is raised.

The default configuration for an audited table is to set the user and timestamp audit columns, but not the IP address ones (because they are generally less useful):

Therefore the simplest example:

schema example_schema:
  table audit_examples:
    audited: true
    columns:
    - audit_code:
        not_null: true
        type: character varying(5)
    - audit_name:
        not_null: true
        type: character varying(40)

[jmafc] As mentioned in Augmenter, I'd get rid of the columns as these would come from the database catalogs.

Will generate a database table with 4 extra columns, where those columns are maintained automatically and cannot be set by the session user:

  • created_by_user
  • created_timestamp
  • modified_by_user
  • modified_timestamp

A more complicated example with an audit configuration specifying the columns required:

config:
  audit:
  - default:
      columns:
      - created_by_user:
          name: created_by # The name that the generated columns will have
          not_null: true
          type: character varying(63)
      - created_timestamp:
          name: created_date_time
          not_null: true
          type: timestamp with time zone
      - created_by_ip_address:
          name: created_by_ip
          not_null: true
          type: inet
      - modified_by_user:
          name: modified_by
          not_null: true
          type: character varying(63)
      - modified_timestamp:
          name: modified_date_time
          not_null: true
          type: timestamp with time zone
      - modified_by_ip_address:
          name: modified_by_ip
          not_null: true
          type: inet
schema example_schema:
  table audit_examples:
    audited: true
    columns:
    - audit_code:
        not_null: true
        type: character varying(5)
    - audit_name:
        not_null: true
        type: character varying(40)
    primary_key:
      audit_examples_pkey:
        access_method: btree
        columns:
        - audit_code

A final example with a multiple audit configurations:

config:
  audit:
  - last_modified_only:
      columns:
      - modified_timestamp:
          name: modified_date_time
          not_null: true
          type: timestamp with time zone
  - full:
      columns:
      - created_by_user:
          name: created_by
          not_null: true
          type: character varying(63)
      - created_timestamp:
          name: created_date_time
          not_null: true
          type: timestamp with time zone
      - created_by_ip_address:
          name: created_by_ip
          not_null: true
          type: character varying(15)
      - modified_by_user:
          name: modified_by
          not_null: true
          type: character varying(63)
      - modified_timestamp:
          name: modified_date_time
          not_null: true
          type: timestamp with time zone
      - modified_by_ip_address:
          name: modified_by_ip
          not_null: true
          type: character varying(15)
schema example_schema:
  table audit_examples:
    audited: full
    columns:
    - audit_code:
        not_null: true
        type: character varying(5)
    - audit_name:
        not_null: true
        type: character varying(40)
    primary_key:
      audit_examples_pkey:
        access_method: btree
        columns:
        - audit_code
  table simple_audits:
    audited: last_modified_only
    columns:
    - simple_code:
        not_null: true
        type: character varying(5)
    - simple_name:
        not_null: true
        type: character varying(40)
    primary_key:
      audit_examples_pkey:
        access_method: btree
        columns:
        - simple_code

Note Augmenter will attempt to cast the calculated value for the column to the datatype specified, e.g. inet to character varying(15). Note that this will fail for an IPv6 address, which is not Augmenter's fault or problem!

Here is the intermediate yaml code that would be generated by the last example above. This code is used by yamltodb to generate the required DDL, based on the current state of the target database. Note that this example assumes that the config section is not specifying an owner for the code, or and prefix/suffix text for the trigger and function names (see [Augmenter Configuration](Augmenter Configuration)):

schema example_schema:
  function audit_full():
    language: plpgsql
    returns: trigger
    source: "\nBEGIN\n  IF TG_OP = 'INSERT' THEN\n    NEW.created_date_time := CURRENT_TIMESTAMP::timestamp(0);\n\
      \    NEW.created_by_user := get_audit_user();\n    NEW.created_by_ip := get_audit_ip_address();\n\
      \  ELSIF TG_OP = 'UPDATE' THEN\n    NEW.created_date_time := OLD.created_date_time;\n\
      \    NEW.created_by_user := OLD.created_by_user;\n    NEW.created_by_ip := OLD.created_by_ip;\n\
      \  END IF;\n  NEW.modified_date_time := CURRENT_TIMESTAMP::timestamp(0);\n \
      \ NEW.modified_by_user := get_audit_user(); \n  NEW.modified_by_ip := get_audit_ip_address();\
      \ \n \n  RETURN NEW;\nEND;\n"
  function audit_last_modified_only():
    language: plpgsql
    returns: trigger
    source: "\nBEGIN\n  NEW.modified_date_time := CURRENT_TIMESTAMP::timestamp(0);\n\
      \ \n  RETURN NEW;\nEND;\n"
  function get_audit_ip_address():
    language: plpgsql
    returns: inet
    source: "\nBEGIN\n  RETURN inet_client_addr();\nEND;\n"
  function get_audit_user():
    language: plpgsql
    returns: character varying
    source: "\nBEGIN\n  RETURN CURRENT_USER;\nEND;\n"
  table audit_examples:
    columns:
    - audit_code:
        not_null: true
        type: character varying(5)
    - audit_name:
        not_null: true
        type: character varying(40)
    - created_by:
        not_null: true
        type: character varying(30)
    - created_date_time:
        not_null: true
        type: timestamp with time zone
    - created_by_ip:
        not_null: true
        type: inet
    - modified_by:
        not_null: true
        type: character varying(30)
    - modified_date_time:
        not_null: true
        type: timestamp with time zone
    - modified_by_ip:
        not_null: true
        type: inet
    primary_key:
      audit_examples_pkey:
        access_method: btree
        columns:
        - audit_code
    triggers:
      audit_examples_20_audit_full:
        events:
        - insert
        - update
        level: row
        procedure: fcl_owner.audit_full()
        timing: before
  table simple_audits:
    columns:
    - simple_code:
        not_null: true
        type: character varying(5)
    - simple_name:
        not_null: true
        type: character varying(40)
    - modified_date_time:
        not_null: true
        type: timestamp with time zone
    primary_key:
      audit_examples_pkey:
        access_method: btree
        columns:
        - simple_code  table fcl_clients:
    triggers:
      simple_audits_20_audit_last_modified_only:
        events:
        - insert
        - update
        level: row
        procedure: fcl_owner.audit_last_modified_only()
        timing: before

When yamltodb runs, it creates the following SQL DDL, assuming that the tables already exist in the target database, but the businss logic doesn't

CREATE OR REPLACE FUNCTION example_schema.get_audit_user()
  RETURNS character varying AS
$BODY$
BEGIN
  RETURN COALESCE(get_session_variable('audit_user', NULL), SESSION_USER);
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER
  COST 100;
ALTER FUNCTION example_schema.get_audit_user() SET search_path=example_schema, pg_temp;
REVOKE EXECUTE ON FUNCTION example_schema.get_audit_user() FROM public;

CREATE OR REPLACE FUNCTION example_schema.get_audit_ip_address()
  RETURNS inet AS
$BODY$
BEGIN
  RETURN COALESCE(get_session_variable('audit_ip_address', NULL)::inet, inet_client_addr());
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER
  COST 100;
ALTER FUNCTION example_schema.get_audit_ip_address() SET search_path=example_schema, pg_temp;
REVOKE EXECUTE ON FUNCTION example_schema.get_audit_ip_address() FROM public;

CREATE OR REPLACE FUNCTION example_schema.audit_full()
  RETURNS trigger AS
$BODY$
BEGIN
  IF TG_OP = 'INSERT' THEN
    NEW.created_date_time := CURRENT_TIMESTAMP::timestamp(0);
    NEW.created_by := get_audit_user();
    NEW.created_by_ip := get_audit_ip_address();
  ELSIF TG_OP = 'UPDATE' THEN
    NEW.created_date_time := OLD.created_date_time;
    NEW.created_by := OLD.created_by;
    NEW.created_by_ip := OLD.created_by_ip;
  END IF;
  NEW.modified_date_time := CURRENT_TIMESTAMP::timestamp(0);
  NEW.modified_by := get_audit_user(); 
  NEW.modified_by_ip := get_audit_ip_address(); 
 
  RETURN NEW;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER
  COST 100;
ALTER FUNCTION example_schema.audit_full() SET search_path=example_schema, pg_temp;

CREATE OR REPLACE FUNCTION example_schema.audit_last_modified_only()
  RETURNS trigger AS
$BODY$
BEGIN
  NEW.modified_date_time := CURRENT_TIMESTAMP::timestamp(0);
 
  RETURN NEW;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER
  COST 100;
ALTER FUNCTION example_schema.audit_last_modified_only() SET search_path=example_schema, pg_temp;

CREATE TRIGGER audit_examples_20_audit_full
  BEFORE INSERT OR UPDATE
  ON example_schema.audit_examples
  FOR EACH ROW
  EXECUTE PROCEDURE example_schema.audit_full();

CREATE TRIGGER simple_audits_20_audit_last_modified_only
  BEFORE INSERT OR UPDATE
  ON example_schema.simple_audits
  FOR EACH ROW
  EXECUTE PROCEDURE example_schema.audit_last_modified_only();
Add a custom footer
Add a custom sidebar
Clone this wiki locally