Rate Limiting Supabase Requests with PostgreSQL and pg_headerkit
Tags: webdev, ratelimit, api, database
Published: 2023-09-05 12:04:13

---
seoTitle: "Optimize Supabase API: Rate Limiting Guide"
seoDescription: "Learn how to enhance Supabase API performance with rate limiting using PostgREST and pg_headerkit. Ensure fair resource allocation."
datePublished: Tue Sep 05 2023 11:57:30 GMT+0000 (Coordinated Universal Time)
canonical_url: https://blog.mansueli.com/rate-limiting-supabase-requests-with-postgresql-and-pgheaderkit
cuid: clm69aa0i000709ic8xducice
slug: rate-limiting-supabase-requests-with-postgresql-and-pgheaderkit
cover: https://cdn.hashnode.com/res/hashnode/image/upload/v1693690195937/89768eca-c96e-4095-b410-45a115a6b194.png

---

## Introduction

Rate limiting is a critical aspect of web applications that ensures fair usage of resources and prevents abuse. In this blog post, we'll explore how to implement rate limiting for [Supabase](https://supabase.com/) requests using PostgreSQL and the pg\_headerkit extension. This article is part of a series on optimizing Supabase performance, and it builds upon our previous guide on [Boosting Supabase Reliability](https://blog.mansueli.com/how-to-boost-supabase-reliability-a-guide-to-using-postgres-foreign-data-wrappers).

Supabase, a powerful backend platform built on top of PostgreSQL, relies on PostgreSQL as its underlying database. By leveraging PostgreSQL and pg\_headerkit, we can efficiently control the rate at which requests are made to Supabase, ensuring optimal performance and resource allocation.

## Prerequisites

Before we dive into the implementation, make sure you have the following prerequisites:

1. **Supabase Project**: Ensure you have an existing Supabase project with the necessary API endpoints set up.
    
2. **PostgreSQL Database**: Use PostgreSQL as your backend database for Supabase. If you haven't set up PostgreSQL with Supabase yet, follow the official documentation to get started.
    
