Monday, June 30, 2014

How to Implement Audit Functionality In PostgreSQL

As I can remember 6 years back in 2008 Dec. we were researching on a better audit module to audit all transactions (Create, Modify, Delete) in PostgreSQL 8.0.3. It was a Friday which I started to implement this and I spent couple of days including Saturday and Sunday to get this completed. By Monday Morning I was able to implement the audit module to the existing product. Since then the time I'm writing this Blog still the audit functionality is working perfectly without any issues even with upgrade of the PostgreSQL versions.

So I though share this experience with you'll as when we eager to do something and also when we have the determination and courage, we are very much keen on to get things done whether it is weekend or 1 - 2 am in the morning. I know most of the Software Development enthusiastic would agree with me.

While I was researching on this, I found a way to implement the audit module with PL/Tcl language.
PL/Tcl is a loadable procedural language for the PostgreSQL database system that enables the Tcl language to be used to write functions and trigger procedures.

Please allow me to explain how we can get this implemented easily. Steps 1 - 3 has to be implemented only once. So thereafter if you need to implement audit then follow steps 4 - 5 (Create the Audit Table and Trigger). Pretty easy ha. Let's start...

  • Step 1 - In order to implement the audit module install the PL/TCL language.
 
createlang -U postgres pltcl <DB-Name>
  • Step 2 - Create the Function -  log_to_audit_table
Function          Arguments     Returns   Programming language
log_to_audit_table             "trigger"   pltcl
Definition
 
#This function crated to audit the data for all the modules
#Created By : Kosala De Silva
#Created On : 12 - Dec - 2008

spi_exec "SELECT CURRENT_USER AS tguser"
spi_exec "SELECT c.relname AS tgname,n.nspname AS schema_name
           FROM pg_class c , pg_namespace n
           WHERE n.oid = c.relnamespace
           AND relfilenode = $TG_relid"

 
if {[string equal -nocase $tgname audit_table]} { return OK }

set pk_name ""

 spi_exec "SELECT a.attname AS pk_name FROM pg_class c, pg_attribute a, pg_index i
 WHERE c.relname = '$tgname'
 AND c.oid=i.indrelid
 AND a.attnum > 0
 AND a.attrelid = i.indexrelid
 AND i.indisprimary='t'"

  spi_exec " select substring('$schema_name',0,instr('$schema_name','_', 1))||'_admin'
  AS main_schema"

 spi_exec "SELECT audit_table_name AS m_aud_tbl_name
 FROM $main_schema.audit_table_mapping
 where schema_name = '$schema_name'
 and tabel_name = '$tgname'"

switch $TG_op {
INSERT {
  set pk_value ""

  #get PK value
  foreach field $TG_relatts {
    if {[string equal -nocase [lindex [array get NEW $field] 0] $pk_name]} {
      set pk_value [lindex [array get NEW $field] 1]
      break;
    }
  }
  #log inserted row values
  foreach field $TG_relatts {
    if {! [string equal -nocase [lindex [array get NEW $field] 0] $pk_name]} {
      set modified_field [lindex [array get NEW $field] 0]
      set current_value [lindex [array get NEW $field] 1]
      spi_exec -array C "INSERT INTO $schema_name.$m_aud_tbl_name
         (time_stamp,pg_user,tbl_name,fld_name,primarykey_name,primarykey_value,modify_type,old_value,new_value)
        VALUES (CURRENT_TIMESTAMP, '$tguser', '$tgname', '$modified_field', '$pk_name', '$pk_value', '$TG_op', NULL, '$current_value')"
    }
  }
}
UPDATE {
  set pk_value ""

  #get PK value
  foreach field $TG_relatts {
    if {[string equal -nocase [lindex [array get NEW $field] 0] $pk_name]} {
      set pk_value [lindex [array get NEW $field] 1]
      break;
    }
  }
  #log inserted row values
  foreach field $TG_relatts {
    #check changed fields
    if {[string equal -nocase [array get NEW $field] [array get OLD $field]] == 0} {
      set modified_field [lindex [array get OLD $field] 0]
      if {[string compare $modified_field ""] == 0} {
        set modified_field [lindex  [array get NEW $field] 0]
      }
      set previous_value [lindex [array get OLD $field] 1]
      set current_value  [lindex [array get NEW $field] 1]
      spi_exec -array C "INSERT INTO $schema_name.$m_aud_tbl_name
        (time_stamp,pg_user,tbl_name,fld_name,primarykey_name,primarykey_value,modify_type,old_value,new_value)
        VALUES (CURRENT_TIMESTAMP, '$tguser', '$tgname', '$modified_field', '$pk_name', '$pk_value', '$TG_op', '$previous_value', '$current_value')"
    }
  }
}
DELETE {
  set pk_value ""

  #get PK value
  foreach field $TG_relatts {
    if {[string equal -nocase [lindex [array get OLD $field] 0] $pk_name]} {
      set pk_value [lindex [array get OLD $field] 1]
      break;
    }
  }
  #log inserted row values
  foreach field $TG_relatts {
    if {! [string equal -nocase [lindex [array get OLD $field] 0] $pk_name]} {
      set modified_field [lindex [array get OLD $field] 0]
      set previous_value [lindex [array get OLD $field] 1]
      spi_exec -array C "INSERT INTO $schema_name.$m_aud_tbl_name
        (time_stamp,pg_user,tbl_name,fld_name,primarykey_name,primarykey_value,modify_type,old_value,new_value)
        VALUES (CURRENT_TIMESTAMP, '$tguser', '$tgname', '$modified_field', '$pk_name', '$pk_value', '$TG_op', '$previous_value', NULL)"
    }
  }
}
}
return OK


