Enforcing policies on queries
읽는 데 약 73분분 걸린다, 13절에 16개 예제이(가) 있음.
AI로 번역함. 영어 원문을 사람이 검증함. 우리가 책을 만드는 방식
영어판 2026.11.31판을 번역한 것이다. 원문 읽기
이 페이지의 내용
- Find every table and column a query reads
- Allow only catalog tables, CTEs and subqueries
- Masking column references leaks
- 각 테이블을 필터링과 마스킹이 적용된 서브쿼리로 교체한다
- Write each mask as SQL in one place
- 결정의 데이터로 행 필터를 만든다
- 모든 열을 행 필터로 보호한다
- 쿼리를 실행해 누가 무엇을 보는지 테스트하기
- Hold back small groups in reports
- 같은 정책을 그 정책을 집행하는 엔진에 넘긴다
- Query on someone's behalf
- What goes wrong in production
- 이 장에서 결정한 것
이 부분은 아직 번역되지 않았으므로 영어로 표시된다.
By the end of this chapter you can enforce access decisions on every query the platform runs. You'll rewrite each query so that every table it reads is replaced by a subquery that keeps only the rows the user may see and masks the columns they may not, deny every query that reads anything else, test who gets what by running real queries, and say where a rewrite in front of DuckDB stops protecting anything.
A policy protects the online learning platform's warehouse only if it holds for every query that reaches it, and that warehouse keeps learners' contact details, their payments and the text they write. Chapter 5 wrote the platform's access policy in Rego. Given a user, a table and the columns they want, and optionally why they want them, the policy returns a decision: whether to allow the read, which row filters keep the rows the user may see, which column masks change how a column is shown (redact, last4, pseudonymize or redact_contacts), and the reasons. A decision does nothing on its own. A support agent's SQL reaches DuckDB as text, so something between the two must apply the decision to every table that text reads, however the query is written.
The book's shared module lib.enforce enforces decisions; we call it the enforcement layer. Its Enforcer holds a DuckDB connection with a view for each catalog table (lib.warehouse.connect), the tables' schema, the policy and a key for pseudonyms. Users never get the connection. Enforcer.sql(query, user) rewrites a user's query and runs the rewrite. A service exposes only lib.enforce.answer, which calls it and returns the rows: never the relation, a handle on the connection, or Enforcer.run, which executes SQL the layer wrote with the key attached. The code runs sqlglot 30.33.0 and DuckDB 2.0.0.
이 부분은 아직 번역되지 않았으므로 영어로 표시된다.
Find every table and column a query reads
A policy can only decide about what it's asked, so the layer has to know every table a query reads and the columns it reads from each. Reading that off SQL text is a parsing problem with traps: *, aliases, joins with USING, common table expressions (CTEs) and identifiers in any case. sqlglot parses a query into a tree, and its qualify step resolves every column against a schema, qualifies it with its table, and expands * into the columns it stands for when it has a schema to expand against (qualify at v30.24.0). The layer builds that schema when it starts, listing the catalog's tables and reading each one's columns and types from the view lib.warehouse.connect created for it, so a view DuckDB holds for anything else never enters the schema.
Four queries, each with a trap, show what Enforcer.reads returns: the columns the policy is asked about, which must match what each query names.
1"""The columns four queries read from each table, and how each dialect normalizes the quoted name "LEARNERS". The2prose's point rests on the query with a CTE and on the normalization lines at the end."""3import sqlglot4from sqlglot import exp5from sqlglot.optimizer.normalize_identifiers import normalize_identifiers67from lib.catalog import open_catalog8from lib.enforce import Enforcer910# only these two tables are loaded, so the layer's schema holds nothing else11platform = Enforcer(open_catalog(["crm.learners", "support.tickets"]))1213# What each query reads, found by sqlglot's qualify against the catalog's schema, with `*` expanded.14queries = {15 "every column": "select * from crm.learners",16 "a join": """select l.country, count(*) as tickets17 from crm.learners as l join support.tickets as t using (learner_id) group by all""",18 # exclude drops notes from the CTE, so the reads name every other column of crm.learners19 "a CTE": """with emea as (select * exclude (notes) from crm.learners where support_region = 'emea')20 select email from emea""",21 "names in capitals": 'select EMAIL from "CRM"."LEARNERS"',22}23for name, sql in queries.items():24 for table, columns in platform.reads(sql).items():25 print(f"{name}, {table}: {', '.join(columns)}")2627# DuckDB resolves quoted capitals to the lowercase table. Whether sqlglot lowercases them depends on the dialect.28capitals = 'select count(*) from "CRM"."LEARNERS"'29print('DuckDB, "CRM"."LEARNERS":', f"{platform.connection.sql(capitals).fetchall()[0][0]:,} rows")30for dialect in ["duckdb", "trino", "postgres"]:31 # the name the dialect's parser resolves the quoted identifier to32 table = normalize_identifiers(sqlglot.parse_one('select * from "LEARNERS"', read=dialect), dialect=dialect).find(exp.Table)33 print(f'{dialect} normalizes "LEARNERS" to:', table.name)
python tables-a-query-reads.pyevery 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
The reads follow what a query names, not what it returns. SELECT * reads every column of crm.learners, and the join reads learner_id from both tables, because USING compares them, though neither appears in the output. The CTE returns only email, but it names every column of crm.learners except notes, so the layer asks about all of those. A query's reads can be more than the query returns, and never fewer. "CRM"."LEARNERS" resolves to crm.learners as well, because the layer normalizes identifiers the way DuckDB resolves them: DuckDB returns 5,000 rows of crm.learners for that name. The dialect matters. sqlglot lowercases "LEARNERS" for DuckDB and Trino, and keeps it as LEARNERS for PostgreSQL, so the layer normalizes with the dialect of the engine that runs the query.
qualify has one gap that decides the layer's design: it fails on an unknown column of a known table, but it passes over a table that isn't in the schema at all, and leaves that table's * unexpanded. Run it on a join with marketing.audience, which stands for a table a pipeline wrote after the schema was read, then send that query and five other reads through the layer:
1"""Runs sqlglot's qualify on a join with a table the schema lacks, then sends that join and five other reads through2the enforcement layer. The prose's point rests on the qualify line, where the star stays, and on the denial lines."""3import sqlglot4from sqlglot.optimizer.qualify import qualify56from lib.catalog import open_catalog7from lib.enforce import Denied, Enforcer89platform = Enforcer(open_catalog(["crm.learners"]))1011# marketing.audience stands for a table a pipeline wrote after the schema was read. qualify alone lets it through.12query = "select * from crm.learners as l join marketing.audience as a on a.learner_id = l.learner_id"13print("qualify on its own:", qualify(sqlglot.parse_one(query, read="duckdb"), schema=platform.schema, dialect="duckdb").sql("duckdb"))1415# The enforcement layer denies anything that isn't a catalog table, before it asks the policy.16attempts = {17 "a table missing from the catalog": query,18 "a column the table doesn't have": "select shoe_size from crm.learners",19 # scan_crm_learners is the scan lib.warehouse registers for crm.learners, and DuckDB resolves it like a table20 "the scan behind a view": "select count(*) from scan_crm_learners",21 "a Parquet file": "select count(*) from read_parquet('data/platform/learners.parquet')",22 "a table function": "select count(*) from query_table('crm.learners')",23 "a statement that isn't a query": "summarize crm.learners",24}25for name, sql in attempts.items():26 try:27 platform.rewrite(sql, "analyst-1")28 print(f"{name}: allowed")29 except Denied as reason:30 print(f"{name}: denied, {reason}")3132# What those reads would do on the layer's own connection, without the rewrite in front of it.33rows = lambda sql: f"{platform.connection.sql(sql).fetchall()[0][0]:,} rows"34print("the scan, run directly:", rows("select count(*) from scan_crm_learners"))35print("the table function, run directly:", rows("select count(*) from query_table('crm.learners')"))36try:37 rows("select count(*) from read_parquet('data/platform/learners.parquet')")38except Exception as error:39 print("the file, run directly:", str(error).split(":")[0])40# the layer turned external access off, and DuckDB refuses to turn it back on while the database runs41try:42 platform.connection.execute("set enable_external_access = true")43except Exception as error:44 print("turning external access back on:", str(error).split(": ", 1)[1])
python unknown-tables-denied.pyqualify 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 on its own returns the query with SELECT * intact and no error, so a layer that trusted it would ask the policy about crm.learners and pass marketing.audience through unchecked. The layer denies every table outside the catalog before it asks the policy anything, and qualify itself denies a column the table doesn't have. The other four reads show why that rule is broad. lib.warehouse registers each table's scan under a name of its own, such as scan_crm_learners. Run directly on the layer's connection, that scan returns 5,000 rows of raw learners. query_table reads whatever table its string names, and returns 5,000 rows when run directly. read_parquet reads a file, and SUMMARIZE isn't a query the rewrite can analyse. The first three aren't catalog tables and the last isn't a query, so all four are denied. The layer's connection also has DuckDB's enable_external_access turned off, so a file read that got past the rewrite would still fail, with a Permission Error (Securing DuckDB).
Working rule: Ask the policy about every table and column a query names, with * expanded against the catalog's schema, and deny whatever that schema doesn't hold: unknown tables, files, table functions and statements other than a single query. A denial costs one question to the platform team; a pass-through costs whatever the table holds.
이 부분은 아직 번역되지 않았으므로 영어로 표시된다.
Allow only catalog tables, CTEs and subqueries
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:
1"""Runs the allowlist's regression cases through the layer and prints each denial with its reason; a case passes when it's2denied 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."""3from lib.catalog import open_catalog4from lib.enforce import MUST_ALLOW, MUST_DENY, Enforcer, source_cases56platform = Enforcer(open_catalog(["crm.learners", "support.tickets"]))78# The allowlist's regression test, lib.enforce.source_cases, which chapter 10's CI runs too: queries that read data9# other than through a catalog table, a call to each function DuckDB marks as having side effects, and a query the10# layer must still allow. A case passes when the layer denies it exactly when it must, and its parse logs nothing.11cases = source_cases(platform)12for case in cases:13 if case["case"] in MUST_DENY:14 print(f"{case['case']}:", f"denied, {case['reason']}" if case["denied"] else "ALLOWED")15functions = [case for case in cases if case["must"] == "deny" and case["case"] not in MUST_DENY]16print("functions with side effects:", ", ".join(case["case"].removeprefix("the function ") for case in functions))17print("functions with side effects, denied:", f"{sum(case['denied'] for case in functions)} of {len(functions)}")18print("cases whose parse sqlglot logged, quoting the query:", f"{sum(case['logged'] for case in cases)} of {len(cases)}")19passing = [case for case in cases if case["denied"] == (case["must"] == "deny") and not case["logged"]]20print("cases that pass:", f"{len(passing)} of {len(cases)}")2122# What two of the cases do on the layer's own connection, without the layer in front of it.23# the name is used before its CTE is defined, so DuckDB resolves it to the scan of the same name24before_the_cte = platform.connection.sql(MUST_DENY["a CTE's name before the CTE"]).fetchall()[0][0]25print("a CTE's name before the CTE, run directly:", f"{before_the_cte:,} rows")26# turns on DuckDB's query log, the one write_log writes into, so the entries below are what the call left27platform.connection.execute("CALL enable_logging('QueryLog')")28platform.connection.sql(MUST_DENY["a function that writes DuckDB's log"]).fetchall()29logged = [message for (message,) in platform.connection.sql("select message from duckdb_logs where type = 'QueryLog'").fetchall()]30print("the log function, run directly, query-log entries it leaves:", logged)3132# the query the allowlist must admit: a CTE, a join and a scalar subquery over catalog tables33for name, query in MUST_ALLOW.items():34 print(f"{name}, allowed:", f"{platform.sql(query, 'agent-a05').fetchall()[0][0]:,} rows")
python catalog-sources-only.pya 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.
이 부분은 아직 번역되지 않았으므로 영어로 표시된다.
Masking column references leaks
With the columns known, the obvious enforcement rewrites them: wherever the query selects email and the decision masks it, select the mask instead. It needs only the column list from the first section, and it looks right on the queries people test with. It fails because a query can read a column without selecting it, and DuckDB can return a column without naming it.
Enforcer.mask_select_list does the column rewrite, kept only to show its failure. We test it with five reads aimed at learner 2's email, run as analyst-1, a data scientist who may read crm.learners with email and the other personal columns pseudonymized and free text such as notes redacted. The first print is the rewrite of a plain read, with any stored email replaced by a placeholder, since the examples never print one. Predict how many of the five the rewrite stops:
1"""Masks the email in the select list only, then runs five reads aimed at one learner's email through that rewrite and2prints what each returns. The prose's point rests on the five attack lines, each of which returns learner 2's ID, the3stored email, or an error that holds it."""4import re56import duckdb78from lib.catalog import open_catalog9from lib.enforce import EMAIL, Enforcer1011platform = Enforcer(open_catalog(["crm.learners"]))12# analyst-1, a data scientist, may read crm.learners with `email` pseudonymized. The attacks aim at learner 2.13email = platform.connection.sql("select email from crm.learners where learner_id = 2").fetchall()[0][0]14print("the column rewrite:", platform.mask_select_list("select learner_id, email from crm.learners", "analyst-1"))1516attacks = {17 "a WHERE on the raw column": f"select learner_id from crm.learners where email = '{email}'",18 "the whole row as a struct": "select struct_extract(l, 'email') from crm.learners as l where learner_id = 2",19 "COLUMNS(...)": "select columns('^email$') from crm.learners where learner_id = 2",20 "to_json": "select to_json(l) ->> 'email' from crm.learners as l where learner_id = 2",21 "a cast error": "select learner_id from crm.learners where cast(email as integer) = 0",22}23for name, sql in attacks.items():24 try:25 # the column rewrite changes only the select list, so every other clause reads the stored email26 answer = str(platform.run(platform.mask_select_list(sql, "analyst-1")).fetchall())27 except duckdb.Error as error:28 answer = str(error).splitlines()[0]29 print(f"{name}:", re.sub(EMAIL, "<a stored email>", answer)) # the book never prints a stored email
python select-list-masks-leak.pythe 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
The column rewrite stops none of the five. Its rewrite of the plain read is correct: email comes back as a hash. The five reads get past it in three ways:
- A
WHEREon the raw column never passes through the select list, so it compares the stored email. The analyst guesses an email and gets back learner 2's ID,[(2,)], which links that person to every row they can join onlearner_id. - The whole row as a struct,
COLUMNS(...)andto_jsonreturn the stored email,[('<a stored email>',)], without any column reference namedemailfor the rewrite to find: DuckDB expands the row, or matches column names against a pattern, itself. - A cast error prints a stored email in its message. DuckDB's cast errors include the value, and PostgreSQL's documentation warns that "most casting functions include their input values in the error messages they produce" (PostgreSQL 18, Rules and Privileges).
Masking the column wherever the query names it, in the WHERE clause too, would stop the first and the last read. It can't stop the middle three, because they never name the column.
Working rule: Don't enforce masks by rewriting column references. However many reference shapes you cover, the engine has more ways to read a column than the rewrite can recognize. Mask where the data comes from instead, so every read sees the masked value, as the next section does.
각 테이블을 필터링과 마스킹이 적용된 서브쿼리로 교체한다
열 참조 다시 쓰기는 원본 테이블이 쿼리에 남아 있으므로 정보가 새어 나간다. 해결책은 테이블 자체를 교체하는 것이다. Enforcer.rewrite는 쿼리에 나오는 각 카탈로그 테이블을, 쿼리가 그 테이블에 쓰는 별칭을 그대로 붙여, 사용자의 결정으로 만든 서브쿼리로 교체한다.
- 테이블의 모든 열을 테이블의 순서대로 포함하므로,
*, 구조체,COLUMNS(...)는 기대하는 형태를 그대로 본다. - 쿼리가 읽는 각 열은 결정에 따라 저장된 값 그대로 또는 마스킹된 값으로 내보내며, 이는 이후 절에서 설명하는 행 필터에 대한
CASE안에서 이루어진다. - 쿼리가 읽지 않는 각 열은 정책에 문의하지 않았으므로 타입이 지정된
NULL로 내보낸다. - 결정에 포함된 모든 행 필터를 담은
WHERE절을 둔다.
그러면 사용자 쿼리가 하는 모든 일, 곧 WHERE부터 구조체 읽기까지가 이 서브쿼리 위에서 일어난다. 이는 Trino의 OPA 플러그인이 따르는 모델이다. 이 모델에서 행 필터는 본질적으로 추가 WHERE 절처럼 동작하며, 엔진에 관한 절에서 이를 확인할 수 있다.
instructor-17를 예로 들어 보자. 이 강사는 자신이 맡은 강좌의 리뷰 중 낮은 평점을 읽는다. 정책은 강사 본인 강좌의 리뷰만 남기고 body에서 연락처를 제거한다. 출력된 재작성 결과는 그 방식을 보여 준다.
1"""Rewrites instructor-17's query over the reviews, prints the rewrite and runs it. The prose's point rests on the two2result lines: how many reviews come back, and how many of them still hold an email."""3import sqlglot45from lib.catalog import open_catalog6from lib.enforce import Enforcer78platform = Enforcer(open_catalog(["learning.reviews"]))910# instructor-17 reads the low ratings of their courses' reviews. The policy filters reviews to their six courses and11# shows `body` with contact details removed.12query = "select course_id, rating, body from learning.reviews where rating <= 2"13# the query as text, with the reviews table replaced by a subquery built from the decision14rewritten = platform.rewrite(query, "instructor-17")15print(sqlglot.transpile(rewritten, read="duckdb", write="duckdb", pretty=True)[0])16print("reviews returned:", len(platform.run(rewritten).fetchall()))17print("of them holding an email:", platform.run(f"select count(*) from ({rewritten}) where body like '%@%'").fetchall()[0][0])
python table-rewrite.pySELECT
"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를 거쳐서도 실행한 결과는 다음과 같다.
1"""Sends the five reads from the column rewrite through the table rewrite, and through `answer`, the service's boundary.2It then shows what the analyst receives and what the platform team's log holds. The prose's point rests on the five attack3lines, the operator's log line and the relation line."""4import logging5import re67from lib.catalog import open_catalog8from lib.enforce import EMAIL, Enforcer, QueryFailed, answer910platform = Enforcer(open_catalog(["crm.learners"]))11operator_log = [] # the platform team's log, kept in a list here so the example can print it12logging.getLogger("lib.enforce").addHandler(handler := logging.Handler())13handler.emit = lambda record: operator_log.append(record.getMessage().splitlines()[0])1415# The five attacks on the column rewrite, run by analyst-1 again: through the table rewrite this time, and through16# `answer`, the function a service calls at its boundary.17email = platform.connection.sql("select email from crm.learners where learner_id = 2").fetchall()[0][0]18attacks = {19 "a WHERE on the raw column": f"select learner_id from crm.learners where email = '{email}'",20 "the whole row as a struct": "select struct_extract(l, 'email') from crm.learners as l where learner_id = 2",21 "COLUMNS(...)": "select columns('^email$') from crm.learners where learner_id = 2",22 "to_json": "select to_json(l) ->> 'email' from crm.learners as l where learner_id = 2",23 "a cast error": "select learner_id from crm.learners where cast(email as integer) = 0",24}25# a pseudonym is 64 hex digits, shortened here; the example never prints a stored email26shorten = lambda text: re.sub(r"\b([0-9a-f]{8})[0-9a-f]{56}\b", r"\1…", re.sub(EMAIL, "<a stored email>", text))27for name, sql in attacks.items():28 try:29 # answer returns the rows, or raises QueryFailed with a generic message, as a service sees it30 got = str(answer(platform, sql, "analyst-1"))31 except QueryFailed as error:32 got = f"{type(error).__name__}, {error}"33 print(f"{name}:", shorten(got))34for line in operator_log:35 print("in the operator's log:", shorten(line)) # a pseudonym, 64 hex digits, shortened3637# Why `answer` returns rows: the relation Enforcer.sql returns is a handle on the layer's connection, and runs any SQL.38# the relation is a handle on the layer's connection, so its .query runs SQL of its own there39relation = platform.sql("select learner_id from crm.learners", "analyst-1")40print("the relation, running SQL of its own:", shorten(str(relation.query("r", "select email from crm.learners where learner_id = 2").fetchall())))41print("what answer returns:", type(answer(platform, "select learner_id from crm.learners", "analyst-1")).__name__)
python table-rewrite-holds.pya 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
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:
1"""Prints the SQL the enforcement layer writes for each of the four masks the policy names, and counts DuckDB's functions2named like HMAC. The prose's points rest on the pseudonymize line and on the HMAC count."""3import duckdb4from sqlglot import exp56from lib.enforce import masked78# The four masks the policy names, as the SQL the enforcement layer writes for them.9# the column's type, which the redact mask casts its NULL to10for kind, column, type_ in [("redact", "date_of_birth", "DATE"), ("last4", "card_number", "VARCHAR"),11 ("pseudonymize", "email", "VARCHAR"), ("redact_contacts", "body", "VARCHAR")]:12 # redact is a typed NULL; the other masks' SQL comes from lib.enforce's MASKS table13 print(f"{kind}:", masked(kind, exp.column(column), type_).sql("duckdb"))1415# The pseudonymize mask hashes the key followed by the value, because DuckDB has no HMAC function to call.16# DuckDB's function catalog, searched for any name containing hmac, whatever its case17hmac = duckdb.sql("select count(*) from duckdb_functions() where function_name ilike '%hmac%'").fetchall()[0][0]18print("DuckDB functions named like HMAC:", hmac)
python masks-as-sql.pyredact: 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:
redactis aNULLcast 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.last4keeps the last four digits of a card number, after removing anything that isn't a digit.pseudonymizeis 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_contactsreplaces 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:
1"""Hashes a list of emails the way the layer hashes pseudonyms, with and without its key, then rotates the key and counts2the pseudonyms that change. The prose's points rest on the three count lines: the matches without the key and with it, and3the pseudonyms that change on rotation."""4from lib.catalog import open_catalog5from lib.enforce import Enforcer67# The pseudonyms analyst-1, the platform's data scientist, sees in crm.learners. Someone holding a list of the8# platform's emails hashes each one and looks for it among them, first without the key and then with it.9# an example key, not a secret: production keeps the real one where only the layer can read it10platform = Enforcer(open_catalog(["crm.learners"]), key="example-key-not-a-secret")11seen = platform.sql("select learner_id, email from crm.learners", "analyst-1").to_arrow_table()12platform.connection.register("seen", seen)13# a guess is an email's hash; the second also puts the key first, as the layer hashes a pseudonym14for name, guess in [("without the key", "sha256(email)"), ("with the key", "sha256($pseudonym_key || email)")]:15 matched = platform.run(f"select count(*) from crm.learners where {guess} in (select email from seen)").fetchall()[0][0]16 print(f"pseudonyms matched from a list of emails, {name}: {matched:,} of {seen.num_rows:,}")1718# A new key gives every learner a new pseudonym, so data pseudonymized under the old key no longer joins.19platform.key = "example-key-rotated"20rotated = platform.sql("select learner_id, email from crm.learners", "analyst-1").to_arrow_table()21# pairs each learner's old and new pseudonym, row by row22changed = sum(a != b for a, b in zip(seen["email"].to_pylist(), rotated["email"].to_pylist()))23print("pseudonyms that change when the key is rotated:", f"{changed:,} of {seen.num_rows:,}")
python pseudonym-key.pypseudonyms 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:
1"""Applies the redact_contacts mask to every ticket and review, then counts the labelled personal data still left in the2masked text, and the phone replacements made in the tickets. The prose's points rest on the 'left in the text' lines, the3replacement count, and the assert on the tickets' card, IBAN and date lines."""4import duckdb5from sqlglot import exp67from lib.enforce import masked8from lib.platform import load910# The redact_contacts mask, run over every ticket and review, against the personal data the generator placed in them11# (pii_spans, labelled by construction).12con = duckdb.connect()13# each table registers under its short name, which the queries below use14for name in ["support.tickets", "learning.reviews", "pii_spans"]:15 con.register(name.split(".")[-1], load(name))16mask = masked("redact_contacts", exp.column("body"), "VARCHAR").sql("duckdb")17# a span counts as left when its exact text is still in the masked body, which the row_id join finds18counts = con.sql(f"""19 with masked as (select 'tickets' as source, ticket_id as row_id, {mask} as body from tickets20 union all select 'reviews', review_id, {mask} from reviews)21 select source, entity, count(*), count(*) filter (where contains(masked.body, pii_spans.text))22 from pii_spans join masked using (source, row_id) group by all order by all""").fetchall()23# the phone pattern takes every card number, IBAN and date in the tickets, so none is left in the masked text24left_in_tickets = {entity: left for source, entity, _, left in counts if source == "tickets"}25assert all(left_in_tickets[entity] == 0 for entity in ("CREDIT_CARD", "IBAN_CODE", "DATE_TIME"))26for source, entity, placed, left in counts:27 print(f"{source}, {entity}, left in the text: {left:,} of {placed:,}")28# The phone pattern also takes card numbers, IBANs and dates, which are digits too.29replaced = con.sql(f"select sum(len(regexp_extract_all({mask}, '\\[phone\\]'))) from tickets").fetchall()[0][0]30print("[phone] replacements in tickets:", f"{replaced:,}")
python redact-contacts-recall.pyreviews, 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".
결정의 데이터로 행 필터를 만든다
마스크는 값이 보이는 방식을 바꾸고, 행 필터는 사용자가 받을 행을 정한다. 행 필터는 결정에 데이터로 담겨 오며, 형태는 세 가지다. 열이 어떤 값과 같은 경우, 열이 값 목록에 속하는 경우, 행이 속한 학습자에 대한 동의 확인이다. lib.enforce.row_filter은 각 형태를 SQL 조건으로 바꾸며, 이때 값은 sqlglot이 SQL 리터럴로 작성하고 SQL 텍스트에 그대로 붙여 넣지 않는다. 알 수 없는 형태나 테이블에 없는 열이 나오면 쿼리를 거부하며, 동의 필터의 경우 계층은 확인 조건을 만들기 전에 그 열을 crm.learners과 대조한다. 5장에서 정책이 필터를 데이터로 반환하게 한 것도 바로 이 때문이다. 요청에 실린 어떤 것도 코드로서 쿼리에 도달할 수 없다.
동의 형태에는 주의가 필요하다. 강의 어시스턴트 assistant-1는 리뷰 텍스트가 플랫폼의 AI를 학습시키는 데 동의한 학습자가 쓴 리뷰만 읽을 수 있다. 그 동의는 어시스턴트가 읽을 수 없는 crm.learners에 있다. 계층은 이를 세미 조인으로 바꾼다. 세미 조인은 리뷰의 learner_id가 동의한 학습자 중에 있는지 검사하는 조건이며, 계층 자신의 crm.learners 접근 권한으로 실행된다.
1"""Prints the condition the layer writes for each row filter in three decisions, then the reviews the assistant reads2through its consent filter, and the denial of its read of the learners table. The prose's points rest on the read counts3and the consent counts, and on the denied read at the end."""4from lib.catalog import open_catalog5from lib.enforce import Enforcer, row_filter67platform = Enforcer(open_catalog(["crm.learners", "learning.reviews", "support.tickets"]))89# Each row filter in a decision, as the condition the enforcement layer writes for it.10for user, table in [("agent-a05", "support.tickets"), ("instructor-17", "learning.reviews"), ("assistant-1", "learning.reviews")]:11 # the decision for this read; its row filters are data, written as SQL in the loop below12 decision = platform.decide(user, {table: set()})[table]13 for rule in decision["row_filters"]:14 print(f"{user}, {table}: {row_filter(rule, platform.columns(table)).sql('duckdb')}")1516# A filter's values come from data, so a value that looks like SQL stays a value.17odd = {"column": "support_region", "equals": "emea' or '1' = '1"}18print("a region that looks like SQL:", row_filter(odd, platform.columns("support.tickets")).sql("duckdb"))1920# The assistant's consent filter reads crm.learners, which the assistant itself may not read.21total = platform.connection.sql("select count(*) from learning.reviews").fetchall()[0][0]22read = platform.sql("select count(*) from learning.reviews", "assistant-1").fetchall()[0][0]23print("reviews the assistant reads:", f"{read:,} of {total:,}")24# a null consent means no answer was recorded, so the label reads 'never asked'25by_consent = platform.connection.sql("""select coalesce(l.ai_training_consent::varchar, 'never asked'), count(*)26 from learning.reviews r join crm.learners l using (learner_id) group by all order by all""").fetchall()27for consent, reviews in by_consent:28 print(f"reviews whose author's AI-training consent is {consent}: {reviews:,}")29try:30 platform.sql("select count(*) from crm.learners", "assistant-1")31except Exception as reason:32 print("the assistant reading crm.learners:", reason)
python row-filters-from-data.pyagent-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 빌더로 결정의 값에서 만들고, 계층이 인식하지 못하는 필터 형태나 열은 거부한다. 알 수 없는 동의는 동의 없음으로 취급한다. 필터에 사용자가 읽을 수 없는 테이블이 필요하면, 사용자의 접근 권한을 넓히지 말고 서브쿼리 안에서 계층 자신의 접근 권한으로 읽는다.
모든 열을 행 필터로 보호한다
행 필터는 서브쿼리의 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를 숫자로 캐스트하는 쿼리를 실행한다. 이 캐스트는 제외된 행의 값을 볼 때만 실패한다.
1"""Runs the evaluation-order test for each filter shape the layer writes, with the filter in a WHERE alone and as the layer2writes it. The prose's point rests on the consent semi-join's two lines, and on the count at the end."""3from lib.enforce import order_cases45# The evaluation-order regression test, lib.enforce.order_cases, which chapter 10's CI runs on every DuckDB upgrade.6# For each shape of row filter the layer writes, it takes a real user's filter and a copy of the filtered table whose7# `body` holds digits in the rows the filter keeps and text in the rows it drops. A query that casts `body` to a number8# fails only if the cast sees a dropped row's value. It runs over the filter in a WHERE alone, and over the layer's9# subquery.10cases = order_cases()11for case in cases:12 for form, safe in [("with the filter in WHERE only", case["safe_with_where_only"]),13 ("as the layer writes it", case["safe_as_written"])]:14 print(f"{case['shape']}, {form}: the cast {'never saw' if safe else 'saw'} a dropped row's value")15print("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.pyequality, 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 뷰처럼 엔진에 자체 방벽이 있다면 그것도 함께 사용한다.
쿼리를 실행해 누가 무엇을 보는지 테스트하기
5장에서는 누가 무엇을 보는지를 나타내는 표 lib.policy.WHO_SEES_WHAT로 정책을 검사한다. 이 표는 요청의 목록이며, 각 요청마다 검토자가 기대하는 결정이 붙어 있다. 이 검사는 결정을 테스트할 뿐 사용자가 실제로 받는 결과를 테스트하지는 않는다. 사용자가 받는 결과는 재작성, 마스크의 SQL, 데이터에도 좌우되기 때문이다. lib.enforce.observe는 각 케이스를 해당 케이스의 사용자로서 계층을 거쳐 실행하고, 돌아온 결과를 저장된 데이터와 비교한다. 비교하는 것은 행의 수와 값이 달라진 열이다. 케이스가 결정과 일치한다는 것은 세 조건을 모두 만족한다는 뜻이다. 첫째, 케이스가 거부라고 할 때에만 사용자가 거부된다. 둘째, 케이스가 행 필터를 기대할 때에만 사용자가 저장된 것보다 적은 행을 받는다. 셋째, 케이스가 마스킹을 기대하는 열에서만 값이 바뀐 채로 받는다.
1"""Runs chapter 5's who-sees-what cases as queries and compares what each user gets with what is stored, then reads2marketing.audience, which has no metadata. The prose's points rest on the agreement count and on the two marketing lines."""3from lib import pipelines, policy4from lib.catalog import open_catalog5from lib.enforce import Enforcer, agrees, observe67catalog = open_catalog(["crm.learners", "learning.reviews", "billing.payments", "learning.enrollments", "learning.courses"])8pipelines.run(catalog, "marketing_audience") # a copy of learners' names and emails, with no metadata yet9platform = Enforcer(catalog)1011# Chapter 5's who-sees-what table, run as queries: what each user gets, against what's stored. A column is "changed"12# when at least one value the user gets differs from the stored one.13agreeing = 014for case in policy.WHO_SEES_WHAT:15 got = observe(platform, case.user, case.table, case.columns, case.purpose)16 who = case.user + (f" for {case.purpose}" if case.purpose else "")17 if got is None:18 print(f"{who}, {case.table}: denied")19 else:20 print(f"{who}, {case.table}: {got['rows']:,} of {got['stored']:,} rows, changed: {', '.join(got['changed']) or 'none'}")21 agreeing += agrees(case, got)22print("cases where what the user gets agrees with the decision:", f"{agreeing} of {len(policy.WHO_SEES_WHAT)}")2324# marketing.audience is a table marketing may read, but nobody has classified its columns, so the policy treats each25# one as sensitive: marketing gets every row, and no value in any column.26rows = platform.sql("select * from marketing.audience", "marketing-1").fetchall()27# a column counts as shown when one of its values isn't None, which is what a redact mask returns28shown = sum(any(value is not None for value in column) for column in zip(*rows))29print("marketing-1, marketing.audience, which has no metadata, rows:", f"{len(rows):,}")30print("marketing-1, marketing.audience, which has no metadata, columns with a value:", f"{shown} of {len(rows[0])}")
python who-sees-what-in-queries.pyagent-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를 두 줄 고치는 변경이며, 지원 행 필터가 필요로 하는 그룹과 지역이다이다. 아래 코드는 이 변경을 메모리에서 적용하고 같은 케이스를 그 위에서 실행한다.
1"""Makes one change to the users file in memory, runs the who-sees-what cases again, and runs chapter 5's decision check2on the same change. The prose's points rest on the lines that say which cases fail, and on the decision count."""3from lib import policy4from lib.catalog import open_catalog5from lib.enforce import Enforcer, agrees, observe67# A pull request changes one user in governance/users.toml, in two lines: analyst-1 joins support, with the region8# support's row filter needs, EMEA, to help with tickets.9# the users file as a document, changed here in memory and not on disk10data = policy.data_document()11data["platform"]["users"]["analyst-1"] = {"groups": ["data-science", "support"], "region": "emea"}12platform = Enforcer(open_catalog(["crm.learners", "learning.reviews", "billing.payments"]),13 rego=policy.interpreter(data=data))1415# The who-sees-what cases, run as queries with the change in place. The cases are unchanged: they say what each16# user should get, and the change was meant to touch support tickets only.17failing = 018for case in policy.WHO_SEES_WHAT:19 got = observe(platform, case.user, case.table, case.columns, case.purpose)20 # got is None when the user is denied, which agrees only with a case that expects a denial21 if not agrees(case, got):22 failing += 123 who = case.user + (f" for {case.purpose}" if case.purpose else "")24 print(f"fails, {who}, {case.table}, rows: {got['rows']:,} of {got['stored']:,}")25 print(f"fails, {who}, {case.table}, columns changed: {', '.join(got['changed']) or 'none'}, "26 f"expected: {', '.join(case.masked)}")27print("cases that fail:", f"{failing} of {len(policy.WHO_SEES_WHAT)}")2829# Chapter 5's table, which checks the decisions themselves, on the same change.30decisions = [policy.decide(policy.request(c.user, c.table, c.columns, c.purpose), platform.rego) for c in policy.WHO_SEES_WHAT]31print("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.pyfails, 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이다. 이 장의 테스트는 분석가가 실제로 무엇을 받는지 보여 준다.
작업 규칙: 정책, 사용자 파일, 메타데이터, 계층이 바뀔 때마다 누가 무엇을 보는지에 대한 케이스를 계층을 거치는 쿼리로 실행하고, 하나라도 일치하지 않으면 빌드를 실패시킨다. 실패한 케이스는 정책이나 사용자 파일을 고쳐서 해결한다. 케이스는 사용자가 받아야 할 결과가 바뀌었다는 데 데이터 소유자들이 동의할 때에만 바꾼다.
이 부분은 아직 번역되지 않았으므로 영어로 표시된다.
Hold back small groups in reports
Row filters and masks decide which rows and values a user sees. Aggregates are a second channel: a count or an average over a few people describes those people. The usual defence is a minimum group size, below which a reported group is held back. The number is a rule of thumb, not a measurement. One public data-use agreement from the US Centers for Medicare & Medicaid Services sets 11, and adds that "no use of percentages or other mathematical formulas may be used if they result in the display of a cell less than 11" (CMS-R-0235L). The platform uses the same minimum, 11, and keeps it in governance/enforcement.toml, where a change is reviewed like a policy change, and lib.enforce.minimum_group_size reads it.
Instructors get a daily course report of learners and average progress per course and learner country, built by a job that reads countries instructors may not read. Instructors may read their own courses' enrollments with each one's progress. Combining those two gives instructor-17's report:
1"""Builds instructor-17's daily course report, counts its cells under the minimum and its cells of one learner, and matches2each cell of one against the enrollments the instructor may read. The prose's points rest on the cell and lookup counts, and3on the last line, which shows the differencing."""4from lib.catalog import open_catalog5from lib.enforce import Enforcer, minimum_group_size67platform = Enforcer(open_catalog(["crm.learners", "learning.courses", "learning.enrollments"]))8minimum = minimum_group_size()9print("minimum group size:", minimum)10count = lambda sql: platform.connection.sql(f"select count(*) from ({sql})").fetchall()[0][0]1112# The platform's daily course report for instructor-17: learners and average progress, per course and learner country.13# The report job reads learners' countries, which instructors may not read.14platform.connection.execute("""create view mine as15 select e.course_id, e.progress_pct, e.enrolled_at::date as day, l.country16 from learning.enrollments e join crm.learners l using (learner_id)17 where e.course_id in (select course_id from learning.courses where instructor_id = 17)""")18platform.connection.execute("""create view report as19 select course_id, country, count(*) as learners, avg(progress_pct) as progress from mine group by all""")20print("cells in the report:", count("from report"))21# the minimum is the threshold: a cell with fewer learners than it is held back22print("cells under the minimum:", count(f"from report where learners < {minimum}"))2324# instructor-17 may read their courses' enrollments, with each one's progress. A cell of one learner, matched on its25# progress, names that learner's country.26# what instructor-17 may read: enrollments with each one's progress, and no country27seen = platform.sql("select course_id, progress_pct from learning.enrollments", "instructor-17").to_arrow_table()28platform.connection.register("seen", seen)29print("cells of one learner:", count("from report where learners = 1"))30print("learners a cell of one names:", count("""from report as cell where learners = 1 and (select count(*) from seen31 where seen.course_id = cell.course_id and seen.progress_pct = cell.progress) = 1"""))32print("cells shown with the minimum:", count(f"from report where learners >= {minimum}"))3334# The minimum doesn't stop differencing. An enrollment that arrives alone in its course on its day, in a cell already35# shown, raises that cell by one in the next day's report, and the instructor sees which enrollment arrived.36# an enrollment alone in its course that day, whose country already has the minimum on earlier days37revealed = count(f"""from mine as m38 where (select count(*) from mine as o where o.course_id = m.course_id and o.day = m.day) = 139 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}""")40print("learners whose country the daily report reveals anyway:", f"{revealed} of {count('from mine')}")
python small-groups-difference.pyminimum 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
A cell of one names its learner's country to anyone who can find that learner, and the instructor can: matching the cell's average progress against their own enrollments names 3 learners exactly. The report has 63 cells, of which 51 are under the minimum and 6 hold one learner each.
With the minimum, 12 cells are shown, and none of those lookups works. The minimum doesn't stop differencing. An enrollment that arrives alone in its course on its day, into a cell that's already shown, raises that cell by one in the next day's report, and the instructor sees which enrollment arrived. That reveals the country of 92 of 482 learners.
Differencing, comparing consecutive reports, is the attack Denning, Denning and Schwartz called a tracker. They showed that "the compromise of small query sets can in fact almost always be accomplished with the help of characteristic formulas called trackers" (Denning et al., 1979).
Snowflake, which enforces a minimum group size in its engine, says its policies "do not guarantee a malicious actor could not use deliberate queries to obtain potentially sensitive data" (Snowflake aggregation policies). Against differencing, we recommend noise, as in differential privacy (Dwork and Roth, 2014), rather than higher thresholds, and Privacy Engineering for Data Teams covers it.
Working rule: Hold back every reported group below the minimum in governance/enforcement.toml, and treat the minimum as protection against looking one person up, never against differencing. Don't break a report down by an attribute its reader may not read, sent to a reader who can see the rows behind it: the instructor's report by country is a lookup table for countries. Drop the breakdown, or report it only after it has been made noisy.
같은 정책을 그 정책을 집행하는 엔진에 넘긴다
운영 환경에서는 집행이 모든 도구가 질의를 보내는 엔진에서 이루어져야 한다. Saltzer와 Schroeder의 완전 중재(complete mediation) 원칙은 "모든 객체에 대한 모든 접근은 권한이 있는지 검사되어야 한다"고 말하며(Saltzer and Schroeder, 1975), 한 도구에서는 정책을 집행하고 다른 도구에서는 집행하지 않으면 이 원칙을 충족하지 못하기 때문이다. 오픈 소스 분산 SQL 엔진인 Trino는 플러그인을 통해 Open Policy Agent(OPA)에서 접근 제어를 받을 수 있다. 5장은 플랫폼의 요청을 이 플러그인의 요청과 같은 형태로 구성했으므로, 같은 정책이 양쪽을 모두 처리할 수 있었다. Trino 484는 파일 기반, OPA, Ranger 접근 제어를 제공하며 Cedar는 제공하지 않는다(Trino 484, Security).
이 플러그인은 서로 다른 URI에서 OPA에 별개의 질문을 한다(Trino 484, OPA access control).
SelectFromColumns같은 작업을 허용할지 여부. 불리언 값allow로 답한다.- 테이블에 대한 행 필터,
GetRowFilters. OPA는{"expression": ...}의 목록으로 답하며, 각 표현식은 "본질적으로 추가적인 WHERE 절처럼 동작한다". 이 URI는 선택 사항이며, 설정하지 않으면 Trino는 행 필터링을 적용하지 않는다. - 한 열에 대한 열 마스크,
GetColumnMask. 또는opa.policy.batch-column-masking-uri에서 여러 열을 한 번에 처리하는 배치 방식도 있다. "하나의 열에는 하나의 열 마스크만 반환할 수 있다."
작업 이름과 각 요청에서 테이블이나 열이 놓이는 위치는 태그 484의 플러그인 소스에 있다(OpaAccessControl.java, OpaHighLevelClient.java). 문서의 배치 마스크 정책은 filterResources[i].catalogName를 읽지만, 같은 문서의 요청 예제는 해당 필드를 .column 아래에 중첩해 두므로, 출력된 그대로는 일치하지 않는다. 이 형태로 만든 요청을 그 정책에 보낸다.
1"""Builds requests in the shape Trino 484's OPA plugin sends, and runs them through the platform's policy. The prose's2points rest on the allow lines, which the example asserts are False, and on the GetRowFilters and GetColumnMask lines."""3import json45from lib import metadata, policy6from lib.enforce import row_filter78# The platform's policy, with a support agent added before anyone set their region (chapter 5's missing-region case).9data = policy.data_document()10data["platform"]["users"]["agent-new"] = {"groups": ["support"]}11rego = policy.interpreter(data=data)12described = metadata.load()131415def trino_request(user: str, operation: str, resource: dict | None = None) -> dict:16 """A request in the shape Trino 484's OPA plugin sends (OpaAccessControl, OpaHighLevelClient)."""17 action = {"operation": operation} | ({"resource": resource} if resource else {})18 return {"context": {"identity": {"user": user, "groups": []}, "softwareStack": {"trinoVersion": "484"}}, "action": action}192021# Trino asks `allow` about every operation: ExecuteQuery before any query runs, AccessCatalog for each catalog it names.22for operation, resource in [("ExecuteQuery", None), ("AccessCatalog", {"catalog": {"name": "platform"}})]:23 allowed = policy.decide(trino_request("agent-a05", operation, resource), rego)["allow"]24 # allow admits only SelectFromColumns, so Trino's checks before any query are denied25 assert allowed is False26 print(f"allow, {operation}, agent-a05:", allowed)2728# GetRowFilters names a table. The policy's row_filters rule on its own skips `allow` and its missing-attribute guard,29# so for agent-new it returns no filter, which Trino reads as no filtering. The answer comes from the decision instead,30# for the same read asked as SelectFromColumns: its filters in Trino's SQL, or a filter that keeps no rows.31for user, schema, table in [("agent-a05", "support", "tickets"), ("instructor-17", "learning", "reviews"),32 ("agent-new", "support", "tickets")]:33 request = trino_request(user, "GetRowFilters", {"table": {"catalogName": "platform", "schemaName": schema, "tableName": table}})34 print(f"GetRowFilters, {user}, {schema}.{table}, the rule alone:",35 json.dumps(policy.evaluate(rego, "data.platform.access.row_filters", request)))36 decision = policy.decide(policy.request(user, f"{schema}.{table}", []), rego)37 columns = {c.name: "" for c in described[f"{schema}.{table}"].columns.values()}38 # no table alias: Trino applies the filter to the table itself, as a WHERE clause on it39 filters = [{"expression": row_filter(rule, columns, table=None).sql("trino")} for rule in decision["row_filters"]]40 # a denied read gets a filter that keeps no rows41 print(f"GetRowFilters, {user}, {schema}.{table}, answered:", json.dumps(filters if decision["allow"] else [{"expression": "false"}]))4243# GetColumnMask names one column, under action.resource.column. The policy reads the columns of a SelectFromColumns44# request instead, so it masks nothing here until the request is translated.45column = {"catalogName": "platform", "schemaName": "crm", "tableName": "learners", "columnName": "email", "columnType": "varchar"}46decision = policy.decide(trino_request("analyst-1", "GetColumnMask", {"column": column}), rego)47print("GetColumnMask as sent, analyst-1, crm.learners.email:", json.dumps(decision["column_masks"]), decision["reasons"][0])48translated = policy.request("analyst-1", "crm.learners", [column["columnName"]])49print("GetColumnMask translated, analyst-1, crm.learners.email:", json.dumps(policy.decide(translated, rego)["column_masks"]))
python trino-requests.pyallow, 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"}
이 정책을 변경 없이 Trino에 연결하면, Trino가 가장 먼저 수행하는 검사들을 거부하게 된다. 정책의 allow가 SelectFromColumns만 허용하기 때문이다. allow는 어떤 질의가 실행되기 전에 Trino가 묻는 ExecuteQuery에 대해서도, 그리고 AccessCatalog에 대해서도 false다.
row_filters 규칙은 작업을 검사하지 않으므로 GetRowFilters에 답하지만, 이 규칙만으로는 allow와 5장의 누락 속성 방어 장치를 건너뛴다. 아직 지역이 없는 지원 에이전트인 agent-new에 대해서는 []를 반환하며, Trino는 이를 필터링 없음으로 해석한다. 따라서 답은 같은 읽기에 대한 결정에서 나와야 하며, 그 읽기는 SelectFromColumns로 묻는다. 그 결정의 필터는 재작성에 쓰이는 것과 같은 row_filter가 Trino의 SQL로 작성하며, 읽기를 거부하는 경우에는 어떤 행도 남기지 않는 [{"expression": "false"}]를 반환한다.
GetColumnMask는 열을 action.resource.column 아래에 명시하는 반면, 정책은 SelectFromColumns 요청의 열을 읽는다. 따라서 보낸 그대로는 아무것도 마스킹하지 못하고 정책이 그 작업을 거부한다. 변환하면 pseudonymize를 얻는다. 운영 환경에서는 이러한 변환, allow 검사, Trino SQL이 정책 옆의 Rego 모듈에 있어야 OPA가 Trino에 직접 답할 수 있다. 여기서는 OPA 서버가 실행되지 않으므로 코드는 그 모듈이 반환해야 하는 내용만 보여 준다. 한 가지 요소는 Trino에 곧바로 대응한다. 동의 필터는 어시스턴트가 읽을 수 없는 crm.learners를 읽는데, Trino에서는 필터가 실행 주체로 사용할 identity를 지정할 수 있어 "필터가 요청한 사용자에게 보이지 않는 열을 대상으로 삼을 수 있다".
1.9.0부터 OPA의 Compile API는 부분 평가를 통해 정책에서 SQL WHERE 절을 작성할 수도 있다(OPA v1.9.0, Data filtering with OPA). 다만 OPA 서버가 필요하며, 예제는 이를 실행하지 않는다.
작업 규칙: 집행은 모든 도구가 질의를 보내는 엔진에 두고, 그 엔진과 이 장의 재작성 같은 모든 재작성에 하나의 정책을 사용한다. Trino에서는 플러그인이 묻는 모든 작업에 답하고, 행 필터 URI와 열 마스크 URI를 모두 설정하며(설정하지 않으면 필터나 마스크가 적용되지 않는다), 행 필터는 읽기를 허용하는 결정에서만 반환한다. 테스트는 문서의 예제 정책이 아니라 플러그인 소스의 요청 형태로 만든다.
이 부분은 아직 번역되지 않았으므로 영어로 표시된다.
Query on someone's behalf
An assistant that answers questions from the warehouse is one more user of the layer, with a choice to make: whose identity does its query carry? The course assistant, assistant-1, has its own access, for reviews by consenting learners. When it answers a person, it must query as that person, so the answer holds exactly what they could read themselves. OWASP's guidance for language-model applications says the same: "Implement authorization in downstream systems rather than relying on an LLM to decide if an action is allowed or not" (OWASP LLM06:2025). The assistant itself belongs to Data Assistants in Practice; this section is about its queries.
instructor-17 asks the assistant how their courses' reviews look, and the assistant writes a query. The tool takes the user from the signed-in session, never from the model or the conversation, so no prompt can change who the query runs as:
1"""Runs the course assistant's review query as instructor-17 and as assistant-1, through a tool that takes the user from2the signed-in session. The prose's point rests on the reviews-behind-it lines for each user."""3from lib import policy4from lib.catalog import open_catalog5from lib.enforce import Enforcer67platform = Enforcer(open_catalog(["crm.learners", "learning.reviews"]))8courses = set(policy.data_document()["platform"]["instructor_courses"]["17"]) # instructor-17's own courses91011def query_tool(session_user: str):12 """The tool the course assistant calls to query the warehouse. The model writes the SQL; the user it runs as comes13 from the signed-in session, never from the model or the conversation."""14 # the user is the session's; the SQL is all the model contributes15 return lambda sql: platform.sql(sql, user=session_user).fetchall()161718# instructor-17 asks the assistant how their courses' reviews look. The assistant writes this query.19question = "select course_id, count(*) as reviews, round(avg(rating), 2) as rating from learning.reviews group by all"20for runs_as in ["instructor-17", "assistant-1"]:21 answer = query_tool(runs_as)(question)22 print(f"run as {runs_as}, courses in the answer:", len(answer))23 print(f"run as {runs_as}, reviews behind it:", sum(reviews for _, reviews, _ in answer))24 # each answer row is (course, reviews, rating), so row[0] is the course25 mine = [row for row in answer if row[0] in courses]26 print(f"run as {runs_as}, instructor-17's reviews behind it:", sum(reviews for _, reviews, _ in mine))
python assistant-on-behalf.pyrun 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
Whose identity the query carries decides what the answer covers. The assistant's own identity is wrong both ways: it shows the instructor what they may not see, and hides what they may. Run as instructor-17, the answer covers their 6 courses and 69 reviews. Run as the assistant, it covers 130 courses, most of them other instructors', and only 7 of the instructor's own reviews, the ones whose authors consented to AI training. The same holds for retrieval: an assistant grounded on governed text needs "permission-aware vector and embedding stores" (OWASP LLM08:2025).
Working rule: Run an assistant's queries as the person it answers, with that identity taken from their authenticated session, and let the layer apply that person's decision. Use the assistant's own identity only for work it does for no one in particular, such as a nightly summary of consented reviews for the course pages, and record both identities in the audit log. Whoever builds the assistant's training set queries as themselves, with the purpose ai-training, as chapter 9 does.
이 부분은 아직 번역되지 않았으므로 영어로 표시된다.
What goes wrong in production
An enforcement layer fails by letting through a read nobody tested, and most of these failures look like correct answers:
SELECT *slips past on a table missing from the schema.qualifyleaves the star unexpanded and raises nothing. Deny every table the catalog doesn't hold, and rebuild the schema whenever the catalog changes.- Aggregates leak small groups. A cell of one names its member, and differences between reports name members of cells that pass any minimum, as the report example showed. Set the minimum, and remove breakdowns by attributes the reader may not read.
- Joins and copies bring masked columns back. A pipeline with full access, such as the one that writes
marketing.audience, copiesemailinto a table protected only by its own classification. Unclassified, the copy is hidden from everyone, as the who-sees-what test showed; classifiedinternalby mistake, it's shown to anyone who may read the table. Chapter 4 propagates classifications along lineage to catch that. Unmasked columns re-identify through joins too: Sweeney found 87% of the US population likely unique on ZIP code, gender and date of birth in 1990 census data (Sweeney, 2002), and Golle, counting again, found 61% on the same 1990 data and 63% on 2000's (Golle, 2006). Rocher and colleagues estimate, from a model fitted to census data, that 15 demographic attributes would make 99.98% of people in Massachusetts unique (Rocher et al., 2019). - Direct file reads bypass the rewrite.
read_parquet, a path inFROMand table functions read data without naming a table. Deny them in the rewrite, and turn offenable_external_access, which DuckDB then won't turn back on: "Cannot enable external access while database is running".lock_configurationkeeps every other setting as the layer left it; set it once the layer's connection is fully configured (Securing DuckDB), not when it's created, since chapter 8 starts DuckDB's query log after that. Anyone who can read the warehouse's files in storage needs no SQL, so storage permissions are part of enforcement. - Identifier case decides what a name matches. A layer that matched names without normalizing them would let
"LEARNERS", which DuckDB resolves tolearners, reach the raw table, as the first section showed. Normalize with the dialect of the engine that runs the query before matching any name. - Error messages print values. DuckDB's cast errors include the value. The table rewrite makes it a masked one, and the gate on each column keeps dropped rows out of reach, but the message still reaches the user unless something stops it. Return a generic error at the service boundary, as
lib.enforce.answerdoes, and log the engine's message where only the platform team reads it, apart from the audit log, which chapter 8 keeps free of values. PostgreSQL warns that plans and timings remain covert channels even behind a security barrier (PostgreSQL 18, Rules and Privileges). - DuckDB is not a security boundary. "DuckDB executes SQL with the full privileges of the user running it, much like a shell or scripting interpreter" (Securing DuckDB). A rewrite inside the same process protects data only while users can't reach DuckDB by any other route. Treat this chapter's layer as a tested model of what an engine such as Trino enforces, and enforce there in production.
이 장에서 결정한 것
- 집행은 열이 아니라 테이블을 대체한다. 각 카탈로그 테이블은 결정에서 생성한 서브쿼리가 된다. 열 참조를 다시 쓰는 방식은 정보가 새어 나간다는 점을 보이기 위해서만 남겨 둔다.
- 계층은 닫힌 채로 실패한다. 이 계층이 허용하는 소스는 카탈로그 테이블의 허용 목록, 범위 안의 CTE, 서브쿼리이다. 부작용이 있는 함수, 매개변수, 단일 쿼리가 아닌 모든 것은 거부한다. 쿼리가 읽지 않는 열은
NULL값으로 형을 지정하고, 읽는 열은 행 필터를 통과해야 한다. - 사용자에게는
answer에서 나온 일반적인 오류 하나만 보여 주고, DuckDB의 메시지는 플랫폼 팀에 전달한다. - 마스크는 한 곳에서 SQL로 만든다. 정책은 마스크의 이름만 정하고,
lib.enforce가 그 SQL을 작성한다. 가명은 계층만 가진 비밀 값을 키로 삼아 만들며, 이 값은 매개변수로 전달한다. - 행 필터는 데이터로 만들며, 값은 리터럴로 넣는다. 동의는 계층이 자신의 접근 권한으로 실행하는 세미 조인이고, 알 수 없는 동의는 거부로 센다.
- 누가 무엇을 보는지는 쿼리로 테스트한다. 5장의 사례를 변경이 있을 때마다 계층을 통해 실행한다.
- 보고서는 최소 집단 크기 미만의 집단을 제외하며, 그 값은
governance/enforcement.toml에 있는 11이다. 또한 독자가 읽을 수 없는 속성으로는 세분하지 않는다. - 어시스턴트는 답하는 상대 본인의 자격으로 쿼리한다. 신원은 세션에서 가져온다.
- 운영 환경에서는 엔진이 집행한다. 같은 정책에서 만든 OPA와 함께 Trino를 사용하고, 두 URI를 모두 설정하며, 행 필터는 허용된 결정에서만 응답한다. 계층의 안전성은 그 테스트의 수준을 넘지 못한다.
이 판의 이 장
2026.11.31판예제 통과 2026년 11월 19이 장에서 16개 예제개 중 16개가 통과한다
영어판 2026.11.31판을 번역한 것이다.