commit a50d3fef7b2a31b06ca3ed1b0d33e06b89cf0506
parent df54b1d8a344d1ad0dad41ae116ca0c1042ab34c
Author: Andreas Gruhler <andreas.gruhler@adfinis.com>
Date: Tue, 14 May 2024 16:12:07 +0200
fix(psql): implement supabase security advisories
Supabase advises to set an empty search_path on the functions to
encourage you to always specify the full schema and table names. Also,
it is encouraged to enable security_invoker on the views so the views
make use of RLS.
https://supabase.com/blog/security-performance-advisor
Diffstat:
1 file changed, 9 insertions(+), 5 deletions(-)
diff --git a/README.md b/README.md
@@ -36,7 +36,9 @@ Additionally, following views and functions are required:
```sql
-- Summarize all scores of a heat
-create or replace view score_summary as
+create or replace view score_summary
+ with (security_invoker=on)
+ as
select a.id as athlete_id, s.heat as heat_id, SUM(s.score) as score_summary
from scores s
join athletes as a on a.id = s.athlete
@@ -47,12 +49,13 @@ create or replace function distinct_startlist(heat_ids numeric[])
returns table(id bigint, athlete bigint, nr bigint, firstname text, lastname text,
birthday date, school text)
language plpgsql
+set search_path = ''
as $$
begin
return query
select distinct on (a.id) s.id, a.id, a.nr, a.firstname, a.lastname, a.birthday, a.school
- from startlist as s
- join athletes as a on s.athlete = a.id
+ from public.startlist as s
+ join public.athletes as a on s.athlete = a.id
where s.heat = any (heat_ids);
end;
$$;
@@ -62,10 +65,11 @@ Add a trigger for connecting users with judges:
```sql
-- https://supabase.com/docs/guides/auth/managing-user-data#using-triggers
-- inserts a row into public.judges
-create function public.handle_new_judge()
+--drop trigger if exists on_auth_user_created on auth.users;
+create or replace function public.handle_new_judge()
returns trigger
language plpgsql
-security definer set search_path = public
+security definer set search_path = ''
as $$
begin
insert into public.judges (id)
