Skip to content
1        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}