Re: Limiting number of connections to PostgreSQL per IP (not per DB/user)?

On 1 Prosinec 2011, 13:47, Magnus Hagander wrote:
> On Thu, Dec 1, 2011 at 01:03, Tomas Vondra <tv(at)fuzzy(dot)cz> wrote:
>> Anyway, I do have an idea how this could be done using a shared library
>> (so it has the same disadvantages as logon triggers). Hopefully I'll
>> have time to implement a PoC of this over the weekend.
>
> We have an authentication hook that could probably be used to
> implement this. See the authdelay module for an example that uses it.
> It does require it to be written in C, of course, but for a usecase
> like this that is probably not unreasonable..

Hm, I was thinking about that, but my original idea was to keep my own
counters and update them at backend start/end (using
local_preload_libraries). The auth hook handles just the logon event, not
logout, so I would be unable to update the counters when the user
disconnects.

But now I think it might actually work quite well with pg_stat_activity
instead of custom counters. And IIRC it's called before a separate backend
is forked, so it avoids the overhead of forking a backend and then finding
out the user/IP already uses too many connections.

Tomas