scorestack · oneNutW0nder · Feb 21, 2021 · Feb 21, 2021 · Feb 21, 2021 · Feb 21, 2021
diff --git a/CHANGELOG.md b/CHANGELOG.md
@@ -25,6 +25,10 @@ Each section organizes entries into the following subsections:
 
 ### Dynamicbeat
 
+#### Added
+
+- PostgreSQL check type (#294)
+
 #### Fixed
 
 - Spurious EOF errors when closing SSH connections are ignored (#292)

diff --git a/docs/reference/postgresql.md b/docs/reference/postgresql.md
@@ -0,0 +1,27 @@
+PostgreSQL
+==========
+
+| Name         | Type   | Required     | Description                                                          |
+| ------------ | ------ | ------------ | -------------------------------------------------------------------- |
+| Host         | String | Y            | IP or FQDN for the PostgreSQL server                                 |
+| Username     | String | Y            | Username for the database                                            |
+| Password     | String | Y            | Password for the user                                                |
+| Database     | String | Y            | Name of the database to access                                       |
+| Table        | String | Y            | Name of the table to access                                          |
+| Column       | String | Y            | Name of the column to access                                         |
+| MatchContent | String | N :: "false" | Whether to perform a regex content match on the results of the query |
+| ContentRegex | String | N :: "\.\*"  | Regex to match on                                                    |
+| Port         | String | N :: "3306"  | Port for the server                                                  |
+
+Example Check
+-------------
+
+Unlike MySQL, PostgreSQL does not ship by default with a queryable database. The example check definition provided in the `examples/` folder uses the following simple schema:
+
+```sql
+CREATE TABLE testtable(
+  EntryID SERIAL PRIMARY KEY,
+  Name TEXT NOT NULL
+);
+INSERT INTO testtable (Name) VALUES ('scorestack');
+```
diff --git a/dynamicbeat/checks/checks.go b/dynamicbeat/checks/checks.go
@@ -23,6 +23,7 @@ import (
 	"github.com/scorestack/scorestack/dynamicbeat/checks/ldap"
 	"github.com/scorestack/scorestack/dynamicbeat/checks/mysql"
 	"github.com/scorestack/scorestack/dynamicbeat/checks/noop"
+	"github.com/scorestack/scorestack/dynamicbeat/checks/postgresql"
 	"github.com/scorestack/scorestack/dynamicbeat/checks/schema"
 	"github.com/scorestack/scorestack/dynamicbeat/checks/smb"
 	"github.com/scorestack/scorestack/dynamicbeat/checks/smtp"
@@ -161,6 +162,8 @@ func unpackDef(config schema.CheckConfig) (schema.Check, error) {
 		def = &mysql.Definition{}
 	case "smb":
 		def = &smb.Definition{}
+	case "postgresql":
+		def = &postgresql.Definition{}
 	default:
 		logp.Warn("Invalid check type found. Offending check : %s:%s", config.Name, config.Type)
 		def = &noop.Definition{}

diff --git a/dynamicbeat/checks/mysql/mysql.go b/dynamicbeat/checks/mysql/mysql.go
@@ -95,7 +95,8 @@ func (d *Definition) Run(ctx context.Context) schema.CheckResult {
 		return result
 	}
 
-	// Check fails if we reach here
+	// Check passes if we reach here
+	result.Passed = true
 	return result
 }
 

diff --git a/dynamicbeat/checks/postgresql/postgresql.go b/dynamicbeat/checks/postgresql/postgresql.go
@@ -0,0 +1,107 @@
+package postgresql
+
+import (
+	"context"
+	"fmt"
+	"regexp"
+	"strconv"
+
+	// PostgreSQL driver
+	"github.com/jackc/pgx/v4"
+	"github.com/scorestack/scorestack/dynamicbeat/checks/schema"
+)
+
+// The Definition configures the behavior of the MySQL check
+// it implements the "check" interface
+type Definition struct {
+	Config       schema.CheckConfig // generic metadata about the check
+	Host         string             `optiontype:"required"`                      // IP or Hostname for the PostgreSQL server
+	Username     string             `optiontype:"required"`                      // Username for the database
+	Password     string             `optiontype:"required"`                      // Password for the user
+	Database     string             `optiontype:"required"`                      // Name of the database to access
+	Table        string             `optiontype:"required"`                      // Name of the table to access
+	Column       string             `optiontype:"required"`                      // Name of the column to access
+	MatchContent string             `optiontype:"optional"`                      // Whether to perform a regex content match on the results of the query
+	ContentRegex string             `optiontype:"optional" optiondefault:".*"`   // Regex to match on
+	Port         string             `optiontype:"optional" optiondefault:"5432"` // Port for the server
+}
+
+// Run a single instance of the check
+func (d *Definition) Run(ctx context.Context) schema.CheckResult {
+	// Initialize empty result
+	result := schema.CheckResult{}
+
+	// Create DB handle
+	db, err := pgx.Connect(ctx, fmt.Sprintf("postgresql://%s:%s@%s:%s/%s", d.Username, d.Password, d.Host, d.Port, d.Database))
+	if err != nil {
+		result.Message = fmt.Sprintf("Creating database handle failed : %s", err)
+		return result
+	}
+	defer db.Close(ctx)
+
+	// Check db connection
+	err = db.Ping(ctx)
+	if err != nil {
+		result.Message = fmt.Sprintf("Failed to ping database : %s", err)
+	}
+
+	// Query the DB
+	// TODO: This is SQL injectable. Figure out Paramerterized queries
+	rows, err := db.Query(ctx, fmt.Sprintf("SELECT %s FROM %s;", d.Column, d.Table))
+	if err != nil {
+		result.Message = fmt.Sprintf("Could not query database : %s", err)
+		return result
+	}
+	defer rows.Close()
+
+	// Store the value from the column
+	var val string
+
+	// Perform regex matching, if necessary
+	if matchContent, _ := strconv.ParseBool(d.MatchContent); matchContent {
+		// Compile the regex
+		regex, err := regexp.Compile(d.ContentRegex)
+		if err != nil {
+			result.Message = fmt.Sprintf("Error compiling regex string %s : %s", d.ContentRegex, err)
+			return result
+		}
+
+		// Check the rows
+		for rows.Next() {
+			// Grab a value
+			err := rows.Scan(&val)
+			if err != nil {
+				result.Message = fmt.Sprintf("Could not scan row values : %s", err)
+				return result
+			}
+			// Check value with regex
+			if regex.MatchString(val) {
+				// If we reach here the check passes
+				result.Passed = true
+				return result
+			}
+
+		}
+	}
+
+	// Check for error in the rows
+	if rows.Err() != nil {
+		result.Message = fmt.Sprintf("Something happened to the rows : %s", err)
+		return result
+	}
+
+	// Check passes if we reach here
+	result.Passed = true
+	return result
+}
+
+// GetConfig returns the current CheckConfig struct this check has been
+// configured with
+func (d *Definition) GetConfig() schema.CheckConfig {
+	return d.Config
+}
+
+// SetConfig reconfigures this check with a new CheckConfig struct
+func (d *Definition) SetConfig(config schema.CheckConfig) {
+	d.Config = config
+}
diff --git a/dynamicbeat/go.mod b/dynamicbeat/go.mod
@@ -35,6 +35,7 @@ require (
 	github.com/go-sql-driver/mysql v1.5.0
 	github.com/hashicorp/errwrap v1.1.0 // indirect
 	github.com/hirochachacha/go-smb2 v1.0.3
+	github.com/jackc/pgx/v4 v4.10.1
 	github.com/jlaffaye/ftp v0.0.0-20200812143550-39e3779af0db
 	github.com/josephspurrier/goversioninfo v1.2.0 // indirect
 	github.com/magefile/mage v1.10.0