audit

package module
v0.0.0-...-45e4aa3 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Sep 22, 2021 License: MIT Imports: 18 Imported by: 0

README

Introduction

Audit database queries and execs by making use of interceptors from ngrok/sqlmw.

Any record modifications including insert, update and delete create a new record in the audits table. Audit values like

  • who is making the change
  • old value
  • new value
  • modification time

among others are recorded.

Full list:

type Event struct {
    Organisation uint64    `db:"organisation"` // or tenant
    ActorID      uint64    `db:"actor_id"`
    TableRowID   uint64    `db:"table_row_id"`
    Table        string    `db:"table_name"`
    Action       Action    `db:"action"`
    OldValues    string    `db:"old_values"`
    NewValues    string    `db:"new_values"`
    HTTPMethod   string    `db:"http_method"`
    URL          string    `db:"url"`
    IPAddress    string    `db:"ip_address"`
    UserAgent    string    `db:"user_agent"`
    CreatedAt    time.Time `db:"created_at"`
}

Both old_values and new_values are stored in JSON format. For example:

id organisation_id actor_id table_row_id table_name action old_values new_values http_method url ip_address user_agent created_at
42 1 2 15 users update {"name":"test name","id":"42"} {"name":"changed name","id":"42"} PUT /api/v1/user/42 localhost:8080 PostmanRuntime/7.28.4 2021-09-15 02:10:02

Install

go get github.com/gmhafiz/audit
go get github.com/go-sql-driver/mysql

Usage

  1. Open database connection pool and register the database middleware and auditor

Create a new audit instance

auditor, err := audit.NewAudit()

Optionally, you can customize the audit table name

auditor, err := audit.NewAudit(audit.WithTableName("other_audit_table_name")) // only alphanumeric name is accepted

You can also add a list of tables to be exempted:

auditor, err := audit.NewAudit(
    audit.WithTableName("other_audit_table_name"),
    audit.WithTableException("schema_migrations", "other_tables"),
)

Add the code to where you open database connection:

package database

import (
    "database/sql"
    "log"
    
    "github.com/gmhafiz/audit"
    "github.com/go-sql-driver/mysql"
    "github.com/ngrok/sqlmw"
)

func NewDB(dataSourceName string) (*sql.DB, auditor *audit.Auditor) {
    // initialise auditor
    auditor, err := audit.NewAudit()
    if err != nil {
        log.Fatal(err)
    }
   
    // register sql interceptor and our custom driver
    databaseDriverName, err := audit.RegisterDriverInterceptor(auditor, "mysql")
    if err != nil {
        log.Fatal(err)
    }
    
    // open database connection using that driver
    db, err := sql.Open(databaseDriverName, dataSourceName)
    if err != nil {
        log.Fatal(err)
    }

Adding your application database instance is compulsory and is done after connection pool is opened. This is used to query and save both old values and new values of the affected record.

    err = auditor.SetDB(
        audit.Sql(db),
    )
    if err != nil {
        log.Fatal(err)
    }
   
    return db, auditor
}

By setting the database, the library will create an audits table automatically for you. Index creation is left to the user. Often, you would want a composite index of (table_row_id, table_name) - and possibly a separate actor_id index.

For Mysql:

create index audits_table_row_id_table_name_index
	on audits (table_row_id, table_name);
create index audits_actor_id_index
    on audits (actor_id);
  1. A middleware is needed to capture current user ID and optionally organisation/tenant ID from the current request context. In order to use it, both user ID and organisation ID must be saved into context in UserID and OrganisationID respectively. These two values are retrieved from JWT or session cookies.
import (
	"github.com/gmhafiz/audit/middleware"
)

func Auth(store *redisstore.RedisStore) Adapter {
    return func(next http.Handler) http.Handler {
        return http.HandlerFunc(func (w http.ResponseWriter, r *http.Request) {
            var organisationID = get session.Values["organisationID"]
            var userID = session.Values["userID"]
            
            ctx := context.WithValue(r.Context(), middleware.OrganisationID, organisationID)
            ctx = context.WithValue(ctx, middleware.UserID, userID)
            
            next.ServeHTTP(w, r.WithContext(ctx))
        })
    }
}
  1. Use the provided middleware that captures current user ID and optionally organisation ID by registering it to your router.

Your own Auth middleware has to be registered before middleware.Audit.

import (
    "github.com/gmhafiz/audit/middleware"
    "github.com/go-chi/chi/v5"
)

