# ADR-077: OpenClaw Integration — Read-Only DB Role, Schema Grants, and Auth Layers

## Status
Accepted

## Status History
```yaml
- 2026-06-27: Proposed
- 2026-06-27: Accepted (decision recorded as implementation begins)
```

## Context

OpenClaw is a personal AI assistant (WhatsApp/Telegram bridge) deployed on the same VPS as 360lm production infrastructure (see ADR-015: Dev/Prod Two Stacks on Same VPS). It runs as a native Node.js systemd service (`openclaw-gateway.service` on port 18789) and is proxied through Traefik at `https://openclaw.srv1111289.hstgr.cloud`.

OpenClaw needs read access to 360lm business data to:
- Answer queries about financial state (expense reimbursements, transfers, impress balance, salary)
- Surface operational insights (counters, tours, recce submissions, vendor catalogs, sales offers)
- Provide cross-schema reporting without direct Postgres/PostgREST access

**Current State:**
- OpenClaw uses API key auth (Anthropic Claude Sonnet 4.6 via shared `ANTHROPIC_API_KEY`)
- WhatsApp linked to +917973685934 (single user, personal assistant)
- No dedicated database role — initially accessed via `postgres` superuser (unacceptable long-term)
- Two-layer auth in place: Traefik basic auth (harishlal/Harish@2032) + OpenClaw token

**Affected Schemas (per ADR-009: Each PWA Owns a Dedicated PostgreSQL Schema):**
- **expense** — employee reimbursement, impress tracking
- **finance** — accounts, transactions, salary register, impress
- **installation** — campaigns, tours, jobs, counters
- **stores**, **production**, **vrs**, **recce**, **sales** — catalog and operational data

**Schemas NOT Granted (reasons below):**
- **activity** — contains user session logs (PII: login history, IP, timestamps)
- **dispatch** — operational staff scheduling, internal logistics (not customer-facing, limits risk exposure)
- **client** — external partner data, confidential pricing and engagement terms
- **vehicle** — fleet maintenance, fuel cost, mileage (sensitive operational data; see ADR-058)

**Operational Context:**
- OpenClaw is a convenience tool (AI assistant via WhatsApp), not mission-critical
- It may answer with stale data (eventual consistency acceptable)
- Shared `ANTHROPIC_API_KEY` is used by Paperclip and PWA proxies — single key for operational simplicity
- Rate limits and cost controls are set at Anthropic account level, not per-service

## Decision

We will create a dedicated, read-only PostgreSQL role `openclaw_ro` with the following constraints:

1. **Role Definition:**
   - Name: `openclaw_ro`
   - Permissions: SELECT only on granted schemas
   - No INSERT, UPDATE, DELETE, CREATE, DROP, or schema-creation permissions
   - No ALTER or REINDEX privileges

2. **Schema Grants (Explicit Include List):**
   - **expense**: `employee_list`, `sheet_summary`, `approved_pending_reimbursement`, `sheets`, `expenses`
   - **finance**: `accounts`, `transactions`, `salary_register`, `impress_accounts`, `impress_transactions`
   - **installation**: `campaigns`, `tours`, `tour_list`, `jobs`, `counters`
   - **stores**: ALL tables (read-only)
   - **production**: ALL tables (read-only)
   - **vrs**: ALL tables (read-only)
   - **recce**: ALL tables (read-only)
   - **sales**: `offers`, `offer_lines`, `companies`

3. **Schema Exclusions (Explicit Deny):**
   - **activity**: User session logs; PII risk
   - **dispatch**: Internal operational scheduling; exposure risk
   - **client**: External partner confidential data; contractual boundaries
   - **vehicle**: Fleet sensitive data (ADR-058); restricted access

4. **Default Policy for New Schemas:**
   - When a new PWA schema is added (future), it defaults to **NO ACCESS** for openclaw_ro
   - Explicit approval from hkl required to grant read access to new schemas
   - Rationale: deny-by-default principle; business decision required for each schema

5. **Authentication Layers (Two-Layer Auth Mandatory):**
   - **Layer 1 (Traefik):** Basic HTTP auth at reverse proxy (username: `harishlal`, password-protected)
   - **Layer 2 (Application):** OpenClaw internal token-based auth (stored in openclaw.json `gateway.auth.token`)
   - Both must succeed for access; no exceptions

6. **API Key Sharing (Interim Policy):**
   - OpenClaw uses the shared `ANTHROPIC_API_KEY` (same as Paperclip, proxy services)
   - This is acceptable for Phase 4.13+ due to single-user WhatsApp bridge (low contention)
   - **Upgrade trigger:** If OpenClaw's requests exceed 20% of monthly API spend OR rate limits are hit, spin up a dedicated API key with separate rate-limit bucket
   - Decision maker for key split: hkl

