{
  "$type": "site.standard.document",
  "bskyPostRef": {
    "cid": "bafyreihq5eidqpmrlio22o7x3qqqp6yvxnxckhvuo6yuennlydj74p4a3m",
    "uri": "at://did:plc:25rdn5elo5izoxrmtis34zuk/app.bsky.feed.post/3mojie4eqrk42"
  },
  "coverImage": {
    "$type": "blob",
    "ref": {
      "$link": "bafkreichmpyvf7n47qubbj4qwsfhxrqp3ibta6v3pjfnm3n3bmhbcrik3y"
    },
    "mimeType": "image/webp",
    "size": 67015
  },
  "path": "/mahdi_benrhouma_fe1c6005/supabase-rls-policy-design-patterns-beyond-the-basics-1g1h",
  "publishedAt": "2026-06-17T22:55:10.000Z",
  "site": "https://dev.to",
  "tags": [
    "supabase",
    "rls",
    "rowlevelsecurity",
    "postgres",
    "https://www.iloveblogs.blog",
    "@supabase"
  ],
  "textContent": "#  Supabase RLS Policy Design Patterns Beyond the Basics\n\nRow Level Security is one of the most powerful features in Supabase — and one of the most misunderstood. Most tutorials stop at `auth.uid() = user_id`. That gets you through a simple personal data model, but the moment you introduce teams, roles, organizations, or any shared resource, you need a more structured approach.\n\nThis guide covers advanced RLS policy patterns used in real production apps: multi-role systems, team-based access control, hierarchical permissions, and how to keep policies performant as your data grows. Every pattern includes copy-paste SQL you can adapt immediately.\n\n**Estimated read time: 16 minutes**\n\n##  Prerequisites\n\n  * Supabase project with Auth enabled\n  * Familiarity with basic RLS (`CREATE POLICY`, `auth.uid()`)\n  * Basic PostgreSQL knowledge (JOINs, functions)\n  * Next.js app using `@supabase/ssr` or `@supabase/auth-helpers-nextjs`\n\n\n\n##  How RLS Actually Works (The Mental Model You Need)\n\nBefore patterns, get this straight: RLS policies are **predicate filters appended to every query**. When you write:\n\n\n\n    CREATE POLICY \"users can read own data\"\n    ON profiles\n    FOR SELECT\n    USING (auth.uid() = user_id);\n\n\nPostgres rewrites every `SELECT` on `profiles` to include `WHERE auth.uid() = user_id`. This happens at the database level — no application code can bypass it (unless using the service role key).\n\nTwo clauses matter:\n\n  * `USING` — filters rows for SELECT, UPDATE, DELETE\n  * `WITH CHECK` — validates rows on INSERT, UPDATE\n\n\n\nAlways define both for tables that accept writes.\n\n##  Pattern 1: Role-Based Access with a Roles Table\n\nThe naive approach is storing roles in `user_metadata`. Don't. Metadata is controlled by the client and can be spoofed. Store roles in a database table.\n\n\n\n    -- roles table\n    CREATE TABLE user_roles (\n      user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,\n      role TEXT NOT NULL CHECK (role IN ('admin', 'editor', 'viewer')),\n      PRIMARY KEY (user_id)\n    );\n\n    -- helper function (security definer = runs as postgres, not the calling user)\n    CREATE OR REPLACE FUNCTION get_user_role()\n    RETURNS TEXT\n    LANGUAGE sql\n    STABLE\n    SECURITY DEFINER\n    AS $$\n      SELECT role FROM user_roles WHERE user_id = auth.uid();\n    $$;\n\n\nNow use it in policies:\n\n\n\n    -- admins can read everything, others read only published content\n    CREATE POLICY \"role-based content access\"\n    ON articles\n    FOR SELECT\n    USING (\n      get_user_role() = 'admin'\n      OR status = 'published'\n    );\n\n    -- only admins and editors can insert\n    CREATE POLICY \"editors can create articles\"\n    ON articles\n    FOR INSERT\n    WITH CHECK (\n      get_user_role() IN ('admin', 'editor')\n    );\n\n\nThe `SECURITY DEFINER` function is critical here. Without it, the subquery inside the policy runs as the calling user, which can cause permission errors on the `user_roles` table itself.\n\n[INTERNAL LINK: supabase-authentication-authorization]\n\n##  Pattern 2: Team / Organization Membership\n\nThis is the most common pattern in SaaS apps. Users belong to organizations, and resources belong to organizations.\n\n\n\n    -- org membership table\n    CREATE TABLE org_members (\n      org_id UUID NOT NULL,\n      user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,\n      role TEXT NOT NULL CHECK (role IN ('owner', 'admin', 'member')),\n      PRIMARY KEY (org_id, user_id)\n    );\n\n    -- projects belong to orgs\n    CREATE TABLE projects (\n      id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n      org_id UUID NOT NULL,\n      name TEXT NOT NULL,\n      created_at TIMESTAMPTZ DEFAULT now()\n    );\n\n\nPolicy: members of an org can read its projects.\n\n\n\n    CREATE POLICY \"org members can read projects\"\n    ON projects\n    FOR SELECT\n    USING (\n      EXISTS (\n        SELECT 1 FROM org_members\n        WHERE org_members.org_id = projects.org_id\n        AND org_members.user_id = auth.uid()\n      )\n    );\n\n    -- only org admins/owners can create projects\n    CREATE POLICY \"org admins can create projects\"\n    ON projects\n    FOR INSERT\n    WITH CHECK (\n      EXISTS (\n        SELECT 1 FROM org_members\n        WHERE org_members.org_id = projects.org_id\n        AND org_members.user_id = auth.uid()\n        AND org_members.role IN ('owner', 'admin')\n      )\n    );\n\n\n**Performance note:** Add an index on `org_members(user_id)` and `org_members(org_id)`. The `EXISTS` subquery runs on every row evaluation — without indexes, this becomes a full table scan at scale.\n\n\n\n    CREATE INDEX idx_org_members_user_id ON org_members(user_id);\n    CREATE INDEX idx_org_members_org_id ON org_members(org_id);\n\n\n##  Pattern 3: Hierarchical Permissions (Owner > Admin > Member)\n\nSometimes you need cascading permissions: owners can do everything admins can, admins can do everything members can. A helper function keeps policies clean.\n\n\n\n    CREATE OR REPLACE FUNCTION user_org_role(p_org_id UUID)\n    RETURNS TEXT\n    LANGUAGE sql\n    STABLE\n    SECURITY DEFINER\n    AS $$\n      SELECT role FROM org_members\n      WHERE org_id = p_org_id AND user_id = auth.uid();\n    $$;\n\n    -- helper: check if user has at least a given role level\n    CREATE OR REPLACE FUNCTION user_has_org_role(p_org_id UUID, p_min_role TEXT)\n    RETURNS BOOLEAN\n    LANGUAGE sql\n    STABLE\n    SECURITY DEFINER\n    AS $$\n      SELECT CASE\n        WHEN p_min_role = 'member' THEN\n          user_org_role(p_org_id) IN ('member', 'admin', 'owner')\n        WHEN p_min_role = 'admin' THEN\n          user_org_role(p_org_id) IN ('admin', 'owner')\n        WHEN p_min_role = 'owner' THEN\n          user_org_role(p_org_id) = 'owner'\n        ELSE false\n      END;\n    $$;\n\n\nNow policies read like documentation:\n\n\n\n    -- delete requires owner\n    CREATE POLICY \"owners can delete projects\"\n    ON projects\n    FOR DELETE\n    USING (user_has_org_role(org_id, 'owner'));\n\n    -- update requires admin or above\n    CREATE POLICY \"admins can update projects\"\n    ON projects\n    FOR UPDATE\n    USING (user_has_org_role(org_id, 'admin'))\n    WITH CHECK (user_has_org_role(org_id, 'admin'));\n\n\n##  Pattern 4: Resource Sharing and Invitations\n\nUsers sometimes need access to specific resources without being full org members — think shared documents or guest access.\n\n\n\n    CREATE TABLE resource_shares (\n      resource_id UUID NOT NULL,\n      resource_type TEXT NOT NULL,\n      shared_with UUID REFERENCES auth.users(id) ON DELETE CASCADE,\n      permission TEXT NOT NULL CHECK (permission IN ('read', 'write')),\n      expires_at TIMESTAMPTZ,\n      PRIMARY KEY (resource_id, shared_with)\n    );\n\n\nPolicy that combines ownership and sharing:\n\n\n\n    CREATE POLICY \"owners and shared users can read documents\"\n    ON documents\n    FOR SELECT\n    USING (\n      -- owner\n      owner_id = auth.uid()\n      OR\n      -- explicitly shared\n      EXISTS (\n        SELECT 1 FROM resource_shares\n        WHERE resource_shares.resource_id = documents.id\n        AND resource_shares.resource_type = 'document'\n        AND resource_shares.shared_with = auth.uid()\n        AND (resource_shares.expires_at IS NULL OR resource_shares.expires_at > now())\n      )\n    );\n\n\n##  Pattern 5: Public + Authenticated Mixed Access\n\nA common pattern for content sites: public content is readable by anyone, private content only by the owner.\n\n\n\n    CREATE POLICY \"public content is readable by all\"\n    ON posts\n    FOR SELECT\n    USING (\n      visibility = 'public'\n      OR author_id = auth.uid()\n    );\n\n\nFor anonymous users, `auth.uid()` returns `NULL`. The `OR author_id = auth.uid()` clause evaluates to `NULL = NULL` which is `false` in SQL — so anonymous users only see public posts. This is correct behavior, but it's worth being explicit about.\n\nIf you want to allow anonymous reads of public content but block all writes:\n\n\n\n    -- no INSERT policy = nobody can insert (including authenticated users)\n    -- add explicit policies only for the roles that need write access\n\n\nAbsence of a policy is a deny. This is the default-deny model that makes RLS safe.\n\n##  Pattern 6: Audit Trails Without Bypassing RLS\n\nA common mistake: using the service role in a trigger to write audit logs, which bypasses RLS on the audit table. Instead, use a `SECURITY DEFINER` function that writes to the audit table directly.\n\n\n\n    CREATE TABLE audit_log (\n      id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n      table_name TEXT,\n      operation TEXT,\n      row_id UUID,\n      changed_by UUID,\n      changed_at TIMESTAMPTZ DEFAULT now(),\n      old_data JSONB,\n      new_data JSONB\n    );\n\n    -- RLS: only admins can read audit logs\n    ALTER TABLE audit_log ENABLE ROW LEVEL SECURITY;\n\n    CREATE POLICY \"admins read audit log\"\n    ON audit_log\n    FOR SELECT\n    USING (get_user_role() = 'admin');\n\n    -- trigger function runs as postgres (SECURITY DEFINER)\n    CREATE OR REPLACE FUNCTION log_changes()\n    RETURNS TRIGGER\n    LANGUAGE plpgsql\n    SECURITY DEFINER\n    AS $$\n    BEGIN\n      INSERT INTO audit_log (table_name, operation, row_id, changed_by, old_data, new_data)\n      VALUES (\n        TG_TABLE_NAME,\n        TG_OP,\n        COALESCE(NEW.id, OLD.id),\n        auth.uid(),\n        CASE WHEN TG_OP = 'DELETE' THEN row_to_json(OLD)::jsonb ELSE NULL END,\n        CASE WHEN TG_OP != 'DELETE' THEN row_to_json(NEW)::jsonb ELSE NULL END\n      );\n      RETURN COALESCE(NEW, OLD);\n    END;\n    $$;\n\n\n[INTERNAL LINK: supabase-postgres-functions-triggers-guide]\n\n##  Common Pitfalls\n\n**Forgetting`WITH CHECK` on UPDATE policies.** `USING` controls which rows can be targeted, but `WITH CHECK` controls what the row looks like after the update. Without `WITH CHECK`, a user could update a row they own to assign it to another user.\n\n**Using`auth.jwt()` claims for authorization.** JWT claims are set at login time and don't reflect real-time role changes. A user whose role was revoked still has the old claim until their token expires. Use database lookups via `SECURITY DEFINER` functions instead.\n\n**Not testing with the anon key.** Always test your policies using the anon key (not the service role) in the Supabase dashboard SQL editor. The service role bypasses everything.\n\n**Policies on tables without indexes.** Every policy condition is evaluated per row. A subquery in a policy without an index causes a full table scan for every row in the outer query. Profile with `EXPLAIN ANALYZE` before going to production.\n\n**Multiple permissive policies are OR'd together.** If you have two `FOR SELECT` policies on the same table, a row is visible if _either_ policy passes. This is often surprising. Use a single policy with explicit OR conditions if you want clear logic.\n\n##  Testing Your Policies\n\nUse the Supabase SQL editor with `SET LOCAL role = authenticated` and `SET LOCAL request.jwt.claims = '{\"sub\": \"<user-id>\"}'` to simulate a specific user:\n\n\n\n    -- simulate a specific user\n    SET LOCAL role = authenticated;\n    SET LOCAL \"request.jwt.claims\" = '{\"sub\": \"your-user-uuid-here\"}';\n\n    -- now run your query — it will respect RLS as that user\n    SELECT * FROM projects WHERE org_id = 'your-org-id';\n\n\nThis is far faster than testing through your application layer.\n\n##  Summary and Next Steps\n\nAdvanced RLS comes down to a few principles: store roles in the database (not JWT claims), use `SECURITY DEFINER` functions to encapsulate expensive lookups, index every column used in policy conditions, and always test with the anon key.\n\nThe patterns here — role tables, org membership, hierarchical roles, resource sharing — cover the vast majority of real-world authorization requirements. Combine them as needed for your data model.\n\nRelated reading:\n\n  * [INTERNAL LINK: nextjs-supabase-advanced-authentication-patterns]\n  * [INTERNAL LINK: nextjs-supabase-security-best-practices]\n  * [INTERNAL LINK: nextjs-supabase-multi-tenant-saas-architecture]\n\n\n\n_Originally published at https://www.iloveblogs.blog_",
  "title": "Supabase RLS Policy Design Patterns Beyond the Basics"
}