Busk: Created page with "Awesome — here’s a clean, working pattern to add username/password login → JWT → protected CRUD to the Vue + Vuetify + vue-postgrest app. It includes: SQL to create..."

2025-08-10T10:47:13Z

Created page with "Awesome — here’s a clean, working pattern to add username/password login → JWT → protected CRUD to the Vue + Vuetify + vue-postgrest app. It includes: SQL to create..."

New page

Awesome — here’s a clean, working pattern to add username/password login → JWT → protected CRUD to the Vue + Vuetify + vue-postgrest app.

It includes:

SQL to create a users table, bcrypt hashing, and an api.login RPC that returns a JWT

A Vue auth store (tiny, no Pinia needed) that persists the token

A router guard that sends unauth’d users to /login

A Login.vue page that POSTs to /rpc/login, saves the token, and redirects

Minor changes to the TodosCrud.vue calls so every request carries Authorization: Bearer <jwt>

---

1) Postgres / PostgREST: users + login RPC

-- Enable needed extensions
CREATE EXTENSION IF NOT EXISTS pgcrypto; -- for crypt() / gen_salt()
CREATE EXTENSION IF NOT EXISTS pgjwt; -- for sign()

-- Users table (bcrypt hashed password)
CREATE TABLE public.users (
id serial PRIMARY KEY,
username text UNIQUE NOT NULL,
pass_hash text NOT NULL, -- bcrypt
role text NOT NULL DEFAULT 'anon', -- app role in the JWT (e.g., 'web', 'anon')
created_at timestamptz NOT NULL DEFAULT now()
);

-- Demo user: username 'demo', password 'demo123'
INSERT INTO public.users (username, pass_hash, role)
VALUES (
'demo',
crypt('demo123', gen_salt('bf')),
'web' -- whatever role your PostgREST uses for authenticated requests
);

-- Example todos table (CRUD role grants)
CREATE TABLE public.todos(
id serial PRIMARY KEY,
title text NOT NULL,
done boolean NOT NULL DEFAULT false,
created_at timestamptz NOT NULL DEFAULT now()
);

-- Grant access to web role (and maybe read to anon if you want)
GRANT SELECT, INSERT, UPDATE, DELETE ON public.todos TO web;
-- If your PostgREST anon role is `anon`, keep this locked down for anon:
REVOKE ALL ON public.todos FROM anon;

-- JWT login RPC (returns { token: <jwt> })
-- NOTE: set your secret to exactly what PostgREST uses as PGRST_JWT_SECRET
CREATE OR REPLACE FUNCTION api.login(username text, password text)
RETURNS TABLE (token text) LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE
_user public.users%ROWTYPE;
_claims json;
_secret text := current_setting('app.jwt_secret', true); -- we'll set this GUC in postgrest
BEGIN
SELECT * INTO _user FROM public.users WHERE users.username = login.username;
IF NOT FOUND THEN
RAISE EXCEPTION 'invalid_user_or_password' USING ERRCODE = '28P01';
END IF;

IF crypt(password, _user.pass_hash) <> _user.pass_hash THEN
RAISE EXCEPTION 'invalid_user_or_password' USING ERRCODE = '28P01';
END IF;

-- JWT claims — include PostgREST-required 'role'
_claims := json_build_object(
'sub', _user.id,
'role', _user.role,
'username', _user.username,
'iat', extract(epoch from now())::int,
'exp', (extract(epoch from now())::int + 60*60*8) -- 8h
);

IF coalesce(_secret,'') = '' THEN
RAISE EXCEPTION 'jwt_secret_not_configured';
END IF;

token := sign(_claims, _secret);
RETURN NEXT;
END;
$$;

-- PostgREST config notes (postgrest.conf):
-- db-anon-role = "anon"
-- db-pre-request = "app.settings" (optional)
-- app.settings.jwt_secret = "<your-long-secret>"
-- (or set environment: PGRST_JWT_SECRET to the same value)

