AOSSIE-Org / AOSSIE-Org/Ell-ena

BUG: RLS UPDATE policy on tasks/tickets allows non-admins to self-approve via approval_status

Đang mở
#319 0 bình luận 0 reaction 0 người được giao Xem trên GitHub
bug enhancement
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

Nhận issue mới trong hộp thư của bạn

Bản tóm tắt ngắn những issue GitHub phù hợp với người mới.