在查询上执行策略

阅读约需 87分钟 分钟,13节中含16个示例。

版次 2026.11.31 示例于 2026年11月19日 通过 108个示例中有108个通过 变化内容

AI 翻译。由人工以英文核验。 我们如何制作图书

译自英文版图书,版次 2026.11.31。 阅读原文

学完本章,你将能够对平台运行的每一条查询执行访问决策。你将重写每条查询,把它读取的每张表替换为一个子查询,该子查询只保留用户有权查看的行,并对用户无权查看的列做脱敏;拒绝所有读取其他内容的查询;通过运行真实查询来测试谁能得到什么;并能指出在 DuckDB 前面做查询重写,在什么情况下就不再起任何保护作用。

只有当策略对到达在线学习平台数据仓库的每一条查询都成立时,它才真正保护了这个仓库,而该仓库保存着学习者的联系方式、付款记录以及他们写下的文本。第 5 章用 Rego 写出了平台的访问策略。给定一个用户、一张表和用户想要读取的列,以及(可选的)读取的理由,策略会返回一个决策:是否allow这次读取,哪些行过滤器保留用户有权查看的行,哪些列掩码改变某一列的显示方式(redact、last4、pseudonymize或redact_contacts),以及reasons。决策本身不起任何作用。客服人员的 SQL 以文本形式到达 DuckDB,因此两者之间必须有某个环节,把决策应用到这段文本读取的每一张表上,无论查询是怎样写的。

本书的共享模块lib.enforce负责执行决策;我们称之为执行层。它的Enforcer持有一个 DuckDB 连接,其中为目录里的每张表建有一个视图(lib.warehouse.connect),还持有这些表的模式、策略,以及一把用于生成化名的密钥。用户永远拿不到这个连接。Enforcer.sql(query, user)重写用户的查询并运行重写后的结果。服务只暴露lib.enforce.answer,由它调用前者并返回行:绝不暴露关系对象、连接的句柄,也不暴露Enforcer.run,因为它会带着密钥执行由执行层编写的 SQL。代码运行在 sqlglot 30.33.0和 DuckDB 2.0.0上。

找出查询读取的每张表和每一列

最近一次修改见于版次 2026.11.27

策略只能对被问到的内容作出决定,因此这一层必须知道查询读取的每张表,以及从每张表读取的列。从 SQL 文本中读出这些信息是一个带有陷阱的解析问题:*、别名、带有 USING 的连接、公共表表达式(CTE)以及任意大小写的标识符。sqlglot 会把查询解析成一棵树,它的 qualify 步骤会依据模式(schema)解析每一列,用所属的表对其限定,并且在有模式可供展开时,把 * 展开为它所代表的各列(v30.24.0 中的 qualify)。这一层在启动时构建该模式:列出目录中的各张表,并从 lib.warehouse.connect 为每张表创建的视图中读取其各列及类型,因此 DuckDB 为其他用途持有的视图永远不会进入该模式。

下面四个查询各带一个陷阱,用来展示 Enforcer.reads 返回什么:返回的是策略被询问的那些列,它们必须与每个查询所点名的列一致。

tables-a-query-reads.py Python 3.12.15 · duckdb 2.0.0 · pyarrow 26.0.1 · pyiceberg 0.12.0 · regopy 1.5.2 · sqlglot 30.33.0 通过
"""The columns four queries read from each table, and how each dialect normalizes the quoted name "LEARNERS". Theprose's point rests on the query with a CTE and on the normalization lines at the end."""import sqlglotfrom sqlglot import expfrom sqlglot.optimizer.normalize_identifiers import normalize_identifiersfrom lib.catalog import open_catalogfrom lib.enforce import Enforcer# only these two tables are loaded, so the layer's schema holds nothing elseplatform = Enforcer(open_catalog(["crm.learners", "support.tickets"]))# What each query reads, found by sqlglot's qualify against the catalog's schema, with `*` expanded.queries = {    "every column": "select * from crm.learners",    "a join": """select l.country, count(*) as tickets                 from crm.learners as l join support.tickets as t using (learner_id) group by all""",    # exclude drops notes from the CTE, so the reads name every other column of crm.learners    "a CTE": """with emea as (select * exclude (notes) from crm.learners where support_region = 'emea')                select email from emea""",    "names in capitals": 'select EMAIL from "CRM"."LEARNERS"',}for name, sql in queries.items():    for table, columns in platform.reads(sql).items():        print(f"{name}, {table}: {', '.join(columns)}")# DuckDB resolves quoted capitals to the lowercase table. Whether sqlglot lowercases them depends on the dialect.capitals = 'select count(*) from "CRM"."LEARNERS"'print('DuckDB, "CRM"."LEARNERS":', f"{platform.connection.sql(capitals).fetchall()[0][0]:,} rows")for dialect in ["duckdb", "trino", "postgres"]:    # the name the dialect's parser resolves the quoted identifier to    table = normalize_identifiers(sqlglot.parse_one('select * from "LEARNERS"', read=dialect), dialect=dialect).find(exp.Table)    print(f'{dialect} normalizes "LEARNERS" to:', table.name)
输出python tables-a-query-reads.py
every column, crm.learners: ai_training_consent, city, closed_at, country, date_of_birth, email, full_name, learner_id, marketing_consent, notes, phone, signup_at, status, street_address, support_region
a join, crm.learners: country, learner_id
a join, support.tickets: learner_id
a CTE, crm.learners: ai_training_consent, city, closed_at, country, date_of_birth, email, full_name, learner_id, marketing_consent, phone, signup_at, status, street_address, support_region
names in capitals, crm.learners: email
DuckDB, "CRM"."LEARNERS": 5,000 rows
duckdb normalizes "LEARNERS" to: learners
trino normalizes "LEARNERS" to: learners
postgres normalizes "LEARNERS" to: LEARNERS

读取的内容取决于查询点名了什么,而不是返回了什么。SELECT * 读取 crm.learners 的每一列;连接则从两张表中都读取 learner_id,因为 USING 会比较它们,尽管两者都没有出现在输出中。CTE 只返回 email,但它点名了 crm.learners 中除 notes 之外的所有列,所以这一层会就这些列全部询问策略。查询的读取范围可能大于它的返回内容,但绝不会小于它。"CRM"."LEARNERS" 同样解析为 crm.learners,因为这一层按 DuckDB 解析标识符的方式对其进行规范化:对于该名称,DuckDB 返回 5,000 rows(来自 crm.learners)。方言很重要。对于 DuckDB 和 Trino,sqlglot 会把 "LEARNERS" 转为小写;对于 PostgreSQL,则保留为 LEARNERS,所以这一层使用运行该查询的引擎所用的方言进行规范化。

qualify 有一个缺口,它决定了这一层的设计:对于已知表中的未知列,它会失败;但对于完全不在模式中的表,它会直接略过,并让该表的 * 保持未展开。请在与 marketing.audience 的连接上运行它,该表代表在读取模式之后由某条流水线写入的表;然后把该查询和另外五个读取操作送入这一层:

unknown-tables-denied.py Python 3.12.15 · duckdb 2.0.0 · pyarrow 26.0.1 · pyiceberg 0.12.0 · regopy 1.5.2 · sqlglot 30.33.0 通过
"""Runs sqlglot's qualify on a join with a table the schema lacks, then sends that join and five other reads throughthe enforcement layer. The prose's point rests on the qualify line, where the star stays, and on the denial lines."""import sqlglotfrom sqlglot.optimizer.qualify import qualifyfrom lib.catalog import open_catalogfrom lib.enforce import Denied, Enforcerplatform = Enforcer(open_catalog(["crm.learners"]))# marketing.audience stands for a table a pipeline wrote after the schema was read. qualify alone lets it through.query = "select * from crm.learners as l join marketing.audience as a on a.learner_id = l.learner_id"print("qualify on its own:", qualify(sqlglot.parse_one(query, read="duckdb"), schema=platform.schema, dialect="duckdb").sql("duckdb"))# The enforcement layer denies anything that isn't a catalog table, before it asks the policy.attempts = {    "a table missing from the catalog": query,    "a column the table doesn't have": "select shoe_size from crm.learners",    # scan_crm_learners is the scan lib.warehouse registers for crm.learners, and DuckDB resolves it like a table    "the scan behind a view": "select count(*) from scan_crm_learners",    "a Parquet file": "select count(*) from read_parquet('data/platform/learners.parquet')",    "a table function": "select count(*) from query_table('crm.learners')",    "a statement that isn't a query": "summarize crm.learners",}for name, sql in attempts.items():    try:        platform.rewrite(sql, "analyst-1")        print(f"{name}: allowed")    except Denied as reason:        print(f"{name}: denied, {reason}")# What those reads would do on the layer's own connection, without the rewrite in front of it.rows = lambda sql: f"{platform.connection.sql(sql).fetchall()[0][0]:,} rows"print("the scan, run directly:", rows("select count(*) from scan_crm_learners"))print("the table function, run directly:", rows("select count(*) from query_table('crm.learners')"))try:    rows("select count(*) from read_parquet('data/platform/learners.parquet')")except Exception as error:    print("the file, run directly:", str(error).split(":")[0])# the layer turned external access off, and DuckDB refuses to turn it back on while the database runstry:    platform.connection.execute("set enable_external_access = true")except Exception as error:    print("turning external access back on:", str(error).split(": ", 1)[1])
输出python unknown-tables-denied.py
qualify on its own: SELECT * FROM "crm"."learners" AS "l" JOIN "marketing"."audience" AS "a" ON "a"."learner_id" = "l"."learner_id"
a table missing from the catalog: denied, not a table in the catalog: marketing.audience AS a
a column the table doesn't have: denied, Column 'shoe_size' could not be resolved. Line: 1, Col: 16
the scan behind a view: denied, not a table in the catalog: scan_crm_learners
a Parquet file: denied, not a table in the catalog: READ_PARQUET('data/platform/learners.parquet')
a table function: denied, not a table in the catalog: QUERY_TABLE('crm.learners')
a statement that isn't a query: denied, only a single query is allowed
the scan, run directly: 5,000 rows
the table function, run directly: 5,000 rows
the file, run directly: Permission Error
turning external access back on: Cannot enable external access while database is running

