AOSSIE-Org / AOSSIE-Org/Ell-ena
BUG: RLS UPDATE policy on tasks/tickets allows non-admins to self-approve via approval_status
- Ngôn ngữ chính
- Dart
- Star
- 54
- Fork
- 110
- Chỉ số merge pull request
- Không có pull request nào được merge trong 30 ngày
Mô tả
### Is there an existing issue for this?
- [x] I have searched the existing issues
### What happened?
**Issue Overview**
The RLS UPDATE policy on tasks and tickets allows non-admins to self-approve their own task or ticket. There are two separate permissive UPDATE policies on each table — a broad one letting the creator/assignee update any column, and a narrower one meant to restrict approval_status changes to admins. Postgres RLS combines multiple permissive policies with OR and doesn't support column-level restriction, so the broad policy alone is enough to let a non-admin change approval_status directly, bypassing the admin-only intent.
**Steps to Reproduce**
Log in as a non-admin user who created or is assigned to a task/ticket.
Attempt to update that task/ticket's approval_status field directly (e.g. via a client call to updateTaskApproval/updateTicketApproval, or any UPDATE hitting the tasks/tickets table with that row's id).
Observe that the update succeeds even though only admins should be permitted to change approval_status.
**Expected Behavior**
Only a team admin should be able to change a task's or ticket's approval_status. Non-admin creators/assignees should be able to update other fields they're permitted to, but any attempt to change approval_status should be rejected.
**Actual Behavior**
Because Postgres RLS ORs multiple permissive policies together for the same command, the broad "creator or assignee can update" policy on tasks/tickets is sufficient on its own to permit an approval_status change the separate "admins only" policy doesn't actually narrow this down. A non-admin can self-approve their own task or ticket.
**Suggested Improvements**
Add a BEFORE UPDATE trigger on tasks (and mirror on tickets) that raises an exception if approval_status changes and the caller is not an admin of the row's team independent of the coarse UPDATE policy. This closes the gap since RLS policies alone can't enforce column-level restrictions here.
### Record
- [x] I agree to follow this project's Code of Conduct
- [x] I want to work on this issue
Hướng dẫn đóng góp
Chưa lập chỉ mục được hướng dẫn đóng góp cho kho mã nguồn này
Hướng nghiên cứu
Start by locating the PostgreSQL RLS UPDATE policies and approval entry points named updateTaskApproval and updateTicketApproval for the tasks and tickets tables, then reproduce the change as a non-admin. Review the existing team-admin check and verify that non-admin approval_status changes are rejected while permitted edits to other fields and admin approvals still succeed.
Do mô hình lập chỉ mục viết ra từ nội dung của issue.
Đánh giá
- Công nghệ
- postgresql
- Lĩnh vực
- databases, security
- Loại issue
- Lỗi
- Độ khó
- 4/5
- Thời gian dự kiến
- 3-5 ngày
- Mức độ hoạt động
- Sôi nổi
- Độ rõ ràng
- Khá rõ ràng
- Mức phù hợp với người mới
- 48/100