Skip to content

Commit 8ecf60f

Browse files
HMarzbanclaude
HMarzban
and
claude
committed
fix(supabase): hide user status and last-active time from other users
Any visitor could read users.status and users.online_at. They could also watch both columns change over Realtime. That showed when each person was online. A new migration revokes the anon and authenticated SELECT grants on both columns. It also takes public.users out of supabase_realtime and drops the connected-app policy that only the publication needed. A fail-closed block stops the apply if either column is still readable. The block also stops the apply if the heartbeat loses its UPDATE grant on status. Scripts 13, 17 and 29 mirror the change, and seed.sql is regenerated. The webapp profile read no longer selects either column, so the heartbeat writes status and reads nothing back. The admin Users page drops its users subscription and its Live badge, and it refreshes on demand. Hocuspocus drops status from owner and author profiles, because the service role sent it to anon. Deploy the apps first. Apply the migration by hand at least 24 hours later. Refs #434 Co-Authored-By: Claude Opus 5.5 <[email protected]>
1 parent 73e5bd9 commit 8ecf60f

19 files changed

Lines changed: 97 additions & 85 deletions

File tree

‎apps/admin-dashboard/src/pages/users.tsx‎

Lines changed: 3 additions & 31 deletions
Original file line numberDiff line numberDiff line change
@@ -1,9 +1,9 @@
11
import { useMutation, useQuery, useQueryClient } from '@tanstack/react-query'
22
import type { GetServerSideProps } from 'next'
33
import Head from 'next/head'
4-
import { useCallback, useEffect, useMemo, useRef, useState } from 'react'
4+
import { useCallback, useEffect, useMemo, useState } from 'react'
55
import toast from 'react-hot-toast'
6-
import { LuCircle, LuFileText, LuRadio, LuShieldCheck, LuShieldOff } from 'react-icons/lu'
6+
import { LuCircle, LuFileText, LuShieldCheck, LuShieldOff } from 'react-icons/lu'
77

88
import { SectionCard } from '@/components/cards/SectionCard'
99
import { AdminLayout } from '@/components/layout/AdminLayout'
@@ -12,7 +12,6 @@ import { DataTable } from '@/components/tables/DataTable'
1212
import { Avatar } from '@/components/ui/Avatar'
1313
import { NotificationBadges } from '@/components/ui/NotificationBadges'
1414
import { SearchInput } from '@/components/ui/SearchInput'
15-
import { useRealtimeSubscription } from '@/hooks/useRealtimeSubscription'
1615
import { useTableParams } from '@/hooks/useTableParams'
1716
import { supabase } from '@/lib/supabase'
1817
import {
@@ -105,26 +104,6 @@ export default function UsersPage() {
105104
})
106105
}, [data?.data, docCounts, notifSubs, adminIdSet])
107106

108-
// Real-time subscription to users table. Webapp presence/status writes fire
109-
// often, so debounce the refetch to collapse bursts into one query against
110-
// the shared DB instead of one per event.
111-
const refetchTimer = useRef<ReturnType<typeof setTimeout> | null>(null)
112-
const handleRealtimeChange = useCallback(() => {
113-
if (refetchTimer.current) clearTimeout(refetchTimer.current)
114-
refetchTimer.current = setTimeout(() => refetch(), 1500)
115-
}, [refetch])
116-
useEffect(
117-
() => () => {
118-
if (refetchTimer.current) clearTimeout(refetchTimer.current)
119-
},
120-
[]
121-
)
122-
123-
useRealtimeSubscription({
124-
table: 'users',
125-
onChange: handleRealtimeChange
126-
})
127-
128107
const handleExport = () => {
129108
if (!usersWithExtras.length) {
130109
toast.error('No data to export')
@@ -300,14 +279,7 @@ export default function UsersPage() {
300279
<AdminLayout>
301280
<Header
302281
title="Users"
303-
subtitle={
304-
<span className="flex items-center gap-2">
305-
{data?.total || 0} total users
306-
<span className="badge badge-success badge-xs gap-1">
307-
<LuRadio className="h-3 w-3" /> Live
308-
</span>
309-
</span>
310-
}
282+
subtitle={`${data?.total || 0} total users`}
311283
onRefresh={() => refetch()}
312284
refreshing={isRefetching}
313285
onExport={handleExport}

‎apps/hocuspocus.server/API.md‎

Lines changed: 2 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -476,8 +476,7 @@ Metadata for the document's versions, newest first. No snapshot bytes — read t
476476
"avatar_url": "https://example.test/ada.png",
477477
"avatar_updated_at": "2026-07-01T09:12:00.000Z",
478478
"full_name": "Ada Lovelace",
479-
"display_name": "Ada",
480-
"status": "online"
479+
"display_name": "Ada"
481480
},
482481
"contributors": [],
483482
"createdAt": "2026-07-25T13:58:40.117Z"
@@ -739,8 +738,7 @@ It reads Postgres in the REST process and never opens the live document. Neither
739738
"avatar_url": "https://example.test/ada.png",
740739
"avatar_updated_at": "2026-07-01T09:12:00.000Z",
741740
"full_name": "Ada Lovelace",
742-
"display_name": "Ada",
743-
"status": "online"
741+
"display_name": "Ada"
744742
}
745743
]
746744
}