单独使用 qualify 时,它会原样返回带有 SELECT * 的查询,且不报错,所以信任它的一层会就 crm.learners 询问策略,而让 marketing.audience 不经检查就通过。这一层会在询问策略之前,先拒绝目录之外的每张表,而 qualify 本身会拒绝表中并不存在的列。其余四个读取操作说明了为什么这条规则要定得宽泛。lib.warehouse 会以各自独立的名称注册每张表的扫描,例如 scan_crm_learners。在这一层的连接上直接运行时,该扫描会返回 5,000 rows(原始学习者数据)。query_table 会读取其字符串所指名的任何表,直接运行时返回 5,000 rows。read_parquet 读取文件,而 SUMMARIZE 不是改写步骤能够分析的查询。前三者不是目录中的表,最后一个不是查询,因此这四个都会被拒绝。这一层的连接还关闭了 DuckDB 的 enable_external_access,所以即使某次文件读取绕过了改写,也仍会失败,并给出 Permission Error(Securing DuckDB)。

工作准则: 对查询点名的每张表和每一列都要询问策略,其中 * 要依据目录的模式展开,并拒绝该模式中不存在的一切内容:未知的表、文件、表函数,以及单个查询之外的语句。一次拒绝的代价是向平台团队提一个问题;一次放行的代价则是该表所存的全部内容。

这部分尚未翻译,因此以英文显示。

Allow only catalog tables, CTEs and subqueries

最近一次修改见于版次 2026.11.31

Denying names the catalog doesn't hold isn't enough on its own, because a query can read data through sources that aren't table names. A table function can sit under LATERAL, or in a subquery in the select list. Outside a CTE's scope, or before the CTE is defined, DuckDB resolves its name to whatever else the connection holds under that name. A check that inspects only table names, and accepts any name a CTE uses, passes both.

So the layer checks sources against an allowlist. Enforcer.check_sources walks the whole query and requires every FROM and JOIN source, at every level, to be a catalog table, a CTE in scope or a subquery, and a LATERAL to be over a subquery. Everything else is denied, including table functions wherever they appear, VALUES, UNNEST and joins in parentheses. A CTE is in scope in its own query, below it and in later CTEs, and a recursive one also in its UNION's recursive branch. An allowlist fails closed on a kind of source nobody thought of.

A query through the layer must change nothing. DuckDB's scalar write_log writes into the engine's query log. duckdb_functions() marks such functions: has_side_effects is "true if this function changes the database state" (DuckDB, duckdb_functions). At 2.0.0 it also marks functions that only vary, such as random and uuid. The layer denies every flagged function, and every macro that calls one, such as pg_sleep, reading the list from duckdb_functions() when it starts. A list sorted by hand into harmful and harmless can go stale on an upgrade without anyone noticing, and a governed read seldom needs a random number.

lib.enforce keeps the cases as a regression test, source_cases, which chapter 10's CI runs along with the order test below. Running them shows each denial and whether anything was logged:

catalog-sources-only.py Python 3.12.15 · duckdb 2.0.0 · pyarrow 26.0.1 · pyiceberg 0.12.0 · regopy 1.5.2 · sqlglot 30.33.0 通过
"""Runs the allowlist's regression cases through the layer and prints each denial with its reason; a case passes when it'sdenied exactly when it must be and its parse logs nothing. The prose's points rest on the count of cases that pass, the functions-denied line and the allowed-query line at the end."""from lib.catalog import open_catalogfrom lib.enforce import MUST_ALLOW, MUST_DENY, Enforcer, source_casesplatform = Enforcer(open_catalog(["crm.learners", "support.tickets"]))# The allowlist's regression test, lib.enforce.source_cases, which chapter 10's CI runs too: queries that read data# other than through a catalog table, a call to each function DuckDB marks as having side effects, and a query the# layer must still allow. A case passes when the layer denies it exactly when it must, and its parse logs nothing.cases = source_cases(platform)for case in cases:    if case["case"] in MUST_DENY:        print(f"{case['case']}:", f"denied, {case['reason']}" if case["denied"] else "ALLOWED")functions = [case for case in cases if case["must"] == "deny" and case["case"] not in MUST_DENY]print("functions with side effects:", ", ".join(case["case"].removeprefix("the function ") for case in functions))print("functions with side effects, denied:", f"{sum(case['denied'] for case in functions)} of {len(functions)}")print("cases whose parse sqlglot logged, quoting the query:", f"{sum(case['logged'] for case in cases)} of {len(cases)}")passing = [case for case in cases if case["denied"] == (case["must"] == "deny") and not case["logged"]]print("cases that pass:", f"{len(passing)} of {len(cases)}")# What two of the cases do on the layer's own connection, without the layer in front of it.# the name is used before its CTE is defined, so DuckDB resolves it to the scan of the same namebefore_the_cte = platform.connection.sql(MUST_DENY["a CTE's name before the CTE"]).fetchall()[0][0]print("a CTE's name before the CTE, run directly:", f"{before_the_cte:,} rows")# turns on DuckDB's query log, the one write_log writes into, so the entries below are what the call leftplatform.connection.execute("CALL enable_logging('QueryLog')")platform.connection.sql(MUST_DENY["a function that writes DuckDB's log"]).fetchall()logged = [message for (message,) in platform.connection.sql("select message from duckdb_logs where type = 'QueryLog'").fetchall()]print("the log function, run directly, query-log entries it leaves:", logged)# the query the allowlist must admit: a CTE, a join and a scalar subquery over catalog tablesfor name, query in MUST_ALLOW.items():    print(f"{name}, allowed:", f"{platform.sql(query, 'agent-a05').fetchall()[0][0]:,} rows")
输出python catalog-sources-only.py
a table function in FROM: denied, not a table in the catalog: QUERY_TABLE('crm.learners')
a table function in a scalar subquery: denied, not a table in the catalog: QUERY_TABLE('crm.learners')
a table function under LATERAL: denied, a LATERAL that isn't a subquery: LATERAL QUERY_TABLE('crm.learners')
a catalog-qualified name: denied, not a table in the catalog: memory.crm.learners
an information_schema view: denied, not a table in the catalog: information_schema.tables
a CTE's name outside its scope: denied, not a table in the catalog: other
a CTE's name before the CTE: denied, not a table in the catalog: scan_crm_learners
a function that writes DuckDB's log: denied, a function DuckDB marks as having side effects: write_log
a statement sqlglot keeps as text: denied, only a single query is allowed
functions with side effects: current_connection_id, current_query, current_query_id, current_transaction_id, currval, error, gen_random_uuid, nextval, pg_sleep, random, setseed, setval, sleep_ms, stats, uuid, uuidv4, uuidv7, vector_type, write_log
functions with side effects, denied: 19 of 19
cases whose parse sqlglot logged, quoting the query: 0 of 29
cases that pass: 29 of 29
a CTE's name before the CTE, run directly: 5,000 rows
the log function, run directly, query-log entries it leaves: ["select write_log('an entry', log_type := 'QueryLog')", 'an entry']
a CTE, a join and a scalar subquery, allowed: 61 rows

Each case the layer must deny is refused before any policy is asked, with a reason that names the source. The parse has to leave no trace of its own. sqlglot logs any statement it keeps as text, such as a CALL, values included (sqlglot source, v30.24.0), so the layer silences the parser's warnings, and every case checks that nothing was logged. 29 of 29 cases pass.

On the layer's own connection, two cases show what their denials prevent. The name used before its CTE reads the scan of the same name, 5,000 rows of raw learners, and write_log leaves ["select write_log('an entry', log_type := 'QueryLog')", 'an entry'] in the query log: its own statement, and an entry no query made. Every function with side effects is denied, 19 of 19.

The allowlist also has to let ordinary queries through. Run as agent-a05, the EMEA support agent, a CTE, a join and a scalar subquery over catalog tables return 61 rows.

Working rule: Check every source against an allowlist of catalog tables, CTEs in scope and subqueries, deny the rest, and deny every function the engine marks as having side effects, read from its own list. Keep the cases as a regression test for every layer change and engine upgrade, and add one for each new kind of source or function.

屏蔽列引用会泄露数据

最近一次修改见于版次 2026.11.27

在已知列之后,最直观的强制执行方式是改写这些列:只要查询选择了 email,而决策要求对其屏蔽,就改为选择屏蔽后的值。这种做法只需要第一节中的列清单,而且在人们用来测试的查询上看起来正确。它之所以失败,是因为查询可以读取一列而不选择它,DuckDB 也可以在不指明列名的情况下返回一列。

Enforcer.mask_select_list 完成这种列改写,保留它只是为了展示其失败之处。我们用五个针对学习者 2 的电子邮件的读取来测试它,执行身份是 analyst-1,这是一位数据科学家,可以读取 crm.learners,其中 email 和其他个人列经过假名化,notes 这类自由文本则被涂黑。第一次输出是对普通读取的改写结果,其中任何已存储的电子邮件都被替换为占位符,因为这些示例从不输出真实邮件。请先预测改写能拦住五个读取中的几个:

select-list-masks-leak.py Python 3.12.15 · duckdb 2.0.0 · pyarrow 26.0.1 · pyiceberg 0.12.0 · regopy 1.5.2 · sqlglot 30.33.0 通过
"""Masks the email in the select list only, then runs five reads aimed at one learner's email through that rewrite andprints what each returns. The prose's point rests on the five attack lines, each of which returns learner 2's ID, thestored email, or an error that holds it."""import reimport duckdbfrom lib.catalog import open_catalogfrom lib.enforce import EMAIL, Enforcerplatform = Enforcer(open_catalog(["crm.learners"]))# analyst-1, a data scientist, may read crm.learners with `email` pseudonymized. The attacks aim at learner 2.email = platform.connection.sql("select email from crm.learners where learner_id = 2").fetchall()[0][0]print("the column rewrite:", platform.mask_select_list("select learner_id, email from crm.learners", "analyst-1"))attacks = {    "a WHERE on the raw column": f"select learner_id from crm.learners where email = '{email}'",    "the whole row as a struct": "select struct_extract(l, 'email') from crm.learners as l where learner_id = 2",    "COLUMNS(...)": "select columns('^email$') from crm.learners where learner_id = 2",    "to_json": "select to_json(l) ->> 'email' from crm.learners as l where learner_id = 2",    "a cast error": "select learner_id from crm.learners where cast(email as integer) = 0",}for name, sql in attacks.items():    try:        # the column rewrite changes only the select list, so every other clause reads the stored email        answer = str(platform.run(platform.mask_select_list(sql, "analyst-1")).fetchall())    except duckdb.Error as error:        answer = str(error).splitlines()[0]    print(f"{name}:", re.sub(EMAIL, "<a stored email>", answer))      # the book never prints a stored email
输出python select-list-masks-leak.py
the column rewrite: SELECT "learners"."learner_id" AS "learner_id", SHA256($pseudonym_key || CAST("learners"."email" AS TEXT)) AS "email" FROM "crm"."learners" AS "learners"
a WHERE on the raw column: [(2,)]
the whole row as a struct: [('<a stored email>',)]
COLUMNS(...): [('<a stored email>',)]
to_json: [('<a stored email>',)]
a cast error: Conversion Error: Could not convert string '<a stored email>' to INT32 when casting from source column email