3. **pg\_headerkit**: You will need to install the pg\_headerkit library. Find installation instructions and more information on this library at [https://database.dev/burggraf/pg\_headerkit](https://database.dev/burggraf/pg_headerkit).
    

## Setting up the Environment

Before we dive into rate limiting, let's ensure we have the necessary prerequisites in place:

1. **Supabase Account**: Make sure you have a Supabase account set up.
    
2. **Database.dev**: Install dbdev using [https://database.dev/installer](https://database.dev/installer).
    

Next, let's install and set up pg\_headerkit:

```sql
SELECT dbdev.install('burggraf-pg_headerkit');
CREATE EXTENSION "burggraf-pg_headerkit" VERSION '1.0.0';
```

## Creating the Rate Limiting Infrastructure

In this section, we'll dive into the process of creating the essential infrastructure for rate limiting within your Supabase-powered application. Rate limiting is a crucial mechanism that allows you to control the number of requests made to your Supabase endpoints, ensuring fair usage of resources and maintaining system stability.

### The `request_log` Table

We begin with the creation of the `request_log` table. This table serves as a main component for tracking and monitoring incoming requests. Here's how we set it up:

```sql
CREATE UNLOGGED TABLE request_log (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY,
  ip inet NOT NULL,
  timestamp timestamptz DEFAULT NOW()
);
```

The `request_log` table has three essential columns:

* `id`: A unique identifier for each log entry, automatically generated.
    
* `ip`: This column captures the client's IP address, helping us identify the source of each request.
    
* `timestamp`: It records the exact time each request was made, ensuring accurate tracking.
    

### The `register_request` Function

With the `request_log` table in place, we proceed to create the `register_request` function. This function plays a pivotal role in the rate-limiting process by logging every incoming request and associating it with the client's IP address. Here's how it's defined:

```sql
CREATE OR REPLACE FUNCTION register_request(ip_in TEXT) 
RETURNS VOID 
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO request_log (ip) 
  VALUES (inet(ip_in));
END;
$$;
```

The `register_request` function takes the client's IP address as input and inserts a corresponding entry into the `request_log` table. This action ensures that we have a comprehensive record of all incoming requests, which is essential for rate limiting and analytics.

With the infrastructure for tracking requests established, we're now ready to move forward with the rate-limiting implementation. In the following sections, we'll explore how to set rate limits and enforce them effectively.

## Cleaning Old Requests

To maintain the efficiency of our system, it's crucial to regularly clean up old request logs. The `clean_old_requests` function takes care of this task:

```sql
CREATE OR REPLACE FUNCTION clean_old_requests() 
RETURNS VOID 
LANGUAGE plpgsql AS $$
BEGIN
  -- Delete request logs older than 12 hours
  DELETE FROM request_log 
  WHERE timestamp < NOW() - INTERVAL '12 hours';
END;
$$;
```

This function ensures that our database remains clutter-free and retains only the most relevant request data.

## Implementing Rate Limiting

Now, let's delve into implementing rate limiting within our Supabase-powered application. Rate limiting is essential to prevent abuse and ensure fair resource allocation. We achieve this through the `exceeded_rate_limit` and `check_rate_limit` functions.

### The `exceeded_rate_limit` Function

The `exceeded_rate_limit` function is responsible for checking if a client has exceeded the rate limit, which in this example is set at 5 requests per minute. Here's how it's defined:

```sql
CREATE OR REPLACE FUNCTION exceeded_rate_limit(ip_in TEXT) 
RETURNS BOOLEAN 
LANGUAGE plpgsql AS $$
DECLARE
  request_count INTEGER;
BEGIN
  SELECT count(*) INTO request_count 
  FROM request_log 
  WHERE ip = inet(ip_in) AND timestamp > NOW() - INTERVAL '1 minute';
    
  RETURN request_count >= 5; -- limit of 5 requests per minute
END;
$$;
```

This function counts the number of requests made by a client within the last minute and returns `true` if the limit is exceeded.

### The `check_rate_limit` Function

The `check_rate_limit` function is pivotal for enforcing rate limits. It effectively manages rate limiting by logging the current request using the `register_request` function and verifying if the rate limit has been surpassed. If the limit is exceeded, it raises an exception:

```sql
CREATE OR REPLACE FUNCTION check_rate_limit() 
RETURNS VOID 
LANGUAGE plpgsql 
SET search_path = public, hdr, extensions
SECURITY DEFINER
AS $$
DECLARE 
  current_ip TEXT := hdr.ip();
  request_method TEXT := current_setting('request.method', TRUE);
BEGIN
  -- Only log non-GET requests because they are run
  -- in read-only transactions
  IF request_method IS NULL OR request_method <> 'GET' THEN
    PERFORM register_request(current_ip);
  END IF;

  -- Check if the rate limit has been exceeded 
  -- and raise an exception if necessary
  IF exceeded_rate_limit(current_ip) THEN
    RAISE EXCEPTION 'Rate limit exceeded';
  END IF;
END;
$$;
```

This function is a crucial component of your rate-limiting strategy, ensuring that each incoming request is correctly monitored and preventing clients from exceeding their allocated rate limits. It's important to note that this function primarily focuses on rate limiting for insert operations. While rate limiting for GET requests is possible, it may introduce performance concerns, such as making network requests that insert rate-limiting data.

## Configuring pg\_headerkit with PostgREST

To seamlessly integrate rate limiting with your Supabase-powered application, configure the `pgrst.db_pre_request` option to utilize the `check_rate_limit` function as a [pre-request](https://postgrest.org/en/stable/references/transactions.html#pre-request) action within PostgREST:

```sql
ALTER ROLE authenticator 
SET pgrst.db_pre_request = 'check_rate_limit';
NOTIFY pgrst, 'reload config';
```

This configuration ensures that every request made to Supabase undergoes rate limit validation before execution, guaranteeing a fair and controlled usage of resources. Now, we can test the rate limit by sending a few post requests to a table:

![Console log showing a few requests, then:{"code":"P0001","details":null,"hint":null,"message":"Rate limit exceeded"}%](https://cdn.hashnode.com/res/hashnode/image/upload/v1693688731283/82c0b0a8-7a04-4840-ac2f-584e79cf99a5.png)

## Scheduled Cleanup

Maintaining the performance of your database requires periodic cleanup of old request logs. Schedule the `clean_old_requests` function to run automatically every midnight:

```sql
SELECT cron.schedule(
  'clean_old_requests',
  '0 0 * * *', -- Run every midnight
  $$ SELECT clean_old_requests(); $$
);
```

This automated cleanup process is crucial for keeping your database in an optimal state, free from unnecessary clutter, and ensuring efficient resource management.

## Conclusion

In this comprehensive blog post, we've delved into the intricacies of implementing rate limiting for your Supabase-powered applications. Leveraging the power of PostgreSQL and the versatile pg\_headerkit extension, we've provided you with a step-by-step guide to ensure fair resource allocation and safeguard your application against abuse.

Rate limiting is a fundamental tool in your arsenal to maintain top-notch performance and deliver a consistently excellent user experience. Armed with the knowledge gained from this article, you're now well-prepared to seamlessly integrate rate limiting into your Supabase application.

Don't stop here; take the concepts discussed in this post and adapt them to your specific use cases. Experiment, explore, and fine-tune your rate-limiting strategy to perfectly align with your application's unique requirements.

If you found this article valuable, you might also be interested in exploring related topics:

* [Boosting Supabase Reliability: A Guide to Using Postgres Foreign Data Wrappers](https://blog.mansueli.com/how-to-boost-supabase-reliability-a-guide-to-using-postgres-foreign-data-wrappers): Learn how to enhance the reliability of your Supabase applications.
    
* [Exploring Data Relationships with Supabase and PostgreSQL](https://blog.mansueli.com/exploring-data-relationships-with-supabase-and-postgresql): Dive deeper into understanding data relationships in Supabase.
    
* [Safeguarding Data Integrity with pg-safeupdate in PostgreSQL and Supabase](https://blog.mansueli.com/safeguarding-data-integrity-with-pg-safeupdate-in-postgresql-and-supabase): Explore techniques to maintain data integrity in your database.
    

For further information and guidance, consider these valuable references. For any questions, feedback, or assistance, please don't hesitate to reach out to me. We're here to help you on your journey to mastering rate limiting in Supabase.

## References

For further information and guidance, consider these valuable references:

* [Supabase Official Documentation](https://supabase.com/docs): Explore Supabase's official documentation for in-depth insights into this powerful backend platform.
    
* [PostgreSQL Official Documentation](https://www.postgresql.org/docs/): Dive into the official documentation of PostgreSQL, the robust database system at the core of Supabase.
    
* [pg\_headerkit Extension](https://database.dev/burggraf/pg_headerkit): For more details on pg\_headerkit, visit the official GitHub repository.
    
* [PostgREST Official Documentation](https://postgrest.org/): Discover PostgREST's documentation for additional information on this helpful tool.

{% cta http://supabase.com?utm_source=devto&utm_medium=referral&utm%5C_term=devtocta %} 🚀 Learn more about Supabase {% endcta %}