-- Finally, expose RPC to anon so they can log in
GRANT EXECUTE ON FUNCTION api.login(text, text) TO anon;

> Make sure PostgREST uses the same secret as the function (app.settings.jwt_secret or PGRST_JWT_SECRET). The function reads it via current_setting('app.jwt_secret', true).

---

2) Tiny auth helper (token store)

// src/auth.ts
import { ref, computed } from 'vue'

const LS_KEY = 'jwt'
const _token = ref<string | null>(localStorage.getItem(LS_KEY))

export const isAuthed = computed(() => !!_token.value)
export const token = computed({
get: () => _token.value,
set: (t: string | null) => {
_token.value = t
if (t) localStorage.setItem(LS_KEY, t)
else localStorage.removeItem(LS_KEY)
}
})

export function authHeaders() {
return token.value ? { Authorization: `Bearer ${token.value}` } : {}
}

---

3) Router with auth guard

// src/router.ts
import { createRouter, createWebHistory } from 'vue-router'
import { isAuthed } from './auth'
import TodosCrud from './components/TodosCrud.vue'
import Login from './views/Login.vue'

const routes = [
{ path: '/login', name: 'login', component: Login, meta: { public: true } },
{ path: '/', name: 'home', component: TodosCrud }, // protected
]

export const router = createRouter({
history: createWebHistory(),
routes,
})

router.beforeEach((to) => {
if (to.meta.public) return true
if (!isAuthed.value) return { name: 'login', query: { redirect: to.fullPath } }
return true
})

---

4) main.ts (Vuetify + vue-postgrest + Router)

// src/main.ts
import { createApp } from 'vue'
import App from './App.vue'

import 'vuetify/styles'
import { createVuetify } from 'vuetify'
import { aliases, mdi } from 'vuetify/iconsets/mdi'
import '@mdi/font/css/materialdesignicons.css'
const vuetify = createVuetify({ icons: { defaultSet: 'mdi', aliases, sets: { mdi } } })

import VuePostgrest from 'vue-postgrest'
import { router } from './router'

const app = createApp(App)

app.use(VuePostgrest, {
apiRoot: 'http://localhost:3000/', // your PostgREST base URL
// We’ll pass per-request headers from components so we can update after login.
})

app.use(vuetify)
app.use(router)
app.mount('#app')

---

5) Login page (POST /rpc/login, save token, redirect)


<template>
<v-container class="d-flex justify-center align-center" style="min-height: 70vh">
<v-card width="420">
<v-card-title class="text-h6">Sign in</v-card-title>
<v-card-text>
<v-form @submit.prevent="submit">
<v-text-field
v-model="username"
label="Username"
autocomplete="username"
required
/>
<v-text-field
v-model="password"
label="Password"
type="password"
autocomplete="current-password"
required
/>
<v-alert v-if="error" type="error" variant="tonal" class="mt-2">{{ error }}</v-alert>
<div class="d-flex justify-end mt-4">
<v-btn type="submit" :loading="loading" color="primary">Login</v-btn>
</div>
</v-form>
</v-card-text>
</v-card>
</v-container>
</template>

<script setup lang="ts">
import { ref } from 'vue'
import { token } from '@/auth'
import { useRouter, useRoute } from 'vue-router'

const router = useRouter()
const route = useRoute()

const username = ref('')
const password = ref('')
const loading = ref(false)
const error = ref('')

async function submit() {
loading.value = true
error.value = ''
try {
const res = await fetch('http://localhost:3000/rpc/login', {
method: 'POST',
headers: { 'Content-Type': 'application/json', 'Accept': 'application/json' },
body: JSON.stringify({ username: username.value, password: password.value }),
})
if (!res.ok) {
const t = await res.text()
throw new Error(t || `Login failed (${res.status})`)
}
const json = await res.json() as { token: string }[] | { token: string }
// PostgREST returns rows; handle both shapes safely
const tok = Array.isArray(json) ? json[0]?.token : (json as any)?.token
if (!tok) throw new Error('No token in response')
token.value = tok

const to = (route.query.redirect as string) || '/'
router.replace(to)
} catch (e: any) {
error.value = e?.message || 'Login failed'
} finally {
loading.value = false
}
}
</script>