func router() *chi.Mux {
    r := chi.NewRouter()
    r.Use(middleware.Auth)
    r.Use(middleware.Audit)
   
    return r
}

Test

  1. Create an appropriate testing database for each postgres and mysql

  2. Set both MYSQL_DSN and POSTGRES_DSN in your environment variable.

    POSTGRES_DSN=host=0.0.0.0 port=5432d user=users password=password dbname=audit_test sslmode=disable MYSQL_DSN=root:password@tcp(0.0.0.0:3306)/audit_test?parseTime=true&interpolateParams=true

  3. Run

    go test ./...

Limitations

  1. Table ID
  2. Hooks
  3. Login
  4. IN operator

Table ID

This library assumes your table ids are in uint64 format and named id,

Hooks

Login

Scenario: Say for every login, you save the time that user last logged in - and you want this to be audited.

Remember that an Auth middleware is needed to capture user id (from JWT or session token). This audit library won't be able to capture the IDs because you do not place an Auth middleware to logging in a user.

To work around this, save the IDs manually before making a call to set user's last login time.

func (u *userService) Login(ctx context.Context, loginReq *LoginRequest) (*models.User, error) {
    user, err := u.repository.Get(ctx, loginReq)
    if err != nil {
        return nil, err
    }
    isValidPassword, err := checkValidPassword(loginReq.Password, user.Password)
    if err != nil {
        return nil, err
    }
	
	// once you've checked that the login is valid, you may save the IDs
    ctx = context.WithValue(ctx, "userID", user.ID)

    // Finally, you may set the time this user last login. The hooks will be applied since you are making an `update` database operation.
    err = u.repository.SetLastLogin(ctx, user)

    return user, nil
}

IN Operator

Currently, this audit package doesn't parse IN operator correctly.

Credit

Documentation

Index

Constants

View Source 
const (
	MysqlDB    string = "mysql"
	PostgresDB string = "postgres"
	MongoDB    string = "mongo"
)

Variables

View Source 
var (
	MysqlCreate    = "" /* 509-byte string literal not displayed */
	MysqlInsert    = "" /* 167-byte string literal not displayed */
	MysqlSelect    = "SELECT * FROM %s WHERE %v %s ?"
	PostgresCreate = ""                                /* 299-byte string literal not displayed */
	PostgresInsert = ""                                /* 180-byte string literal not displayed */
	PostgresSelect = "SELECT * FROM %s WHERE %v %s $1" // todo: support IN operator
)
View Source 
var (
	ErrInvalidQuery       = fmt.Errorf("invalid query")
	ErrDriverNotSupported = fmt.Errorf("driver is not supported")
)
View Source 
var (
	ErrInvalidDatabaseDriver = fmt.Errorf("invalid database driver")
	ErrNoAuditSet            = fmt.Errorf("no audit is set from the request context")
)
View Source 
var (
	ErrInvalidConnection = fmt.Errorf("invalid database connection")
)

Functions

func After 

func After(query, word string) string

func RegisterHooks 

func RegisterHooks(auditor *Auditor, dbType string) (string, error)

Types

type Action 

type Action string
const (
	Select Action = "select"
	Insert Action = "insert"
	Update Action = "update"
	Delete Action = "delete"
)

type Auditor 

type Auditor struct {
	// contains filtered or unexported fields
}

func NewAudit 

func NewAudit(opts ...Option) (*Auditor, error)

NewAudit created a new auditor instance

func (*Auditor) GetTableName 

func (a *Auditor) GetTableName(query string) (tableName string, err error)

func (*Auditor) Save 

func (a *Auditor) Save(ctx context.Context, query string, args []interface{}, lastInsertID int64, event Event) error

func (*Auditor) SetDB 

func (a *Auditor) SetDB(opts ...DBOption) error

func (*Auditor) SetEvent 

func (a *Auditor) SetEvent(ctx context.Context, event Event, tableName, query string, args []interface{}) (Event, error)

type DBOption 

type DBOption func(*Auditor)

func MySql 

func MySql(db *sql.DB, dsn string) DBOption

func Postgres 

func Postgres(db *sql.DB, dsn string) DBOption

type Event 

type Event struct {
	ActorID    uint64    `db:"actor_id"`
	TableRowID uint64    `db:"table_row_id"`
	Table      string    `db:"table_name"`
	Action     Action    `db:"action"`
	OldValues  string    `db:"old_values"`
	NewValues  string    `db:"new_values"`
	HTTPMethod string    `db:"http_method"`
	URL        string    `db:"url"`
	IPAddress  string    `db:"ip_address"`
	UserAgent  string    `db:"user_agent"`
	CreatedAt  time.Time `db:"created_at"`

	WhereClause WhereClause
	IsExempted  bool
}

