Andrew S Erwin
4e421f6ea2
release gate / check (push) Failing after 6m43s
High severity: - wire TRUSTED_PROXY through Compose/deploy/docs and warn at web startup - bucket public rate limits on effective client IP, add per-bookmark budget, and forward the signed browser identity from the share page - disable first-run bootstrap in server deployments and document seeding - serialize last-admin demotion in one advisory-locked transaction Medium/low: - cap and batch share recipient inserts - add a shared outbound budget for preview/CardDAV/IMAP/signal work - pin CardDAV dials to 443 and bound IMAP logout - fix rightward kanban column drags and normalize cards on column delete - classify backend outages in auth guards, add request timeouts and an error boundary, remove the dead login route, surface throttling - enforce pinned/unpinned page reorder grouping server-side - avoid preview work for stale bookmark versions; drop duplicate dialog fetch - sequence-guard collection/tag/calendar/quick-find loads - validate property config and fix MULTI_SELECT/DATE filter inputs - document index maintenance; apply same-origin check to sign-out - trim signup names, unique shared dates and kanban keys (migration 010), reset reference cache on identity changes, hide OIDC issuer, return 413 for oversized direct bodies, escape Markdown link destinations - remove dead template files and duplicate test helper Verified with go build/vet, full PostgreSQL race suite, db convergence, tsc, both ESLint configs and a Next production build.
55 lines
4.2 KiB
Go
55 lines
4.2 KiB
Go
package db
|
|
|
|
// PostgreSQL builds the camelCase JSON shape consumed by the existing UI.
|
|
// The password-bearing User and ImapConfig rows are never embedded.
|
|
//
|
|
// Review PRIV-02: bookmark projections are permission-specific. Permission to
|
|
// read a bookmark (direct share, shared tag, or shared date) does not imply
|
|
// permission to read the owner's address-book entry, enumerate the other
|
|
// recipients, or see the owner's operational metadata (alerts, mailbox watch,
|
|
// board placement). BookmarkSharedView is an allowlist: new columns are NOT
|
|
// exposed to shared readers without a deliberate addition there.
|
|
|
|
// BookmarkOwnerView is the full projection, for the owner only. It embeds the
|
|
// attached contact record and the recipient list with names and emails — the
|
|
// share panel renders those for the owner.
|
|
const BookmarkOwnerView = `to_jsonb(b) || jsonb_build_object(
|
|
'tags',COALESCE((SELECT jsonb_agg(to_jsonb(bt)||jsonb_build_object('tag',to_jsonb(t))) FROM "BookmarkTag" bt JOIN "Tag" t ON t.id=bt."tagId" WHERE bt."bookmarkId"=b.id),'[]'::jsonb),
|
|
'contact',(SELECT to_jsonb(c) FROM "Contact" c WHERE c.id=b."contactId"),
|
|
'sharedWith',COALESCE((SELECT jsonb_agg(to_jsonb(s)||jsonb_build_object('user',jsonb_build_object('id',u.id,'name',u.name,'email',u.email))) FROM "BookmarkShare" s JOIN "User" u ON u.id=s."userId" WHERE s."bookmarkId"=b.id),'[]'::jsonb),
|
|
'owner',(SELECT jsonb_build_object('id',u.id,'name',u.name,'email',u.email,'image',u.image) FROM "User" u WHERE u.id=b."ownerId"))`
|
|
|
|
// BookmarkSharedView is the narrowed projection for authenticated non-owner
|
|
// readers: the link itself, its presentation metadata, the content the owner
|
|
// attached for readers (description, notes, due date, tags), and enough owner
|
|
// identity to attribute the share inside the workspace. No contact record, no
|
|
// recipient list, no alerts, no mailbox-watch settings, no board placement
|
|
// (kanbanColumnId/kanbanPosition). The legacy kanbanStatus badge IS included
|
|
// deliberately: it is status, not placement, and API.md/web/AGENTS.md say so
|
|
// (review L-10).
|
|
const BookmarkSharedView = `jsonb_build_object(
|
|
'id',b.id,'url',b.url,'title',b.title,'description',b.description,'notes',b.notes,
|
|
'favicon',b.favicon,'ogImage',b."ogImage",'ogTitle',b."ogTitle",'ogDescription',b."ogDescription",
|
|
'dueDate',b."dueDate",'isPublic',b."isPublic",'kanbanStatus',b."kanbanStatus",
|
|
'ownerId',b."ownerId",'createdAt',b."createdAt",'updatedAt',b."updatedAt",
|
|
'tags',COALESCE((SELECT jsonb_agg(to_jsonb(bt)||jsonb_build_object('tag',to_jsonb(t))) FROM "BookmarkTag" bt JOIN "Tag" t ON t.id=bt."tagId" WHERE bt."bookmarkId"=b.id),'[]'::jsonb),
|
|
'owner',(SELECT jsonb_build_object('id',u.id,'name',u.name) FROM "User" u WHERE u.id=b."ownerId"))`
|
|
|
|
// BookmarkViewerView picks the projection by ownership. Like BookmarkVisible
|
|
// it binds the requesting user as $2, so it drops into the same queries.
|
|
const BookmarkViewerView = `CASE WHEN b."ownerId"=$2 THEN ` + BookmarkOwnerView + ` ELSE ` + BookmarkSharedView + ` END`
|
|
|
|
// BookmarkVisible binds the requesting user as $2. Tag sharing is group-wide
|
|
// by product decision: any recipient of a tag sees every bookmark carrying
|
|
// it, including bookmarks other recipients added. The share UI says so.
|
|
const BookmarkVisible = `(b."ownerId"=$2 OR EXISTS(SELECT 1 FROM "BookmarkShare" s WHERE s."bookmarkId"=b.id AND s."userId"=$2)
|
|
OR EXISTS(SELECT 1 FROM "BookmarkTag" bt JOIN "TagShare" ts ON ts."tagId"=bt."tagId" WHERE bt."bookmarkId"=b.id AND ts."userId"=$2)
|
|
-- Explicit collection sharing (trust phase): membership in a collection the
|
|
-- bookmark's OWNER deliberately shared grants the narrowed bookmark
|
|
-- projection. The owner match is essential: a collection can contain
|
|
-- bookmarks merely shared with its owner, and sharing that collection must
|
|
-- not widen a third party's grant. Removing the item or revoking the share
|
|
-- removes this edge immediately.
|
|
OR EXISTS(SELECT 1 FROM "CollectionItem" ci JOIN "Collection" c ON c.id=ci."collectionId" JOIN "CollectionShare" cs ON cs."collectionId"=ci."collectionId" WHERE ci."bookmarkId"=b.id AND cs."userId"=$2 AND c."ownerId"=b."ownerId")
|
|
OR EXISTS(SELECT 1 FROM "SharedDate" sd WHERE sd."creatorId"=b."ownerId" AND sd."recipientId"=$2 AND (b."dueDate" AT TIME ZONE 'UTC')::date=sd.date))`
|