RLS Policy Patterns

Policy Pattern Overview

Common Access Patterns

┌─────────────────────────────────────────────────────────────┐
│                   RLS POLICY PATTERNS                        │
├─────────────────────────────────────────────────────────────┤
│                                                              │
│  1. Owner Access         - User owns the row                │
│  2. Role-based Access    - Based on user role               │
│  3. Organization Access  - Same org can access              │
│  4. Team Access          - Team members can access          │
│  5. Public Read          - Anyone can read                  │
│  6. Hierarchical Access  - Based on relationships           │
│  7. Time-based Access    - Based on timestamps              │
│                                                              │
└─────────────────────────────────────────────────────────────┘

---

Pattern 1: Owner Access

User Owns Data

-- Table structure
CREATE TABLE public.tasks (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title TEXT NOT NULL,
  user_id UUID NOT NULL REFERENCES auth.users(id),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Enable RLS
ALTER TABLE public.tasks ENABLE ROW LEVEL SECURITY;

-- Policy: Users can CRUD their own tasks
CREATE POLICY "Users manage own tasks"
  ON public.tasks
  FOR ALL
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

Separate Policies per Operation

-- More granular control
CREATE POLICY "Users can view own tasks"
  ON tasks FOR SELECT
  USING (auth.uid() = user_id);

CREATE POLICY "Users can create tasks"
  ON tasks FOR INSERT
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update own tasks"
  ON tasks FOR UPDATE
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can delete own tasks"
  ON tasks FOR DELETE
  USING (auth.uid() = user_id);

---

Pattern 2: Role-based Access

Admin vs Regular Users

-- Helper function to check role
CREATE OR REPLACE FUNCTION is_admin()
RETURNS BOOLEAN AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.profiles
    WHERE id = auth.uid()
    AND role = 'admin'
  );
$$ LANGUAGE sql SECURITY DEFINER STABLE;

-- Regular users see own data
CREATE POLICY "Users see own data"
  ON tasks FOR SELECT
  USING (auth.uid() = user_id);

-- Admins see all data
CREATE POLICY "Admins see all data"
  ON tasks FOR SELECT
  USING (is_admin());

-- Only admins can delete
CREATE POLICY "Only admins can delete"
  ON tasks FOR DELETE
  USING (is_admin());

Role from JWT

-- Check role from JWT metadata
CREATE POLICY "Premium users access premium content"
  ON premium_content FOR SELECT
  USING (
    (auth.jwt() ->> 'user_metadata')::jsonb ->> 'plan' = 'premium'
  );

---

Pattern 3: Organization Access

Multi-tenant with Organizations

-- Organization members table
CREATE TABLE public.organization_members (
  organization_id UUID REFERENCES organizations(id),
  user_id UUID REFERENCES auth.users(id),
  role TEXT DEFAULT 'member',
  PRIMARY KEY (organization_id, user_id)
);

-- Projects belong to organizations
CREATE TABLE public.projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES organizations(id),
  name TEXT NOT NULL
);

-- Helper function
CREATE OR REPLACE FUNCTION user_organizations()
RETURNS SETOF UUID AS $$
  SELECT organization_id FROM organization_members
  WHERE user_id = auth.uid();
$$ LANGUAGE sql SECURITY DEFINER STABLE;

-- Policy: Access projects in your organizations
CREATE POLICY "Org members access projects"
  ON projects FOR ALL
  USING (organization_id IN (SELECT user_organizations()))
  WITH CHECK (organization_id IN (SELECT user_organizations()));

---

Pattern 4: Team/Group Access

Project Team Members

-- Project members junction table
CREATE TABLE project_members (
  project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  role TEXT DEFAULT 'member' CHECK (role IN ('owner', 'admin', 'member', 'viewer')),
  PRIMARY KEY (project_id, user_id)
);

-- Helper function
CREATE OR REPLACE FUNCTION is_project_member(p_project_id UUID)
RETURNS BOOLEAN AS $$
  SELECT EXISTS (
    SELECT 1 FROM project_members
    WHERE project_id = p_project_id
    AND user_id = auth.uid()
  );
$$ LANGUAGE sql SECURITY DEFINER STABLE;

-- Tasks policies
CREATE POLICY "Project members can view tasks"
  ON tasks FOR SELECT
  USING (is_project_member(project_id));

CREATE POLICY "Project members can create tasks"
  ON tasks FOR INSERT
  WITH CHECK (is_project_member(project_id));

Role-specific within Team

-- Only admin/owner can delete
CREATE OR REPLACE FUNCTION is_project_admin(p_project_id UUID)
RETURNS BOOLEAN AS $$
  SELECT EXISTS (
    SELECT 1 FROM project_members
    WHERE project_id = p_project_id
    AND user_id = auth.uid()
    AND role IN ('owner', 'admin')
  );
$$ LANGUAGE sql SECURITY DEFINER STABLE;

CREATE POLICY "Only admins can delete tasks"
  ON tasks FOR DELETE
  USING (is_project_admin(project_id));

---

Pattern 5: Public Read, Auth Write

Blog Posts / Public Content

-- Posts table
CREATE TABLE public.posts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  author_id UUID REFERENCES auth.users(id),
  title TEXT NOT NULL,
  content TEXT,
  status TEXT DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'archived')),
  published_at TIMESTAMPTZ
);

