Rate Limiting Supabase Requests with PostgreSQL and pg_headerkit

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 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.

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.

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.

Next, let's install and set up pg_headerkit:

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:

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:

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:

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:

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:

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 action within PostgREST:

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:

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:

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: Learn how to enhance the reliability of your Supabase applications.

• 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: 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: Explore Supabase's official documentation for in-depth insights into this powerful backend platform.

• PostgreSQL Official Documentation: Dive into the official documentation of PostgreSQL, the robust database system at the core of Supabase.

• pg_headerkit Extension: For more details on pg_headerkit, visit the official GitHub repository.

• PostgREST Official Documentation: Discover PostgREST's documentation for additional information on this helpful tool.

🚀 Learn more about Supabase