Supabase IP Ban Issue with Connection Pooling

[fredericenard]

Bug report

• [x] I confirm this is a bug with Supabase, not with my own application.
• [X] I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

I recently added new tests in my backend directly using the connection string (with pooling) to:

• Create new databases
• Apply migrations with Django
• Create db roles and check if the right permissions have been granted. This includes tests with unauthorized SQL statements for some roles.

When running tests, I frequently observe an IP address being banned in Network Restrictions. It's always the same: 2600:1f18:2e13:9d01:19c9:c8a9:9833:652d

This occurs both when running tests locally and in my CI environment and prevent any subsequent query to run against the db. If I remove this IP from Network Restrictions it just reappears few seconds later. But if I wait around 30 minutes, I can run my tests 1 or 2 times before it starts happening again.

What is this IP? It seems it's located in Virginia, next to my Supabase instance. Does it have something to do with pooling?

Expected behavior

I expect to execute the operations outlined in my test suite without experiencing repeated bans of this IP:

• Such tests should not inherently trigger security measures leading to an IP ban.
• I don't even understand what this IP is, it's not mine and not the one of a Github runner.

System information

This occurs both when running tests locally and in my CI environment.

[fredericenard]

@encima thanks for looking at it! Happy to have a quick call with you to provide more details.

[encima]

Let me check internally first and see if we can find anything odd. Will come back to you soon :)

[encima]

You say that this IP ban prevents you from running subsequent queries but it is not yours or the runner, so it is related to your infra (or ours) somehow.

First, are you connecting to the DB via Supavisor or directly?

[fredericenard]

I'm using the connection string appearing below this:

It's happening in transaction and session mode.

I don't think this could be in my infra given I got the same problem when using this connection string with psql.

[encima]

Thanks for the additional info, this looks to be an issue with Supavisor (the connection pooler); logic is in place for this not to get banned but there may be some of your tests that are an edge case.

The unauthorized statements that you try, are they unauthorized actions based on your schema or are there also tests for items that may not exist?

[fredericenard]

Hey sorry for the late reply. The unauthorized statements I tried were actions based on my schema. But I think I have a better understanding of the problem now.

During my testing, I can create and interact with databases and roles seamlessly. Yet, I encounter issues when attempting to delete them. Although no errors are reported in my code, Postgres logs indicate problems with non-existent databases or roles.

Specifically, when I attempt to delete a role, the action is mistakenly interpreted as a password error, leading to the banning of an unidentified IP address. This is puzzling because I can successfully connect using the role in question. After ceasing the deletion of roles at the end of my tests, the IP banning issue stopped, but I still observe log entries concerning non-existent databases:

Additionally, after updating a role's password, I found that it was still possible to log in using the old password, while the new password was not recognized.

Is it possible that there's a delay or caching issue within Supavisor that affects the immediate recognition of changes to database objects?