Skip to content

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 WHERE clause functions

Custom Functions

The @authorizations() Function

This function returns the authorization values for a given authorization name or a placeholder if empty.

Parameters

# Parameter Type Required Description
1 Authorization Name String Required The name of the authorization 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.

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 @hasAuthorization() Function

This function returns a boolean indicating if the current user has the specified authorization name and value combination. If the specified authorization name or authorization 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 Authorization Name String Required The name of the authorization
2 Authorization Value String Required The value to correspond with the authorization 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 @authorizations
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.