prole/docs/onboarding.md

# Engineer onboarding — first-day checklist

Welcome. This doc gets you from "I just got an onboarding link" to "psql is open
and I'm productive". Should take ~5 minutes.

> **Phase 1 (current).** Each engineer has a per-name postgres role with a
> temp password (24h) delivered via a one-time link. **Phase 2 (queued)**
> replaces the password mechanism with libpq OAUTHBEARER (Google sign-in).
> Your role name persists; only the connection string changes when Phase 2 lands.

---

## What you should have

1. An onboarding link that looks like
   `https://db.0.knoe.dev/onboard.html#user=...&pw=...&exp=...`,
   delivered by chrisfu via either:
   - A Gmail message to your `@knoey.com` mailbox, or
   - A QR code shown on screenshare during a Signal call.
2. A clone of the [`knoe-db` repo](https://git.knoe.dev/knoey.dev/knoe-db).

If you don't have either, ping chrisfu.

---

## Step 1 — Open the link / scan the QR

Click the URL (or scan the QR with your phone camera). The page at
`db.0.knoe.dev/onboard.html` decodes the URL fragment **client-side** and
displays:

- Your temporary password — with a **[Copy]** button
- Your psql connection string — pre-filled with your role name, also **[Copy]**
- A bootstrap one-liner that fetches the CA cert and connects you
- Inline `\password` rotation instructions

Save the password to your **personal** 1Password (or whichever password
manager you use). The page strips the password from your browser history on
first render, but DO NOT close it before saving — the page is one-shot.

---

## Step 2 — Connect and rotate

Clone the repo if you haven't, then connect:

```bash
git clone git@git.knoe.dev:knoey.dev/knoe-db.git
cd knoe-db
psql "host=pg.0.knoe.dev port=5432 user=$YOUR_USERNAME dbname=postgres \
      sslmode=verify-full sslrootcert=etc/knoe-db-ca.crt"
```

Paste the temporary password when prompted.

**Rotate immediately:**

```sql
postgres=> \password
Enter new password: <type a strong password you generate locally>
Enter it again:     <repeat>
postgres=> -- save the new password to your personal 1Password.
postgres=> -- everything from here uses YOUR password.
```

Why immediately? The temp `VALID UNTIL` is 24h; if you don't rotate before
then, you'll be locked out (chrisfu can rerun the onboard script to issue a
fresh temp).

---

## Step 3 — What you can do as `knoe_developer`

Your role is a member of the `knoe_developer` group. Grants:

| Schema       | Permission                       | Notes |
|--------------|----------------------------------|-------|
| `knoe`       | `SELECT, INSERT, UPDATE, DELETE` | Application schema |
| `public`     | `SELECT, INSERT, UPDATE, DELETE` | Same |
| `auth`       | `SELECT` only                    | Read-only on Supabase GoTrue tables |
| `storage`    | `SELECT` only                    | Read-only on Supabase storage tables |
| `extensions` | `USAGE`                          | Lets you reach `extensions.pg_stat_statements` etc. |

What you can't do:
- Drop / truncate `auth.*` or `storage.*` (read-only on those by design).
- `CREATE ROLE`, `CREATE EXTENSION` (postgres superuser only).
- Connect from outside the cluster as `postgres`, `supabase_admin`, etc.
  (those roles are pinned to RFC1918 by `pg_hba.conf`).

Need broader access? File an issue or ping chrisfu — easier to extend the
group than mint per-engineer overrides.

---

## Step 4 — Rust apps (if applicable)

Same connection string in `sqlx` / `tokio-postgres` / `deadpool-postgres`:

```rust
let url = format!(
    "postgres://{user}:{password}@pg.0.knoe.dev:5432/postgres\
     ?sslmode=verify-full&sslrootcert={ca}",
    user = std::env::var("KNOE_PG_USER")?,
    password = std::env::var("KNOE_PG_PASSWORD")?,
    ca = std::env::current_dir()?.join("etc/knoe-db-ca.crt").display(),
);
let pool = sqlx::PgPool::connect(&url).await?;
```

For deployed services (not your laptop): Phase 2 swaps the user/password env
vars for an OIDC token. The connection string skeleton stays the same.

---

## Step 5 — Studio access (web UI, optional)

Open <https://db.0.knoe.dev/> in any browser, sign in with your `@knoey.com`
Google account. Studio is the same database; you can use it for ad-hoc
queries, table inspection, RLS rule editing. Most of what you need is in the
SQL editor.

---

## Troubleshooting

- **`pg_hba.conf rejects connection ... no encryption`** — you set
  `sslmode=disable`. Use `verify-full` (with `sslrootcert=etc/knoe-db-ca.crt`)
  or at minimum `require`.

- **`password authentication failed`** — wrong password (typo when copy-paste,
  or temp expired). Run psql with `\password` once you're in, OR ping chrisfu
  to rerun the onboard script.

- **`role "<u>" is not permitted to log in`** — your `VALID UNTIL` lapsed.
  Ask for rotation.

- **Connection times out** — DNS for `pg.0.knoe.dev` may not have propagated
  on your network yet. Try `dig pg.0.knoe.dev`. Should resolve to
  `34.106.156.196` (or whatever the current LB IP is).

- **`server certificate for "..." does not match host name "pg.0.knoe.dev"`**
  — you're using `verify-full` but the CA cert in your repo is stale (CNPG
  rotated the CA). `git pull` and try again, or fetch the fresh cert from
  chrisfu.

---

## Related

- [`docs/db-access.md`](db-access.md) — the underlying access architecture
  (LB, pg_hba, role grants, Phase 2 plan).
- The plan file (live, not in git) at
  `~/.claude/plans/we-re-continuing-work-on-happy-toast.md` for chrisfu — has
  the design rationale for why this delivery channel was chosen over a shared
  vault.
- [`etc/onboard_engineer.sh`](../etc/onboard_engineer.sh) — the script chrisfu
  runs to provision your role and emit your URL. Same script handles
  `--rotate` and `--revoke`.