Close the remaining 47 linter warnings on functions #318

Key: SPR-07 · Type: AFK · Part of: #327 · Blocked by: #314 · Checked against: 3bb26de

Problem

Prod still has 47 anon_security_definer_function_executable warnings (lint 0028). Anon holds EXECUTE on 17 SECURITY DEFINER RPCs, 29 trigger functions and one event-trigger function.

• The 17 RPCs check auth.uid() or is_admin in the repo body, so anon gets an error or zero rows. They should still not be open to anon. One missed gate in a future body change would expose data.
• The 30 trigger and event-trigger functions cannot be called through PostgREST. The grant is hygiene, but it keeps the linter noisy, and real findings hide in the noise.

The 2026-09-28 emergency fix left all 47 open on purpose. It changed only the functions with no gate. This issue closes the rest with grants only. It changes no function body.

Context

Where the 47 come from. Prod pg_default_acl (role postgres, schema public) gives anon and authenticated EXECUTE on every new function. CREATE FUNCTION also grants EXECUTE to PUBLIC. REVOKE ... FROM anon does not remove the PUBLIC grant, and anon inherits it. Many scripts revoke only one half:

scripts/29-lint-hardening.sql §5 (~161-184) revokes every definer function from public, anon, authenticated. §6 (~198-254) grants the browser list back to authenticated. No migration carries §5 or §6, so prod never ran them. The local seed runs 29 after every script that defines these functions, so a local database already looks clean.

Measured on a local reset, 2026-09-28 (read-only catalog query on supabase_db_docsplus_supabase):

• Anon cannot execute any of the 46 repo names below. rls_auto_enable does not exist locally.
• The 15 signed-in RPCs have authenticated=X and service_role=X.
• admin_get_user_notification_subs is already service_role only.
• Two names differ from the target. get_bookmark_count still has authenticated=X; Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314 removes it from §6. update_user_online_at is a trigger function, yet 29 §6 (~218) grants it to authenticated. This issue removes that entry.
• Locally a 30th definer trigger function exists: update_message_bookmarks_updated_at (06-message-bookmarks.sql ~35, made SECURITY DEFINER at ~82). It is not on the prod lint list, so prod probably has it as invoker. Block B of the migration covers it either way.

So local is the target state, and only prod lags. The durable fix is a paired migration, plus script edits that make each file correct without relying on 29.

Group A: 17 self-gated RPCs (repo bodies; nobody has read the prod bodies).

Caller audit for guests. A revoke turns an anon call into 42501. Every caller except one already gates on a signed-in user:

• useJoinWorkspace.ts ~31 returns early without userId.
• DocumentSettingsPanel.tsx ~63-71 enables follow only for a signed-in member.
• useDocumentMembers is called with !!userId (DocumentsSection.tsx ~159).
• useBookmarkPanelFeed.ts ~42 returns early without user.
• BookmarkButton.tsx is disabled without profile.
• The gap: apps/webapp/src/components/TipTap/hyperlinkPopovers/hooks/useHyperlinkSuggestions.ts ~50 sets queriesEnabled = Boolean(workspaceId) && !disabled. It does not check userId (~28). A guest who opens the link picker calls get_user_bookmarks. Locally anon already lacks EXECUTE, so this 42501 error already shows on localhost today.

The fix breaks one existing spec unless you also change it. apps/webapp/cypress/e2e/editor/hyperlink/hyperlink-picker.cy.js runs on the /editor playground with no signed-in profile. It waits for @getUserBookmarks (for example ~118, ~133, ~304). With a userId gate that request never fires, so the spec times out. The helper setupBookmarkSuggestions (apps/webapp/cypress/support/hyperlinkPicker.ts ~88-107) seeds only workspaceId through window._store. The playground exposes window._store = useStore at apps/webapp/src/components/pages/editor/EditorPlayground.tsx ~96. It does not expose the auth store.

Group B: 29 trigger functions and one event-trigger function. All are SECURITY DEFINER, owned by postgres locally, and return trigger, except where noted.

