Database Security

CommonPlace uses PostgreSQL Row Level Security (RLS) to enforce access control at the database level.

Why RLS?

Row Level Security ensures that:

1. Security is enforced at the database level — Not just application code
2. Every query is filtered — No accidental data leaks
3. Direct database access is safe — Even with the anon key

RLS Principles

Enable RLS on All Tables

ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

Every table with user data has RLS enabled.

Default Deny

By default, with RLS enabled, all access is denied. Policies explicitly grant access.

User Context

Policies use auth.uid() to get the current user:

CREATE POLICY "Users can read own posts"
ON posts FOR SELECT
USING (user_id = auth.uid());

Common Policy Patterns

Owner-Only Access

-- Only owner can read
CREATE POLICY "Users read own data"
ON user_settings FOR SELECT
USING (user_id = auth.uid());

-- Only owner can update
CREATE POLICY "Users update own data"
ON user_settings FOR UPDATE
USING (user_id = auth.uid());

Friend Access

-- Friends can read posts
-- Note: friendships uses requester_id/addressee_id columns, checked bidirectionally
CREATE POLICY "Friends can read posts"
ON posts FOR SELECT
USING (
  user_id = auth.uid()
  OR EXISTS (
    SELECT 1 FROM friendships
    WHERE status = 'accepted'
    AND (
      (requester_id = posts.user_id AND addressee_id = auth.uid())
      OR (requester_id = auth.uid() AND addressee_id = posts.user_id)
    )
  )
);

Block Filtering

-- Blocked users don't exist
CREATE POLICY "Blocked users invisible"
ON profiles FOR SELECT
USING (
  NOT EXISTS (
    SELECT 1 FROM blocks
    WHERE (blocker_id = auth.uid() AND blocked_id = profiles.id)
    OR (blocker_id = profiles.id AND blocked_id = auth.uid())
  )
);

Admin Access

-- Admins can read all reports
-- Note: Admin status is determined via the admin_roles table, NOT an is_admin column on profiles.
-- See 20260202_admin_system.sql for the admin_roles schema.
CREATE POLICY "Admins can view all reports"
ON reports FOR SELECT TO authenticated
USING (
  EXISTS (
    SELECT 1 FROM admin_roles
    WHERE admin_roles.user_id = auth.uid()
  )
);

Security Functions

admin_resolve_report()

Allows admins to resolve reports with proper permission checks. Caveat: This function was added in migration 20260207_admin_report_functions.sql, but its implementation references a profiles.is_admin column that does not exist. The actual admin system uses the admin_roles table (see 20260202_admin_system.sql). This function needs to be updated to use admin_roles instead. See CLAUDE.md known bugs (P1).

is_friend()

Returns true if two users are friends.

can_message()

Checks if messaging is allowed between two users.

Testing Policies

Test policies in the SQL Editor:

-- Set user context
SET request.jwt.claim.sub = 'user-uuid-here';

-- Test query
SELECT * FROM posts;

Security Checklist

• RLS enabled on all user data tables
• No SELECT * without policy
• Block filtering applied everywhere
• Admin functions verify role via admin_roles table (not is_admin column)
• Sensitive operations use SECURITY DEFINER

---

Last updated: 2026-02-07