列改写一个也没有拦住。它对普通读取的改写是正确的:email 返回的是哈希值。这五个读取通过三种方式绕过了它:

  • 对原始列的 WHERE 从不经过选择列表,因此比较的是已存储的电子邮件。分析师猜出一个电子邮件地址,就能得到学习者 2 的 ID,即 [(2,)],而这个 ID 可以通过 learner_id 把此人与其所有可连接的行关联起来。
  • 以结构体形式返回整行、COLUMNS(...) 和 to_json 会返回已存储的电子邮件,即 [('<a stored email>',)],而改写无从找到任何名为 email 的列引用:DuckDB 自己展开整行,或按模式匹配列名。
  • 类型转换错误会在报错信息中打印已存储的电子邮件。DuckDB 的转换错误包含该值,PostgreSQL 的文档也警告说,“大多数转换函数会在它们产生的错误信息中包含其输入值”(PostgreSQL 18, Rules and Privileges)。

在查询提到该列的每个位置都进行屏蔽,包括 WHERE 子句,可以拦住第一个和最后一个读取。但它拦不住中间三个,因为它们从不提及该列。

工作准则: 不要通过改写列引用来强制执行屏蔽。无论覆盖多少种引用形式,引擎读取列的方式总比改写能够识别的更多。应当在数据的来源处进行屏蔽,使每次读取看到的都是屏蔽后的值,下一节就是这样做的。

用经过过滤和掩码处理的子查询替换每张表

最近一次修改见于版次 2026.11.27

改写列引用之所以泄露,是因为原始表仍留在查询中。解决办法是替换表本身。Enforcer.rewrite会把查询中的每张目录表替换为一个根据该用户的决策构建的子查询,并沿用查询自己为该表所起的别名:

  • 表的每一列,按表中的顺序排列,因此*、结构体或COLUMNS(...)看到的是它所预期的形状;
  • 查询读取的每一列,按决策的要求原样保留或加以掩码,置于一个依据行过滤条件的CASE之中,行过滤条件将在后面的一节中说明;
  • 查询未读取的每一列,用带类型的NULL表示,因为策略并未被问及这些列;
  • 一个WHERE子句,包含决策中的所有行过滤条件。

用户查询所做的一切,从它的WHERE到它对结构体的读取,随后都发生在这个子查询之上。这与 Trino 的 OPA 插件所遵循的模型相同:行过滤条件在本质上相当于一个额外的WHERE子句,关于引擎的一节对此有所展示。

以instructor-17为例,该用户读取自己所授课程的评价中的低分评价。策略保留其自己课程的评价,并从body中去除联系方式。打印出的改写结果展示了做法:

table-rewrite.py Python 3.12.15 · duckdb 2.0.0 · pyarrow 26.0.1 · pyiceberg 0.12.0 · regopy 1.5.2 · sqlglot 30.33.0 通过
"""Rewrites instructor-17's query over the reviews, prints the rewrite and runs it. The prose's point rests on the tworesult lines: how many reviews come back, and how many of them still hold an email."""import sqlglotfrom lib.catalog import open_catalogfrom lib.enforce import Enforcerplatform = Enforcer(open_catalog(["learning.reviews"]))# instructor-17 reads the low ratings of their courses' reviews. The policy filters reviews to their six courses and# shows `body` with contact details removed.query = "select course_id, rating, body from learning.reviews where rating <= 2"# the query as text, with the reviews table replaced by a subquery built from the decisionrewritten = platform.rewrite(query, "instructor-17")print(sqlglot.transpile(rewritten, read="duckdb", write="duckdb", pretty=True)[0])print("reviews returned:", len(platform.run(rewritten).fetchall()))print("of them holding an email:", platform.run(f"select count(*) from ({rewritten}) where body like '%@%'").fetchall()[0][0])
输出python table-rewrite.py
SELECT
  "reviews"."course_id" AS "course_id",
  "reviews"."rating" AS "rating",
  "reviews"."body" AS "body"
FROM (
  SELECT
    CAST(NULL AS BIGINT) AS review_id,
    CAST(NULL AS BIGINT) AS learner_id,
    CASE WHEN source.course_id IN (2, 75, 80, 87, 103, 134) THEN source.course_id END AS course_id,
    CASE WHEN source.course_id IN (2, 75, 80, 87, 103, 134) THEN source.rating END AS rating,
    CASE
      WHEN source.course_id IN (2, 75, 80, 87, 103, 134)
      THEN REGEXP_REPLACE(
        REGEXP_REPLACE(source.body, '[A-Za-z0-9._%+-]+@[A-Za-z0-9-]+(\.[A-Za-z0-9-]+)+', '[email]', 'g'),
        '\+?\(?[0-9][0-9 ().-]{6,}[0-9]',
        '[phone]',
        'g'
      )
    END AS body,
    CAST(NULL AS TIMESTAMPTZ) AS posted_at
  FROM learning.reviews AS source
  WHERE
    source.course_id IN (2, 75, 80, 87, 103, 134)
) AS "reviews"
WHERE
  "reviews"."rating" <= 2
reviews returned: 9
of them holding an email: 0

该查询返回9条评价,其中0条含有电子邮件。讲师的查询仍在最外层,只补全了限定名,其余未变。在内部,course_id和rating按存储的原样传出,body则经过redact_contacts掩码传出,每一列都包在一个依据行过滤条件的CASE中,该行过滤条件是该讲师所授课程的字面列表;查询未读取的列则作为带类型的NULL传出。

使列改写失效的五种读取,由analyst-1通过表改写来运行,并通过lib.enforce.answer来运行,后者是服务在其边界处调用的函数:

table-rewrite-holds.py Python 3.12.15 · duckdb 2.0.0 · pyarrow 26.0.1 · pyiceberg 0.12.0 · regopy 1.5.2 · sqlglot 30.33.0 通过
"""Sends the five reads from the column rewrite through the table rewrite, and through `answer`, the service's boundary.It then shows what the analyst receives and what the platform team's log holds. The prose's point rests on the five attacklines, the operator's log line and the relation line."""import loggingimport refrom lib.catalog import open_catalogfrom lib.enforce import EMAIL, Enforcer, QueryFailed, answerplatform = Enforcer(open_catalog(["crm.learners"]))operator_log = []                     # the platform team's log, kept in a list here so the example can print itlogging.getLogger("lib.enforce").addHandler(handler := logging.Handler())handler.emit = lambda record: operator_log.append(record.getMessage().splitlines()[0])# The five attacks on the column rewrite, run by analyst-1 again: through the table rewrite this time, and through# `answer`, the function a service calls at its boundary.email = platform.connection.sql("select email from crm.learners where learner_id = 2").fetchall()[0][0]attacks = {    "a WHERE on the raw column": f"select learner_id from crm.learners where email = '{email}'",    "the whole row as a struct": "select struct_extract(l, 'email') from crm.learners as l where learner_id = 2",    "COLUMNS(...)": "select columns('^email$') from crm.learners where learner_id = 2",    "to_json": "select to_json(l) ->> 'email' from crm.learners as l where learner_id = 2",    "a cast error": "select learner_id from crm.learners where cast(email as integer) = 0",}# a pseudonym is 64 hex digits, shortened here; the example never prints a stored emailshorten = lambda text: re.sub(r"\b([0-9a-f]{8})[0-9a-f]{56}\b", r"\1…", re.sub(EMAIL, "<a stored email>", text))for name, sql in attacks.items():    try:        # answer returns the rows, or raises QueryFailed with a generic message, as a service sees it        got = str(answer(platform, sql, "analyst-1"))    except QueryFailed as error:        got = f"{type(error).__name__}, {error}"    print(f"{name}:", shorten(got))for line in operator_log:    print("in the operator's log:", shorten(line))      # a pseudonym, 64 hex digits, shortened# Why `answer` returns rows: the relation Enforcer.sql returns is a handle on the layer's connection, and runs any SQL.# the relation is a handle on the layer's connection, so its .query runs SQL of its own thererelation = platform.sql("select learner_id from crm.learners", "analyst-1")print("the relation, running SQL of its own:", shorten(str(relation.query("r", "select email from crm.learners where learner_id = 2").fetchall())))print("what answer returns:", type(answer(platform, "select learner_id from crm.learners", "analyst-1")).__name__)
输出python table-rewrite-holds.py
a WHERE on the raw column: []
the whole row as a struct: [(None,)]
COLUMNS(...): [(None,)]
to_json: [(None,)]
a cast error: QueryFailed, the query failed: the platform team can see why
in the operator's log: a query by analyst-1 failed in the engine: Conversion Error: Could not convert string 'e4f62f1a…' to INT32 when casting from source column email
the relation, running SQL of its own: [('<a stored email>',)]
what answer returns: list

这种改写挡住了全部五种读取。WHERE返回[],因为在子查询内部,email已经是假名。结构体、COLUMNS(...)和to_json的读取返回NULL,而不是假名,因为这些查询除learner_id之外没有指名任何列。策略没有被问及其他列,带类型的NULL也不会给其余列任何值。改写看不到的读取会默认拒绝(失败即关闭)。类型转换错误现在包含的是假名而不是电子邮件,而answer连这一点也隐藏了:它在try中获取行,把任何引擎错误转为QueryFailed,并把 DuckDB 的消息写入平台团队的日志,该日志由示例在五次读取之后打印。分析员得到的是QueryFailed, the query failed: the platform team can see why。