‎apps/hocuspocus.server/CHANGELOG.md‎

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -28,6 +28,14 @@ This file is the operator and API changelog. The pad product lives in the [root
2828
Supabase migration `20261009120400_admin_revoke_admin` before this server
2929
ships. Without it, every admin revoke answers `500`. Then check that only
3030
`service_role` can run `public.admin_revoke_admin(uuid)`.
31+
**User status columns ([#434](https://github.com/docs-plus/docs.plus/issues/434)).** Ship the
32+
webapp, this server and the admin dashboard first. Wait at least 24 hours,
33+
then apply the Supabase migration `20261009120500_hide_user_presence_columns`
34+
by hand. An older webapp tab still selects `status`, so an early migration
35+
breaks its sign-in and its heartbeat. After the push, check that
36+
`has_column_privilege` is false for `anon` and `authenticated` on
37+
`users.status` and `users.online_at`. Also check that `pg_publication_tables`
38+
has no `users` row.
3139

3240
**Media objects ([#408](https://github.com/docs-plus/docs.plus/issues/408)).** New S3 uploads are
3341
private. Objects written before this release stay `public-read` until you
@@ -641,6 +649,11 @@ SMTP_PASS=...
641649
links. It sets the username to `deleted_` plus the first 12 hex characters
642650
of the id, and removes the user's `user_avatars` objects. Messages keep their
643651
author, who shows the placeholder name and the default avatar.
652+
- **Owner and author profiles no longer carry `status`
653+
([#434](https://github.com/docs-plus/docs.plus/issues/434)).** The
654+
`ownerProfile` on `GET /api/documents/:docName`, the documents list `owner`,
655+
and the `history.list` profiles leave it out. Anyone could read the first of
656+
these without signing in. No client read the field.
644657

645658
### Removed
646659

‎apps/hocuspocus.server/src/api/services/documents.service.ts‎

Lines changed: 1 addition & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -510,8 +510,7 @@ export const searchDocuments = async (prisma: PrismaClient, params: SearchDocume
510510
id: ownerProfile.id,
511511
avatar_url: ownerProfile.avatar_url,
512512
avatar_updated_at: ownerProfile.avatar_updated_at,
513-
display_name: ownerProfile.display_name || ownerProfile.full_name,
514-
status: ownerProfile.status
513+
display_name: ownerProfile.display_name || ownerProfile.full_name
515514
}
516515
}
517516
})

‎apps/hocuspocus.server/src/lib/profiles.ts‎

Lines changed: 2 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -12,13 +12,12 @@ export interface ProfileLite {
1212
avatar_updated_at: string | null
1313
full_name: string | null
1414
display_name: string | null
15-
status: string | null
1615
}
1716

1817
export type GetOwnerProfiles = (userIds: string[]) => Promise<ProfileLite[]>
1918

20-
/** The exact column list every attribution surface reads. */
21-
const OWNER_PROFILE_COLUMNS = 'id, avatar_url, avatar_updated_at, full_name, display_name, status'
19+
/** The exact column list every attribution surface reads. Never add status or online_at (#434). */
20+
const OWNER_PROFILE_COLUMNS = 'id, avatar_url, avatar_updated_at, full_name, display_name'
2221

2322
/**
2423
* Contractually fail-soft: `[]` rather than a throw. Attribution decorates a

‎apps/hocuspocus.server/src/modules/document-changes/__tests__/integration/changesRoutes.test.ts‎

Lines changed: 1 addition & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -95,8 +95,7 @@ const harness = (options: { rows?: Row[]; missing?: boolean; profilesThrow?: boo
9595
avatar_url: null,
9696
avatar_updated_at: null,
9797
full_name: null,
98-
display_name: id,
99-
status: null
98+
display_name: id
10099
}))
101100
}
102101
}),

‎apps/hocuspocus.server/src/modules/document-changes/__tests__/unit/computeDocumentChanges.test.ts‎

Lines changed: 1 addition & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -41,8 +41,7 @@ describe('createComputeDocumentChanges', () => {
4141
avatar_url: null,
4242
avatar_updated_at: null,
4343
full_name: null,
44-
display_name: null,
45-
status: null
44+
display_name: null
4645
})
4746
const second = await summaryOf()
4847
expect(second.triggers).toEqual([])

‎apps/hocuspocus.server/src/modules/document-versions/__tests__/integration/versionRoutes.test.ts‎

Lines changed: 1 addition & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -49,8 +49,7 @@ const profile = (id: string): ProfileLite => ({
4949
avatar_url: null,
5050
avatar_updated_at: null,
5151
full_name: `Full ${id}`,
52-
display_name: `Display ${id}`,
53-
status: 'online'
52+
display_name: `Display ${id}`
5453
})
5554

5655
interface Harness {

‎apps/webapp/CHANGELOG.md‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -574,6 +574,11 @@ plus the house order in [`RELEASE_POLICY.md`](../../RELEASE_POLICY.md).
574574
`channel_avatars` buckets accept JPEG, PNG, GIF and WebP only. Settings
575575
converts other images, or shows an error
576576
([#431](https://github.com/docs-plus/docs.plus/issues/431)).
577+
- **The profile read no longer asks for online status or last-active time.**
578+
The status heartbeat still writes `status` and does not read it back.
579+
Deploy this at least 24 hours before the Supabase migration. Otherwise, an
580+
older open tab fails to sign in
581+
([#434](https://github.com/docs-plus/docs.plus/issues/434)).
577582

578583
### Removed
579584

‎apps/webapp/src/api/users/getUserById.ts‎

Lines changed: 9 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -2,17 +2,19 @@ import { PostgrestSingleResponse } from '@supabase/supabase-js'
22
import { Database } from '@types'
33
import { supabaseClient } from '@utils/supabase'
44

5-
// `notification_preferences` has no client grant; see get_notification_preferences().
6-
export type TUser = Omit<Database['public']['Tables']['users']['Row'], 'notification_preferences'>
5+
// No client can read these columns; get_notification_preferences() serves the owner.
6+
export type TUser = Omit<
7+
Database['public']['Tables']['users']['Row'],
8+
'notification_preferences' | 'status' | 'online_at'
9+
>
710

811
/**
9-
* `email` is intentionally excluded from the column-level GRANT on
10-
* `public.users` (see scripts/29-lint-hardening.sql §3). `select('*')`
11-
* fails for both anon and authenticated; the whitelist below mirrors
12-
* the GRANT so the query lands.
12+
* The column-level GRANT on `public.users` leaves out `email`, `status` and
13+
* `online_at` (#434), so `select('*')` fails for anon and authenticated. This
14+
* list mirrors the GRANT. `updateUser` reuses it, so the heartbeat never reads status back.
1315
*/
1416
export const USER_PROFILE_COLUMNS =
15-
'id, username, full_name, display_name, avatar_url, avatar_updated_at, profile_data, status, online_at, created_at, updated_at, deleted_at'
17+
'id, username, full_name, display_name, avatar_url, avatar_updated_at, profile_data, created_at, updated_at, deleted_at'
1618

1719
export const getUserById = async (userId: string): Promise<PostgrestSingleResponse<TUser>> => {
1820
// `maybeSingle()` returns `data: null` when the row is missing, instead of throwing

0 commit comments

Comments
 (0)