PostgreSQL SQL_POLICY Check

The SQL_POLICY check executes a SQL statement on the DBMS being audited, and evaluates the columns in the SQL result.

Usage

<custom_item>

   type: SQL_POLICY

   description: ["description"]

   sql_request: ["sql statement to run"]

   sql_types: [STRING|REGEX|INTEGER][,....]

   sql_expect: ["text"|"regex"|number]

   (optional) match_all : [YES|NO]

   (optional) match_case : [YES|NO]

   (optional) num_rows : [number|range]

</custom_item>

sql_request

The SQL statement that is queried against the DBMS.

sql_types

A comma-separated list of the types for the columns that are returned from the SQL query. The values that are allowed are:

  • STRING - Text based results.

  • REGEX - Text based results, evaluated by a regular expression.

  • INTEGER - Numeric based results, including the use of a range as designated by [MIN..MAX].

  • NULL - NULL value return.

  • STRING_OR_NULL - Text based results or NULL value.

  • REGEX_OR_NULL - Text based results, evaluated by a regular expression or NULL value.

  • INTEGER_OR_NULL - Numeric based results or NULL value.

sql_expect

A comma-separated list of the values, or regular expression, to evaluate the results from the SQL query.

The values for each of the columns must match the types that are defined in the sql_types. With this, text values need to be wrapped in double quotes (“) and numbers do not need the double quotes.

A special value of NO_ROWS_RETURNED can be used as the only expected value to match on cases that no rows are returned. This is more explicit than using a check_option as described below.

match_all

(Optional) Set match_all to YES to require all lines of text to match the expectation, and not just a single line of text. If you set match_all to the default of NO, only one line must match for the check to pass.

match_case

(Optional) Set match_case to YES to make the comparison case-sensitive. Set match_case to the default of NO to make the comparison case-insensitive.

num_rows

(Optional) Setting num_rows to an integer value will allow the check to pass if that number of rows is returned, and fail if more or less rows are returned. Not setting num_rows should not affect the result based on the number of rows returned. A variable number of rows can be evaluated by specifying a range in the form of [MIN..MAX].

Notes

The number of sql_expect must match the number of sql_types, with the exception of NO_ROWS_RETURNED expect.

Example

<custom_item>

   type : SQL_POLICY

   description : "Application password lockout parameter is 5"

   sql_request : "SELECT key, value FROM parameters WHERE key = 'PASSWORD_LOCKOUT';"

   sql_types : STRING, INTEGER

   sql_expect : "PASSWORD_LOCKOUT", 5

</custom_item>