answer返回的是行,而不是关系,因为 DuckDB 的关系可以在其连接上运行任意 SQL。分析员的关系在被要求提供电子邮件时,返回的是[('<a stored email>',)]。

按查询生成子查询,而不是为每个角色编写一个视图,才能使其与策略保持一致。平台的决策取决于地区、课程、同意和用途,为每种组合手工编写视图,就是 NIST 所描述的“角色爆炸”,即条件变成“数量众多、临时拼凑且成员有限的角色”(NIST SP 800-162)。这样的视图一旦有人只修改了其中之一,就会与策略脱节。

工作准则: 用根据决策生成的子查询替换每张受保护的表,沿用查询自己的别名,并保持用户查询的其余部分不变。对未读取的列给出带类型的NULL。如果用户需要读取整行才能显示值,则改为就每张表的每一列向策略发问,使掩码覆盖所有列,代价是请求和审计记录中会出现查询从未用到的列名。

这部分尚未翻译,因此以英文显示。

Write each mask as SQL in one place

最近一次修改见于版次 2026.11.31

The policy names a mask; the layer decides what SQL it becomes. Keeping that mapping in one place, the MASKS table in lib.enforce and the typed NULL beside it, means a fix to a mask reaches every table that uses it, and the policy never carries SQL, which would be code arriving as data. The table rewrite calls lib.enforce.masked for every masked column, and prints the SQL for the four masks the policy names:

masks-as-sql.py Python 3.12.15 · duckdb 2.0.0 · pyarrow 26.0.1 · pyiceberg 0.12.0 · regopy 1.5.2 · sqlglot 30.33.0 通过
"""Prints the SQL the enforcement layer writes for each of the four masks the policy names, and counts DuckDB's functionsnamed like HMAC. The prose's points rest on the pseudonymize line and on the HMAC count."""import duckdbfrom sqlglot import expfrom lib.enforce import masked# The four masks the policy names, as the SQL the enforcement layer writes for them.# the column's type, which the redact mask casts its NULL tofor kind, column, type_ in [("redact", "date_of_birth", "DATE"), ("last4", "card_number", "VARCHAR"),                            ("pseudonymize", "email", "VARCHAR"), ("redact_contacts", "body", "VARCHAR")]:    # redact is a typed NULL; the other masks' SQL comes from lib.enforce's MASKS table    print(f"{kind}:", masked(kind, exp.column(column), type_).sql("duckdb"))# The pseudonymize mask hashes the key followed by the value, because DuckDB has no HMAC function to call.# DuckDB's function catalog, searched for any name containing hmac, whatever its casehmac = duckdb.sql("select count(*) from duckdb_functions() where function_name ilike '%hmac%'").fetchall()[0][0]print("DuckDB functions named like HMAC:", hmac)
输出python masks-as-sql.py
redact: CAST(NULL AS DATE)
last4: RIGHT(REGEXP_REPLACE(card_number, '[^0-9]', '', 'g'), 4)
pseudonymize: SHA256($pseudonym_key || CAST(email AS TEXT))
redact_contacts: REGEXP_REPLACE(REGEXP_REPLACE(body, '[A-Za-z0-9._%+-]+@[A-Za-z0-9-]+(\.[A-Za-z0-9-]+)+', '[email]', 'g'), '\+?\(?[0-9][0-9 ().-]{6,}[0-9]', '[phone]', 'g')
DuckDB functions named like HMAC: 0

Each mask does one job:

  • redact is a NULL cast to the column's type, so the column keeps its type for anything the query does with it, such as a union or a date function.
  • last4 keeps the last four digits of a card number, after removing anything that isn't a digit.
  • pseudonymize is a keyed hash, SHA-256 over the key followed by the value, because DuckDB 2.0.0 has no HMAC function: duckdb_functions() lists 0 named like it. Where your engine has one, prefer HMAC (RFC 2104).
  • redact_contacts replaces emails and phone numbers in free text with [email] and [phone].

Picture someone holding a list of the platform's emails, who hashes each one and looks for it among the pseudonyms analyst-1 sees in crm.learners:

pseudonym-key.py Python 3.12.15 · duckdb 2.0.0 · pyarrow 26.0.1 · pyiceberg 0.12.0 · regopy 1.5.2 · sqlglot 30.33.0 通过
"""Hashes a list of emails the way the layer hashes pseudonyms, with and without its key, then rotates the key and countsthe pseudonyms that change. The prose's points rest on the three count lines: the matches without the key and with it, andthe pseudonyms that change on rotation."""from lib.catalog import open_catalogfrom lib.enforce import Enforcer# The pseudonyms analyst-1, the platform's data scientist, sees in crm.learners. Someone holding a list of the# platform's emails hashes each one and looks for it among them, first without the key and then with it.# an example key, not a secret: production keeps the real one where only the layer can read itplatform = Enforcer(open_catalog(["crm.learners"]), key="example-key-not-a-secret")seen = platform.sql("select learner_id, email from crm.learners", "analyst-1").to_arrow_table()platform.connection.register("seen", seen)# a guess is an email's hash; the second also puts the key first, as the layer hashes a pseudonymfor name, guess in [("without the key", "sha256(email)"), ("with the key", "sha256($pseudonym_key || email)")]:    matched = platform.run(f"select count(*) from crm.learners where {guess} in (select email from seen)").fetchall()[0][0]    print(f"pseudonyms matched from a list of emails, {name}: {matched:,} of {seen.num_rows:,}")# A new key gives every learner a new pseudonym, so data pseudonymized under the old key no longer joins.platform.key = "example-key-rotated"rotated = platform.sql("select learner_id, email from crm.learners", "analyst-1").to_arrow_table()# pairs each learner's old and new pseudonym, row by rowchanged = sum(a != b for a, b in zip(seen["email"].to_pylist(), rotated["email"].to_pylist()))print("pseudonyms that change when the key is rotated:", f"{changed:,} of {seen.num_rows:,}")
输出python pseudonym-key.py
pseudonyms matched from a list of emails, without the key: 0 of 5,000
pseudonyms matched from a list of emails, with the key: 5,000 of 5,000
pseudonyms that change when the key is rotated: 5,000 of 5,000

The key is what makes a pseudonym a pseudonym. Without it, the hashes match 0 of 5,000 pseudonyms; with it, they match 5,000 of 5,000, so whoever holds the key and a list of emails can name every pseudonym on it. The key belongs to the layer alone. It stays out of the policy, which is code read in pull requests, and out of the query, where a user who chose the key could compute pseudonyms of guessed emails. The layer passes the key to DuckDB as a query parameter, so it stays out of the query text that DuckDB's log records, as chapter 8 shows, and the layer denies any user query that carries a parameter, since one named $pseudonym_key would receive the key. In production, keep the key in a secret manager that only the layer can read. Rotate it when it may have leaked, and plan for what rotation does: 5,000 of 5,000 pseudonyms change, so data pseudonymized under the old key no longer joins with data under the new one.

redact_contacts is two regular expressions, and it's worth measuring what they remove. The platform's tickets and reviews come with every piece of personal data the generator placed in them, labelled by construction, which chapter 3 calls the truth. Each text goes through the mask here, and the labels count what survives:

redact-contacts-recall.py Python 3.12.15 · duckdb 2.0.0 · pyarrow 26.0.1 · pyiceberg 0.12.0 · regopy 1.5.2 · sqlglot 30.33.0 通过
"""Applies the redact_contacts mask to every ticket and review, then counts the labelled personal data still left in themasked text, and the phone replacements made in the tickets. The prose's points rest on the 'left in the text' lines, thereplacement count, and the assert on the tickets' card, IBAN and date lines."""import duckdbfrom sqlglot import expfrom lib.enforce import maskedfrom lib.platform import load# The redact_contacts mask, run over every ticket and review, against the personal data the generator placed in them# (pii_spans, labelled by construction).con = duckdb.connect()# each table registers under its short name, which the queries below usefor name in ["support.tickets", "learning.reviews", "pii_spans"]:    con.register(name.split(".")[-1], load(name))mask = masked("redact_contacts", exp.column("body"), "VARCHAR").sql("duckdb")# a span counts as left when its exact text is still in the masked body, which the row_id join findscounts = con.sql(f"""    with masked as (select 'tickets' as source, ticket_id as row_id, {mask} as body from tickets                    union all select 'reviews', review_id, {mask} from reviews)    select source, entity, count(*), count(*) filter (where contains(masked.body, pii_spans.text))    from pii_spans join masked using (source, row_id) group by all order by all""").fetchall()# the phone pattern takes every card number, IBAN and date in the tickets, so none is left in the masked textleft_in_tickets = {entity: left for source, entity, _, left in counts if source == "tickets"}assert all(left_in_tickets[entity] == 0 for entity in ("CREDIT_CARD", "IBAN_CODE", "DATE_TIME"))for source, entity, placed, left in counts:    print(f"{source}, {entity}, left in the text: {left:,} of {placed:,}")# The phone pattern also takes card numbers, IBANs and dates, which are digits too.replaced = con.sql(f"select sum(len(regexp_extract_all({mask}, '\\[phone\\]'))) from tickets").fetchall()[0][0]print("[phone] replacements in tickets:", f"{replaced:,}")
输出python redact-contacts-recall.py
reviews, EMAIL_ADDRESS, left in the text: 0 of 120
reviews, PERSON, left in the text: 798 of 798
tickets, CREDIT_CARD, left in the text: 0 of 213
tickets, DATE_TIME, left in the text: 0 of 196
tickets, EMAIL_ADDRESS, left in the text: 0 of 423
tickets, IBAN_CODE, left in the text: 0 of 236
tickets, LOCATION, left in the text: 210 of 210
tickets, PERSON, left in the text: 1,657 of 1,657
tickets, PHONE_NUMBER, left in the text: 0 of 439
[phone] replacements in tickets: 1,520

The mask removes contact details and leaves names alone. In the reviews, which instructors and the assistant read through this mask, 0 of 120 emails are left, and 798 of 798 names are.

In the tickets, 0 of 439 phone numbers are left, but the phone pattern makes 1,520 replacements. Card numbers, IBANs and dates are digits too, so the pattern takes them as well, and none of them is left. Names and street addresses stay, so the mask doesn't make text anonymous.