Why a trigger-function revoke is safe.

• Documented: Postgres checks EXECUTE on the trigger function when CREATE TRIGGER runs. See https://www.postgresql.org/docs/15/sql-createtrigger.html, section Notes.
• Measured, not documented: Postgres does not check EXECUTE when the trigger fires. A local rolled-back probe on 2026-09-28 showed it. The trigger fired for authenticated after all EXECUTE grants were revoked. Re-run it yourself:

begin;
create table public.__trg_probe(id int, note text);
grant insert, select on public.__trg_probe to authenticated;
create function public.__trg_probe_fn() returns trigger language plpgsql security definer
  set search_path = public as $$ begin new.note := 'fired'; return new; end $$;
create trigger __trg_probe_t before insert on public.__trg_probe
  for each row execute function public.__trg_probe_fn();
revoke all on function public.__trg_probe_fn() from public, anon, authenticated;
select has_function_privilege('authenticated', 'public.__trg_probe_fn()', 'EXECUTE'); -- f
set local role authenticated;
insert into public.__trg_probe values (1, null);
select * from public.__trg_probe;  -- note = 'fired'
reset role;
rollback;

• Measured: the event trigger. Only a superuser can create an event trigger. Locally that is supabase_admin. A rolled-back local probe on 2026-09-28 showed the event trigger still fires for a role without EXECUTE on its function. Run it as supabase_admin over TCP, with the Supabase CLI local database password. Use docker exec -i supabase_db_docsplus_supabase psql -h 127.0.0.1 -U supabase_admin -d postgres:

begin;
create role __evt_probe_role nologin;
create schema __evt_probe authorization __evt_probe_role;
create table public.__evt_log(n int);
grant insert on public.__evt_log to public;
create function public.__evt_probe_fn() returns event_trigger language plpgsql security definer
  set search_path = public as $$ begin insert into public.__evt_log values (1); end $$;
alter function public.__evt_probe_fn() owner to postgres;
revoke all on function public.__evt_probe_fn() from public, anon, authenticated;
create event trigger __evt_probe_t on ddl_command_end execute function public.__evt_probe_fn();
select has_function_privilege('__evt_probe_role', 'public.__evt_probe_fn()', 'EXECUTE'); -- f
set local role __evt_probe_role;
create table __evt_probe.t(id int);
reset role;
select count(*) from public.__evt_log;  -- 1
rollback;

• rls_auto_enable is also owned by postgres, and an owner always holds EXECUTE. A post-check below confirms the event trigger is still enabled.

Final authenticated allowlist. After #314 and this issue, authenticated can execute exactly 19 SECURITY DEFINER functions in public. These are the 15 names in Group A marked "authenticated", plus add_reaction, remove_reaction, advance_read_cursor and is_admin. #317 uses this list for its definer_exec check. Lint 0029 (authenticated_security_definer_function_executable) will still list these 19. That is by design.

What to do

1. [maintainer] Read-only prod precheck. Save the output in a comment on this issue.

   select p.oid::regprocedure as sig, pg_get_userbyid(p.proowner) as owner,
          p.prorettype::regtype as returns,
          has_function_privilege('authenticated', p.oid, 'EXECUTE') as auth_exec,
          has_function_privilege('service_role', p.oid, 'EXECUTE') as svc_exec
   from pg_proc p join pg_namespace n on n.oid = p.pronamespace
   where n.nspname = 'public' and p.prosecdef
     and has_function_privilege('anon', p.oid, 'EXECUTE')
   order by returns, sig;

   Expect 47 rows, one per name in Groups A and B. Stop and report if any of these hold:

   • an owner is not postgres;
   • a row names a function outside Groups A and B. One exception is allowed: update_message_bookmarks_updated_at() returning trigger. Block B covers it, so 48 rows with that one is fine;
   • a Group B name does not return trigger or event_trigger.

