Custom WHERE Clause Functions
Audience: Data Owners
Content Summary: The Immuta Policy Builder allows you to use custom functions that extend the PostgreSQL WHERE syntax. These custom functions can be seen as utilities that help you create policies easier. Using the Immuta Policy Builder, you can include these functions in your policy queries by choosing where in the sub-action drop-down menu.
This page defines the parameters for various custom WHERE clause functions.
Custom Functions
The @attributes() Function
This function returns the attribute values for a given attribute name or a placeholder if empty.
Parameters
| # | Parameter | Type | Required | Description |
|---|---|---|---|---|
| 1 | Attribute Name | String | Required | The name of the attribute to retrieve values for |
| 2 | Placeholder | String | Optional | A placeholder in case the list of values is empty |
The @columnTagged() Function
This function returns the column name with the specified tag.
If this function is used in a Global Policy and the tag doesn't exist on a data source, the policy will not be applied.
Parameters
| # | Parameter | Type | Required | Description |
|---|---|---|---|---|
| 1 | Tag Name | String | Required | The name of the tag |
The @groups() Function
This function returns a list of groups the user is a member of or a placeholder if empty.
Parameters
| # | Parameter | Type | Required | Description |
|---|---|---|---|---|
| 1 | Placeholder | String | Optional | A placeholder in case the list of values is empty |
The @hasAttribute() Function
This function returns a boolean indicating if the current user has the specified attribute name and value
combination. If the specified attribute name or attribute value has a single quote, you will need to escape it
using a \'\' expression within a custom WHERE policy.
Parameters
| # | Parameter | Type | Required | Description |
|---|---|---|---|---|
| 1 | Attribute Name | String | Required | The name of the attribute |
| 2 | Attribute Value | String | Required | The value to correspond with the attribute name |
The @iam Function
This function returns the IAM ID for the current user.
Parameters
None.
The @interpolatedComparison() Function
The Interpolated Comparison function is perhaps the most complex custom WHERE clause function. It essentially generates
chained WHERE clauses. As opposed to the rest of the custom WHERE clause functions, its output is not a list of
values or a boolean, but rather the comparison itself.
Parameters
| # | Parameter | Type | Required | Description |
|---|---|---|---|---|
| 1 | Column Name | String | Required | The name of a column in the remote database to use in the left-hand side of the clauses |
| 2 | Comparison Operator | String | Required | The comparison operator to use to compare the column and array value |
| 3 | Literal Template | String | Required | A necessary SQL logic and the template string which will be replaced with an array |
| 4 | Template String to Replace | String | Required | The string to replace in Literal Template with the array value |
| 5 | Custom function | Function | Required | A tagged function¹. Valid functions: @groups, @purposes and @attributes |
| 6 | Logical (Chain) Operator | String | Required | The logical operator used to link each generated clause (AND, OR, etc.) |
¹ A custom function will not accept a placeholder argument when used inside @interpolatedComparison.
Example
The following invocation of interpolatedComparison():
WHERE @interpolatedComparison("group", "=", "upper('##')", "##", @groups, "OR")
is equivalent to writing the following SQL WHERE clause, given that a user belongs to two groups called founders
and engineers:
WHERE ((group = upper('founders')) or (group = upper('engineers')))
The @isInGroups() Function
This function returns a boolean indicating if the current user is a member of all of the specified groups.
If any of the specified groups has a single quote, you will need to escape it
using a \'\' expression within a custom WHERE policy.
Parameters
| # | Parameter | Type | Required | Description |
|---|---|---|---|---|
| 1 | Group names | Array (String) | Required | A list of group names, e.g. groups('group_a', 'group_b', 'group_c') |
The @isUsingPurpose() Function
This function returns a boolean indicating if the current user is using the specified purpose. If the specified
purpose has a single quote, you will need to escape it using a \'\' expression within a custom WHERE policy.
Parameters
| # | Parameter | Type | Required | Description |
|---|---|---|---|---|
| 1 | Purpose | String | Required | The name of the purpose to check the user against |
The @purposes() Function
This function returns a list of purposes the user is currently using or a placeholder if empty.
Parameters
| # | Parameter | Type | Required | Description |
|---|---|---|---|---|
| 1 | Placeholder | String | Optional | A placeholder in case the list of values is empty |
The @username Function
This function returns the current user's user name.
Parameters
None.