7. **Credentials & Secrets:**
   - `openclaw_ro` password stored in `/root/.openclaw_ro_pgpass` (mode 600, root only)
   - Connection string format: `postgresql://openclaw_ro:<password>@localhost:5432/lm360`
   - Credentials NOT stored in OpenClaw config files; loaded from environment or external secret file only

## Implementation Notes

**Creating the Role:**
```sql
-- Create the read-only role
CREATE ROLE openclaw_ro WITH LOGIN PASSWORD '<secure-password>';
REVOKE ALL PRIVILEGES ON ALL SCHEMAS IN DATABASE lm360 FROM openclaw_ro;
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM openclaw_ro;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM openclaw_ro;

-- Grant CONNECT only to the database
GRANT CONNECT ON DATABASE lm360 TO openclaw_ro;
GRANT USAGE ON SCHEMA expense, finance, installation, stores, production, vrs, recce, sales TO openclaw_ro;

-- Grant SELECT on all tables in included schemas
GRANT SELECT ON ALL TABLES IN SCHEMA expense TO openclaw_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA finance TO openclaw_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA installation TO openclaw_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA stores TO openclaw_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA production TO openclaw_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA vrs TO openclaw_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA recce TO openclaw_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO openclaw_ro;

-- Ensure future tables in these schemas are also readable
ALTER DEFAULT PRIVILEGES IN SCHEMA expense GRANT SELECT ON TABLES TO openclaw_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA finance GRANT SELECT ON TABLES TO openclaw_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA installation GRANT SELECT ON TABLES TO openclaw_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA stores GRANT SELECT ON TABLES TO openclaw_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA production GRANT SELECT ON TABLES TO openclaw_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA vrs GRANT SELECT ON TABLES TO openclaw_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA recce GRANT SELECT ON TABLES TO openclaw_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA sales GRANT SELECT ON TABLES TO openclaw_ro;
```

**Audit Access:**
```sql
-- Verify role permissions
SELECT grantee, privilege_type, table_name
FROM information_schema.table_privileges
WHERE grantee = 'openclaw_ro'
ORDER BY table_schema, table_name;
```

**Revoking Access (if a schema must be removed):**
```sql
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA <schema_name> FROM openclaw_ro;
REVOKE USAGE ON SCHEMA <schema_name> FROM openclaw_ro;
```

**Schema Grant Addition (future, requires ADR/decision record):**
When a new schema or table access is needed, create a follow-up commit with:
1. Updated ADR or linked decision document (e.g., "Schema X added to openclaw_ro per request ADR-NNN-002")
2. SQL grant statement
3. Test query verifying access

## Alternatives Considered

### Alternative 1: No Database Access — HTTP-Only via PostgREST
**Approach:** OpenClaw queries via PostgREST REST API (same as PWAs), uses JWT tokens.

**Rejected because:**
- PostgREST grants are table-level, not schema-level; would require thousands of individual grants across all eligible tables
- JWT token expiry management and refresh overhead for a background service
- No performance benefit over direct role-based Postgres access
- Adds unnecessary network hop and API latency for reporting queries

### Alternative 2: Superuser or Limited Admin Role
**Approach:** Grant OpenClaw `SUPERUSER` or high-privilege role (e.g., `db_owner`).

**Rejected because:**
- Violates principle of least privilege; OpenClaw is a convenience tool, not a DBA
- If OpenClaw service is compromised, attacker gains unrestricted DB access
- No clear limit on what OpenClaw can read; operational risk
- Cannot revoke access without recreating the role

### Alternative 3: Virtual Views for OpenClaw Reporting
**Approach:** Create separate reporting views (read-only, aggregated) in a dedicated `openclaw` schema; grant access only to those views.

**Rejected because:**
- Creates maintenance burden: every schema addition triggers view creation decisions
- Views would eventually number 100+, requiring schema governance documentation
- Adds no security value over direct table SELECT grants
- Complicates debugging (queries against views instead of tables directly)
- Later, if OpenClaw is expanded to multiple users or more AI services, views become a bottleneck

**Kept as future optimization:** If query complexity or aggregation needs grow, views can be added on top of existing grants without revoking table access.

### Alternative 4: Dedicated Postgres Service (separate DB instance)
**Approach:** Run OpenClaw against a read-only replica or dedicated DB snapshot.

**Rejected because:**
- Adds complexity (second database instance, replication setup, failover)
- VPS is single-server; replication requires second host
- Cost/latency benefit is zero (same physical server)
- Operational burden (backup, upgrade, monitoring two databases)

## Consequences

