Skip to content

Commit 5a69583

Browse files
anandgupta42claude
andcommitted
fix(analyst): deny PostgreSQL control, large-object, dblink and checkpoint function classes structurally in read-only sql_check sources
The function denylist grew one name per review round (`pg_log_standby_snapshot`, `lo_import_with_oid`, `lo_creat`, DuckDB `checkpoint`/`force_checkpoint` were the latest escapes). It is replaced by a structural rule: every `pg_*` call is refused unless it is a known pure read (type/size/catalog inspection, `pg_get_*`, stat getters other than `pg_stat_reset*`, LSN and snapshot reads, privilege checks); every `lo_*`, `lowrite`/`loread`, `dblink*`, `checkpoint`/`force_checkpoint`, sequence, config and file-write call is refused. A future server-control function is therefore denied by default. tests: class-denied cases (lo_import_with_oid, lo_creat, pg_log_standby_snapshot, pg_read_file, pg_sleep, checkpoint, force_checkpoint, an invented future pg_ function) rejected before execution in predicates and populations; eight pure reads (pg_typeof, pg_size_pretty, pg_column_size, pg_get_expr, pg_stat_get_numscans, pg_relation_size, pg_current_wal_lsn, pg_has_role) still execute; all earlier denylist and literal/comment/identifier cases unchanged. Co-Authored-By: Claude Fable 5.1 <[email protected]>
1 parent c9cfe17 commit 5a69583

2 files changed

Lines changed: 57 additions & 1 deletion

File tree

‎packages/opencode/src/altimate/tools/sql-check.ts‎

Lines changed: 22 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -6,6 +6,27 @@ import * as Registry from "../native/connections/registry"
66
import { classifyAndCheck, isSingleQuery } from "./sql-classify"
77
import { maskSql, tokenizeSql } from "./sql-tokenize"
88

9+
// Function calls that can change state outside the read-only transaction. PostgreSQL administrative,
10+
// WAL, replication, statistics-reset, large-object, file and dblink functions are denied as whole classes:
11+
// every `pg_*` call is refused unless it is a known pure read (type/size/catalog inspection, stat getters,
12+
// LSN and snapshot reads), so a new server-control function cannot slip through one name at a time.
13+
const PURE_PG_FUNCTIONS =
14+
"typeof|size_pretty|column_size|\\w*_size|backend_pid|is_in_recovery|get_\\w*|describe_object|identify_object\\w*|" +
15+
"input_is_valid|trigger_depth|has_role|\\w*_is_visible|current_\\w*_lsn|last_\\w*_lsn|wal_lsn_diff|walfile_name\\w*|" +
16+
"split_walfile_name|postmaster_start_time|conf_load_time|my_temp_schema|is_other_temp_schema|jit_available|" +
17+
"collation_for|tablespace_\\w*|relation_filepath|relation_filenode|filenode_relation|char_to_encoding|" +
18+
"encoding_to_char|client_encoding|column_compression|xact_commit_timestamp\\w*|current_xact_id\\w*|current_snapshot|" +
19+
"snapshot_\\w*|visible_in_snapshot|notification_queue_usage|listening_channels|blocking_pids|" +
20+
"safe_snapshot_blocking_pids|isolation_test_session_is_blocked|stat_(?!reset)\\w*|statio_\\w*|mcv_list_\\w*|" +
21+
"index\\w*_has_property|options_to_table|settings_get_flags|size_bytes|lsn\\w*|partition_\\w*|" +
22+
"indexes_size|table_is_visible|has_\\w*_privilege|get_userbyid|database_collation_actual_version|" +
23+
"collation_actual_version|available_extension\\w*|control_\\w*|ls_\\w*dir|cursor\\w*|event_trigger_\\w*"
24+
const SIDE_EFFECT_FUNCTION_CALL = new RegExp(
25+
"\\b(nextval|setval|set_config|load_extension|writefile|dblink\\w*|lo_\\w*|lowrite|loread|lo_creat|checkpoint|force_checkpoint|" +
26+
`pg_(?!(?:${PURE_PG_FUNCTIONS})\\s*\\()\\w*)\\s*\\(`,
27+
"i",
28+
)
29+
930
const column = z
1031
.string()
1132
.min(1)
@@ -187,7 +208,7 @@ export function readSql(sql: string, dialect?: string): string {
187208
body,
188209
) ||
189210
/\bREPLACE\b(?!\s*\()/i.test(body) ||
190-
/\b(nextval|setval|set_config|load_extension|writefile|dblink\w*|lo_import|lo_export|lo_create|lo_unlink|lo_put|lo_from_bytea|lo_truncate\w*|lo_open|lowrite|pg_write_file|pg_append_file|pg_file_write|pg_file_rename|pg_file_unlink|pg_(?:try_)?advisory_\w*|pg_(?:stat_reset\w*|wal_replay_\w*|logical_\w*|switch_wal|switch_xlog|create_restore_point|(?:start|stop)_backup|backup_(?:start|stop)|\w*replication\w*|terminate_backend|cancel_backend|promote|reload_conf|rotate_logfile|log_backend_memory_contexts))\s*\(/i.test(
211+
SIDE_EFFECT_FUNCTION_CALL.test(
191212
functions,
192213
)
193214
) {

‎packages/opencode/test/altimate/sql-check-postgres.test.ts‎

Lines changed: 35 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -141,6 +141,19 @@ describe("predicate_effect with mocked Postgres", () => {
141141
"lowrite(lo_open(1, 131072), '\\x00'::bytea)",
142142
],
143143
],
144+
[
145+
"class-denied",
146+
[
147+
"lo_import_with_oid('/tmp/x', 1)",
148+
"lo_creat(131072)",
149+
"pg_log_standby_snapshot()",
150+
"PG_READ_FILE('/etc/passwd')",
151+
"pg_sleep(30)",
152+
"checkpoint()",
153+
"force_checkpoint(current_database())",
154+
"pg_some_future_control_function(1)",
155+
],
156+
],
144157
[
145158
"recovery-control",
146159
[
@@ -188,6 +201,28 @@ describe("predicate_effect with mocked Postgres", () => {
188201
})
189202
}
190203

204+
test.each([
205+
"pg_typeof(id)",
206+
"PG_SIZE_PRETTY(id::bigint)",
207+
"pg_column_size(id)",
208+
"pg_get_expr('1', 1)",
209+
"pg_stat_get_numscans(1)",
210+
"pg_relation_size('orders')",
211+
"pg_current_wal_lsn()",
212+
"pg_has_role('x', 'USAGE')",
213+
])("accepts the pure read function %s in predicates and populations", async (call) => {
214+
const execute = postgres("r", 5, (sql) => sql.replaceAll(call, "1"))
215+
const result = await checkSql({
216+
warehouse,
217+
check: "predicate_effect",
218+
base_sql: "orders",
219+
predicates: ["id > 0", `${call} IS NOT NULL`],
220+
})
221+
expect(result.metadata).toMatchObject({ total_rows: 5 })
222+
// The pure call reached execution (a bare table base also issues the row-estimate query).
223+
expect(execute).toHaveBeenCalled()
224+
})
225+
191226
test.each([
192227
"pg_promote", "pg_reload_conf", "dblink_send_query", "dblink_connect", "dblink_connect_u",
193228
"pg_logical_emit_message", "dblink", "pg_advisory_lock_shared", "pg_try_advisory_lock_shared",

0 commit comments

Comments
 (0)