LINE1 10 20 30 40 50 60 70 80 90 100 110 1201//! Decides whether a piece of SQL may run against the lake. DuckDB's own parser2//! turns the text into a JSON AST, and the walk below only accepts a single3//! SELECT that reads base tables from the `lake` catalog. Table functions4//! (read_csv, glob, query, ...) are rejected wholesale, and a few scalar functions5//! that expose process state are denied by name.6//!7//! This is the first layer. The session also disables local file access and8//! locks the configuration, so a missed case still cannot read the host.910const std = @import("std");11const duckdb = @import("duckdb.zig");1213const allowed_schema = "lake";14const denied_functions = [_][]const u8{ "getenv", "current_setting", "query", "query_table" };1516/// Returns null when `sql` is one read-only SELECT over the lake. Otherwise returns a17/// message that is fine to show to the user and to the model. Strings live in `allocator`.18pub fn check(allocator: std.mem.Allocator, connection: *duckdb.Connection, sql: []const u8) !?[]const u8 {19 var result = try connection.queryWithText("SELECT json_serialize_sql(?)", sql);20 defer result.deinit();21 if (!result.ok) return "could not parse the query";2223 const json_text = (try result.valueText(allocator, 0, 0)) orelse return "could not parse the query";24 const root = try std.json.parseFromSliceLeaky(std.json.Value, allocator, json_text, .{});25 const root_object = switch (root) {26 .object => |object| object,27 else => return "could not parse the query",28 };2930 if (boolField(root_object, "error") orelse true) {31 return stringField(root_object, "error_message") orelse "syntax error";32 }3334 const statements = switch (root_object.get("statements") orelse return "could not parse the query") {35 .array => |array| array.items,36 else => return "could not parse the query",37 };38 if (statements.len != 1) return "exactly one SQL statement is allowed";3940 const statement = switch (statements[0]) {41 .object => |object| object,42 else => return "could not parse the query",43 };44 const node = switch (statement.get("node") orelse return "could not parse the query") {45 .object => |object| object,46 else => return "could not parse the query",47 };48 const node_type = stringField(node, "type") orelse "";49 if (!std.mem.eql(u8, node_type, "SELECT_NODE") and !std.mem.eql(u8, node_type, "SET_OPERATION_NODE")) {50 return "only SELECT queries are allowed";51 }5253 if (findRejection(root)) |reason| return reason;54 if (findUnboundedMatchScan(root)) |reason| return reason;55 return null;56}5758const match_table_message = "lake.match_player holds the whole match history (hundreds of GB); " ++59 "add a start_time or match_id filter, for example start_time > now() - INTERVAL 1 DAY";60const match_view_message = "match_player_latest reads the whole match history; " ++61 "add a start_time or match_id filter, or query lake.match_player with a recent start_time window";6263/// The match tables are hundreds of GB and spread over remote parquet files. A scan64/// without a start_time/match_id filter makes DuckDB download everything, even for a65/// LIMIT; the watchdog cannot interrupt those HTTP reads fast enough to save the66/// request. Reject the shape with advice the model can follow.67fn findUnboundedMatchScan(value: std.json.Value) ?[]const u8 {68 switch (value) {69 .array => |array| {70 for (array.items) |item| {71 if (findUnboundedMatchScan(item)) |reason| return reason;72 }73 },74 .object => |object| {75 if (stringField(object, "type")) |node_type| {76 if (std.mem.eql(u8, node_type, "SELECT_NODE") and !nodeHasRangeFilter(object)) {77 if (fromReferencesTable(object, "match_player_latest")) return match_view_message;78 if (fromReferencesTable(object, "match_player") and !isBoundedSelect(object)) return match_table_message;79 }80 }81 var iterator = object.iterator();82 while (iterator.next()) |entry| {83 if (findUnboundedMatchScan(entry.value_ptr.*)) |reason| return reason;84 }85 },86 else => {},87 }88 return null;89}9091fn fromReferencesTable(object: std.json.ObjectMap, table: []const u8) bool {92 const from = object.get("from_table") orelse return false;93 return referencesTable(from, table);94}9596fn nodeHasRangeFilter(object: std.json.ObjectMap) bool {97 for ([_][]const u8{ "where_clause", "having", "qualify" }) |key| {98 const value = object.get(key) orelse continue;99 if (referencesRangeColumn(value)) return true;100 }101 return false;102}103104/// Only a bare aggregate is safe on the whole table; DuckLake answers those from105/// file metadata. LIMIT does not help because DuckDB still opens every data file.106fn isBoundedSelect(object: std.json.ObjectMap) bool {107 if (arrayLen(object.get("group_expressions")) != 0) return false;108 if (arrayLen(object.get("group_sets")) != 0) return false;109 // GROUP BY ALL serializes as an empty group list plus FORCE_AGGREGATES.110 if (stringField(object, "aggregate_handling")) |handling| {111 if (std.mem.eql(u8, handling, "FORCE_AGGREGATES")) return false;112 }113 for ([_][]const u8{ "where_clause", "having", "qualify" }) |key| {114 switch (object.get(key) orelse .null) {115 .null => {},116 else => return false,117 }118 }119120 if (object.get("modifiers")) |modifiers| {121 switch (modifiers) {122 .array => |array| {123 for (array.items) |modifier| {124 const modifier_object = switch (modifier) {125 .object => |value| value,126 else => continue,127 };128 const modifier_type = stringField(modifier_object, "type") orelse continue;129 if (std.mem.eql(u8, modifier_type, "DISTINCT_MODIFIER") or130 std.mem.eql(u8, modifier_type, "ORDER_MODIFIER"))131 {132 // DISTINCT and ORDER BY need every row before they can answer.133 return false;134 }135 }136 },137 else => {},138 }139 }140141 return aggregateOnlySelect(object);142}143144fn aggregateOnlySelect(object: std.json.ObjectMap) bool {145 const select_list = switch (object.get("select_list") orelse return false) {146 .array => |array| array.items,147 else => return false,148 };149 if (select_list.len == 0) return false;150 for (select_list) |item| {151 if (!isAggregateFunction(item)) return false;152 }153 return true;154}155156fn isAggregateFunction(value: std.json.Value) bool {157 const object = switch (value) {158 .object => |object| object,159 else => return false,160 };161 if (!std.mem.eql(u8, stringField(object, "type") orelse return false, "FUNCTION")) return false;162 switch (object.get("distinct") orelse std.json.Value{ .bool = false }) {163 .bool => |flag| if (flag) return false,164 else => {},165 }166 switch (object.get("filter") orelse .null) {167 .null => {},168 else => return false,169 }170 const name = stringField(object, "function_name") orelse return false;171 // Only these are answered from DuckLake file metadata without reading rows.172 const aggregates = [_][]const u8{ "count_star", "min", "max" };173 for (aggregates) |aggregate| {174 if (std.ascii.eqlIgnoreCase(name, aggregate)) return true;175 }176 return false;177}178179fn arrayLen(value: ?std.json.Value) usize {180 const actual = value orelse return 0;181 return switch (actual) {182 .array => |array| array.items.len,183 else => 0,184 };185}186187fn referencesTable(value: std.json.Value, table: []const u8) bool {188 switch (value) {189 .array => |array| {190 for (array.items) |item| {191 if (referencesTable(item, table)) return true;192 }193 },194 .object => |object| {195 if (stringField(object, "type")) |node_type| {196 if (std.mem.eql(u8, node_type, "BASE_TABLE")) {197 if (stringField(object, "table_name")) |name| {198 if (std.mem.eql(u8, name, table)) return true;199 }200 }201 }202 var iterator = object.iterator();203 while (iterator.next()) |entry| {204 if (referencesTable(entry.value_ptr.*, table)) return true;205 }206 },207 else => {},208 }209 return false;210}211212fn referencesRangeColumn(value: std.json.Value) bool {213 switch (value) {214 .array => |array| {215 for (array.items) |item| {216 if (referencesRangeColumn(item)) return true;217 }218 },219 .object => |object| {220 if (stringField(object, "type")) |node_type| {221 if (std.mem.eql(u8, node_type, "COLUMN_REF")) {222 const names = switch (object.get("column_names") orelse return false) {223 .array => |array| array.items,224 else => return false,225 };226 if (names.len == 0) return false;227 const column = switch (names[names.len - 1]) {228 .string => |text| text,229 else => return false,230 };231 if (std.mem.eql(u8, column, "start_time") or std.mem.eql(u8, column, "match_id")) return true;232 }233 }234 var iterator = object.iterator();235 while (iterator.next()) |entry| {236 if (referencesRangeColumn(entry.value_ptr.*)) return true;237 }238 },239 else => {},240 }241 return false;242}243244fn findRejection(value: std.json.Value) ?[]const u8 {245 switch (value) {246 .array => |array| {247 for (array.items) |item| {248 if (findRejection(item)) |reason| return reason;249 }250 },251 .object => |object| {252 if (stringField(object, "type")) |node_type| {253 if (std.mem.eql(u8, node_type, "TABLE_FUNCTION")) {254 return "table functions are not allowed; query lake.<table> directly";255 }256 if (std.mem.eql(u8, node_type, "BASE_TABLE")) {257 const catalog = stringField(object, "catalog_name") orelse "";258 const schema = stringField(object, "schema_name") orelse "";259 const table = stringField(object, "table_name") orelse "";260 if (catalog.len != 0 or (schema.len != 0 and !std.mem.eql(u8, schema, allowed_schema))) {261 return "only tables in the lake catalog can be queried";262 }263 // Unqualified names are CTE references, or replacement scans of a path or URL264 // such as FROM 'https://...'. Only plain identifiers are allowed here.265 if (schema.len == 0 and !isIdentifier(table)) {266 return "table references must be lake.<table> or a CTE name";267 }268 }269 }270 if (stringField(object, "function_name")) |name| {271 for (denied_functions) |denied| {272 if (std.ascii.eqlIgnoreCase(name, denied)) return "that function is not allowed";273 }274 }275 var iterator = object.iterator();276 while (iterator.next()) |entry| {277 if (findRejection(entry.value_ptr.*)) |reason| return reason;278 }279 },280 else => {},281 }282 return null;283}284285fn isIdentifier(text: []const u8) bool {286 if (text.len == 0) return false;287 for (text) |char| {288 if (!std.ascii.isAlphanumeric(char) and char != '_') return false;289 }290 return true;291}292293fn stringField(object: std.json.ObjectMap, key: []const u8) ?[]const u8 {294 return switch (object.get(key) orelse return null) {295 .string => |text| text,296 else => null,297 };298}299300fn boolField(object: std.json.ObjectMap, key: []const u8) ?bool {301 return switch (object.get(key) orelse return null) {302 .bool => |flag| flag,303 else => null,304 };305}306307fn expectAccepted(sql: []const u8) !void {308 var arena = std.heap.ArenaAllocator.init(std.testing.allocator);309 defer arena.deinit();310 var db = try duckdb.Database.openMemory("/tmp", "/tmp");311 defer db.close();312 var connection = try duckdb.Connection.connect(&db);313 defer connection.disconnect();314 const reason = try check(arena.allocator(), &connection, sql);315 if (reason) |text| {316 std.debug.print("expected accept for {s}, got: {s}\n", .{ sql, text });317 return error.TestUnexpectedResult;318 }319}320321fn expectRejected(sql: []const u8) !void {322 var arena = std.heap.ArenaAllocator.init(std.testing.allocator);323 defer arena.deinit();324 var db = try duckdb.Database.openMemory("/tmp", "/tmp");325 defer db.close();326 var connection = try duckdb.Connection.connect(&db);327 defer connection.disconnect();328 if (try check(arena.allocator(), &connection, sql) == null) {329 std.debug.print("expected rejection for {s}\n", .{sql});330 return error.TestUnexpectedResult;331 }332}333334test "accepts single read-only selects over the lake" {335 try expectAccepted("SELECT 1");336 try expectAccepted("WITH t AS (SELECT 1 AS x) SELECT x FROM t");337 try expectAccepted("SELECT hero_id, count(*) FROM lake.match_player WHERE start_time > now() - INTERVAL 1 DAY GROUP BY ALL");338 try expectAccepted("SELECT * FROM lake.leaderboard UNION ALL SELECT * FROM lake.leaderboard");339}340341test "rejects statements that are not a single select" {342 try expectRejected("DROP TABLE lake.leaderboard");343 try expectRejected("SELECT 1; SELECT 2");344 try expectRejected("INSERT INTO lake.leaderboard SELECT * FROM lake.leaderboard");345 try expectRejected("ATTACH '/tmp/x.duckdb' AS x");346 try expectRejected("SET enable_external_access = true");347 try expectRejected("SELECT 1 FROM");348}349350test "rejects table functions and tables outside the lake" {351 try expectRejected("SELECT * FROM read_csv('/etc/passwd')");352 try expectRejected("SELECT * FROM 'https://example.com/x.csv'");353 try expectRejected("SELECT * FROM 'x.csv'");354 try expectRejected("SELECT * FROM information_schema.tables");355 try expectRejected("SELECT * FROM memory.main.x");356 try expectRejected("SELECT * FROM __ducklake_metadata_lake.ducklake_snapshot");357}358359test "rejects denied scalar functions" {360 try expectRejected("SELECT getenv('ANTHROPIC_API_KEY')");361 try expectRejected("SELECT current_setting('home_directory')");362}363364test "requires a range filter on the full-history match view" {365 try expectRejected("SELECT * FROM lake.match_player_latest");366 try expectRejected("SELECT * FROM lake.match_player_latest LIMIT 3");367 try expectRejected("SELECT * FROM lake.match_player_latest WHERE account_id = 42");368 try expectAccepted("SELECT * FROM lake.match_player_latest WHERE start_time > now() - INTERVAL 1 DAY");369 try expectAccepted("SELECT * FROM lake.match_player_latest WHERE match_id = 42");370}371372test "rejects broad scans of the huge match table but allows cheap shapes" {373 try expectRejected("SELECT hero_id, count(*) FROM lake.match_player GROUP BY ALL");374 try expectRejected("SELECT hero_id, count(*) FROM lake.match_player GROUP BY ALL LIMIT 10");375 try expectRejected("SELECT DISTINCT hero_id FROM lake.match_player");376 try expectRejected("SELECT * FROM lake.match_player WHERE account_id = 42");377 try expectRejected("SELECT * FROM lake.match_player ORDER BY start_time DESC LIMIT 20");378 try expectRejected("SELECT * FROM lake.match_player LIMIT 3");379 try expectRejected("SELECT sum(kills) FROM lake.match_player");380 try expectRejected("SELECT count(*) FILTER (WHERE won) FROM lake.match_player");381 try expectRejected("SELECT count(*) FROM lake.match_player WHERE won");382 try expectAccepted("SELECT hero_id, count(*) FROM lake.match_player WHERE start_time > now() - INTERVAL 1 DAY GROUP BY ALL");383 try expectAccepted("SELECT count(*) FROM lake.match_player");384 try expectAccepted("SELECT min(start_time), max(start_time) FROM lake.match_player");385 try expectAccepted("SELECT * FROM lake.match_player WHERE start_time > now() - INTERVAL 1 DAY LIMIT 3");386}