sqlsafe

package
v0.8.0 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Aug 21, 2026 License: MIT Imports: 12 Imported by: 0

Documentation

Overview

Package sqlsafe implements the guarded SQL execution contract for sshx: fail-closed statement classification, policy gates, backup planning, and PostgreSQL remote command assembly. It never connects anywhere itself; it only analyzes SQL text and builds commands for the SSH execution layer.

Index

Constants

View Source 
const (
	EnginePostgres = "postgres"
	EngineSQLite   = "sqlite"
)

Engine names accepted by sshx sql.

View Source 
const DefaultBackupDir = ".sshx/sql-backups"

DefaultBackupDir is where remote backups land, relative to the SSH user's home directory (the working directory of a fresh SSH exec session).

View Source 
const DefaultRowThreshold = 1000

DefaultRowThreshold bounds row-level backups: estimates above it switch to a full table dump.

Variables

This section is empty.

Functions

func BackupPath 

func BackupPath(dir, database, table string, kind BackupKind) string

BackupPath builds a unique remote backup file path under dir (default DefaultBackupDir, relative to the SSH user's home). The generated path only contains shell- and \copy-safe bytes.

func CheckPolicy 

func CheckPolicy(cls *Classification, opts Options) error

CheckPolicy enforces the execution gates on a classified statement. It returns a *BlockedError describing the first violated gate, or nil.

func NormalizeEngine added in v0.5.0 

func NormalizeEngine(engine string) string

NormalizeEngine maps a user-supplied --engine value to a canonical name. An empty value is postgres, matching the historical default.

func ParseBooleanOutput 

func ParseBooleanOutput(output string) (bool, error)

ParseBooleanOutput parses the terse 0/1 result emitted by catalog preflight queries.

func ParseChangesOutput added in v0.5.0 

func ParseChangesOutput(output string) (int64, bool)

ParseChangesOutput reads the last integer line emitted by SELECT changes().

func ParseCommandTag 

func ParseCommandTag(output string) (rows int64, ok bool)

ParseCommandTag scans psql output (last line first) for a DML command tag and returns the affected/copied row count.

func ParseExplainRows 

func ParseExplainRows(output string) (int64, error)

ParseExplainRows extracts the top-level plan row estimate from EXPLAIN (FORMAT JSON) output.

func RedactForAudit 

func RedactForAudit(sql string) string

RedactForAudit removes comments and literal values while retaining enough SQL structure for an audit record to remain useful. Exact-statement correlation is provided separately by a SHA-256 digest.

func RestoreHint 

func RestoreHint(plan BackupPlan, path string) string

RestoreHint documents how to restore from the backup artifact.

func RestoreHintFor added in v0.5.0 

func RestoreHintFor(engine string, plan BackupPlan, path string) string

RestoreHintFor is the engine-aware restore hint used in JSON results.

func ValidateBackupDir 

func ValidateBackupDir(dir string) error

ValidateBackupDir rejects control characters that could escape psql meta-command or shell line boundaries.

func ValidateContainerName 

func ValidateContainerName(name string) error

ValidateContainerName bounds the docker container reference used in argv.

func ValidateDatabaseName 

func ValidateDatabaseName(name string) error

func ValidateSQLitePath added in v0.5.0 

func ValidateSQLitePath(path string) error

ValidateSQLitePath accepts an absolute filesystem path and rejects URI forms, relative paths, parent segments, and characters that would break the file: URI or the sqlite3 script we generate.

func ValidateTableIdent 

func ValidateTableIdent(table string) error

ValidateTableIdent accepts plain or quoted, optionally schema-qualified table identifiers and rejects anything that could break out of the SQL or psql meta-command context the name is embedded into. Fail-closed.

func WrapSudoStdin added in v0.8.0 

func WrapSudoStdin(command string) string

WrapSudoStdin runs command under `sudo -S` with an empty prompt. The caller must prepend the sudo password plus a newline to stdin; sudo consumes that first line and the original command sees the rest. The password never enters argv. sh -c preserves pipelines, mkdir prefixes, and PGPASSWORD readers.

Types

type BackupKind 

type BackupKind string

BackupKind names the backup strategy chosen for one statement. 

const (
	// BackupNone means no pre-change backup is taken.
	BackupNone BackupKind = "none"
	// BackupRows snapshots exactly the affected rows to a CSV file before the
	// change using the statement's own WHERE clause.
	BackupRows BackupKind = "rows"
	// BackupTable snapshots the whole target table to CSV before the change.
	BackupTable BackupKind = "table"
	// BackupFile snapshots the whole SQLite database file with .backup.
	BackupFile BackupKind = "file"
)

type BackupPlan 

type BackupPlan struct {
	Kind   BackupKind `json:"kind"`
	Table  string     `json:"table,omitempty"`
	Reason string     `json:"reason"`
}

BackupPlan describes the pre-change backup decided for one statement.

func DecideBackup 

func DecideBackup(cls *Classification, estimatedRows int64, opts Options) (BackupPlan, error)

DecideBackup chooses the backup strategy for a classified statement given the EXPLAIN row estimate (pass a negative estimate when unknown, e.g. for a local dry-run plan). It is fail-closed: when a backup is required but the target table could not be extracted, it returns a *BlockedError.

func DecideSQLiteBackup added in v0.5.0 

func DecideSQLiteBackup(cls *Classification, opts Options) (BackupPlan, error)

DecideSQLiteBackup chooses a SQLite backup. L1 never uses row-level CSV: bounded single-table DML snapshots the table; everything else that needs a backup snapshots the whole database file (always available).

type BlockedError 

type BlockedError struct {
	Reason string
}

BlockedError wraps every classification/policy rejection so callers can map it to the stable "blocked" error kind.

func (*BlockedError) Error 

func (e *BlockedError) Error() string

type Class 

type Class string

Class is the coarse risk class of one SQL statement. 

const (
	// ClassRead statements never mutate data (SELECT, SHOW, EXPLAIN, ...).
	ClassRead Class = "read"
	// ClassDML statements mutate rows (INSERT, UPDATE, DELETE, MERGE).
	ClassDML Class = "dml"
	// ClassDDL statements mutate schema or run privileged maintenance and
	// always require --force.
	ClassDDL Class = "ddl"
	// ClassBlocked statements are never executed by sshx sql.
	ClassBlocked Class = "blocked"
)

type Classification 

type Classification struct {
	// Statement is the single trimmed statement without a trailing semicolon.
	Statement string `json:"statement"`
	Class     Class  `json:"class"`
	Verb      string `json:"verb"`
	// Table is the primary target table (DML and destructive DDL). Empty when
	// not applicable or not extractable.
	Table string `json:"table,omitempty"`
	// HasWhere reports a top-level WHERE clause (UPDATE/DELETE).
	HasWhere bool `json:"has_where"`
	// WhereClause is the original text of the top-level WHERE condition
	// (excluding the WHERE keyword and any trailing RETURNING clause).
	WhereClause string `json:"-"`
	// ComplexSource marks UPDATE ... FROM, DELETE ... USING, MERGE, and
	// CTE-wrapped DML whose affected row set cannot be reproduced by a simple
	// SELECT; these always take a table-level backup.
	ComplexSource bool `json:"complex_source,omitempty"`
	// Destructive marks DDL that destroys data (DROP TABLE, TRUNCATE) and
	// therefore requires a table backup in addition to --force.
	Destructive bool `json:"destructive,omitempty"`
	// MayAffectRelated marks syntax such as TRUNCATE ... CASCADE whose effects
	// are known to extend beyond the primary target table.
	MayAffectRelated bool `json:"may_affect_related,omitempty"`
}

Classification is the fail-closed analysis of exactly one SQL statement.

func Classify 

func Classify(sql string) (*Classification, error)

Classify analyzes sql and returns the classification of the single statement it contains. It is fail-closed: multiple statements, lexing errors, and unrecognized statement heads are all rejected.

func ClassifyFor added in v0.5.0 

func ClassifyFor(engine, sql string) (*Classification, error)

ClassifyFor dispatches statement analysis to the engine-specific classifier.

func ClassifySQLite added in v0.5.0 

func ClassifySQLite(sql string) (*Classification, error)

ClassifySQLite analyzes sql under the SQLite dialect. It shares the fail-closed lexer with Classify but uses a SQLite verb table and blocks sqlite3 dot-commands, ATTACH, load_extension, and writable PRAGMA forms.

type Conn 

type Conn struct {
	Database      string
	User          string // database role, not the SSH user
	Host          string // database host as seen from the remote machine
	Port          string
	PasswordStdin bool
	Docker        string // container name/ID; empty = clients run on the host
}

Conn describes how the remote psql process reaches PostgreSQL on the target host. Credentials are never placed in argv: when PasswordStdin is set, the generated command reads PGPASSWORD from the first stdin line. When Docker is set, the database clients run inside the container via `docker exec -i` (the password crosses only via env passthrough, not argv).

Conn implements SQLExecutor.

func (Conn) ExecuteCommand 

func (c Conn) ExecuteCommand(stmt string) RemoteCommand

ExecuteCommand runs the statement itself. Command tags (e.g. "UPDATE 3") stay on stdout so the affected row count can be parsed afterwards.

func (Conn) ExecuteReadCommand 

func (c Conn) ExecuteReadCommand(stmt string) RemoteCommand

ExecuteReadCommand enforces PostgreSQL's transaction-level read-only mode so SELECT-invoked functions cannot mutate persistent database state.

func (Conn) ExecuteWithBackupCommand 

func (c Conn) ExecuteWithBackupCommand(stmt, table, where, path string, kind BackupKind) (RemoteCommand, error)

ExecuteWithBackupCommand locks the target against concurrent writers, snapshots its preimage, and executes the mutation in one PostgreSQL transaction. A failed mutation rolls back while the already-written CSV remains available for diagnosis or restore.

func (Conn) ExplainCommand 

func (c Conn) ExplainCommand(stmt string) RemoteCommand

ExplainCommand runs EXPLAIN (FORMAT JSON) for stmt and prints the bare JSON plan (-q -A -t) so the row estimate can be parsed deterministically.

func (Conn) NeedsPasswordLine 

func (c Conn) NeedsPasswordLine() bool

NeedsPasswordLine reports whether the command expects a leading PGPASSWORD line on stdin before the payload.

func (Conn) RelatedEffectsCommand 

func (c Conn) RelatedEffectsCommand(table, verb string) (RemoteCommand, error)

RelatedEffectsCommand checks whether a mutation can invoke user triggers or cascading referential actions outside its direct target table.

type CredSource 

type CredSource struct {
	// Kind is "docker" (read the container's environment via docker inspect)
	// or "env-file" (read a KEY=VALUE file on the remote host).
	Kind string
	// Container is the docker container name or ID (Kind == "docker").
	Container string
	// Path is the remote env file path (Kind == "env-file").
	Path string
}

CredSource describes where database credentials live on the remote host. Production databases frequently keep credentials in a container's environment or an env file next to the deployment instead of any local keyring; sshx resolves them remotely at execution time and never asks the operator to copy secrets around.

func ParseCredSource 

func ParseCredSource(spec string) (CredSource, error)

ParseCredSource parses a --db-cred-from specification. Supported forms: "docker:<container>" and "env-file:<remote path>".

func (CredSource) ExtractionCommand 

func (s CredSource) ExtractionCommand() (string, error)

ExtractionCommand returns the remote command that prints the credential environment, one KEY=VALUE per line. The command itself contains no secret; its stdout does and must never be logged or audited.

func (CredSource) String 

func (s CredSource) String() string

String returns the canonical spec form (also used as the cache identity).

type Credentials 

type Credentials struct {
	User     string `json:"user,omitempty"`
	Password string `json:"password"`
	Database string `json:"database,omitempty"`
	Host     string `json:"host,omitempty"`
	Port     string `json:"port,omitempty"`
}

Credentials is the outcome of resolving a CredSource. Every field is optional except Password; explicit CLI flags always win over these values.

func ParseCredOutput 

func ParseCredOutput(output string) (Credentials, error)

ParseCredOutput extracts credentials from KEY=VALUE lines (docker inspect env output or an env file). Discrete keys win over a connection URL. The error never embeds raw output, which may contain unrelated secrets.

type Options 

type Options struct {
	// Force allows DDL statements (still never the hard-blocked ones).
	Force bool
	// AllowFullTable allows UPDATE/DELETE without a top-level WHERE clause.
	AllowFullTable bool
	// NoBackup disables pre-change backups. It requires Force.
	NoBackup bool
	// RowThreshold is the estimated-row boundary between a row-level CSV
	// backup and a full table dump. Zero uses DefaultRowThreshold.
	RowThreshold int64
}

Options carries the caller-controlled safety levers for one execution.

type RemoteCommand 

type RemoteCommand struct {
	Command string
	Stdin   string
}

RemoteCommand is one fully assembled remote invocation: the shell command for the SSH exec channel plus the stdin payload (SQL text or psql meta-commands). The caller prepends the password line when needed.

type SQLExecutor added in v0.5.0 

type SQLExecutor interface {
	NeedsPasswordLine() bool
	ExplainCommand(stmt string) RemoteCommand
	ExecuteCommand(stmt string) RemoteCommand
	ExecuteReadCommand(stmt string) RemoteCommand
	ExecuteWithBackupCommand(stmt, table, where, path string, kind BackupKind) (RemoteCommand, error)
	RelatedEffectsCommand(table, verb string) (RemoteCommand, error)
}

SQLExecutor assembles remote client commands for one engine. sshx embeds no database driver: every method returns a command for the SSH exec channel.

type SQLiteConn added in v0.5.0 

type SQLiteConn struct {
	Path string
}

SQLiteConn describes how the remote sqlite3 process opens one database file. There is no password, role, or network endpoint: identity is the file path.

func (SQLiteConn) ExecuteCommand added in v0.5.0 

func (c SQLiteConn) ExecuteCommand(stmt string) RemoteCommand

ExecuteCommand runs the statement and prints sqlite3's changes() count so DML affected-row reporting stays machine-readable.

func (SQLiteConn) ExecuteReadCommand added in v0.5.0 

func (c SQLiteConn) ExecuteReadCommand(stmt string) RemoteCommand

ExecuteReadCommand opens the file URI in read-only mode so writes fail.

func (SQLiteConn) ExecuteWithBackupCommand added in v0.5.0 

func (c SQLiteConn) ExecuteWithBackupCommand(stmt, table, where, path string, kind BackupKind) (RemoteCommand, error)

ExecuteWithBackupCommand locks the database with BEGIN IMMEDIATE, snapshots either the target table (CSV) or the whole file (.backup), then mutates.

func (SQLiteConn) ExplainCommand added in v0.5.0 

func (c SQLiteConn) ExplainCommand(stmt string) RemoteCommand

ExplainCommand runs EXPLAIN QUERY PLAN. SQLite has no JSON plan-row estimate; callers store the text and do not parse a row count.

func (SQLiteConn) NeedsPasswordLine added in v0.5.0 

func (c SQLiteConn) NeedsPasswordLine() bool

NeedsPasswordLine is always false: SQLite file access uses OS permissions.

func (SQLiteConn) RelatedEffectsCommand added in v0.5.0 

func (c SQLiteConn) RelatedEffectsCommand(table, verb string) (RemoteCommand, error)

RelatedEffectsCommand reports whether a mutation may fire triggers or cascading foreign keys outside the copied table CSV.

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.