Store for PostgreSQL (Postgres)

[adrianprelipcean]

Hi, I implemented a PostgreSQL Store for express-rate-limit, in case some of you work with PostgreSQL. The node package is available here and some examples on how to use it are available here. I hope it helps!

[gamemaker1]

Wow! Thank you so much for your contribution!

We should add the store to the stores list in the readme. Would you like to make a PR for the same?

[adrianprelipcean]

PR was created - here

[nfriedly]

That's awesome!

If you'd like, we can invite you to the express-rate-limit org, and then you could move the store here and @gamemaker1 and I can help you maintain it. But no pressure, you can keep it on your own profile if you prefer.

(I made the offer to the other folks who made stores in #374 and that's why the redis and memcached stores are now part of this org.)

[adrianprelipcean]

Sure, I can transfer the repo, let me know when you added me to the organization so I can start the process.

[nfriedly]

Invite sent

[adrianprelipcean]

I have transferred the repo and added you and @gamemaker1 as a maintainer to npm.

[gamemaker1]

Thank you!!

[nfriedly]

I'm curious about the aggregated records vs individual records split - aggregated obviously uses less disk space, but what are the other advantages to one over the other. (It might be worth discussing in the readme also.)

[adrianprelipcean]

That's a good point, at this stage there is no inherent advantage of using the individual records, but this is related to some of the things I want to look into later on. I see two main use cases for individual records:

1. individual records allow for implementing different types of rate limiting strategies (in particular the sliding window rate limiter) - the current implementation will not apply the correct limit at the edge of the time window. Consider the following simple example: if we allow 5 entries per IP every minute, you can have an IP making a request at 12:00:00 (which starts the session time window that will expire at 12:01:00), then 4 requests at 12:00:59, and 5 requests again at 12:01:01 - this means that between 12:00:58 and 12:00:02 the same IP can make 9 requests - which implies a worst case scenario of 2 X limit - 1 (or 2 X limit if the rate limit sessions are not separated per IP) within a time window. A sliding time window wouldn't have a fixed starting point for the rate limit session, since it would be relative to the current time.
2. I view the store as a store of interactions with the API, in which case I can see this being used as either an audit mechanism (with the compliance issues that comes with it) or as building the data input for a threat evaluation mechanism - in which case you could use a history of records to identify bots and bad actors that are misusing the API service, and then applying offsets to the rate counters to either block flagged IPs or apply a lower limit for potentially problematic IPs.

Both of these cases need further work and the limitations are specific to the PostgreSQL store implementation (I haven't checked whether the other stores suffer from the same limitations, although it might be worthwhile to look into)

Hope this clarifies why I added the separate individual record implementation.

[nfriedly]

Yeah, that does help, thanks.

We had a similar issue to what you described for point 1 with the memory store, and I finally just re-wrote it to store a timestamp per user recently. Now it stores records consisting of a key + a hit count in two batches. The "current" batch has records from the current window that won't reset until the next window. The "previous" batch has records from the previous window that will reset sometime during the current window. As new hits come in, we'll move the record from the previous window to the current one, resetting it if necessary. At the end of the window, any records left in the previous batch can be reset in a single group operation, and then the current window becomes the previous window.

Point 2 is interesting. I'm not sure the rate-limiter is the best place to do that, but I suppose it's not the worst either.

[adrianprelipcean]

I understand your concern about point 2, it would indeed introduce out of scope logic into the rate limiter.