Working rule: Keep every mask's SQL in one table in the enforcement layer, and let the policy name masks, never write them. Key every pseudonym with a secret only the layer holds, passed as a parameter. Measure each text mask against labelled data before a policy relies on it, and name masks for what they remove, so nobody reads redact_contacts as "anonymized".

用决策中的数据构建行过滤器

最近一次修改见于版次 2026.11.27

掩码改变的是值的显示方式;行过滤器决定的是用户能得到哪些行。它们以数据的形式出现在决策中,共有三种形态:列等于某个值、列在一组值之中,以及对某行所属学习者的同意检查。lib.enforce.row_filter把每种形态转换为一个 SQL 条件,其中的值由 sqlglot 写成 SQL 字面量,绝不拼接进 SQL 文本。对于不认识的形态,或表中不存在的列,它会拒绝该查询;在构建同意检查之前,该层还会对照crm.learners检查同意过滤器所用的列。第 5 章让策略以数据的形式返回过滤器,正是出于这个原因:请求携带的任何内容都无法作为代码进入查询。

同意这一形态需要格外小心。课程助手assistant-1只能读取那些同意让自己的文字用于训练平台 AI 的学习者所写的评价,而这项同意记录在crm.learners中,助手无权读取该表。该层把它转换为半连接,即检验评价的learner_id是否属于已同意的学习者,并使用该层自身对crm.learners的访问权限来执行:

row-filters-from-data.py Python 3.12.15 · duckdb 2.0.0 · pyarrow 26.0.1 · pyiceberg 0.12.0 · regopy 1.5.2 · sqlglot 30.33.0 通过
"""Prints the condition the layer writes for each row filter in three decisions, then the reviews the assistant readsthrough its consent filter, and the denial of its read of the learners table. The prose's points rest on the read countsand the consent counts, and on the denied read at the end."""from lib.catalog import open_catalogfrom lib.enforce import Enforcer, row_filterplatform = Enforcer(open_catalog(["crm.learners", "learning.reviews", "support.tickets"]))# Each row filter in a decision, as the condition the enforcement layer writes for it.for user, table in [("agent-a05", "support.tickets"), ("instructor-17", "learning.reviews"), ("assistant-1", "learning.reviews")]:    # the decision for this read; its row filters are data, written as SQL in the loop below    decision = platform.decide(user, {table: set()})[table]    for rule in decision["row_filters"]:        print(f"{user}, {table}: {row_filter(rule, platform.columns(table)).sql('duckdb')}")# A filter's values come from data, so a value that looks like SQL stays a value.odd = {"column": "support_region", "equals": "emea' or '1' = '1"}print("a region that looks like SQL:", row_filter(odd, platform.columns("support.tickets")).sql("duckdb"))# The assistant's consent filter reads crm.learners, which the assistant itself may not read.total = platform.connection.sql("select count(*) from learning.reviews").fetchall()[0][0]read = platform.sql("select count(*) from learning.reviews", "assistant-1").fetchall()[0][0]print("reviews the assistant reads:", f"{read:,} of {total:,}")# a null consent means no answer was recorded, so the label reads 'never asked'by_consent = platform.connection.sql("""select coalesce(l.ai_training_consent::varchar, 'never asked'), count(*)    from learning.reviews r join crm.learners l using (learner_id) group by all order by all""").fetchall()for consent, reviews in by_consent:    print(f"reviews whose author's AI-training consent is {consent}: {reviews:,}")try:    platform.sql("select count(*) from crm.learners", "assistant-1")except Exception as reason:    print("the assistant reading crm.learners:", reason)
输出python row-filters-from-data.py
agent-a05, support.tickets: source.support_region = 'emea'
instructor-17, learning.reviews: source.course_id IN (2, 75, 80, 87, 103, 134)
assistant-1, learning.reviews: source.learner_id IN (SELECT consent.learner_id FROM crm.learners AS consent WHERE consent.ai_training_consent IS TRUE)
a region that looks like SQL: source.support_region = 'emea'' or ''1'' = ''1'
reviews the assistant reads: 436 of 3,000
reviews whose author's AI-training consent is false: 955
reviews whose author's AI-training consent is never asked: 1,609
reviews whose author's AI-training consent is true: 436
the assistant reading crm.learners: assistant-1 may not read crm.learners: no group of this user may read this table

每个过滤器的值都会成为 SQL 字面量,因此值始终只是值,未作答的同意按“否”处理。客服人员所在的区域和讲师所教的课程,分别成为一个等值条件和一个IN列表;写成像 SQL 的区域仍然只是一个字符串,其中的引号被加倍。同意过滤器检验的是IS TRUE,因此1,609评价的作者,也就是在平台提出该问题之前注册、没有记录答案的人,与回答“否”的人一样,都算作不同意。

助手读取的是436 of 3,000评价,也就是作者回答“是”的那些评价。助手自己对crm.learners的请求会被拒绝,并附上策略给出的理由:半连接对该表的读取属于该层,而不属于用户。

工作准则: 用 SQL 构建器,根据决策中的值构建每一个行过滤器,并拒绝该层不认识的任何过滤器形态或列。把未知的同意视为不同意。当过滤器需要用户无权读取的表时,在子查询内部使用该层自身的访问权限去读取,绝不通过扩大用户的访问权限来实现。

让每一列都依赖行过滤条件

最近一次修改见于版次 2026.11.27

行过滤条件位于子查询的 WHERE 中,而用户的查询包裹着这个子查询,因此人们很容易认为 DuckDB 会先应用这些过滤条件。但 SQL 并不保证任何求值顺序,优化器会把条件移到代价最低的位置。用户的某个可能失败的条件,例如类型转换,就可能在被过滤条件丢弃的行上运行。而一个通用的错误信息也无济于事:查询失败这一事实本身,就说明了某一行的情况,而用户本不应看到这一行。

PostgreSQL 的文档指出了这种泄露:如果一个函数可能因参数不同而失败,并且它先于视图的行过滤条件运行,就可能泄露有关不可见行的重要信息。在 PostgreSQL 中,标记为 security_barrier 的视图可以防止“恶意选择的函数和运算符在视图完成其工作之前,就从行中获得值”,只有标记为 LEAKPROOF 的函数仍可先运行(PostgreSQL 18, Rules and Privileges)。DuckDB 两者都没有。

因此,本层的做法是让顺序变得无关紧要。查询读取的每一列离开子查询时,都写成 CASE WHEN <row filters> THEN <value> END,就像讲师的改写那样。这样,用户的条件就依赖于过滤条件的结果:无论 DuckDB 先计算什么,对于被过滤条件丢弃的行,用户的条件在每一列中看到的都是 NULL。

lib.enforce.order_cases 会测试本层写出的每种过滤形态:客服人员的等值条件、讲师的 IN 列表,以及课程助手的同意半连接。每个用例都复制被过滤的表,其中的 body 在过滤条件保留的行中是数字,在被丢弃的行中是文本,然后运行一个把 body 转换为数字的查询。只有当这个转换看到被丢弃行的值时才会失败:

row-filter-order.py Python 3.12.15 · duckdb 2.0.0 · pyarrow 26.0.1 · pyiceberg 0.12.0 · regopy 1.5.2 · sqlglot 30.33.0 通过
"""Runs the evaluation-order test for each filter shape the layer writes, with the filter in a WHERE alone and as the layerwrites it. The prose's point rests on the consent semi-join's two lines, and on the count at the end."""from lib.enforce import order_cases# The evaluation-order regression test, lib.enforce.order_cases, which chapter 10's CI runs on every DuckDB upgrade.# For each shape of row filter the layer writes, it takes a real user's filter and a copy of the filtered table whose# `body` holds digits in the rows the filter keeps and text in the rows it drops. A query that casts `body` to a number# fails only if the cast sees a dropped row's value. It runs over the filter in a WHERE alone, and over the layer's# subquery.cases = order_cases()for case in cases:    for form, safe in [("with the filter in WHERE only", case["safe_with_where_only"]),                       ("as the layer writes it", case["safe_as_written"])]:        print(f"{case['shape']}, {form}: the cast {'never saw' if safe else 'saw'} a dropped row's value")print("filter shapes the layer's subquery keeps safe:", f"{sum(case['safe_as_written'] for case in cases)} of {len(cases)}")
输出python row-filter-order.py
equality, with the filter in WHERE only: the cast never saw a dropped row's value
equality, as the layer writes it: the cast never saw a dropped row's value
IN list, with the filter in WHERE only: the cast never saw a dropped row's value
IN list, as the layer writes it: the cast never saw a dropped row's value
consent semi-join, with the filter in WHERE only: the cast saw a dropped row's value
consent semi-join, as the layer writes it: the cast never saw a dropped row's value
filter shapes the layer's subquery keeps safe: 3 of 3

对每一列都加上这道门控,正是同意半连接保持安全的原因。当过滤条件只放在 WHERE 中时,等值条件和 IN 列表是安全的,而同意半连接并不安全:DuckDB 会在作者未同意的评论上运行这个转换。按本层的写法,3 of 3这些过滤形态都能使转换不触及被丢弃的行。前两种形态的安全是 2.0.0 中优化器的选择所致,而非出于任何承诺,因此每次升级都要运行这项测试。这道门控的代价是,在查询读取的每一列中都要再次计算过滤条件。

工作准则: 绝不要依赖引擎的求值顺序,来使用户的条件远离被过滤条件丢弃的行。要让用户查询能读取的每个值都依赖行过滤条件,并在每次引擎升级时测试每种过滤形态。如果引擎有自己的屏障,例如 PostgreSQL 的 security_barrier 视图,也应一并使用。

通过运行查询来测试谁能看到什么

最近一次修改见于版次 2026.11.27

第 5 章用一张“谁能看到什么”的表 lib.policy.WHO_SEES_WHAT 来检查策略:这是一份请求清单,每个请求都附有评审者预期的决定。这测试的是决定,而不是用户实际得到的内容,后者还取决于改写、掩码的 SQL 和数据。lib.enforce.observe 以每个用例所对应的用户身份,让该用例通过这一层运行,并将返回的结果与存储的数据作比较:行数是多少,哪些列不同。一个用例与其决定相符,需要同时满足三点:当且仅当用例声明应拒绝时,用户被拒绝;当且仅当用例预期有行过滤时,用户得到的行数少于存储的行数;当且仅当用例预期有掩码时,用户在相应的列中得到被改动的值:

who-sees-what-in-queries.py Python 3.12.15 · duckdb 2.0.0 · pyarrow 26.0.1 · pyiceberg 0.12.0 · regopy 1.5.2 · sqlglot 30.33.0 通过
"""Runs chapter 5's who-sees-what cases as queries and compares what each user gets with what is stored, then readsmarketing.audience, which has no metadata. The prose's points rest on the agreement count and on the two marketing lines."""from lib import pipelines, policyfrom lib.catalog import open_catalogfrom lib.enforce import Enforcer, agrees, observecatalog = open_catalog(["crm.learners", "learning.reviews", "billing.payments", "learning.enrollments", "learning.courses"])pipelines.run(catalog, "marketing_audience")          # a copy of learners' names and emails, with no metadata yetplatform = Enforcer(catalog)# Chapter 5's who-sees-what table, run as queries: what each user gets, against what's stored. A column is "changed"# when at least one value the user gets differs from the stored one.agreeing = 0for case in policy.WHO_SEES_WHAT:    got = observe(platform, case.user, case.table, case.columns, case.purpose)    who = case.user + (f" for {case.purpose}" if case.purpose else "")    if got is None:        print(f"{who}, {case.table}: denied")    else:        print(f"{who}, {case.table}: {got['rows']:,} of {got['stored']:,} rows, changed: {', '.join(got['changed']) or 'none'}")    agreeing += agrees(case, got)print("cases where what the user gets agrees with the decision:", f"{agreeing} of {len(policy.WHO_SEES_WHAT)}")# marketing.audience is a table marketing may read, but nobody has classified its columns, so the policy treats each# one as sensitive: marketing gets every row, and no value in any column.rows = platform.sql("select * from marketing.audience", "marketing-1").fetchall()# a column counts as shown when one of its values isn't None, which is what a redact mask returnsshown = sum(any(value is not None for value in column) for column in zip(*rows))print("marketing-1, marketing.audience, which has no metadata, rows:", f"{len(rows):,}")print("marketing-1, marketing.audience, which has no metadata, columns with a value:", f"{shown} of {len(rows[0])}")
输出python who-sees-what-in-queries.py
agent-a05, crm.learners: 1,581 of 5,000 rows, changed: none
agent-a05, billing.payments: denied
instructor-17, learning.reviews: 69 of 3,000 rows, changed: body
instructor-17, crm.learners: denied
marketing-1, crm.learners: 1,861 of 5,000 rows, changed: phone
finance-1, billing.payments: 17,562 of 17,562 rows, changed: card_number
analyst-1, billing.payments: 17,562 of 17,562 rows, changed: card_number
analyst-1, crm.learners: 5,000 of 5,000 rows, changed: email, notes
analyst-1, learning.reviews: 3,000 of 3,000 rows, changed: body
analyst-1 for ai-training, learning.reviews: 436 of 3,000 rows, changed: body
assistant-1, learning.reviews: 436 of 3,000 rows, changed: body
someone-else, crm.learners: denied
cases where what the user gets agrees with the decision: 12 of 12
marketing-1, marketing.audience, which has no metadata, rows: 1,829
marketing-1, marketing.audience, which has no metadata, columns with a value: 0 of 5

在 12 of 12 个用例中,用户得到的内容与决定相符。EMEA 支持专员得到的是其所在地区的学习者,且与存储的一致;市场部得到的是已同意且活跃的学习者,其中 phone 被改动;分析师得到的是每一位学习者,其中 email 和 notes 被改动。最后两行涉及 marketing.audience,它由某条流水线写入,尚无人对其分类。市场部可以读取它,得到 1,829 行,其中有值的列为 0 of 5 列,因为策略把未分类的列视为敏感列。

这项测试的价值在变更时体现出来。假设一个拉取请求把 analyst-1 加入 EMEA 的支持组,以便其协助处理工单。这是对 governance/users.toml 的两行改动,涉及一个组,以及支持组的行过滤所需的地区。下面的代码在内存中做出这一改动,并针对它运行同样的用例:

group-change-caught.py Python 3.12.15 · duckdb 2.0.0 · pyarrow 26.0.1 · pyiceberg 0.12.0 · regopy 1.5.2 · sqlglot 30.33.0 通过
"""Makes one change to the users file in memory, runs the who-sees-what cases again, and runs chapter 5's decision checkon the same change. The prose's points rest on the lines that say which cases fail, and on the decision count."""from lib import policyfrom lib.catalog import open_catalogfrom lib.enforce import Enforcer, agrees, observe# A pull request changes one user in governance/users.toml, in two lines: analyst-1 joins support, with the region# support's row filter needs, EMEA, to help with tickets.# the users file as a document, changed here in memory and not on diskdata = policy.data_document()data["platform"]["users"]["analyst-1"] = {"groups": ["data-science", "support"], "region": "emea"}platform = Enforcer(open_catalog(["crm.learners", "learning.reviews", "billing.payments"]),                    rego=policy.interpreter(data=data))# The who-sees-what cases, run as queries with the change in place. The cases are unchanged: they say what each# user should get, and the change was meant to touch support tickets only.failing = 0for case in policy.WHO_SEES_WHAT:    got = observe(platform, case.user, case.table, case.columns, case.purpose)    # got is None when the user is denied, which agrees only with a case that expects a denial    if not agrees(case, got):        failing += 1        who = case.user + (f" for {case.purpose}" if case.purpose else "")        print(f"fails, {who}, {case.table}, rows: {got['rows']:,} of {got['stored']:,}")        print(f"fails, {who}, {case.table}, columns changed: {', '.join(got['changed']) or 'none'}, "              f"expected: {', '.join(case.masked)}")print("cases that fail:", f"{failing} of {len(policy.WHO_SEES_WHAT)}")# Chapter 5's table, which checks the decisions themselves, on the same change.decisions = [policy.decide(policy.request(c.user, c.table, c.columns, c.purpose), platform.rego) for c in policy.WHO_SEES_WHAT]print("cases that fail as decisions:", f"{sum(not policy.matches(c, d) for c, d in zip(policy.WHO_SEES_WHAT, decisions))} of {len(decisions)}")
输出python group-change-caught.py
fails, analyst-1, crm.learners, rows: 1,581 of 5,000
fails, analyst-1, crm.learners, columns changed: none, expected: email, notes
fails, analyst-1, learning.reviews, rows: 3,000 of 3,000
fails, analyst-1, learning.reviews, columns changed: none, expected: body
fails, analyst-1 for ai-training, learning.reviews, rows: 436 of 3,000
fails, analyst-1 for ai-training, learning.reviews, columns changed: none, expected: body
cases that fail: 3 of 12
cases that fail as decisions: 3 of 12

这一改动使 3 of 12 个用例失败,而且没有一个与工单有关。这三个用例全是分析师对学习者和评论的读取。在 crm.learners 上,分析师现在得到 1,581 of 5,000 行,且没有任何列被改动:支持部门的地区过滤适用于他们,而策略中针对个人信息列的掩码只适用于支持组之外的用户,因此每个电子邮件和备注都按存储原样返回。评论文本原本数据科学家完全无法读取,或只能在移除联系方式后用于 AI 训练,现在却完整地返回了。第 5 章的决定级别表在同样的用例上失败,即 3 of 12;而本表显示的是分析师实际得到的内容。

工作准则: 每当策略、用户文件、元数据或这一层发生变更时,都要把“谁能看到什么”的用例作为查询通过这一层运行,并在任何用例不相符时让构建失败。要修复失败的用例,应修改策略或用户文件。只有在数据所有者一致认为用户应得到的内容已经改变时,才修改用例。

在报告中隐去小群组

最近一次修改见于版次 2026.11.31

行过滤器和掩码决定用户能看到哪些行和哪些值。聚合是第二条渠道:对少数几个人的计数或平均值,描述的就是这几个人。常用的防御办法是设定最小群组规模,低于该规模的报告群组一律隐去。这个数字是经验法则,而不是测量结果。美国医疗保险和医疗补助服务中心(CMS)的一份公开数据使用协议把它定为 11,并补充说:"如果百分比或其他数学公式的使用会导致显示小于 11 的单元格,则不得使用"(CMS-R-0235L)。平台采用同样的最小值 11,并把它存放在 governance/enforcement.toml 中,对它的修改与对策略的修改一样要经过评审,而 lib.enforce.minimum_group_size 负责读取它。

讲师每天会收到一份课程报告,按课程和学习者所在国家列出学习者和平均进度,这份报告由一个作业生成,而该作业读取的国家信息是讲师无权读取的。讲师可以读取自己所授课程的选课记录以及每条记录的进度。把这两者结合起来,就得到 instructor-17 的报告:

small-groups-difference.py Python 3.12.15 · duckdb 2.0.0 · pyarrow 26.0.1 · pyiceberg 0.12.0 · regopy 1.5.2 · sqlglot 30.33.0 通过
"""Builds instructor-17's daily course report, counts its cells under the minimum and its cells of one learner, and matcheseach cell of one against the enrollments the instructor may read. The prose's points rest on the cell and lookup counts, andon the last line, which shows the differencing."""from lib.catalog import open_catalogfrom lib.enforce import Enforcer, minimum_group_sizeplatform = Enforcer(open_catalog(["crm.learners", "learning.courses", "learning.enrollments"]))minimum = minimum_group_size()print("minimum group size:", minimum)count = lambda sql: platform.connection.sql(f"select count(*) from ({sql})").fetchall()[0][0]# The platform's daily course report for instructor-17: learners and average progress, per course and learner country.# The report job reads learners' countries, which instructors may not read.platform.connection.execute("""create view mine as    select e.course_id, e.progress_pct, e.enrolled_at::date as day, l.country    from learning.enrollments e join crm.learners l using (learner_id)    where e.course_id in (select course_id from learning.courses where instructor_id = 17)""")platform.connection.execute("""create view report as    select course_id, country, count(*) as learners, avg(progress_pct) as progress from mine group by all""")print("cells in the report:", count("from report"))# the minimum is the threshold: a cell with fewer learners than it is held backprint("cells under the minimum:", count(f"from report where learners < {minimum}"))# instructor-17 may read their courses' enrollments, with each one's progress. A cell of one learner, matched on its# progress, names that learner's country.# what instructor-17 may read: enrollments with each one's progress, and no countryseen = platform.sql("select course_id, progress_pct from learning.enrollments", "instructor-17").to_arrow_table()platform.connection.register("seen", seen)print("cells of one learner:", count("from report where learners = 1"))print("learners a cell of one names:", count("""from report as cell where learners = 1 and (select count(*) from seen    where seen.course_id = cell.course_id and seen.progress_pct = cell.progress) = 1"""))print("cells shown with the minimum:", count(f"from report where learners >= {minimum}"))# The minimum doesn't stop differencing. An enrollment that arrives alone in its course on its day, in a cell already# shown, raises that cell by one in the next day's report, and the instructor sees which enrollment arrived.# an enrollment alone in its course that day, whose country already has the minimum on earlier daysrevealed = count(f"""from mine as m    where (select count(*) from mine as o where o.course_id = m.course_id and o.day = m.day) = 1      and (select count(*) from mine as o where o.course_id = m.course_id and o.country = m.country and o.day < m.day) >= {minimum}""")print("learners whose country the daily report reveals anyway:", f"{revealed} of {count('from mine')}")
输出python small-groups-difference.py
minimum group size: 11
cells in the report: 63
cells under the minimum: 51
cells of one learner: 6
learners a cell of one names: 3
cells shown with the minimum: 12
learners whose country the daily report reveals anyway: 92 of 482

