Discussion on "Rate Limiting Supabase Requests with PostgreSQL and pg_headerkit"

Rodrigo Mansueli · 2023-09-05T11:57:30.546Z

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

One additional gotcha here that we just ran into – an unexpected side effect of:

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

was that while the request_log table was unlogged, the sequence created by id (request_log_id_seq) was logged. this led to us running into a bug when we attempted to update our Postgres: postgrespro.com/list/thread-id/2707900

we were able to resolve (courtesy of Supabase infra team identifying the issue + Claude identifying the fix!) by altering the table to use an unlogged sequence instead.

This is great, thank you Rodrigo Mansueli! You could definitely use this for whitelisting IPs on Supabase as well!

I'm curious- you say it isn't a great idea to rate limit GET requests using this strategy - but then what's to stop someone ddos-ing you (making many repeated requests to the db) simply by selected data frequently?

Oh, and thank you for your excellent article! Hoping this can be used to protect my prod DB from abuse

Thanks Matt C. Supabase (hosted) uses DDoS protection from Cloudflare on the API Gateway, so you'll have some protection for it.

You can still do it, but the extra latency for read calls might be an overkill and hurt your performance more than an acceptable limit IMHO.

Rodrigo Mansueli thanks I appreciate your reply. I've instead set up insert/update rate limiting as above and an IP deny list which is checked during the pre-request. I've got some simple logs to monitor request methods per IP and might try and implement some kind of alerting so I can then add those IPs to the denylist if needed. Would be nice it was possible to trigger this via the logging mechanisms automatically

This is great – we just implemented rate limiting pretty much exactly as described here.

Only small gotcha was the check_rate_limit() function – we forgot that we use HEAD requests to validate username availability during signup, important to exclude those in addition to GET!

Thanks for the feedback, Ann. Great to see you here!

I'll update the article to include HEAD there.

Search Hashnode

Rate Limiting Supabase Requests with PostgreSQL and pg_headerkit

Responses3