go: replace custom table parser

[derekperkins]

Right now, there's custom introspection code in the Go server that pulls out table references for multi tenancy checking. The returned json isn't well typed, and isn't guaranteed to be stable. Since I wrote it, there are a couple extensions available that do everything needed for table and function inclusion/exclusion. This should be more future proof and reliable than the current hand rolled parser.

• https://duckdb.org/community_extensions/extensions/parser_tools / https://github.com/hotdata-dev/duckdb_extension_parser_tools
• https://duckdb.org/community_extensions/extensions/poached / https://github.com/sidequery/poached

I can make it so that whichever extension is chosen is required only if opting into multi-tenancy mode.

[danielbodart]

Research Findings: Extension Evaluation for Replacing Custom Table Parser

I've done extensive research evaluating options for replacing the custom json_serialize_sql AST-walking approach.

Extension Comparison

Aspect	parser_tools	poached
DuckDB v1.4.4	✅ Available	❌ HTTP 404 (not yet built)
DuckDB v1.4.3	✅ Available	✅ Available
GitHub Stars	23	7
Forks	6	0
CTE Handling	✅ Excellent - cte, from_cte contexts	❌ Critical bug (see below)
Function Context	✅ select, where, having, nested	❌ Only function_type (scalar/aggregate)
exclude_cte param	✅ Yes	❌ No

Critical Security Issue with poached

poached has a bug that makes it unsuitable for security validation. When a CTE is present, it fails to return table references:

-- poached returns 0 rows for this query (SECURITY BYPASS!)
SELECT * FROM parse_tables('WITH dummy AS (SELECT 1) SELECT * FROM allowed.users, forbidden.secrets');
┌─────────────┬────────────┬─────────┐
│ schema_name │ table_name │ context │
├─────────────┴────────────┴─────────┤
│              0 rows                │
└────────────────────────────────────┘

-- parser_tools correctly returns all 3 references
SELECT * FROM parse_tables('WITH dummy AS (SELECT 1) SELECT * FROM allowed.users, forbidden.secrets');
┌──────────────────┬─────────┬────────────┐
│      schema      │  table  │  context   │
├──────────────────┼─────────┼────────────┤
│                  │ dummy   │ cte        │
│ allowed          │ users   │ from       │
│ forbidden        │ secrets │ join_right │
└──────────────────┴─────────┴────────────┘

An attacker could completely bypass schema validation by adding a trivial CTE like WITH x AS (SELECT 1) to any query.

Recommendation: parser_tools

parser_tools is the clear choice due to:

1. Available for latest DuckDB (v1.4.4)
2. No CTE security bug
3. Richer context info (where functions are used)
4. More mature (3x stars, active development)
5. exclude_cte parameter for easy CTE filtering

Statement Type Support

Both parser_tools AND the current json_serialize_sql only support SELECT statements:

Statement Type	parser_tools parse_tables()	Current json_serialize_sql
SELECT * FROM schema.t	✅ Works	✅ Works
CREATE TABLE schema.t AS SELECT...	❌ 0 rows	❌ "Only SELECT statements can be serialized"
INSERT INTO schema.t SELECT...	❌ 0 rows	❌ Same error
UPDATE schema.t SET...	❌ 0 rows	❌ Same error
DELETE FROM schema.t	❌ 0 rows	❌ Same error

However, parser_tools provides parse_statements() which can be combined with regex to validate ALL statement types:

-- Extract all schema references from ANY statement type
SELECT 
    statement,
    list_distinct(
        regexp_extract_all(statement, '(?:FROM|JOIN|INTO|UPDATE|TABLE|VIEW)\s+([a-zA-Z_][a-zA-Z0-9_]*)\.', 1)
    ) as schemas_referenced
FROM parse_statements('
    INSERT INTO allowed.target SELECT * FROM forbidden.source JOIN another.tbl;
    CREATE TABLE new_schema.t AS SELECT * FROM old_schema.s
');

┌─────────────────────────────────────────────────────┬───────────────────────────────┐
│                      statement                      │      schemas_referenced       │
├─────────────────────────────────────────────────────┼───────────────────────────────┤
│ INSERT INTO allowed."target" SELECT * FROM forbidd… │ [another, forbidden, allowed] │
│ CREATE TABLE new_schema.t AS SELECT * FROM old_sch… │ [old_schema, new_schema]      │
└─────────────────────────────────────────────────────┴───────────────────────────────┘

This provides more comprehensive validation than the current implementation.

Note: Mosaic's Actual Usage Pattern

The typical Mosaic workflow uses CREATE TABLE schema.mytable AS SELECT * FROM read_parquet(...). The destination schema in CREATE TABLE statements is not currently validated - this is a pre-existing gap, not something introduced by parser_tools.

Additional Finding: Function Blocklist Not Wired Up (as previously discussed)

The current --function-blocklist CLI flag is parsed and stored in options, but functionBlocklistValidator is never actually used in WriteJSON/WriteArrow. This should be fixed as part of this work.

Other Extensions Evaluated

Searched all 150+ DuckDB community extensions. No other extensions provide SQL parsing for table extraction:

• substrait - Query plan conversion, not extraction
• prql/psql - Alternative query languages
• sazgar - Uses SQLGlot internally for routing, doesn't expose parsing

External alternatives like sqlglot (Python) provide full DuckDB dialect support but could not find a Go alternative

Sources

• parser_tools extension
• parser_tools GitHub
• poached extension
• poached GitHub

[danielbodart]

Investigation Update: parser_tools Extension

I tried using the parser_tools DuckDB community extension as a replacement for the custom SQL table parser.

Summary

Unfortunately, parser_tools is not suitable for multi-tenant security validation because it doesn't traverse into subqueries.

The Problem

-- This query references two tables: schema1.users and schema2.orders
SELECT * FROM schema1.users WHERE id IN (SELECT user_id FROM schema2.orders);

SELECT * FROM parse_tables('SELECT * FROM schema1.users WHERE id IN (SELECT user_id FROM schema2.orders)');

Expected: Both schema1.users and schema2.orders are returned
Actual: Only schema1.users is returned - the subquery table is missed

This would allow a malicious user to bypass schema restrictions by hiding unauthorized table access in subqueries.

Filed Issue

I've filed an issue with the parser_tools maintainer: hotdata-dev/duckdb_extension_parser_tools#10

Recommendation

Until parser_tools adds subquery support, the current json_serialize_sql() + AST walking approach remains the most reliable option for comprehensive security validation. The maintainer seems responsive, so this may be fixed in the future.

---

Note: The duckdb_arrow build tag is now required for Arrow support in duckdb-go v2.5.5+ (see PR #98)

[derekperkins]

I appreciate the thorough investigation and upstream issue creation. It's nice to know that our use cases aren't fully supported before going down the implementation rabbit hole.

[danielbodart]

Okay great news parser_tools creator is extremely responsive and has fixed the issue I raised (within an hour!) and is aiming to merge upstream to DuckDB in the next day or two. I actually had a nearly working PR using parser_tools so I will reboot this.

One question I had about this change:

json_serialize_sql() is directly supported by the DuckDB team and is not going to go any where and so will always be updated with every release and potential changes to the parser (which I believe is under active refactoring) will always be handled. Using parser_tools is a community extension (that does appears very well maintained) but this does create a slight potential for a delay in support for this Go server. I think the advantage here is that

1. It will be more performant as you don't need to pass a big blob of JSON back
2. Less logic in the go server (and simpler)

Anything else?

Downside being it's a non official extension so there some risk there.

Overall I think it is the correct direction but wanted to understand if there were other benefits you had in mind.