只有一个人的单元格,会向任何能找到该学习者的人暴露其所在国家,而讲师恰好能找到:把单元格的平均进度与自己的选课记录对照,就能准确指出 3 名学习者。该报告共有 63 个单元格,其中 51 个低于最小值,6 个各只有一名学习者。

设定最小值之后,显示的单元格有 12 个,上述查找都不再奏效。但最小值挡不住差分。若某条选课记录在某天单独进入某门课程,落入一个本来就会显示的单元格,该单元格在次日的报告中就会加一,讲师便能看出是哪条选课记录到达了。这样暴露的是 92 of 482 名学习者的国家。

差分,即比较连续的报告,就是 Denning、Denning 和 Schwartz 所称的追踪器攻击。他们证明:"小查询集的泄露实际上几乎总能借助称为追踪器的特征公式来实现"(Denning et al., 1979)。

Snowflake 在其引擎中强制执行最小群组规模,并声明其策略"不能保证恶意行为者无法通过精心设计的查询获取潜在的敏感数据"(Snowflake aggregation policies)。针对差分,我们建议采用噪声,如差分隐私(Dwork and Roth, 2014),而不是提高阈值,《数据团队的隐私工程》(Privacy Engineering for Data Teams)对此有专门论述。

工作准则: 隐去所有低于 governance/enforcement.toml 中所设最小值的报告群组,并把最小值视为防止查找某个人的保护措施,而绝不是防止差分的保护措施。不要按读者无权读取的属性拆分报告,再把它发给能看到底层行的读者:讲师的按国家拆分的报告,就是一张国家查找表。去掉这种拆分,或者只在加噪之后再报告。

这部分尚未翻译,因此以英文显示。

Hand the same policies to an engine that enforces them

最近一次修改见于版次 2026.11.27

In production, enforcement belongs in the engine every tool queries through, because Saltzer and Schroeder's principle of complete mediation holds that "every access to every object must be checked for authority" (Saltzer and Schroeder, 1975), and a policy enforced in one tool and not another fails it. Trino, an open-source distributed SQL engine, can take its access control from Open Policy Agent (OPA) through a plugin, and chapter 5 shaped the platform's requests like the plugin's so the same policy could serve both. Trino 484 offers file-based, OPA and Ranger access control, and none for Cedar (Trino 484, Security).

The plugin asks OPA separate questions at separate URIs (Trino 484, OPA access control):

  • Whether to allow an operation, such as SelectFromColumns, answered with a boolean allow.
  • Row filters, GetRowFilters, for a table. OPA answers with a list of {"expression": ...}, and each expression "essentially behaves like an additional WHERE clause". The URI is optional, and Trino applies no row filtering when it isn't set.
  • Column masks, GetColumnMask, for one column, or a batch of columns at opa.policy.batch-column-masking-uri. "Only a single column mask may be returned for a given column."

The operation names and where each request puts its table or column are in the plugin's source at tag 484 (OpaAccessControl.java, OpaHighLevelClient.java). The documentation's batch-mask policy reads filterResources[i].catalogName where its own request example nests those fields under .column, so as printed it wouldn't match. Requests built in those shapes go to its policy:

trino-requests.py Python 3.12.15 · duckdb 2.0.0 · pyarrow 26.0.1 · pyiceberg 0.12.0 · regopy 1.5.2 · sqlglot 30.33.0 通过
"""Builds requests in the shape Trino 484's OPA plugin sends, and runs them through the platform's policy. The prose'spoints rest on the allow lines, which the example asserts are False, and on the GetRowFilters and GetColumnMask lines."""import jsonfrom lib import metadata, policyfrom lib.enforce import row_filter# The platform's policy, with a support agent added before anyone set their region (chapter 5's missing-region case).data = policy.data_document()data["platform"]["users"]["agent-new"] = {"groups": ["support"]}rego = policy.interpreter(data=data)described = metadata.load()def trino_request(user: str, operation: str, resource: dict | None = None) -> dict:    """A request in the shape Trino 484's OPA plugin sends (OpaAccessControl, OpaHighLevelClient)."""    action = {"operation": operation} | ({"resource": resource} if resource else {})    return {"context": {"identity": {"user": user, "groups": []}, "softwareStack": {"trinoVersion": "484"}}, "action": action}# Trino asks `allow` about every operation: ExecuteQuery before any query runs, AccessCatalog for each catalog it names.for operation, resource in [("ExecuteQuery", None), ("AccessCatalog", {"catalog": {"name": "platform"}})]:    allowed = policy.decide(trino_request("agent-a05", operation, resource), rego)["allow"]    # allow admits only SelectFromColumns, so Trino's checks before any query are denied    assert allowed is False    print(f"allow, {operation}, agent-a05:", allowed)# GetRowFilters names a table. The policy's row_filters rule on its own skips `allow` and its missing-attribute guard,# so for agent-new it returns no filter, which Trino reads as no filtering. The answer comes from the decision instead,# for the same read asked as SelectFromColumns: its filters in Trino's SQL, or a filter that keeps no rows.for user, schema, table in [("agent-a05", "support", "tickets"), ("instructor-17", "learning", "reviews"),                            ("agent-new", "support", "tickets")]:    request = trino_request(user, "GetRowFilters", {"table": {"catalogName": "platform", "schemaName": schema, "tableName": table}})    print(f"GetRowFilters, {user}, {schema}.{table}, the rule alone:",          json.dumps(policy.evaluate(rego, "data.platform.access.row_filters", request)))    decision = policy.decide(policy.request(user, f"{schema}.{table}", []), rego)    columns = {c.name: "" for c in described[f"{schema}.{table}"].columns.values()}    # no table alias: Trino applies the filter to the table itself, as a WHERE clause on it    filters = [{"expression": row_filter(rule, columns, table=None).sql("trino")} for rule in decision["row_filters"]]    # a denied read gets a filter that keeps no rows    print(f"GetRowFilters, {user}, {schema}.{table}, answered:", json.dumps(filters if decision["allow"] else [{"expression": "false"}]))# GetColumnMask names one column, under action.resource.column. The policy reads the columns of a SelectFromColumns# request instead, so it masks nothing here until the request is translated.column = {"catalogName": "platform", "schemaName": "crm", "tableName": "learners", "columnName": "email", "columnType": "varchar"}decision = policy.decide(trino_request("analyst-1", "GetColumnMask", {"column": column}), rego)print("GetColumnMask as sent, analyst-1, crm.learners.email:", json.dumps(decision["column_masks"]), decision["reasons"][0])translated = policy.request("analyst-1", "crm.learners", [column["columnName"]])print("GetColumnMask translated, analyst-1, crm.learners.email:", json.dumps(policy.decide(translated, rego)["column_masks"]))
输出python trino-requests.py
allow, ExecuteQuery, agent-a05: False
allow, AccessCatalog, agent-a05: False
GetRowFilters, agent-a05, support.tickets, the rule alone: [{"column": "support_region", "equals": "emea"}]
GetRowFilters, agent-a05, support.tickets, answered: [{"expression": "support_region = 'emea'"}]
GetRowFilters, instructor-17, learning.reviews, the rule alone: [{"column": "course_id", "in": [2, 75, 80, 87, 103, 134]}]
GetRowFilters, instructor-17, learning.reviews, answered: [{"expression": "course_id IN (2, 75, 80, 87, 103, 134)"}]
GetRowFilters, agent-new, support.tickets, the rule alone: []
GetRowFilters, agent-new, support.tickets, answered: [{"expression": "false"}]
GetColumnMask as sent, analyst-1, crm.learners.email: {} GetColumnMask is not an operation this policy allows
GetColumnMask translated, analyst-1, crm.learners.email: {"email": "pseudonymize"}

Pointed at Trino unchanged, the policy would deny the checks Trino makes first, because its allow admits only SelectFromColumns. allow is false for ExecuteQuery, which Trino asks before any query runs, and for AccessCatalog.

The row_filters rule doesn't test the operation, so it answers GetRowFilters, but on its own it skips allow and chapter 5's missing-attribute guard. For agent-new, a support agent with no region yet, it returns [], which Trino reads as no filtering. So the answer has to come from the decision for the same read, asked as SelectFromColumns: its filters, written in Trino's SQL by the same row_filter the rewrite uses, or, when it denies the read, [{"expression": "false"}], which keeps no rows.

GetColumnMask names the column under action.resource.column, while the policy reads a SelectFromColumns request's columns, so as sent it masks nothing and the policy rejects the operation; translated, it gets pseudonymize. In production, those translations, the allow check and the Trino SQL belong in a Rego module beside the policy, so OPA can answer Trino itself. No OPA server runs here, so the code shows only what that module must return. One more piece maps onto Trino directly: the consent filter reads crm.learners, which the assistant may not, and Trino lets a filter carry an identity to run as, "such that a filter can target a column the requesting user cannot see".

