Using Triggers to Map Database Relationships in Custom Claims
Tags: postgres, sql, supabase, triggers
Published: 2023-05-30 17:22:12

---
title: "Using Triggers to Map Database Relationships in Custom Claims"
datePublished: Tue May 30 2023 14:25:39 GMT+0000 (Coordinated Universal Time)
cuid: cliadfbb703x9zhnv7jt79cdi
canonical_url: https://blog.mansueli.com/using-triggers-to-map-database-relationships-in-custom-claims
slug: using-triggers-to-map-database-relationships-in-custom-claims
cover: https://cdn.hashnode.com/res/hashnode/image/upload/v1684956068071/7505d73f-4005-4268-8a91-f27362d49ba6.png
tags: postgresql, sql, supabase, triggers

---

Custom claims are a powerful tool for implementing row-level security (RLS) policies in your applications which was popularized by [Supabase](https://supabase.com). In a previous blog post (Part 1: "[Using Custom Claims: Testing RLS with Supabase](https://blog.mansueli.com/using-custom-claims-testing-rls-with-supabase)"), we explored the basics of custom claims and their application in Supabase. In this Part 2, we will delve deeper into the topic by introducing triggers and how they can be used to map database relationships in [custom claims](https://github.com/supabase-community/supabase-custom-claims).

Mapping relationships between database tables is not only essential for enforcing access control but also plays a crucial role in managing data visibility. Traditionally, RLS policies would require multiple joins to determine the relationships between entities and apply appropriate access restrictions. However, with triggers and custom claims, we can achieve a more performant solution.

By leveraging triggers in PostgreSQL, we can automate the process of updating custom claims whenever there are changes in the relationships between entities. This means that instead of making multiple joins every time access control is enforced, the relationships are stored within the custom claims themselves. This approach significantly improves the efficiency and performance of your application's access control implementation.

In this blog post, we will walk you through the creation and implementation of a trigger function that updates custom claims based on changes in a relationship table. By utilizing triggers, you can simplify the management of claims and enhance the security of your Supabase-powered applications while maintaining optimal performance.

Let's explore how triggers can revolutionize the way you handle claims and take your application's security to the next level.

## Mapping Relationships with Triggers

To demonstrate the concept, we'll use a relationship table called `team_members`. This table connects users and teams, allowing us to define which users belong to which teams. Here's the structure of the `team_members` table:

```sql
-- Table that we will be mapping on claims
create table
  public.team_members (
    id bigint generated by default as identity not null,
    created_at timestamp with time zone null default now(),
    user_id uuid null,
    team_id integer null,
    constraint profiles_pkey primary key (id),
    constraint team_members_team_id_fkey foreign key (team_id) references teams (id),
    constraint team_members_user_id_fkey foreign key (user_id) references auth.users (id)
  );
```

Now, let's dive into the trigger function responsible for updating the custom claims whenever changes occur in the `team_members` table.

## **Full Trigger Function for Updating Custom Claims**

In this section, we will explore the complete trigger function responsible for updating the custom claims when changes occur in the `team_members` table. The trigger function plays a crucial role in automating the process of managing and maintaining accurate custom claims based on the relationships between entities. By understanding the inner workings of this function, you'll gain valuable insights into how Supabase enables seamless and efficient implementation of row-level security policies. Then, we will delve into a detailed explanation of each component of the trigger function. But first, let's take a look at the full code of the function itself:

```sql
CREATE FUNCTION team_member_claim()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS $$
DECLARE
  -- This function works to map if team_id was UUID
  old_team_id INTEGER; 
  new_team_id INTEGER;
  affected_user_id UUID;
  current_claim jsonb;
  updated_claim jsonb;
BEGIN
  -- Retrieve the relevant IDs based on the operation type
  IF TG_OP = 'DELETE' THEN
    old_team_id := OLD.team_id;
    affected_user_id := OLD.user_id;
  ELSIF TG_OP = 'UPDATE' THEN
    old_team_id := OLD.team_id;
    new_team_id := NEW.team_id;
    affected_user_id := NEW.user_id;
  ELSE
    new_team_id := NEW.team_id;
    affected_user_id := NEW.user_id;
  END IF;
  -- Retrieve the current custom claim for the affected user
  current_claim := get_claim(affected_user_id, 'team_member');
  -- Update the claim based on the operation type
  IF TG_OP = 'UPDATE' THEN
    -- Remove the old_team_id from the claim
    SELECT jsonb_agg(elem)
    INTO updated_claim
    FROM jsonb_array_elements(current_claim) elem
    WHERE elem::integer <> old_team_id;
    -- Add the new_team_id to the claim
    updated_claim := updated_claim || jsonb_build_array(new_team_id);
    -- Update the claim
    PERFORM set_claim(affected_user_id, 'team_member', updated_claim);
    RAISE NOTICE 'Set claim (update team): %', updated_claim;
  ELSE
    IF current_claim IS NULL THEN
      IF TG_OP <> 'DELETE' THEN
      -- Set the claim with a new_team_id when it was previously empty
        PERFORM set_claim(affected_user_id, 'team_member', jsonb_build_array(new_team_id));
        RAISE NOTICE 'Set claim (null): %', jsonb_build_array(new_team_id);
      END IF;
    ELSIF current_claim @> jsonb_build_array(old_team_id) THEN
      IF TG_OP = 'DELETE' THEN
        -- Remove the old_team_id from the claim
        SELECT jsonb_agg(elem)
        INTO updated_claim
        FROM jsonb_array_elements(current_claim) elem
        WHERE elem::integer <> old_team_id;

        -- Update the claim
        PERFORM set_claim(affected_user_id, 'team_member', updated_claim);
        RAISE NOTICE 'Set claim (remove team): %', updated_claim;
      END IF;
    ELSE
      IF TG_OP <> 'DELETE' THEN
        -- Add the new_team_id to the claim
        PERFORM set_claim(affected_user_id, 'team_member', current_claim || jsonb_build_array(new_team_id));
        RAISE NOTICE 'Set claim (add team): %', current_claim || jsonb_build_array(new_team_id);
      END IF;
    END IF;
  END IF;
  -- Return the appropriate value based on the operation type
  IF TG_OP = 'DELETE' THEN
    RETURN OLD;
  ELSE
    RETURN NEW;
  END IF;
END;
$$;
```

## Explanation of the Trigger Function

Let's break down the logic behind the trigger function:

1. The function is defined as a trigger function, indicated by the `RETURNS TRIGGER` statement.
    
2. Inside the function, we declare variables to store the old and new team IDs, the affected user ID, the current custom claim, and the updated custom claim.
    
3. Depending on the operation type (DELETE, UPDATE, or INSERT), we assign values to the variables accordingly.
    
4. We retrieve the current custom claim for the affected user using the `get_claim` function.
    
5. Based on the operation type, we modify the claim:
    * For an UPDATE operation, we remove the `old_team_id` from the claim and add the `new_team_id` to it.
    * If the current claim is NULL, we set the claim with the `new_team_id`.
    * If the claim already contains the `old_team_id`, we remove it.
    * If none of the above conditions apply, we add the `new_team_id` to the claim.
        
6. We update the claim using the `set_claim` function and raise a notice to log the updated claim.
    
7. Finally, we return the appropriate value based on the operation type.
    

## Deploying the trigger to leverage this function

We can create a single trigger that works on all operations and use it to manage the table relationships in our claims.

```sql
CREATE TRIGGER team_member_claim_trigger
AFTER INSERT OR UPDATE OR DELETE ON team_members
FOR EACH ROW
EXECUTE FUNCTION team_member_claim();
```

This huge trigger function might feel overwhelming. It is possible and fine if you split it into different functions to simplify the logic. Here's an example of splitting the logic across different trigger functions and creating the triggers for each case.

### Insert trigger function:

```sql
CREATE FUNCTION team_member_claim_insert()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS $$
DECLARE
  new_team_id INTEGER := NEW.team_id;
  affected_user_id UUID := NEW.user_id;
  current_claim jsonb := get_claim(affected_user_id, 'team_member');
BEGIN
  IF current_claim IS NULL THEN
    PERFORM set_claim(affected_user_id, 'team_member', jsonb_build_array(new_team_id));
    RAISE NOTICE 'Set claim (null): %', jsonb_build_array(new_team_id);
  ELSE
    PERFORM set_claim(affected_user_id, 'team_member', current_claim || jsonb_build_array(new_team_id));
    RAISE NOTICE 'Set claim (add team): %', current_claim || jsonb_build_array(new_team_id);
  END IF;
  RETURN NEW;
END;
$$;
```

### Delete trigger function:

```sql
CREATE FUNCTION team_member_claim_delete()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS $$
DECLARE
  old_team_id INTEGER := OLD.team_id;
  affected_user_id UUID := OLD.user_id;
  current_claim jsonb := get_claim(affected_user_id, 'team_member');
  updated_claim jsonb;
BEGIN
  IF current_claim @> jsonb_build_array(old_team_id) THEN
    SELECT jsonb_agg(elem)
    INTO updated_claim
    FROM jsonb_array_elements(current_claim) elem
    WHERE elem::integer <> old_team_id;
    PERFORM set_claim(affected_user_id, 'team_member', updated_claim);
    RAISE NOTICE 'Set claim (remove team): %', updated_claim;
  END IF;
  RETURN OLD;
END;
$$;
```

### Update trigger function

```sql
CREATE FUNCTION team_member_claim_update()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS $$
DECLARE
  old_team_id INTEGER := OLD.team_id;
  new_team_id INTEGER := NEW.team_id;
  affected_user_id UUID := NEW.user_id;
  current_claim jsonb := get_claim(affected_user_id, 'team_member');
  updated_claim jsonb;
BEGIN
  SELECT jsonb_agg(elem)
  INTO updated_claim
  FROM jsonb_array_elements(current_claim) elem
  WHERE elem::integer <> old_team_id;
  updated_claim := updated_claim || jsonb_build_array(new_team_id);
  PERFORM set_claim(affected_user_id, 'team_member', updated_claim);
  RAISE NOTICE 'Set claim (update team): %', updated_claim;
  RETURN NEW;
END;
$$;
```

### Creating individual triggers

```sql
CREATE TRIGGER team_member_claim_insert_trigger
AFTER INSERT ON team_member
FOR EACH ROW EXECUTE PROCEDURE team_member_claim_insert();

CREATE TRIGGER team_member_claim_delete_trigger
AFTER DELETE ON team_member
FOR EACH ROW EXECUTE PROCEDURE team_member_claim_delete();

CREATE TRIGGER team_member_claim_update_trigger
AFTER UPDATE ON team_member
FOR EACH ROW EXECUTE PROCEDURE team_member_claim_update();
```

## Conclusion

In this blog post, we explored how triggers can be used to map database relationships in custom claims. By automating the process of updating custom claims when changes occur in the relationship table, we can enhance the efficiency and security of our applications. The trigger function we created allows for the seamless management of claims and ensures that access control remains accurate and up-to-date.

By leveraging triggers and custom claims, you can build robust row-level security mechanisms in your Supabase-powered applications, providing a granular level of access control to your users. Having the claims is very helpful when building policies for [storage buckets](https://supabase.com/docs/guides/storage/access-control).

Stay tuned for more exciting topics on leveraging Supabase and custom claims to build powerful and secure applications.