AWS lake-formation: Expand Lake Formation RLS filter expression SQL reference
Summary
Rewrites and greatly expands the row-level security (RLS) policy filter expression reference: adds reference-table subqueries (EXISTS/NOT EXISTS/IN/NOT IN, joins, derived tables, window functions), dynamic user attributes (${context.aws.PrincipalAccount}, ${context.iam.*}, ${context.datazone.projectId}), supported functions/CAST/EXTRACT/CURRENT_DATE, expanded data types and type-compatibility rules, comments, reserved-keyword escaping, unsupported syntax, and updated AWS CLI policy examples.
Security assessment
The page documents Lake Formation row-level security filter-expression syntax, i.e. how per-principal row filtering is authored. The added dynamic user attributes (principal account/ARN, IAM session tags, DataZone project) and reference-table subqueries with stated IAM/Lake Formation SELECT-with-grant-option requirements are authorization-relevant guidance, but the diff is documentation only with no reference to a specific vulnerability, CVE, or incident.
Evidence
+Dynamic user attributes insert a value from the querying identity at query time, so one policy can return different rows for each caller.
Diff
diff --git a/lake-formation/latest/dg/rls-policies-sql-support.md b/lake-formation/latest/dg/rls-policies-sql-support.md index 3d7c085..92f55c9 100644 --- a//lake-formation/latest/dg/rls-policies-sql-support.md +++ b//lake-formation/latest/dg/rls-policies-sql-support.md @@ -6,0 +7,2 @@ +OverviewSimple expressionsComposite expressionsReference table subqueriesSupported operatorsSupported functionsDynamic user attributesSupported data typesCase sensitivity rulesReserved keyword escapingCommentsUnsupported syntaxComplete examplesNested column referencesLimitations + @@ -9 +11 @@ -This section describes the syntax, operators, data types, and rules for filter expressions used in row-level security (RLS) policies. Filter expressions use the PartiQL subset supported by Lake Formation data cell filters. +This section describes the syntax, operators, data types, and rules for filter expressions used in row-level security (RLS) policies. Filter expressions use a subset of PartiQL. @@ -11 +13 @@ This section describes the syntax, operators, data types, and rules for filter e -###### Overview +## Overview @@ -15,11 +17 @@ A filter expression defines the condition that rows must satisfy for a principal -Filter expressions have the following constraints: - - * Maximum length: 2048 characters - - * Must reference only columns declared in `AttachmentColumns` - - * Must use supported operators and data types - - * Cannot contain subqueries, joins, user-defined functions, or column-to-column comparisons - - +## Simple expressions @@ -27,4 +19 @@ Filter expressions have the following constraints: - -###### Simple expressions - -A simple expression compares a column to a literal value. +A simple expression compares an attachment column to a literal value or to another attachment column, or uses a boolean column or literal as the condition. @@ -33,0 +23,2 @@ A simple expression compares a column to a literal value. + column_name operator column_name + boolean_column @@ -40,2 +31,3 @@ A simple expression compares a column to a literal value. - salary < 100000 - country <> 'US' + home_region = region + is_active + NOT is_deleted @@ -43 +35 @@ A simple expression compares a column to a literal value. -###### Composite expressions +## Composite expressions @@ -59 +51 @@ Combine multiple conditions using `AND`, `OR`, and `NOT`. - (department = 'Sales' OR department = 'Marketing') AND country = 'US' + is_active AND (region = 'us-east-1' OR region = 'eu-west-1') @@ -63 +55,68 @@ Use parentheses to control evaluation order. Without parentheses, `AND` has high -###### Supported operators +Each operand of `AND`, `OR`, and `NOT` must be a condition or a boolean value, such as a boolean column, `TRUE`, `FALSE`, or a function that returns a boolean. A subquery can't be an operand directly. For more information, see Reference table subqueries in this section. + +## Reference table subqueries + +Filter expressions support `EXISTS`, `NOT EXISTS`, `IN`, and `NOT IN` subqueries over reference tables. To compare an attachment column to a reference table column, use a subquery. + + + [NOT] EXISTS ( + SELECT select_item [, select_item ...] + FROM database_name.table_name [[AS] alias] + [supported join clauses] + [WHERE condition] + ) + + attachment_column [NOT] IN ( + SELECT select_item + FROM database_name.table_name [[AS] alias] + [supported join clauses] + [WHERE condition] + ) + +A reference table name must be qualified as `databaseName.tableName` or `catalogId.databaseName.tableName`. The catalog ID must be the policy creator's account ID. Enclose a numeric catalog ID in double quotes, such as `"123456789012".security.entitlements`. + +A reference table column can be qualified by a table alias (`e.region`), `databaseName.tableName` (`security.entitlements.region`), or `catalogId.databaseName.tableName`. Scalar struct fields of a reference table can also be referenced, such as `e.profile.customer_id`. An unqualified column name always refers to an attachment column. + +Each reference table must meet the following requirements: + + * The table is a base table registered with Lake Formation. Views and resource links aren't supported. + + * The policy creator's IAM permissions allow `glue:GetTable` on the table. + + * The policy creator has Lake Formation `SELECT` with grant option on the table. + + + + + + EXISTS ( + SELECT 1 + FROM security.entitlements e + WHERE e.region = region + AND e.access_level = 'full' + ) + +A regular subquery select list can contain a column, `*`, a literal, a `CAST`, `TRIM`, `SUBSTRING`, or a supported function call. A star can be unqualified or qualified by a namespace, such as `security.entitlements.*`. + +A subquery's `WHERE` clause can contain another `EXISTS` or `IN` subquery, up to the limit of four subqueries in total. + +###### Note + +Tables included in RLS policy filter expressions are referenced by ID. If a table is deleted, the policy is considered INVALID. INVALID policies are treated as FALSE. For more information, see [Row-level security policy limitations](./rls-policies-limitations.html). + +###### Join clauses + +Use `JOIN` to reference multiple tables in a policy expression. + +The `ON` condition follows the same rules as a `WHERE` condition, and can be a boolean column such as `ON r.enabled`. + + + EXISTS ( + SELECT 1 + FROM security.customer_regions c + JOIN security.region_status r ON r.region = c.region + WHERE c.customer_id = customer_id + AND r.enabled = true + ) + +## Supported operators @@ -74,6 +133,11 @@ Operator | Description | Example -`BETWEEN`| Within a range (inclusive)| `age BETWEEN 18 AND 65` -`IN`| Matches any value in a list| `region IN ('us-east-1', 'us-west-2')` -`LIKE`| Pattern matching with wildcards| `email LIKE '%@example.com'` -`NOT`| Negates a condition| `NOT (status = 'deleted')` -`IS NULL`| Checks for null value| `manager_id IS NULL` -`IS NOT NULL`| Checks for non-null value| `email IS NOT NULL` +`BETWEEN`, `NOT BETWEEN`| Tests an inclusive range| `age BETWEEN 18 AND 65` +`IN`, `NOT IN`| Tests a literal list or a subquery result| `region IN ('us-east-1', 'us-west-2')` +`LIKE`, `NOT LIKE`| Pattern matching with wildcards| `email LIKE '%@example.com'` +`AND`, `OR`, `NOT`| Combines or negates conditions| `active = true AND deleted = false` +`IS NULL`, `IS NOT NULL`| Tests for a null or non-null value| `manager_id IS NULL` +`+`, `-`, `*`| Numeric arithmetic| `(amount * quantity) > 1000` +unary `-`| Negates a numeric value| `balance >= -100` +`EXISTS`, `NOT EXISTS`| Tests whether a subquery returns rows| `EXISTS (SELECT 1 FROM security.entitlements e WHERE e.region = region)` +literal `IN` array| Tests whether an array contains a literal| `'admin' IN roles` + +The operands of a comparison must have compatible types. For example, you can't compare a string column with a numeric literal. For more information, see Type compatibility in this section. @@ -83 +147,9 @@ Operator | Description | Example -The `BETWEEN` operator tests whether a value falls within an inclusive range. `salary BETWEEN 50000 AND 100000` is equivalent to `salary >= 50000 AND salary <= 100000`. +The `BETWEEN` operator tests whether a value falls within an inclusive range. + + + salary BETWEEN 50000 AND 100000 + start_date BETWEEN '2026-01-01' AND CURRENT_DATE + +`salary BETWEEN 50000 AND 100000` is equivalent to `salary >= 50000 AND salary <= 100000`. + +Each bound must be type-compatible with the tested value. A bound can be a column, a literal, or an expression, but not a subquery. @@ -87 +159,20 @@ The `BETWEEN` operator tests whether a value falls within an inclusive range. `s -The `IN` operator tests whether a value matches any value in a list, for example `department IN ('Engineering', 'Sales', 'Marketing')`. +The `IN` operator supports a literal list, a subquery, or array containment. + + + department IN ('Engineering') + region IN ('us-east-1', 'us-west-2', 'eu-west-1') + region IN (SELECT r.region FROM security.region_status r WHERE r.enabled = true) + 'admin' IN roles + +The left operand of an `IN` literal list or an `IN` subquery must be a column. An `IN` literal list must contain one or more literals, and each literal must be type-compatible with the column. For array containment, the left operand must be a literal and the right operand must be an array column. + +When an `IN` subquery selects exactly one item, that item must be type-compatible with the compared column. An `IN` subquery compared with a `string` or `varchar` column has the following additional rules: + + * The subquery must select exactly one column. + + * The subquery can't select an aggregate function (`MIN` or `MAX`). + + * The subquery's `FROM` clause must use table names and aliases that differ from every qualifier of the compared column. + + + @@ -91 +182,8 @@ The `IN` operator tests whether a value matches any value in a list, for example -The `LIKE` operator performs pattern matching. Use `%` as a wildcard for any sequence of characters and `_` for a single character, for example `email LIKE '%@example.com'`. +The `LIKE` operator performs pattern matching on strings. Use `%` as a wildcard for any sequence of characters and `_` for a single character. + + + email LIKE '%@example.com' + name LIKE 'J%' + code LIKE 'A_B' + +The value and the pattern must both be strings. The `ESCAPE` clause isn't supported. @@ -95 +193 @@ The `LIKE` operator performs pattern matching. Use `%` as a wildcard for any seq -Test whether a column value is null or not null, for example `manager_id IS NULL` or `email IS NOT NULL`. +Test whether a value is null or not null. The operand can be a column or an expression of any type. @@ -97 +194,0 @@ Test whether a column value is null or not null, for example `manager_id IS NULL -###### Note @@ -99 +196,153 @@ Test whether a column value is null or not null, for example `manager_id IS NULL -`IS NULL` and `IS NOT NULL` cannot be used on partition columns. See [Row-level security policy limitations](./rls-policies-limitations.html) for details. + manager_id IS NULL + email IS NOT NULL + +To test for null, use `IS NULL`. A comparison such as `manager_id = NULL` isn't supported. + +## Supported functions + +The following builtin functions are supported: + + * `EXISTS` + + * `LENGTH` + + * `LOWER` +