Database advisor report: before

Source file: evidence/audit/before.md

Source: before/supabase/schema.sql, loaded into localbase (PGlite, Postgres with Supabase roles and grants). Lints modelled on Supabase splinter. Generated by scripts/audit-db.ts.

LevelCount
ERROR9
WARN19
INFO14
LevelLintObjectDetailRemediation
ERROR0013_rls_disabled_in_publicpublic.clientsRLS is off; with Supabase default grants the anon key can read and write italter table ... enable row level security; then write per-role policies.
ERROR0013_rls_disabled_in_publicpublic.invoice_itemsRLS is off; with Supabase default grants the anon key can read and write italter table ... enable row level security; then write per-role policies.
ERROR0013_rls_disabled_in_publicpublic.invoicesRLS is off; with Supabase default grants the anon key can read and write italter table ... enable row level security; then write per-role policies.
ERROR0013_rls_disabled_in_publicpublic.paymentsRLS is off; with Supabase default grants the anon key can read and write italter table ... enable row level security; then write per-role policies.
WARN0024_permissive_rls_policypublic.memberships policy "Allow all for authenticated" (ALL)USING (true); WITH CHECK (true)Scope the policy to the caller: (select auth.uid()) = user_id, or a tenant membership check.
WARN0024_permissive_rls_policypublic.messages policy "Users can post messages" (INSERT)WITH CHECK (true)Scope the policy to the caller: (select auth.uid()) = user_id, or a tenant membership check.
WARN0024_permissive_rls_policypublic.organizations policy "Enable insert for authenticated users only" (INSERT)WITH CHECK (true)Scope the policy to the caller: (select auth.uid()) = user_id, or a tenant membership check.
WARN0024_permissive_rls_policypublic.organizations policy "Enable read access for all users" (SELECT)USING (true)Scope the policy to the caller: (select auth.uid()) = user_id, or a tenant membership check.
WARN0024_permissive_rls_policypublic.organizations policy "Enable update for authenticated users" (UPDATE)USING (true)Scope the policy to the caller: (select auth.uid()) = user_id, or a tenant membership check.
WARN0024_permissive_rls_policypublic.profiles policy "Public profiles are viewable by everyone." (SELECT)USING (true)Scope the policy to the caller: (select auth.uid()) = user_id, or a tenant membership check.
ERROR0015_rls_references_user_metadatapublic.messages policy "Users can read messages"references user_metadataAuthorise from app_metadata (server-set) or a roles table only the service role writes.
ERROR0015_rls_references_user_metadatapublic.projects policy "Admins can manage projects"references user_metadataAuthorise from app_metadata (server-set) or a roles table only the service role writes.
ERROR0015_rls_references_user_metadatapublic.projects policy "Members or admins can view projects"references user_metadataAuthorise from app_metadata (server-set) or a roles table only the service role writes.
WARN0003_auth_rls_initplanpublic.messages policy "Users can read messages"calls auth.* or current_setting() without (select ...)Wrap the call in a scalar subquery: (select auth.uid()), so Postgres evaluates it once.
WARN0003_auth_rls_initplanpublic.profiles policy "Users can insert their own profile."calls auth.* or current_setting() without (select ...)Wrap the call in a scalar subquery: (select auth.uid()), so Postgres evaluates it once.
WARN0003_auth_rls_initplanpublic.profiles policy "Users can update own profile."calls auth.* or current_setting() without (select ...)Wrap the call in a scalar subquery: (select auth.uid()), so Postgres evaluates it once.
WARN0003_auth_rls_initplanpublic.projects policy "Admins can manage projects"calls auth.* or current_setting() without (select ...)Wrap the call in a scalar subquery: (select auth.uid()), so Postgres evaluates it once.
WARN0003_auth_rls_initplanpublic.projects policy "Members or admins can view projects"calls auth.* or current_setting() without (select ...)Wrap the call in a scalar subquery: (select auth.uid()), so Postgres evaluates it once.
ERROR0010_security_definer_viewpublic.team_directoryview without security_invoker=truealter view ... set (security_invoker = true), or replace it with a security-invoker function.
ERROR0002_auth_users_exposedpublic.team_directoryview reads auth.users and is selectable by anon or authenticatedDrop the view; expose only the profile fields you need from a table with RLS.
WARN0011_function_search_path_mutablepublic.get_org_revenue(org uuid)SECURITY DEFINER with mutable search_pathAdd set search_path = '' and schema-qualify every name in the body.
WARN0011_function_search_path_mutablepublic.handle_new_user()SECURITY DEFINER with mutable search_pathAdd set search_path = '' and schema-qualify every name in the body.
WARN0028_anon_security_definer_function_executablepublic.get_org_revenue(org uuid)anon can call it via /rest/v1/rpcrevoke execute ... from anon, public; or make it security invoker.
WARN0029_authenticated_security_definer_function_executablepublic.get_org_revenue(org uuid)authenticated can call it via /rest/v1/rpcMove it to a private schema, check membership inside it, or revoke execute from authenticated.
INFO0001_unindexed_foreign_keyspublic.clients (org_id)constraint clients_org_id_fkeycreate index on the referencing columns (leading columns of some index).
INFO0001_unindexed_foreign_keyspublic.invoice_items (invoice_id)constraint invoice_items_invoice_id_fkeycreate index on the referencing columns (leading columns of some index).
INFO0001_unindexed_foreign_keyspublic.invoices (client_id)constraint invoices_client_id_fkeycreate index on the referencing columns (leading columns of some index).
INFO0001_unindexed_foreign_keyspublic.invoices (org_id)constraint invoices_org_id_fkeycreate index on the referencing columns (leading columns of some index).
INFO0001_unindexed_foreign_keyspublic.invoices (project_id)constraint invoices_project_id_fkeycreate index on the referencing columns (leading columns of some index).
INFO0001_unindexed_foreign_keyspublic.memberships (org_id)constraint memberships_org_id_fkeycreate index on the referencing columns (leading columns of some index).
INFO0001_unindexed_foreign_keyspublic.memberships (user_id)constraint memberships_user_id_fkeycreate index on the referencing columns (leading columns of some index).
INFO0001_unindexed_foreign_keyspublic.messages (author_id)constraint messages_author_id_fkeycreate index on the referencing columns (leading columns of some index).
INFO0001_unindexed_foreign_keyspublic.messages (org_id)constraint messages_org_id_fkeycreate index on the referencing columns (leading columns of some index).
INFO0001_unindexed_foreign_keyspublic.messages (project_id)constraint messages_project_id_fkeycreate index on the referencing columns (leading columns of some index).
INFO0001_unindexed_foreign_keyspublic.organizations (owner_id)constraint organizations_owner_id_fkeycreate index on the referencing columns (leading columns of some index).
INFO0001_unindexed_foreign_keyspublic.payments (invoice_id)constraint payments_invoice_id_fkeycreate index on the referencing columns (leading columns of some index).
INFO0001_unindexed_foreign_keyspublic.projects (client_id)constraint projects_client_id_fkeycreate index on the referencing columns (leading columns of some index).
INFO0001_unindexed_foreign_keyspublic.projects (org_id)constraint projects_org_id_fkeycreate index on the referencing columns (leading columns of some index).
WARNcustom_money_floatpublic.invoice_items.unit_pricedouble precisionStore integer minor units (cents) or numeric; floats cannot represent 0.10 exactly.
WARNcustom_money_floatpublic.invoices.totaldouble precisionStore integer minor units (cents) or numeric; floats cannot represent 0.10 exactly.
WARNcustom_money_floatpublic.payments.amountdouble precisionStore integer minor units (cents) or numeric; floats cannot represent 0.10 exactly.
WARNcustom_money_floatpublic.projects.budgetdouble precisionStore integer minor units (cents) or numeric; floats cannot represent 0.10 exactly.

Lints run

LintLevelChecks
0013_rls_disabled_in_publicERRORRLS disabled on a table in an API-exposed schema
0007_policy_exists_rls_disabledERRORPolicies exist but RLS is disabled
0024_permissive_rls_policyWARNPolicy expression is always true
0015_rls_references_user_metadataERRORPolicy trusts user_metadata, which every user can edit
0003_auth_rls_initplanWARNauth.<fn>() evaluated per row in a policy
0010_security_definer_viewERRORView runs with its owner's privileges (bypasses RLS)
0002_auth_users_exposedERRORauth.users exposed through a view in an API schema
0011_function_search_path_mutableWARNFunction without a pinned search_path
0028_anon_security_definer_function_executableWARNSECURITY DEFINER function in public callable by anon
0029_authenticated_security_definer_function_executableWARNSECURITY DEFINER function in public callable by any signed-in user
0001_unindexed_foreign_keysINFOForeign key without a covering index
0008_rls_enabled_no_policyINFORLS enabled with no policies (only the service role can use the table)
custom_money_floatWARNMoney stored as floating point