AWS Security ChangesHomeSearch

AWS lake-formation: Expand Lake Formation RLS filter expression SQL reference

Service: lake-formation · 2026-11-10 · Documentation medium

File: lake-formation/latest/dg/rls-policies-sql-support.md · Type: authz

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`
+