AOSSIE-Org / AOSSIE-Org/NeuroTrack

BUG: Missing RLS policies occur DB access error(P42501)

オープン
#157 コメント 0 件 リアクション 0 件 担当者 0 名 GitHub で見る
主要言語
Dart
スター
29
フォーク
50
PR マージ指標
30日以内にマージされた PR はありません

説明

### Is there an existing issue for this?

- [x] I have searched the existing issues

### What happened?

> This issue and related PR were fully identified and analyzed by myself. Claude was only used to assist with writing descriptions in English.

## 📌 Issue Overview
`schema.sql` defines all tables but has no Row Level Security (RLS) policies. When RLS is enabled on Supabase (default behavior), all authenticated operations are blocked with `42501` errors because no policies exist to grant access.

## 🔍 Steps to Reproduce
1. Deploy the schema to a Supabase project
2. Sign up and log in as a patient
3. Complete the onboarding flow (insert into `patient` table)

## 🎯 Expected Behavior
Authenticated users should be able to perform CRUD operations on their own records according to their role (patient or therapist).

## 🚨 Actual Behavior
All DB operations fail with:
```
PostgrestException(message: new row violates row-level security policy for table "patient", code: 42501)
```

## 💡 Suggested Improvements
Add RLS policies for all 14 tables in `schema.sql` with the following access patterns:

| Table | Policy |
|-------|--------|
| `therapist` | Own record INSERT/SELECT/UPDATE via `auth.uid()` |
| `patient` | Own record INSERT/SELECT/UPDATE + therapist can SELECT their patients |
| `package` | Read-only for all authenticated users |
| `session` | Therapist full CRUD on own sessions, patient SELECT/INSERT/UPDATE on own sessions |
| `therapy` | Read-only for all authenticated users |
| `therapy_goal` | Therapist full CRUD, patient SELECT on own goals |
| `assessments` | Read-only for all authenticated users |
| `assessment_results` | Patient INSERT/SELECT own results, therapist SELECT their patients' results |
| `goal_master`, `observation_master`, `regression_master`, `activity_master` | Read-only for all authenticated users |
| `daily_activities` | Therapist full CRUD, patient SELECT own activities |
| `daily_activity_logs` | Patient full CRUD on own logs, therapist SELECT their patients' logs |

Improvement PR is ready and waiting for PR #156 been merged.

> When PR #156 is merged, additional policy for table `therapist_type` would be needed.

### Record

- [x] I agree to follow this project's Code of Conduct
- [x] I want to work on this issue

コントリビューションガイド

このリポジトリのコントリビューションガイドは索引されていません

評価

この issue はまだ評価されていません。

新しい issue をメールで受け取る

初心者向けの GitHub issue を短くまとめたダイジェスト。