Filtering Expressions

Overview

Devii's filtering expression syntax enables complex, expressive querying capabilities. This mini-language is deliberately designed to be similar to SQL's WHERE clause but introduces unique features that make it more versatile. Specifically, it supports:

1. Comparisons involving NULL: You can directly compare values to NULL using equality (=) or inequality (!=).

• Example: regionid != null translates to regionid IS NOT NULL in SQL.

2. Aggregate Function Comparisons: Devii allows direct comparisons against the return values of aggregate functions like AVG, MIN MAX, SUM, or COUNT.

3. Subquery Evaluations: Devii can evaluate subqueries against related tables, allowing intricate cross-referencing.

This syntax was designed with developers in mind, taking inspiration from familiar programming languages such as C/C++, Java, JavaScript, and Python while integrating key SQL-style operators for consistency.

Syntax Overview

Literals and Operators:

• Strings: Strings can be enclosed in single (') or double (") quotes.
• Parentheses: Parentheses (()) can group expressions and define precedence.
• Operators:
  • Standard comparison operators: =, !=, <, >, <=, >=
  • Mathematical operators: +, -, *, /, %
  • Bitwise operations: &, |, #, <<, >>
• String Matching: The LIKE and ILIKE operators are supported for string matching.
• Whitespace: Whitespace is insignificant, allowing flexible formatting.
• Case Sensitivity: All word-based operators are case-insensitive.
• Function Calls: Function calls use syntax similar to SQL or the aforementioned programming languages.
  • Syntax: function(arg1, arg2,...argN).

Defined Classes of Expressions

Column References:

• Allows direct reference to table columns for comparison or filtering.
  • Syntax: column_name <operator> value
  • Example: price > 100
    • Matches records where the price column is greater than 100.

Role Attributes

• Role Attributes
  • Enables access to attributes of the current role using the $_PRINCIPAL keyword, allowing queries to dynamically filter data based on the role's context.
    • Syntax: $_PRINCIPAL.attribute

• Available attributes:
  • roleid: Unique identifier of the role.
  • capabilities: Permissions or abilities associated with the role.
  • classes: Groups or categories assigned to the role.
  • children / parentid: Role hierarchy.
  • tenantid: Tenant-specific identifier.
    • Example: creatorid = $_PRINCIPAL.roleid
      • This matches records where the creatorid column equals the roleid of the current role.

Value Comparisons

• Performs comparisons between two values (literals, columns, functions, or math expressions).
  • Operators: =, !=, <, >, <=, >=, IN, LIKE, ILIKE
  • IN Syntax: <operator> [value1, value2, ...].
    • Example: id IN [1, 2, 3]
      • Matches records where the id column equals any of the listed values.

• String wildcards in LIKE and ILIKE: % for multi-character matches.
  • Example: name ILIKE "foo%"
    • Matches records whose 'name' column starts with the string 'foo'.

Function Calls

• Executes whitelisted functions, with arguments that may include literals, columns, or other expressions.
• Syntax: function(arg1, arg2, ...)
  • Core Operators: min, max, avg, count, sum, abs (absolute), round, length, lower, upper, contains
    • Examples: createdate = max(createdate)
      • Matched the record with the latest createdate

• Geospatial Operators: st_x, st_y, st_isempty, st_isvalid, st_srid, st_astext, st_buffer, st_boundary, st_envelope, st_centroid, st_equals, st_union, st_intersection, st_difference, st_symdifference, st_contains, st_covers, st_crosses, st_disjoint, st_distance, st_intersects, st_overlaps, st_touches, st_within, st_geomfromtext, geomunion, st_extent, st_z, st_m

  • Example: st_contains(geom, st_setsrid(st_makepoint(-96.8, 46.9), 4326))
    • Matches records whose geometry contains the latlong point 46.9 N, 96.8 W (in northern Fargo, ND), ensuring the coordinate is in the WGS-84 coordinate system.

Math Operations

• Performs binary mathematical operations on columns, literals, or expressions directly in expressions.
• Operators:
  • binary: +, -, *, /, %, ^
  • bitwise: & (bitwise AND), | (bitwise OR), # (bitwise XOR), <<, >>
  • Example: id % 3 = 0
    • Matches records where the id column is divisible by 3.

Unitary Math Operations

• Applies mathematical functions to single values.
  • Operators: @ (absolute), |/ (square root), ! (factorial), ~ (bitwise NOT)
• Example: id = round(|/ 25)
  • Matches the record where the id equals the square root of 25, rounded to the nearest integer.

AND Operation

• Combines two subexpressions; both must evaluate to True.
• Syntax: expr1 AND expr2
• Example: creatorid = $_PRINCIPAL.roleid AND createdate > '2020-01-01'
  • Matches records created by the current role after January 1, 2020.

OR Operation

• Combines two subexpressions; one must evaluate to True.
• Syntax: expr1 OR expr2
• Example: status = 'active' OR status = 'pending'
  • Matches records where the status is either 'active' or 'pending'.

NOT Operation

• Inverts the result of a subexpression.
• Syntax: NOT expr
• Example: NOT (status = 'inactive')
  • Matches records where the status is anything but 'inactive'.

ANY Operation (Relationship Subquery)

• The ANY operation allows advanced subquery checks across related tables, enabling comparisons that traverse relationships between tables. It is particularly useful for scenarios where a match is determined by the existence of related records satisfying specific conditions.

• Syntax: $COLLECTION_ATTRIBUTE ANY(ValueComparison).attribute

  • $COLLECTION_ATTRIBUTE: Refers to a collection attribute in the current table that maps to related records. This can represent a one-to-many or many-to-many relationship.
  • ANY(ValueComparison): Specifies a condition applied to the related records within the collection. If any of the related records meet this condition, the expression evaluates to true.
  • .attribute: Accesses a specific attribute of the related table after confirming the match.

• Example: company.employees_collection ANY(roleid = $_PRINCIPAL.roleid).companyid
  • Matches records where any employee in the company has the same roleid as the current role.
    • This expression works as follows:
      • company: Refers to the relationship between the current table and the company table.
      • employees_collection: Accesses the related employees records.
      • ANY(roleid = $_PRINCIPAL.roleid): Filters employees to those where the roleid matches the current user's role ID.
      • .companyid: Accesses the companyid attribute of the matching employees.
    • This translates into an EXISTS SQL Query

       EXISTS (
       SELECT 1
       FROM employees e
       WHERE e.companyid = company.id
       AND e.roleid = :principal_roleid
       )

Filter Expressions and Policy Rules

Filter Expressions are used by Policy Rules to define precise access controls for users.

How Policy Rules and Filter Expressions Interact

Policy Rules are applied in order of least to most restrictive:

1. Root Role: Bypasses all rules.
2. Global Rule: Applies universally unless overridden.
3. Role Class: Defines access for a general group of users.
4. Role Class with a Filter: Adds conditional access to a general group.
5. Specific Roleid: Assigns access to an individual user or role.
6. Specific Roleid with a Filter: Grants individual access with additional conditions.

note

Root Role has special global admin privileges for the tenancy and should not be assigned a role class, nor any policy applied. The Root Role is created when the Devii account is first created, your login/username will be same for both Devii Portal role or Devii Root role, the passwords for each should be different, instructions on how to change the password for the Root Role are contained HERE

Creating a Policy

Follow these steps to design an effective access policy:

1. Identify Your Data: Review the tables and the data you manage.
2. Define Your Users: Determine who will access the data.
3. Group Your Users: Organize users into categories based on their needs.
4. Apply Filters (if necessary): Add filters to refine access for specific groups.
   1. Example: Allow a group to view only the data they created.

Advanced Examples

Advanced Policy Example:

(company.employees_collection ANY(roleid = $_PRINCIPAL.roleid).status = 'active')
AND 
(
    (policy.startdate <= CURRENT_DATE AND policy.enddate >= CURRENT_DATE)
    OR 
    policy.type IN ['standard', 'premium']
)
AND 
$_PRINCIPAL.tenantid = company.tenantid

Explanation:

Filter Active Employees:

company.employees_collection ANY(roleid = $_PRINCIPAL.roleid).status = 'active'

• Checks if the current role is associated with any active employees in the company.
• Uses a relationship subquery (ANY) to ensure the roleid matches an active employee.

Policy Date Validity:

(policy.startdate <= CURRENT_DATE AND policy.enddate >= CURRENT_DATE)

• Ensures the policy is currently valid by comparing the startdate and enddate against the current date.

Policy Type:

policy.type IN ['standard', 'premium']

• Allows only specific types of policies (standard or premium) if the policy is not active based on dates.

Tenant Validation:

$_PRINCIPAL.tenantid = company.tenantid

• Ensures the current role’s tenant ($_PRINCIPAL.tenantid) matches the company’s tenant. This enforces multi-tenant segregation.

Logical Grouping with AND/OR:

AND
(
    (policy.startdate <= CURRENT_DATE AND policy.enddate >= CURRENT_DATE)
    OR 
    policy.type IN ['standard', 'premium']
)

• Combines both conditions (date and type) with an OR, to match either (or both) conditions.