Note : 
Main function should be created in the public schema. This function can be used for any system audit tracking. ex. "public"."log_to_audit_table" () 
This should be executed only once per DB server.

  • Step 3 - Create a table called audit_table_mapping
CREATE TABLE "<Schema Name>"."audit_table_mapping" (
  "id" INTEGER NOT NULL, 
  "schema_name" VARCHAR(100), 
  "tabel_name" VARCHAR(200), 
  "audit_table_name" VARCHAR(200), 
  CONSTRAINT "audit_table_mapping_pkey" PRIMARY KEY("id")
);
Note : 
When you implement the audit for each table there should be a mapping record in this table as one audit mapping table can be used to audit multiple tables.
DDL script should be executed only once.
A new record should be inserted for each audit table.


  • Step 4 - Create the Audit table which you need to audit data

  • CREATE TABLE "<Schema Name>"."<audit_Table Name>" (
      "time_stamp" TIMESTAMP WITH TIME ZONE,
      "pg_user" VARCHAR(30),
      "tbl_name" VARCHAR(30),
      "fld_name" VARCHAR(30),
      "primarykey_name" VARCHAR(30),
      "primarykey_value" VARCHAR(40),
      "modify_type" CHAR(6),
      "old_value" TEXT,
      "new_value" TEXT
    ) ;
    Note:
    You can use the same DDL script to create other audit tables as well. What you have to do is just change only the audit table name. ex. audit_[table name]

    • Step 5 - Execute the Trigger
     
    CREATE TRIGGER "trig_audit_<audit_Table Name>"
    AFTER INSERT OR DELETE OR UPDATE ON "<Schema Name>"."<Table Name>"
    FOR EACH ROW
    EXECUTE PROCEDURE "log_to_audit_table" ();
    
    Note:
    Trigger name should be created as follows. trig_audit_[table name]

    Once above steps are done you are good to log data. So when you try to insert / modify / delete that will be audited in the audit table. The advantage of using this functionality is that the audit table will have only the modified data with the old value and the new value. 
    
    

    Tuesday, June 17, 2014

    CVS Commands


    Following are the basic CVS commands

  • Update the project - Go to the relevent folder and issue the following command
  •           Option 1 :
            cvs -d:pserver:user@cvsServer:/usr/local/cvsroot -Q update
              Option 2 :
            cvs -d:pserver:user@cvsServer:/usr/local/cvsroot update -d Project_V1
    • Update a file
          cvs -d:pserver:user@cvsServer:/usr/local/cvsroot update -d "ProConfirmSv.java"
    • Update a new folders
          cvs -d:pserver:user@cvsServer:/usr/local/cvsroot update -d
    • Commit a file
    cvs add 
    cvs -d:pserver:user@cvsServer:/usr/local/cvsroot add ModificationUtil.java
      • go to the relevant folder and issue the following command
           cvs -d:pserver:user@cvsServer:/usr/local/cvsroot commit -m '[Message]'
    • Remove a branch 
    Option 1
         cvs -d:pserver:user@cvsServer:/usr/local/cvsroot rtag -d -B [TagName] [Project]
    Option 2
         cvs "-q" "checkout" "DocLibrary"
    • remove a tag
         cvs -d:pserver:user@cvsServer:/usr/local/cvsroot rtag -d Project_V1
    cvs rtag -d Project_V1 
    • chcekout a project - checkout HEAD
    cvs -d:pserver:user@cvsServer:/usr/local/cvsroot checkout Project
    • checkout a branch 
    1. got to the workspace
    cvs -d:pserver:user@cvsServer:/usr/local/cvsroot checkout -r Project_V1_QA Project
    2. then rename the foder
    checkout -d branch name Module name
    ex
    cvs -d:pserver:user@cvsServer:/usr/local/cvsroot checkout -r Project_V1 Project
    • check the status of the files by files or folder  - cvs status
    Summary of CVS commands are:
            add          Add a new file/directory to the repository
            admin        Administration front end for rcs
            annotate     Show last revision where each line was modified
            checkout     Checkout sources for editing
            commit       Check files into the repository
            diff         Show differences between revisions
            edit         Get ready to edit a watched file
            editors      See who is editing a watched file
            export       Export sources from CVS, similar to checkout
            history      Show repository access history
            import       Import sources into CVS, using vendor branches
            init         Create a CVS repository if it doesn't exist
            kserver      Kerberos server mode
            log          Print out history information for files
            login        Prompt for password for authenticating server
            logout       Removes entry in .cvspass for remote repository
            pserver      Password server mode
            rannotate    Show last revision where each line of module was modified
            rdiff        Create 'patch' format diffs between releases
            release      Indicate that a Module is no longer in use
            remove       Remove an entry from the repository
            rlog         Print out history information for a module
            rtag         Add a symbolic tag to a module
            server       Server mode
            status       Display status information on checked out files
            tag          Add a symbolic tag to checked out version of files
            unedit       Undo an edit command
            update       Bring work tree in sync with repository
            version      Show current CVS version(s)
            watch        Set watches
            watchers     See who is watching a file
    (Specify the --help option for a list of other help options)