Table reference
Who may read and write each table, as the policies currently stand. Source of truth is supabase/migrations/; if this page disagrees with those files, the files are right and this page needs fixing.
Throughout, "admin" means has_role_at_least(auth.uid(), 'admin'), which is true for both administrators and owners.
profiles
| Operation | Who | Condition |
|---|---|---|
| Read | Anyone, signed in or not | status = 'verified' |
| Read | Signed-in member | Their own row, at any status |
| Read | Admin | Every row |
| Create | Signed-in member | Own row only, and status must be pending |
| Update | Signed-in member | Own row only, and it must remain theirs |
| Update, delete | Admin | Any row |
Members cannot delete their own profile. There is no policy for it, which means the operation is denied.
Two things are enforced by trigger rather than by policy. status is reset to its previous value if a non-administrator tries to change it, so approving your own profile fails silently rather than erroring. And alumni_id is always derived server-side: it is regenerated when the batch year changes and locked to its previous value otherwise, so a value submitted by the client is discarded either way.
user_roles
| Operation | Who | Condition |
|---|---|---|
| Read | Signed-in member | Their own row |
| Read | Admin | Every row |
| Write | Nobody | No insert, update, or delete policy exists |
The absence of write policies is deliberate and is the main protection against privilege escalation. The table is unwritable through the API. The only path that changes a role is the set_user_role function, described in Authentication.
This table has FORCE ROW LEVEL SECURITY and explicitly revoked grants, so the broad default privileges do not apply to it.
role_hierarchy
| Operation | Who | Condition |
|---|---|---|
| Read | Signed-in member | Always |
| Write | Nobody | No policy |
A static lookup table of four rows. Readable so the interface can render role names in rank order.
role_changes
| Operation | Who | Condition |
|---|---|---|
| Read | Owner | Always |
| Write | Nobody through the API | Written only by set_user_role |
The audit log. Restricted to owners because it reveals the administrative structure of the community.
reunion_announcements
| Operation | Who | Condition |
|---|---|---|
| Read | Anyone, signed in or not | Always |
| Create, update, delete | Admin | Any row |
Announcements are fully public, including to visitors who are not signed in, because the feed is a public page.
Note the asymmetry with storage: publishing an announcement requires administrator level, while uploading an image for one requires only editor. That is a real inconsistency, not a documented intention, and is worth resolving.
newsletter_subscribers
| Operation | Who | Condition |
|---|---|---|
| Create | Anyone, signed in or not | Always |
| Read, update, delete | Admin | Any row |
Anyone may subscribe; nobody below administrator may read the list. There is no rate limiting, so the table can be flooded with junk rows. No data is exposed by that, but it is a known weakness.
announcement_preferences
| Operation | Who | Condition |
|---|---|---|
| Read | Signed-in member | Their own row |
| Create | Signed-in member | Their own row |
| Update | Signed-in member | Their own row |
| Delete | Nobody | No policy |
A row is created automatically by trigger when an account is created.
Worth noting: the update policy has a USING clause but no WITH CHECK. In practice the unique constraint on user_id limits what that allows, but it is the same omission described in How access control works and should be tightened.
Storage: alumni-photos
| Operation | Who | Condition |
|---|---|---|
| Read | Anyone | Any object in the bucket |
| Create | Signed-in member | Path must start with their own account identifier |
| Update | Signed-in member | Object must be in their own folder |
| Delete | Signed-in member | Object must be in their own folder |
The bucket is public to read, which is intentional: profile photos appear in a public directory, and signing every URL was measurably slower for no security benefit.
Known gap. The update policy has no WITH CHECK. A move is an update of the object's name, so a member can move one of their own objects into another member's folder. They cannot overwrite or delete anything that is already there, so the impact is limited to clutter, but the missing clause should be added.
Storage: post-images
| Operation | Who | Condition |
|---|---|---|
| Read | Anyone | Any object in the bucket |
| Create, update, delete | Editor or above | Any object in the bucket |
Deliberately not folder-scoped. An image belongs to the announcement rather than to whoever uploaded it, so any editor must be able to replace or remove it. Scoping per uploader would orphan images whenever a different editor edited the post.
These policies were written after the folder-scoping problem in alumni-photos was understood, and they include WITH CHECK.
Buckets are not created by migrations
Neither bucket is defined in SQL. Both were created by hand in Supabase Studio, and a fresh deployment needs them created the same way before uploads work. The policies above apply harmlessly to a bucket that does not exist; uploads simply fail at runtime with an error that does not obviously point at a missing bucket.