Since 1.9.0, OPA's Compile API can also write SQL WHERE clauses from a policy by partial evaluation (OPA v1.9.0, Data filtering with OPA), but it needs an OPA server, which the examples don't run.

Working rule: Put enforcement in the engine every tool queries through, with one policy for it and for any rewrite like this chapter's. With Trino, answer every operation the plugin asks about, set both the row-filter and column-mask URIs (without them it applies no filters or masks), and return row filters only from a decision that allows the read. Build tests from the request shapes in the plugin's source, not from its documentation's example policies.

以他人身份查询

最近一次修改见于版次 2026.11.31

从仓库中回答问题的助手,是这一层的又一个用户,它面临一个选择:它的查询带着谁的身份?课程助手 assistant-1 有自己的访问权限,可读取经学习者同意的评价。当它回答某个人的问题时,必须以该人的身份查询,这样答案中包含的内容恰好是此人自己能够读取的内容。OWASP 针对语言模型应用的指南也持同样的看法:"在下游系统中实施授权,而不是依赖大语言模型来判断某个操作是否被允许"(OWASP LLM06:2025)。助手本身属于《Data Assistants in Practice》;本节讨论的是它的查询。

instructor-17 询问助手其所授课程的评价情况,助手随之写出一条查询。工具从已登录的会话中获取用户,绝不取自模型或对话,因此任何提示词都无法改变查询以谁的身份运行:

assistant-on-behalf.py Python 3.12.15 · duckdb 2.0.0 · pyarrow 26.0.1 · pyiceberg 0.12.0 · regopy 1.5.2 · sqlglot 30.33.0 通过
"""Runs the course assistant's review query as instructor-17 and as assistant-1, through a tool that takes the user fromthe signed-in session. The prose's point rests on the reviews-behind-it lines for each user."""from lib import policyfrom lib.catalog import open_catalogfrom lib.enforce import Enforcerplatform = Enforcer(open_catalog(["crm.learners", "learning.reviews"]))courses = set(policy.data_document()["platform"]["instructor_courses"]["17"])     # instructor-17's own coursesdef query_tool(session_user: str):    """The tool the course assistant calls to query the warehouse. The model writes the SQL; the user it runs as comes    from the signed-in session, never from the model or the conversation."""    # the user is the session's; the SQL is all the model contributes    return lambda sql: platform.sql(sql, user=session_user).fetchall()# instructor-17 asks the assistant how their courses' reviews look. The assistant writes this query.question = "select course_id, count(*) as reviews, round(avg(rating), 2) as rating from learning.reviews group by all"for runs_as in ["instructor-17", "assistant-1"]:    answer = query_tool(runs_as)(question)    print(f"run as {runs_as}, courses in the answer:", len(answer))    print(f"run as {runs_as}, reviews behind it:", sum(reviews for _, reviews, _ in answer))    # each answer row is (course, reviews, rating), so row[0] is the course    mine = [row for row in answer if row[0] in courses]    print(f"run as {runs_as}, instructor-17's reviews behind it:", sum(reviews for _, reviews, _ in mine))
输出python assistant-on-behalf.py
run as instructor-17, courses in the answer: 6
run as instructor-17, reviews behind it: 69
run as instructor-17, instructor-17's reviews behind it: 69
run as assistant-1, courses in the answer: 130
run as assistant-1, reviews behind it: 436
run as assistant-1, instructor-17's reviews behind it: 7

查询带着谁的身份,决定了答案涵盖什么。助手自己的身份在两个方向上都是错的:它让讲师看到了不该看的内容,又隐藏了本可以看到的内容。以 instructor-17 的身份运行时,答案涵盖其 6 门课程和 69 条评价。以助手的身份运行时,答案涵盖 130 门课程,其中大多数属于其他讲师,而该讲师自己的评价只有 7 条,即作者同意用于 AI 训练的那些。检索也是如此:基于受治理文本的助手需要"具备权限感知能力的向量与嵌入存储"(OWASP LLM08:2025)。

工作准则: 以助手所回答之人的身份运行助手的查询,该身份取自此人已认证的会话,并由这一层应用此人的决定。仅当助手所做的工作并非针对特定的人时,才使用助手自己的身份,例如每晚为课程页面汇总经同意的评价;并将两种身份都记录在审计日志中。构建助手训练集的人以自己的身份查询,并使用用途 ai-training,正如第 9 章所做的那样。

生产环境中会出什么问题

最近一次修改见于版次 2026.11.27

执行层的失败方式,是放过了一个没有人测试过的读取操作,而且这些失败多数看起来像是正确的结果:

  • SELECT * 会在模式中缺失的表上漏过。 qualify 会让星号保持未展开状态,并且不报任何错误。应拒绝目录中没有的每一张表,并在目录变化时重建模式。
  • 聚合会泄露小分组。 只有一人的单元格会指明其成员,而不同报表之间的差异,即使单元格满足任何最小值要求,也会指明其中的成员,正如报表示例所示。应设定最小值,并去掉按读者无权读取的属性所做的细分。
  • 连接和复制会把被掩码的列带回来。 拥有完全访问权限的流水线,例如写入 marketing.audience 的那条,会把 email 复制到一张只受自身分类保护的表中。若未分类,该副本对所有人隐藏,正如谁能看到什么测试所示;若被误分类为 internal,则任何有权读取该表的人都能看到它。第 4 章沿血缘传播分类以捕获这种情况。未被掩码的列也会通过连接重新识别个人:Sweeney 发现,在 1990 年人口普查数据中,美国人口的 87% 可能凭邮政编码、性别和出生日期唯一确定(Sweeney, 2002);Golle 重新计算后,在同一份 1990 年数据上得到 61%,在 2000 年数据上得到 63%(Golle, 2006)。Rocher 等人根据拟合人口普查数据的模型估计,15 项人口统计属性即可使马萨诸塞州 99.98% 的人唯一确定(Rocher et al., 2019)。
  • 直接读取文件会绕过重写。 read_parquet、FROM 中的路径以及表函数无需指明表名即可读取数据。应在重写中拒绝它们,并关闭 enable_external_access,此后 DuckDB 不会再将其打开:“Cannot enable external access while database is running”。lock_configuration 会让其他所有设置保持执行层最后设定的样子;应在执行层的连接完全配置好之后再设置它(Securing DuckDB),而不是在创建连接时设置,因为第 8 章会在此之后启动 DuckDB 的查询日志。能够读取存储中仓库文件的人无需使用 SQL,因此存储权限也是执行的一部分。
  • 标识符的大小写决定名称能匹配什么。 若执行层在匹配名称前不做规范化,"LEARNERS"(DuckDB 将其解析为 learners)就能访问原始表,正如第一节所示。在匹配任何名称之前,应使用运行该查询的引擎的方言进行规范化。
  • 错误信息会打印出值。 DuckDB 的类型转换错误会包含该值。表重写使它成为被掩码的值,而每一列上的关卡使被丢弃的行无法触及,但除非有机制拦截,这条信息仍会到达用户。应在服务边界返回通用错误,如 lib.enforce.answer 所做的那样,并把引擎的信息记录在只有平台团队能读到的地方,与审计日志分开,第 8 章会让审计日志中不含任何值。PostgreSQL 警告说,即使在安全屏障之后,执行计划和耗时仍是隐蔽信道(PostgreSQL 18, Rules and Privileges)。
  • DuckDB 不是安全边界。 “DuckDB 以运行它的用户的全部权限执行 SQL,很像 shell 或脚本解释器”(Securing DuckDB)。同一进程内的重写,只有在用户无法通过其他任何途径访问 DuckDB 时才能保护数据。应把本章的执行层视为 Trino 等引擎所执行内容的一个经过测试的模型,并在生产环境中于该引擎上执行。

本章的决定

最近一次修改见于版次 2026.11.27
  • 强制执行替换的是表,而不是列。 每个目录表都变成由决策生成的子查询;改写列引用的做法只是为了说明它会泄露信息而保留。
  • 该层默认拒绝(fail closed)。 它的数据源是一份允许清单,包括目录表、作用域内的 CTE 和子查询。它拒绝带有副作用的函数、参数,以及任何不是单条查询的语句。查询没有读取的列会被类型化为 NULL,而读取的列则受行过滤器把关。
  • 用户只会从 answer 得到一条通用错误,DuckDB 的报错信息则交给平台团队。
  • 掩码只在一处以 SQL 形式存在。 策略只给掩码命名,由 lib.enforce 编写它们的 SQL。假名使用只有该层持有的密钥生成,密钥以参数形式传入。
  • 行过滤器由数据构建, 其中的值以字面量形式写入。同意情况是该层以自身的访问权限执行的半连接,未知的同意按“否”处理。
  • 谁能看到什么,按查询来测试: 第 5 章的用例在每次变更时都通过该层运行。
  • 报告会隐去人数低于最小分组规模的分组, 该值为 11,设置在 governance/enforcement.toml 中;报告也不会按读者无权读取的属性做细分。
  • 助手以其所回答的那个人的身份查询, 身份取自会话。
  • 在生产环境中,由引擎执行强制: 使用 Trino 与 OPA,来自同一份策略,两个 URI 都要设置,且行过滤器只根据允许的决策来作答。一个层的安全程度,取决于它的测试。

本章,在本版次中

版次 2026.11.31示例于 2026年11月19日 通过本章 16个示例 个示例中有 16 个通过

译自英文版图书,版次 2026.11.31。

引用本节

格式

每种格式均引用英文原书,本译本以其为准。

每种格式都会注明该版次。其链接会打开当前版次,节标题处会说明该节文本最后一次修改的时间。访问日期和检索日期即为您打开此页的当天。

本页内容

第 6 章,共 10 章: 在查询上执行策略

  1. 找出查询读取的每张表和每一列
  2. Allow only catalog tables, CTEs and subqueries
  3. 屏蔽列引用会泄露数据
  4. 用经过过滤和掩码处理的子查询替换每张表
  5. Write each mask as SQL in one place
  6. 用决策中的数据构建行过滤器
  7. 让每一列都依赖行过滤条件
  8. 通过运行查询来测试谁能看到什么
  9. 在报告中隐去小群组
  10. Hand the same policies to an engine that enforces them
  11. 以他人身份查询
  12. 生产环境中会出什么问题
  13. 本章的决定

返回顶部全部章节