Rate Limiting Supabase Requests with PostgreSQL and pg_headerkit
Table of contents
- Setting up the Environment
- Creating the Rate Limiting Infrastructure
- Cleaning Old Requests
- Implementing Rate Limiting
- Configuring pg_headerkit with PostgREST
- Scheduled Cleanup
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.
Before we dive into the implementation, make sure you have the following prerequisites:
Supabase Project: Ensure you have an existing Supabase project with the necessary API endpoints set up.
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.
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:
Supabase Account: Make sure you have a Supabase account set up.
Database.dev: Install dbdev using https://database.dev/installer.
Next, let's install and set up 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.
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()
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.
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)
LANGUAGE plpgsql AS $$
INSERT INTO request_log (ip)
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()
LANGUAGE plpgsql AS $$
-- Delete request logs older than 12 hours
DELETE FROM request_log
WHERE timestamp < NOW() - INTERVAL '12 hours';
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 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)
LANGUAGE plpgsql AS $$
SELECT count(*) INTO request_count
WHERE ip = inet(ip_in) AND timestamp > NOW() - INTERVAL '1 minute';
RETURN request_count >= 5; -- limit of 5 requests per minute
This function counts the number of requests made by a client within the last minute and returns
true if the limit is exceeded.
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()
SET search_path = public, hdr, extensions
current_ip TEXT := hdr.ip();
request_method TEXT := current_setting('request.method', TRUE);
-- Only log non-GET requests because they are run
-- in read-only transactions
IF request_method IS NULL OR request_method <> 'GET' THEN
-- 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';
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:
Maintaining the performance of your database requires periodic cleanup of old request logs. Schedule the
clean_old_requests function to run automatically every midnight:
'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.
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.
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.