fix(database): secure balance definers, least-privilege grants and destructive test isolation #42
Loading…
Reference in a new issue
No description provided.
Delete branch "%!s()"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
Scope and source
Priority: P1. Gap coverage: GAP-015, GAP-019, GAP-039. Derived from GAP_1791945837.md, reviewed code commit
f8dbe93df953a78b17cb3304b717dcf84c4cd517. Currente476a68da09645c2308cbf47b90211fcaffcb7afadds only the report, so implementation findings remain applicable. Report evidence is local reproduction/source inspection unless explicitly proposed; no production exploit or current live-readiness claim.Coverage and sequencing: gap backlog tracker. Workstream 07. Dependencies: 05.
Current codebase
Balance SECURITY DEFINER functions reference unqualified tables without a pinned search_path; restricted-role temporary shadow tables returned forged balance 999. grants.sql uses ALL/default ALL on voting tables and a known login password. Startup does not validate actual privileges. Integration helpers fall back to DATABASE_URL then run migrations/TRUNCATE CASCADE and privilege DDL.
Why this implementation is needed
A restricted API role must not manufacture balances or mutate immutable votes/snapshots, and test tooling must never destroy an application database.
Relevant files
ledger/migrations/20260825000001_ledger.sqlledger/migrations/20260825000003_qualify_balance_fns.sqldeploy/grants.sqloperator-voting/src/config.rsoperator-voting/src/balance_query.rsoperator-voting/tests/privilege.rsoperator-voting/tests/api_flow.rsledger/tests/ledger_integration.rsdocker-compose.test.ymldocs/OPS.mdRecommended solution
Add a forward migration schema-qualifying every definer relation and pinning a trusted search path with pg_temp explicitly last; audit ownership, public EXECUTE and schema CREATE/TEMP grants. Replace broad grants/default privileges with per-table/operation rights and narrow transactional routines where immutable inserts/freeze need mediation. Create roles without a usable default password; provision through authorized secret tooling and validate production effective privileges. Require explicit test-only configuration plus database identity/guard before any migration, truncate or role change; prefer ephemeral databases. Remove application-URL fallback and make required integration CI fail when its test DB is absent.
Constraints and guardrails
Preserve standalone offchain advisory voting, no new smart contracts or archive-node dependency, BSC as a core electorate, raw 18-decimal integer balances and separate Terra/BSC address identities. Keep Legal fail-closed transactional UI and the proven DEX Terra/Bridge EVM connectors; no browser BSC balance RPC, seed/key prompt or embedded admin/model credential. API must retain a restricted role separate from the ledger writer. Freeze at actual opening, never at draft creation or a client-supplied height. Agents own design, implementation, review and evidence under repository guidance; do not shift approvals/tests to the human or invent PASS evidence.
Acceptance criteria
Hostile search_path/temp tables cannot influence real balance queries. Restricted role cannot write ledger or UPDATE/DELETE/TRUNCATE immutable receipts/votes/frozen snapshots; legitimate lifecycle operations still work. Fresh and upgraded grants match the documented privilege matrix with no blanket future-table access. Destructive helpers reject unsafe/missing DB config before connecting or running DDL.
Test plan: supported paths, failure paths and concurrency
Fresh migration and populated upgrade; trusted/restricted/default/public roles; legal balance calls and all legitimate API mutations. Attempt temp/permanent shadow tables, hostile paths and direct mutation of each immutable table. Test guard parsing offline with production-like URLs, missing config, wrong database marker, correct ephemeral DB and required-job fail behavior. Repeat grants safely and exercise secure role rotation.
Test plan: attack, hack and abuse vectors
Temporary-table/function shadowing, schema hijack, inherited/public privileges, new-table privilege creep, known-password authentication, direct control-plane tampering and malicious/mistaken test URLs. No test may connect to production to prove rejection; use synthetic URLs and isolated fixtures.
Verification and completion criteria
Attach actual restricted-role permission matrix and original 999-shadow regression result, migration/grant upgrade evidence and guard refusal tests. Run all isolated DB suites after the guard lands; verify no working default password or credential-bearing logs.
Record commit and pinned toolchain, commands/results and sanitized artifacts. Relevant Rust changes require
cargo test --workspace --libplus isolated Postgres integration (explicit guarded LEDGER_TEST_DATABASE_URL); frontend changes requirenpm testand production build plus relevant browser cases. Run secret scanning for new fixtures/config. Exercise fresh/upgrade migrations when schema/grants change; report skipped/live-only checks explicitly.Implementation status after main merge PR #53, commit
cd07f00f32f04ad02f8b760ed34588fd9645919e:Services are paused while existing storage is audited and the corrected pinned release is prepared. No production wallet transaction or hardware-wallet/Legal QA is claimed. This is an interim report; the issue remains open and the final comment will map the original criteria to actual live results and any unverified cases.
The bounded read-only deployment
o47uvv9xxkwhmmf6xcutmkfycompleted from main1562434c4237367f828ed71748ef71f116a5ff82. Production inspection confirmed15registrations plus2BSCtransfers/2balance checkpoints under the legacyvotingschema; corresponding canonicalpublicelectorate/history tables are empty. The new public migration tracker contains11migrations while the legacy tracker contains5. This explains the undefinedbaseline_heightfailures and proves that switching pool paths alone would omit the existing electorate.Accounting remains paused in read-only inspection mode. A forward import is being implemented and tested to preserve legacy registrations/history and frozen weights, retain the original tables for audit, reject conflicting data, and require explicit canonical baseline recovery. No issue is closed and no healthy voting deployment is claimed yet. The full latest isolated suite passed73unit/supervisor tests,92integration tests,245validated API responses,146frontend tests, production build/nginx/Gitleaks.
Implemented trusted balance definers, explicit SQL privileges and fail-closed destructive-test isolation. Changes are in PR #53, #54, #55, #56, and the throughput/test followup #59. PR #59 is merged with passing CI. Ledger/API/frontend deployments finished and are running healthy at final code
5cd9363307dc1607de952ac3243193c1a16304c7. Final live evidence.Original acceptance and test-plan checklist:
CARGO_BUILD_JOBS=2 scripts/verify-release.shrelease13 passes 74 unit/supervisor tests, 121 integrations (66 ledger), 245 schema validations and 146 frontend tests, production build, both nginx checks and Gitleaks. Machine-readable results, combined report, and lifecycle evidence record the restricted matrix, 999 attack, upgrades and guard refusals. The late supervised recovery fixture now permits at most one nonzero restart and requires post-restore fresh both-chain/intent progress plus sustained readiness; four fresh repeats pass.5cd9363307dc1607de952ac3243193c1a16304c7. All three services are running healthy; smoke 13, canonical fixtures and runtime configuration pass. No post-activation ledger warnings appear in Loki; the 13:56:21 direct Coolify warning precedes 13:58:22 activation. Final artifact.Operational observation: the first public readiness window was 44/45, with one unexplained 503 at 14:00:58 and 200 on the next check. This is recorded, followed by 90 consecutive successful public checks and 1080 successful private observations. Thresholds were preserved and no false healthy state was introduced. The earlier 503 supplies no evidence of forged balances, privilege escape, unsafe test access or immutable-record mutation.
Evidence limits: credential rotation/destructive permission attacks use isolated synthetic fixtures, not live rotation/destructive SQL. No funded-holder/committee blockchain signing, hardware/mobile wallet or live human Legal QA is claimed. Legacy hash-only receipts and uncertified polls retain explicit verification limits. No Prometheus datasource is provisioned; live log evidence uses Grafana/Loki. The issue's SQL privilege/isolation and final-release acceptance criteria are satisfied by the evidence above.
Final evidence is published through PR #60. All mandatory body/comment criteria are satisfied by the checklist above; closing this issue.