Auth and OAuth Tutorial

Auth and OAuth Tutorial

Available since: v3.8.0 (2026-04-02) Build: default — no feature flag required Endpoints: /auth/v1/{signup,token,logout,refresh,user,authorize,callback}

---

UVP

Authentication is normally a separate service: GoTrue, Auth0, Cognito, Firebase. HeliosDB Nano serves the full Supabase-compatible auth surface from the same binary as the database. Argon2id password hashing, JWT bearer tokens, refresh-token rotation, and OAuth2 PKCE for Google + GitHub all live in _auth_users and _auth_refresh_tokens — real DB tables you can audit, branch, and dump. No second process. No drift between user records and the data they own. RLS policies on the REST API read directly from JWT claims.

---

Prerequisites

• HeliosDB Nano v3.8+ (heliosdb-nano --version)
• curl and jq for HTTP examples
• Optional: Google or GitHub OAuth client credentials for §6

---

1. Start the Server

heliosdb-nano start --memory --auth scram-sha-256 --password s3cret

Three protocols come up on the same process. Auth lives on the HTTP listener (default localhost:8080).

Protocol	Port	Auth surface
PostgreSQL wire	5432	SCRAM-SHA-256 (server password from --password)
MySQL wire	3306	mysql_native_password / caching_sha2_password
HTTP / REST	8080	/auth/v1/* — JWT bearer for end users

The wire-protocol auth (--auth scram-sha-256) and the HTTP /auth/v1/* flow are independent. The first is for clients connecting to the database; the second is for end users of your app.

---

2. Sign Up — POST /auth/v1/signup

curl -s -X POST http://localhost:8080/auth/v1/signup \
  -H 'Content-Type: application/json' \
  -d '{"email":"alice@example.com","password":"hunter2!"}' | jq

{
  "access_token": "eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9...",
  "refresh_token": "9b8c2d1e-...",
  "user": {
    "id": "01J7QXR...",
    "email": "alice@example.com",
    "role": "authenticated",
    "created_at": "2026-04-26T10:00:00Z"
  },
  "expires_in": 3600
}

What just happened on the server:

1. Validated email format and rejected passwords < 6 chars.
2. Generated a fresh Argon2id salt and stored encrypted_password in _auth_users.
3. Issued an HS256 JWT with sub, email, role, aud, exp, iat, jti claims.
4. Issued a refresh token row in _auth_refresh_tokens with a 7-day expiry.

Verify with SQL:

psql "postgresql://postgres:s3cret@127.0.0.1:5432/postgres" \
  -c "SELECT id, email, role, created_at FROM _auth_users;"

The encrypted_password column holds the Argon2id PHC string ($argon2id$v=19$m=...$...). Plaintext is never stored or logged.

---

3. Sign In — POST /auth/v1/token

Same shape as signup, returns the same AuthSession:

SESSION=$(curl -s -X POST http://localhost:8080/auth/v1/token \
  -H 'Content-Type: application/json' \
  -d '{"email":"alice@example.com","password":"hunter2!"}')

ACCESS=$(echo "$SESSION" | jq -r .access_token)
REFRESH=$(echo "$SESSION" | jq -r .refresh_token)

echo "Bearer $ACCESS"

The route is named /auth/v1/token to match Supabase / GoTrue. Failed sign-in returns 401 Unauthorized with error: "invalid_credentials".

---

4. Authenticated Calls

Get the current user — GET /auth/v1/user

curl -s http://localhost:8080/auth/v1/user \
  -H "Authorization: Bearer $ACCESS" | jq

{
  "id": "01J7QXR...",
  "email": "alice@example.com",
  "role": "authenticated",
  "created_at": "2026-04-26T10:00:00Z"
}

If the token is missing, expired, or signature-mismatched, the endpoint returns 401 Unauthorized.

Pass the token to the REST API

curl -s "http://localhost:8080/rest/v1/notes" \
  -H "Authorization: Bearer $ACCESS" | jq

JWT claims populate the session context so RLS policies like USING (owner = current_setting('jwt.email', true)) evaluate per-request. See BAAS_REST_API §9 for a complete RLS walkthrough.

---

5. Refresh and Logout

Refresh — POST /auth/v1/refresh

Exchange a long-lived refresh token for a new access token. The old refresh token is rotated (revoked) — repeat use returns 401 invalid_refresh_token.

NEW=$(curl -s -X POST http://localhost:8080/auth/v1/refresh \
  -H 'Content-Type: application/json' \
  -d "{\"refresh_token\":\"$REFRESH\"}")

ACCESS=$(echo "$NEW" | jq -r .access_token)
REFRESH=$(echo "$NEW" | jq -r .refresh_token)

Logout — POST /auth/v1/logout

Revokes the refresh token. The access token remains valid until it expires (server-side revocation list is not part of v3.8 — short access-token lifetimes are the mitigation).

curl -s -X POST http://localhost:8080/auth/v1/logout \
  -H 'Content-Type: application/json' \
  -d "{\"refresh_token\":\"$REFRESH\"}"
# 204 No Content

---

6. OAuth2 — Google and GitHub via PKCE

The OAuth flow follows the standard Authorization Code + PKCE dance with Google or GitHub as the IdP. Nano stores the per-flow PKCE verifier in memory keyed by the state parameter, exchanges the code for an ID token + userinfo, and upserts the user into _auth_users. The user gets a normal HeliosDB session (access + refresh) so subsequent calls are indistinguishable from password-based sign-in.

a. Configure providers at startup

OAuth client IDs and secrets come from environment variables:

export OAUTH_GOOGLE_CLIENT_ID=...apps.googleusercontent.com
export OAUTH_GOOGLE_CLIENT_SECRET=GOCSPX-...
export OAUTH_GOOGLE_REDIRECT_URI=http://localhost:8080/auth/v1/callback

export OAUTH_GITHUB_CLIENT_ID=Iv1....
export OAUTH_GITHUB_CLIENT_SECRET=...
export OAUTH_GITHUB_REDIRECT_URI=http://localhost:8080/auth/v1/callback

heliosdb-nano start --memory

Provider endpoints used (hard-coded in src/api/oauth.rs):

Provider	Auth URL	Token URL	Userinfo URL	Default scopes
google	accounts.google.com/o/oauth2/v2/auth	oauth2.googleapis.com/token	googleapis.com/oauth2/v3/userinfo	email, profile
github	github.com/login/oauth/authorize	github.com/login/oauth/access_token	api.github.com/user	read:user, user:email

b. Authorize — GET /auth/v1/authorize?provider=...

Have the user’s browser visit:

http://localhost:8080/auth/v1/authorize?provider=google

The endpoint returns a 307 Temporary Redirect to the provider’s login page with the PKCE challenge in the URL. The state value is stored server-side and matched on the callback.

c. Callback — GET /auth/v1/callback?code=...&state=...

The provider redirects the user back here. The handler:

1. Looks up the PKCE verifier by state (rejects with 401 invalid_state if unknown).
2. Exchanges code for an access token at the provider’s token endpoint.
3. Fetches the userinfo endpoint and extracts email, name, avatar_url, provider_id.
4. Upserts the user into _auth_users (linking by email).
5. Returns the same AuthSession JSON shape as /auth/v1/token.

{
  "access_token": "eyJ...",
  "refresh_token": "...",
  "user": {
    "id": "01J7QXR...",
    "email": "alice@gmail.com",
    "role": "authenticated",
    "created_at": "2026-04-26T10:00:00Z"
  },
  "expires_in": 3600
}

d. Front-end pattern

Build a “Sign in with Google” button that opens /auth/v1/authorize?provider=google in a popup. After Google redirects to your callback, parse the JSON and stash the access token in localStorage or an httpOnly cookie (your choice — this is now standard JWT handling).

---

7. JWT Anatomy

Decode the access token at jwt.io or with jq:

echo "$ACCESS" | cut -d. -f2 | base64 -d 2>/dev/null | jq

{
  "sub": "01J7QXR...",
  "email": "alice@example.com",
  "role": "authenticated",
  "aud": "authenticated",
  "exp": 1745676000,
  "iat": 1745672400,
  "jti": "9d8c..."
}

Claim	Purpose
sub	User UUID — primary key in _auth_users
email	Convenience copy; mirrors the row
role	Default authenticated; surface for future role-aware policies
aud	authenticated (Supabase compatibility)
exp / iat	Unix-second timestamps
jti	Unique per token; lets you keep distinct tokens within the same second

The signing key comes from $JWT_SECRET (env var). Default is the dev string heliosdb-jwt-secret-change-in-production — set a real value in production.

---

8. SCRAM at the Wire Layer

When you launched the server with --auth scram-sha-256 --password s3cret, every PostgreSQL wire client (psql, psycopg2, JDBC, pgx, postgres-js) negotiates SCRAM-SHA-256 on the connection itself. This is independent of the JWT flow above.

psql "postgresql://postgres:s3cret@127.0.0.1:5432/postgres"

Other accepted methods: trust (no auth), password (cleartext — discouraged), md5 (legacy PG), scram-sha-256 (recommended).

MySQL wire authentication uses mysql_native_password and caching_sha2_password — see MYSQL_WIRE.

---

Troubleshooting

Symptom	Cause	Fix
503 auth_not_enabled on /auth/v1/*	AuthBridge not configured (no _auth_users table)	Restart with the BaaS server enabled — the table is bootstrapped on first start
401 invalid_token immediately after signup	JWT secret rotated between issue and verify	Persist $JWT_SECRET across restarts, or accept that in-memory mode loses sessions
401 invalid_refresh_token after a refresh	Old token reused (rotation invalidates it)	Always use the new refresh_token from the most recent response
503 oauth_not_configured	Provider env vars not set at startup	Export OAUTH_GOOGLE_* / OAUTH_GITHUB_* before start
401 invalid_state on callback	Direct hit on /callback (no preceding /authorize), or in-memory state lost across restart	Always start the flow at /auth/v1/authorize?provider=...

---

Where Next

• BAAS_REST_API — REST surface, RLS via JWT claims, 20 filter operators.
• REALTIME_WEBSOCKET — pass the same JWT to the WebSocket endpoint.
• SWAGGER_UI_QUICKSTART — try every auth call from the browser.
• JWT_AUTH_QUICK_REFERENCE — claim-by-claim reference card.
• SCRAM_QUICK_REFERENCE — wire-protocol auth methods.