type Hooks 

type Hooks struct {
	Auditor *Auditor
}

Hooks satisfies the sqlhook.Hooks interface

func (*Hooks) After 

func (h *Hooks) After(ctx context.Context, result driver.Result, rows driver.Rows, query string, args ...interface{}) (context.Context, error)

func (*Hooks) Before 

func (h *Hooks) Before(ctx context.Context, query string, args ...interface{}) (context.Context, error)

type MysqlParser 

type MysqlParser struct {
	// contains filtered or unexported fields
}

func (*MysqlParser) Save 

func (p *MysqlParser) Save(ctx context.Context, query string, args []interface{}, lastInsertID int64, event Event) error

type Option 

type Option func(*Auditor)

func WithTableException 

func WithTableException(tableNames ...string) Option

WithTableException list of tables not to be audited

func WithTableName 

func WithTableName(tableName string) Option

WithTableName customise the audit table name

type Parser 

type Parser struct {
	*MysqlParser
	*PostgresParser
	// contains filtered or unexported fields
}

func NewParser 

func NewParser(dbType string) *Parser

type PostgresParser 

type PostgresParser struct {
	// contains filtered or unexported fields
}

func (*PostgresParser) Save 

func (p *PostgresParser) Save(ctx context.Context, query string, args []interface{}, lastInsertID int64, event Event) error

type WhereClause 

type WhereClause struct {
	// contains filtered or unexported fields
}

type WhereStmt 

type WhereStmt struct {
	Version int `json:"version"`
	Stmts   []struct {
		Stmt struct {
			DeleteStmt struct {
				Relation struct {
					Relname        string `json:"relname"`
					Inh            bool   `json:"inh"`
					Relpersistence string `json:"relpersistence"`
					Location       int    `json:"location"`
				} `json:"relation"`
				WhereClause struct {
					AExpr struct {
						Kind string `json:"kind"`
						Name []struct {
							String struct {
								Str string `json:"str"`
							} `json:"String"`
						} `json:"name"`
						Lexpr struct {
							ColumnRef struct {
								Fields []struct {
									String struct {
										Str string `json:"str"`
									} `json:"String"`
								} `json:"fields"`
								Location int `json:"location"`
							} `json:"ColumnRef"`
						} `json:"lexpr"`
						Rexpr struct {
							ParamRef struct {
								Number   int `json:"number"`
								Location int `json:"location"`
							} `json:"ParamRef"`
						} `json:"rexpr"`
						Location int `json:"location"`
					} `json:"A_Expr"`
				} `json:"whereClause"`
			} `json:"DeleteStmt"`
			UpdateStmt struct {
				Relation struct {
					Relname        string `json:"relname"`
					Inh            bool   `json:"inh"`
					Relpersistence string `json:"relpersistence"`
					Location       int    `json:"location"`
				} `json:"relation"`
				TargetList []struct {
					ResTarget struct {
						Name string `json:"name"`
						Val  struct {
							ParamRef struct {
								Number   int `json:"number"`
								Location int `json:"location"`
							} `json:"ParamRef"`
						} `json:"val"`
						Location int `json:"location"`
					} `json:"ResTarget"`
				} `json:"targetList"`
				WhereClause struct {
					AExpr struct {
						Kind string `json:"kind"`
						Name []struct {
							String struct {
								Str string `json:"str"`
							} `json:"String"`
						} `json:"name"`
						Lexpr struct {
							ColumnRef struct {
								Fields []struct {
									String struct {
										Str string `json:"str"`
									} `json:"String"`
								} `json:"fields"`
								Location int `json:"location"`
							} `json:"ColumnRef"`
						} `json:"lexpr"`
						Rexpr struct {
							ParamRef struct {
								Number   int `json:"number"`
								Location int `json:"location"`
							} `json:"ParamRef"`
						} `json:"rexpr"`
						Location int `json:"location"`
					} `json:"A_Expr"`
				} `json:"whereClause"`
			} `json:"UpdateStmt"`
		} `json:"stmt"`
	} `json:"stmts"`
}

type Work 

type Work interface {
	Save(ctx context.Context, query string, args []interface{}, lastInsertID int64, event Event) error
	// contains filtered or unexported methods
}

Source Files

View all Source files

Directories

Path Synopsis

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.