2. [agent] Confirm Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314 has landed. get_bookmark_count and create_direct_message_channel must be gone from the user_facing_names array in packages/supabase/scripts/29-lint-hardening.sql §6. Find Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314's migration timestamp, which follows 20260928130000.

3. [agent] Edit scripts first. Grants only. Do not touch any function body.

   • packages/supabase/scripts/07-bookmark-functions.sql, after the ALTER FUNCTION ... SET search_path block (~348-353):
     • for toggle_message_bookmark(uuid), get_user_bookmarks(character varying, boolean, integer, integer, boolean), archive_bookmark(bigint, boolean), mark_bookmark_as_read(bigint, boolean) and get_bookmark_stats(character varying): revoke all ... from public, anon; then grant execute ... to authenticated, service_role;
     • for get_bookmark_count(character varying, boolean): revoke all ... from public, anon, authenticated; then grant execute ... to service_role;
   • packages/supabase/scripts/07-4-push-notifications-pgmq.sql:
     • admin_get_user_notification_subs() (~501-502): revoke from public, anon, authenticated, and grant to service_role only. Its body gates on is_admin(auth.uid()), so under service_role it returns zero rows. Add a one-line comment that says so.
     • admin_get_recent_push_activity(int) (~551) and admin_get_failed_push_subs(int) (~601): change from anon to from public, anon. Keep the authenticated grant.
     • register_push_subscription(text, text, text, jsonb) and unregister_push_subscription(text): add revoke all ... from public, anon; and grant execute ... to authenticated, service_role; after the comment on unregister_push_subscription (~414-415).
   • packages/supabase/scripts/07-0-notifications.sql ~70: change from public to from public, anon.
   • packages/supabase/scripts/10-8-func-workspace_members.sql ~207, ~254, ~320 and ~322: change from anon to from public, anon.
   • packages/supabase/scripts/10-functions.sql: add two lines after the COMMENT ON FUNCTION join_workspace block (~923-925). They are revoke all on function public.join_workspace(varchar) from public, anon; and grant execute on function public.join_workspace(varchar) to authenticated, service_role;.
   • packages/supabase/scripts/29-lint-hardening.sql §6 (~218): remove 'update_user_online_at'. It is a trigger function (10-1-func-users.sql ~140; its trigger is at ~156-159), not an RPC. git grep finds no browser caller.
   • If packages/supabase/scripts/check-grants.sql exists (Add a read-only grant guard to run after each db push #317), remove 'update_user_online_at' from its authenticated_definer_ok list too. Add a read-only grant guard to run after each db push #317 requires that list to equal §6, name for name.
   • The four internal definer functions: act only if [Tracking] Supabase prod security and performance review, 2026-09-28 #327 records the Add a read-only grant guard to run after each db push #317 step 6 ruling as Option A before you start. Then add revoke all ... from public, anon, authenticated; and grant execute ... to service_role; after each body. The bodies are internal.cleanup_push_subscriptions() (07-4 ~643) and three in 07-5-email-notifications-pgmq.sql: is_email_suppressed (~123), check_email_rate_limit (~140) and clear_email_bounce_info (~185). Copy the same statements into the migration. With no ruling, or Option B, leave them.
   • Do not add a trigger sweep to the scripts. 29 §5 already revokes every definer function locally, trigger functions included. The local catalog above shows this.

4. [agent] Write one paired migration. Name it packages/supabase/migrations/<timestamp>_close_self_gated_and_trigger_function_grants.sql. Get the timestamp from date -u +%Y%m%d%H%M%S, and check that it sorts after Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314's migration. Use no BEGIN or COMMIT, like recent migrations. Every statement is idempotent, so a re-run is safe. Start from this body:

   -- Grants only; no body changes. Paired with scripts/07-0, 07-4, 07-bookmark-functions,
   -- 10-8, 10-functions and 29-lint-hardening (§6 list). Closes lint 0028 (#318).
   do $$
   declare
     signed_in text[] := array[
       'admin_get_failed_push_subs','admin_get_recent_push_activity',
       'archive_bookmark','get_bookmark_stats','get_user_bookmarks',
       'mark_bookmark_as_read','toggle_message_bookmark',
       'get_document_follow_state','set_document_follow',
       'get_document_member_previews','get_document_members',
       'join_workspace','register_push_subscription',
       'unregister_push_subscription','update_notification_preferences'];
     service_only text[] := array['get_bookmark_count','admin_get_user_notification_subs'];
     f record;
     bad text[] := '{}';
   begin
     -- A. Named RPCs, every overload. Grant first: prod may give EXECUTE only through PUBLIC.
     for f in
       select p.oid::regprocedure as sig, p.proname, pg_get_userbyid(p.proowner) as owner
       from pg_proc p join pg_namespace n on n.oid = p.pronamespace
       where n.nspname = 'public' and p.proname = any (signed_in || service_only)
     loop
       if f.owner <> current_user then   -- a non-owner REVOKE only warns
         raise exception '% is owned by %, not %', f.sig, f.owner, current_user;
       end if;
       if f.proname = any (service_only) then
         execute format('grant execute on function %s to service_role', f.sig);
         execute format('revoke all on function %s from public, anon, authenticated', f.sig);
       else
         execute format('grant execute on function %s to authenticated, service_role', f.sig);
         execute format('revoke all on function %s from public, anon', f.sig);
       end if;
     end loop;
   
     -- B. Definer trigger and event-trigger functions. EXECUTE is checked at CREATE TRIGGER only.
     for f in
       select p.oid::regprocedure as sig, pg_get_userbyid(p.proowner) as owner
       from pg_proc p join pg_namespace n on n.oid = p.pronamespace
       where n.nspname = 'public' and p.prosecdef
         and p.prorettype in ('trigger'::regtype, 'event_trigger'::regtype)
     loop
       if f.owner <> current_user then
         raise notice 'skipped, owned by %: %', f.owner, f.sig;
         continue;
       end if;
       execute format('revoke all on function %s from public, anon, authenticated', f.sig);
     end loop;
   
     -- C. Assert.
     for f in
       select p.oid, p.oid::regprocedure as sig, p.proname, p.prorettype
       from pg_proc p join pg_namespace n on n.oid = p.pronamespace
       where n.nspname = 'public' and p.prosecdef and p.proowner = current_user::regrole
     loop
       if has_function_privilege('anon', f.oid, 'EXECUTE') then
         bad := bad || ('anon can execute ' || f.sig::text);
       end if;
       if f.prorettype in ('trigger'::regtype, 'event_trigger'::regtype)
          and has_function_privilege('authenticated', f.oid, 'EXECUTE') then
         bad := bad || ('authenticated can execute trigger fn ' || f.sig::text);
       end if;
       if f.proname = any (signed_in) and not has_function_privilege('authenticated', f.oid, 'EXECUTE') then
         bad := bad || ('authenticated lost ' || f.sig::text);
       end if;
       if f.proname = any (service_only) and (has_function_privilege('authenticated', f.oid, 'EXECUTE')
          or not has_function_privilege('service_role', f.oid, 'EXECUTE')) then
         bad := bad || ('service_role-only check failed: ' || f.sig::text);
       end if;
     end loop;
     if cardinality(bad) > 0 then raise exception 'grant assert failed: %', bad; end if;
   end $$;

   The anon check in block C covers every definer function. So it also fails if Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314's work is missing. That is intended.

   Tested locally on 2026-09-28, rolled back. First, EXECUTE went to public, anon, authenticated on the 46 repo names, as on prod. Then this body ran. The step 1 query returned 0 rows. The 17-name query in Verification matched the expected result.

5. [agent] Close the guest caller gap.

   • In useHyperlinkSuggestions.ts ~50, change queriesEnabled to also require userId: Boolean(workspaceId) && Boolean(userId) && !disabled. Headings keep working for guests. Only the Bookmarks section is skipped.
   • Keep hyperlink-picker.cy.js green:
     • expose the auth store next to window._store in EditorPlayground.tsx (~96), for example window._authStore = useAuthStore. Import useAuthStore from @stores (~9), and delete window._authStore in the cleanup (~108);
     • add _authStore to the Window type in EditorPlayground.tsx (~33) and to EditorWindow in apps/webapp/cypress/support/commands.ts (~26);
     • in setupBookmarkSuggestions (cypress/support/hyperlinkPicker.ts ~102-106), also set a test profile id through _authStore.getState().setProfile(...). apps/webapp/src/pages/c/[channelId].tsx ~8-13 seeds a test profile the same way. The auth listener clears the profile only on SIGNED_OUT (useOnAuthStateChange.ts ~74-79), so the seeded profile stays.
   • Do not add new specs. This step only keeps an existing spec passing.

6. [agent] Regenerate and verify locally. Commands are in Verification.

   • bun run --filter @docs.plus/supabase_back reset regenerates packages/supabase/seed.sql and resets the local database. It wipes local data.
   • Apply the new migration on top with docker exec -i ... psql. Run it twice to prove it is idempotent.
   • Run bun run --filter @docs.plus/supabase_back types. Expect no diff, because only grants change. Include apps/webapp/src/types/supabase.ts if it does change.

7. [maintainer] Push to prod. Follow the house procedure in packages/supabase/CLAUDE.md:

   • run bunx supabase migration list first;
   • set [db.migrations] enabled = true only while pushing, and never commit it;
   • never repair --status applied a new version.
     This migration goes after Ship the three migrations that prod's history lacks #313's three migrations and after Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314's migration.

8. [maintainer] Run the prod post-checks in Verification, and re-run the Security Advisor. Paste the results into this issue.

Out of scope

• Functions with no gate, the users column grant and partition RLS: Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314.
• New document_views partitions and recording rls_auto_enable / ensure_rls in the repo: Make new document_views partitions start closed #315. This issue only revokes EXECUTE on rls_auto_enable.
• ALTER DEFAULT PRIVILEGES and the global PUBLIC EXECUTE ruling: Stop new tables and functions from opening by default #316.
• The grant guard: Add a read-only grant guard to run after each db push #317. It uses the 19-name allowlist from Context.
• Any later change to a Group A function body must use create or replace function. A drop plus create resets the grants that this issue sets.
• Dropping create_direct_message_channel: Drop the dead create_direct_message_channel function #319.
• Prod-only drift functions and PushSentLog: Remove prod-only drift: four functions and the PushSentLog table #320.
• Dropping admin_get_user_notification_subs or get_bookmark_count. Both have no caller. A drop needs its own issue.

Acceptance criteria

• Each of the 17 Group A functions has an explicit grant and revoke in its own scripts/*.sql file, as step 3 lists.
• update_user_online_at is not in the 29-lint-hardening.sql §6 array, nor in check-grants.sql if that file exists.
• If [Tracking] Supabase prod security and performance review, 2026-09-28 #327 recorded Option A for the Add a read-only grant guard to run after each db push #317 step 6 ruling, the four internal definer functions are service_role only in scripts and migration.
• One new migration exists, sorts after Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314's migration, and runs twice on a fresh local reset without error.
• On the local database after reset plus migration, the step 1 query returns 0 rows.
• Locally, authenticated can execute exactly the 15 Group A names marked "authenticated". It cannot execute get_bookmark_count or admin_get_user_notification_subs. service_role can execute all 17.
• Locally, no SECURITY DEFINER trigger function in public is executable by anon or authenticated.
• useHyperlinkSuggestions.ts does not call get_user_bookmarks without a signed-in profile.
• hyperlink-picker.cy.js passes.
• packages/supabase/seed.sql is regenerated by the script, not edited by hand. apps/webapp/src/types/supabase.ts is regenerated, or it has no diff.
• [maintainer] After the push, the step 1 query on prod returns 0 rows, and Security Advisor lint 0028 shows 0 rows.
• [maintainer] After the push, ensure_rls is still enabled and still uses rls_auto_enable().

Verification

Local database (agent):

bun run --filter @docs.plus/supabase_back reset
docker exec -i supabase_db_docsplus_supabase psql -U postgres -d postgres -v ON_ERROR_STOP=1 \
  < packages/supabase/migrations/<timestamp>_close_self_gated_and_trigger_function_grants.sql
# run the same command again: expect no error
bun run --filter @docs.plus/supabase_back types

Run the step 1 query locally. Expect 0 rows. Then run this query, locally and on prod:

select p.proname,
       has_function_privilege('anon', p.oid, 'EXECUTE')          as anon_exec,
       has_function_privilege('authenticated', p.oid, 'EXECUTE') as auth_exec,
       has_function_privilege('service_role', p.oid, 'EXECUTE')  as svc_exec
from pg_proc p join pg_namespace n on n.oid = p.pronamespace
where n.nspname = 'public' and p.proname = any (array[
  'admin_get_failed_push_subs','admin_get_recent_push_activity','archive_bookmark',
  'get_bookmark_stats','get_user_bookmarks','mark_bookmark_as_read','toggle_message_bookmark',
  'get_document_follow_state','set_document_follow','get_document_member_previews',
  'get_document_members','join_workspace','register_push_subscription',
  'unregister_push_subscription','update_notification_preferences',
  'get_bookmark_count','admin_get_user_notification_subs'])
order by 1;

Expected results:

• anon_exec is false on all 17 rows.
• auth_exec is true on 15 rows, and false for get_bookmark_count and admin_get_user_notification_subs.
• svc_exec is true on all 17 rows.

Webapp (agent):

bun run --filter @docs.plus/webapp typecheck
cd apps/webapp && bunx cypress run --spec cypress/e2e/editor/hyperlink/hyperlink-picker.cy.js

The spec needs the webapp at http://localhost:3001 (make dev-local). The chatroom suite is bash scripts/run-tests.sh --e2e --scope chatroom. Its specs stub Supabase over HTTP, so they cannot catch a grant fault.

Manual guest check (agent, local). Local anon already has the target grants, so a local guest session previews prod after the fix.

• Sign out, then open a pad with chat.
• Open the link picker. Open Settings.
• In DevTools, watch requests to /rpc/. Expect no 401 or 403, and no get_user_bookmarks request.
• Sign in and repeat. Bookmarks appear in the link picker. The follow toggle and the bookmark panel work.

Prod post-checks (maintainer, read-only):

• The step 1 query returns 0 rows.

• The 17-name query above matches the local result.

• The event trigger is intact. Expect ensure_rls, with evtenabled = 'O' and evtfoid = rls_auto_enable():

  select evtname, evtevent, evtenabled, evtfoid::regprocedure, pg_get_userbyid(evtowner)
  from pg_event_trigger where evtname = 'ensure_rls';

• Dashboard → Advisors → Security Advisor → Rerun linter. Expect 0 lint 0028 rows. Lint 0029 still lists the 19 allowlisted names.

• As a signed-in user, bookmark a chat message, toggle Follow, and change a notification setting. As an admin, open the admin dashboard push page.

House rules

• Scripts first, then the paired migration. db reset must reproduce prod. Never edit an applied migration (packages/supabase/CLAUDE.md §Supabase).
• Never hand-edit packages/supabase/seed.sql or apps/webapp/src/types/supabase.ts. Regenerate them with bun run --filter @docs.plus/supabase_back reset and ... types.
• A browser-called SECURITY DEFINER function must be in 29-lint-hardening.sql §6. Its own grant execute is not enough locally (packages/supabase/CLAUDE.md).
• Bun only (bun, bunx). No commits, pushes or branches unless the maintainer asks. The maintainer commits through /commit-review.
• Tests are opt-in. Keep the existing spec green. Do not add new specs.