-- Anyone can read published posts
CREATE POLICY "Public can read published posts"
  ON posts FOR SELECT
  USING (status = 'published');

-- Authors can see their own drafts
CREATE POLICY "Authors see own drafts"
  ON posts FOR SELECT
  USING (auth.uid() = author_id);

-- Only authors can modify
CREATE POLICY "Authors modify own posts"
  ON posts FOR UPDATE
  USING (auth.uid() = author_id)
  WITH CHECK (auth.uid() = author_id);

-- Only authenticated users can create
CREATE POLICY "Auth users can create posts"
  ON posts FOR INSERT
  WITH CHECK (auth.uid() = author_id);

---

Pattern 6: Hierarchical Access

Comments on Posts

-- Comments reference posts
CREATE TABLE comments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  post_id UUID REFERENCES posts(id) ON DELETE CASCADE,
  author_id UUID REFERENCES auth.users(id),
  content TEXT NOT NULL
);

-- Anyone can read comments on published posts
CREATE POLICY "Read comments on published posts"
  ON comments FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM posts
      WHERE posts.id = comments.post_id
      AND posts.status = 'published'
    )
  );

-- Comment authors can edit their comments
CREATE POLICY "Authors edit own comments"
  ON comments FOR UPDATE
  USING (auth.uid() = author_id);

-- Post authors can delete any comment on their posts
CREATE POLICY "Post authors moderate comments"
  ON comments FOR DELETE
  USING (
    EXISTS (
      SELECT 1 FROM posts
      WHERE posts.id = comments.post_id
      AND posts.author_id = auth.uid()
    )
  );

---

Pattern 7: Time-based Access

Limited Time Access

-- Invitations with expiry
CREATE TABLE invitations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id UUID REFERENCES projects(id),
  email TEXT NOT NULL,
  expires_at TIMESTAMPTZ NOT NULL,
  accepted_at TIMESTAMPTZ
);

-- Only view valid (non-expired) invitations
CREATE POLICY "View valid invitations"
  ON invitations FOR SELECT
  USING (
    expires_at > NOW()
    AND accepted_at IS NULL
  );

-- Scheduled content
CREATE POLICY "View published content"
  ON content FOR SELECT
  USING (
    published_at IS NOT NULL
    AND published_at <= NOW()
    AND (expires_at IS NULL OR expires_at > NOW())
  );

---

Pattern 8: Shared Resources

Document Sharing

-- Documents
CREATE TABLE documents (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_id UUID REFERENCES auth.users(id),
  title TEXT NOT NULL,
  content TEXT
);

-- Document shares
CREATE TABLE document_shares (
  document_id UUID REFERENCES documents(id) ON DELETE CASCADE,
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  permission TEXT CHECK (permission IN ('view', 'edit')),
  PRIMARY KEY (document_id, user_id)
);

-- Owner access
CREATE POLICY "Owners have full access"
  ON documents FOR ALL
  USING (auth.uid() = owner_id);

-- Shared view access
CREATE POLICY "Shared users can view"
  ON documents FOR SELECT
  USING (
    id IN (
      SELECT document_id FROM document_shares
      WHERE user_id = auth.uid()
    )
  );

-- Shared edit access
CREATE POLICY "Shared users with edit can update"
  ON documents FOR UPDATE
  USING (
    id IN (
      SELECT document_id FROM document_shares
      WHERE user_id = auth.uid()
      AND permission = 'edit'
    )
  );

---

Performance Tips

Optimize Policy Queries

-- ❌ SLOW: Subquery in every row
CREATE POLICY "slow_policy"
  ON tasks FOR SELECT
  USING (
    project_id IN (
      SELECT project_id FROM project_members
      WHERE user_id = auth.uid()
    )
  );

-- ✅ FASTER: Use function (can be cached)
CREATE OR REPLACE FUNCTION user_project_ids()
RETURNS SETOF UUID AS $$
  SELECT project_id FROM project_members
  WHERE user_id = auth.uid();
$$ LANGUAGE sql SECURITY DEFINER STABLE;

CREATE POLICY "fast_policy"
  ON tasks FOR SELECT
  USING (project_id IN (SELECT user_project_ids()));

Index for RLS

-- Index columns used in policies
CREATE INDEX idx_tasks_user_id ON tasks(user_id);
CREATE INDEX idx_tasks_project_id ON tasks(project_id);
CREATE INDEX idx_project_members_user ON project_members(user_id);

---

Tổng kết

Pattern Selection Guide

Pattern	Use When
Owner Access	Personal data (tasks, notes)
Role-based	Admin/user distinction
Organization	Multi-tenant apps
Team Access	Collaborative projects
Public Read	Blog, public content
Hierarchical	Nested resources
Time-based	Invites, scheduled content
Shared	Document sharing

Best Practices

✅ Use SECURITY DEFINER functions for complex checks
✅ Index columns used in policies
✅ Keep policies simple and readable
✅ Test with different user roles
✅ Multiple policies = OR (any passes = access)

---

Q&A

1. Pattern nào phù hợp với dự án?
2. Có cần combine nhiều patterns?
3. Performance concerns với complex policies?