---

6) Add a Logout button (optional)

In App.vue (or a small TopBar), add:


<template>
<v-app>
<v-app-bar flat>
<v-toolbar-title>Todos</v-toolbar-title>
<v-spacer />
<v-btn v-if="isAuthed" variant="text" @click="logout">Logout</v-btn>
</v-app-bar>
<v-main>
<router-view />
</v-main>
</v-app>
</template>

<script setup lang="ts">
import { isAuthed, token } from './auth'
import { useRouter } from 'vue-router'
const router = useRouter()
function logout() {
token.value = null
router.replace('/login')
}
</script>

---

7) Update TodosCrud.vue to send Authorization on each request

We’ll reuse the same component you already had, but add headers: authHeaders() wherever we call PostgREST:

<script lang="ts">
import { defineComponent } from 'vue'
import { pg } from 'vue-postgrest'
+import { authHeaders } from '@/auth'

export default defineComponent({
name: 'TodosCrud',
mixins: [pg],
data() {
return {
pgConfig: {
route: 'todos',
query: { select: ['id','title','done','created_at'], order: { id: 'asc' } },
count: 'exact',
},
// ...rest unchanged
}
},
methods: {
refresh() {
- (this as any).pg.$get()
+ (this as any).pg.$get({ headers: authHeaders() })
},
openCreate() {
- this.dialog.model = (this as any).pg.$new({ title: '', done: false })
+ this.dialog.model = (this as any).pg.$new({ title: '', done: false })
this.dialog.mode = 'create'
this.errorMessage = ''
this.dialog.open = true
},
openEdit(item: any) {
this.dialog.model = (this as any).pg.$new({
id: item.id, title: item.title, done: item.done
})
this.dialog.mode = 'edit'
this.errorMessage = ''
this.dialog.open = true
},
async submitDialog() {
this.saving = true
this.errorMessage = ''
try {
if (this.dialog.mode === 'create') {
- await this.dialog.model.$post({ return: 'minimal' })
+ await this.dialog.model.$post({ return: 'minimal', headers: authHeaders() })
} else {
- await this.dialog.model.$patch()
+ await this.dialog.model.$patch({ headers: authHeaders() })
}
this.closeDialog()
this.refresh()
} catch (e: any) {
this.errorMessage = e?.message || 'Save failed'
} finally {
this.saving = false
}
},
async saveInline(item: any) {
try {
- await item.$patch({ columns: ['done'] })
+ await item.$patch({ columns: ['done'], headers: authHeaders() })
} catch (e) {
item.done = !item.done
}
},
async doDelete() {
if (!this.confirm.item) return
this.saving = true
try {
- await this.confirm.item.$delete()
+ await this.confirm.item.$delete({ headers: authHeaders() })
this.confirm.open = false
this.refresh()
} finally {
this.saving = false
}
},
},
mounted() {
// initial load with headers
this.refresh()
}
})
</script>

> This keeps it explicit and guarantees every request carries the latest token. If vue-postgrest in your version supports a global headers setter, you can centralize this, but the above is bulletproof.

---

That’s it

Go to /login, sign in as demo / demo123

You’ll be redirected to /, where all todos requests now include your JWT

Use Add / Edit / Delete / Toggle Done; all calls are authorized

If you want, I can also wire up server-side pagination in the Vuetify v-data-table that drives limit/offset in pg.$get({ range: { limit, offset }}) and uses pg.$range.totalCount for the total row count.

Vuetify login - Revision history

Busk: Created page with "Awesome — here’s a clean, working pattern to add username/password login → JWT → protected CRUD to the Vue + Vuetify + vue-postgrest app. It includes: SQL to create..."