### Benefits
1. **Least-Privilege Security:** OpenClaw can read only what it needs; no INSERT/UPDATE/DELETE possible
2. **Easy Audit Trail:** All openclaw_ro queries are logged to Postgres query logs; can be monitored for anomalies
3. **Reversible Access Control:** Can revoke entire schema access in one SQL statement; no code changes needed
4. **Clear Operational Boundaries:** Business-facing schemas (expense, finance, sales) are explicitly separated from operational (dispatch) and sensitive (activity, vehicle) schemas
5. **Scalable to Multi-User AI Services:** If OpenClaw expands or more AI services join the VPS, role-based grants are the foundation; no redesign needed
6. **Future-Proof New Schemas:** Default deny for new schemas; each addition requires deliberate approval, preventing accidental over-exposure

### Trade-Offs
1. **Eventual Consistency:** OpenClaw queries reflect database state at query time; no transactional consistency with concurrent PWA writes. Acceptable because OpenClaw is conversational and does not drive operational decisions.
2. **Manual Grant Administration:** DBA (hkl) must explicitly GRANT new table access to openclaw_ro when new tables are added to existing schemas. Mitigated by ALTER DEFAULT PRIVILEGES (future tables auto-granted).
3. **No Access to activity/dispatch/client/vehicle:** OpenClaw cannot answer detailed questions about user activity, internal logistics, or partner data. Trade-off is intentional; these are restricted-access schemas.
4. **Shared API Key:** OpenClaw shares `ANTHROPIC_API_KEY` with Paperclip and proxies. If rate limits are hit, OpenClaw is affected. Acceptable until spend exceeds threshold (see Alternative 6 below). Upgrade path is clear (separate key).

### Risks and Mitigations

| Risk | Severity | Mitigation |
|------|----------|-----------|
| Unauthorized Postgres access if `.openclaw_ro_pgpass` is leaked | High | Store pgpass in `/root/.openclaw_ro_pgpass` (mode 600); ensure no world-readable copies; rotate password annually |
| OpenClaw service is compromised; attacker uses openclaw_ro to read sensitive tables | Medium | Two-layer auth (Traefik + app token) limits compromise surface; SELECT-only prevents data corruption; add Postgres query logging and alerting for unusual access patterns |
| New schema added without updating grant policy; developers assume openclaw_ro has access | Medium | Document default-deny policy in this ADR; add checklist item to new-schema onboarding (update SCHEMA_GRANTS.md when applicable) |
| Rate limits exhausted on shared `ANTHROPIC_API_KEY`; OpenClaw requests queued/dropped | Low | Monitor monthly API spend; if OpenClaw >20% of budget, split key. Interim: document escalation path to hkl |
| Data freshness expectations; OpenClaw queries return stale data during high write load | Low | Document in OpenClaw user guide: "responses reflect recent data, not real-time state" |

## Related Decisions

- **ADR-009: Each PWA Owns a Dedicated PostgreSQL Schema** — Defines schema ownership; this ADR applies role-based access control to cross-schema queries.
- **ADR-010: Cross-Schema Data Access Goes Through the Proxy, Not PostgREST Directly** — Establishes proxy pattern for PWA-to-PWA queries; OpenClaw uses direct role-based access (acceptable because OpenClaw is read-only and not a PWA).
- **ADR-014: PostgREST Is the API Layer** — PostgREST serves PWA API traffic; OpenClaw does not use PostgREST, uses direct Postgres connection for performance.
- **ADR-015: Dev and Prod Are Two Full Stacks on the Same VPS** — OpenClaw runs on prod stack only; no separate dev instance needed (single-user WhatsApp bridge).
- **ADR-017: All Service Routing Uses Traefik + Docker Labels** — OpenClaw proxy container is Traefik-routed; this ADR documents the second auth layer (OpenClaw app token).
- **ADR-058: Credit Card PWA Reads vehicle.cc_transactions via View** — vehicle schema is restricted-access; openclaw_ro does not have vehicle access, consistent with this policy.
- **ADR-067: Cross-PWA Change Safety Gate** — When proposing new schema grants for OpenClaw, this ADR requires explicit approval before implementation.
- **ADR-073: Traefik Labels for Routing** (referenced by context) — OpenClaw container labels define the reverse proxy route.
- **ADR-078: Paperclip Isolation** (related) — Paperclip also runs on same VPS; documents its own access controls and API key sharing policy.

## References

- **Memory:** `/root/.claude/projects/-var-www-360lm/memory/openclaw.md` (service config, auth, backups)
- **Memory:** `/root/.claude/projects/-var-www-360lm/memory/infra_vps.md` (VPS topology, Traefik routing)
- **Postgres Role Documentation:** https://www.postgresql.org/docs/current/sql-createrole.html
- **Postgres Privileges:** https://www.postgresql.org/docs/current/ddl-priv.html
- **ADR-009 Schemas:** `/var/www/360lm/docs/adr/ADR-009-each-pwa-owns-its-db-schema.md`

---

**Decision Made By:** hkl  
**Date Decided:** 2026-06-27  
**Changed Via:** adr-kit ADR skill (360lm)