Re: Limiting number of connections to PostgreSQL per IP (not per DB/user)? - Mailing list pgsql-general

From Tomas Vondra
Subject Re: Limiting number of connections to PostgreSQL per IP (not per DB/user)?
Date
Msg-id c36d61aab01cf53876ca8ed6a7ab6eaa.squirrel@sq.gransy.com
Whole thread Raw
In response to Limiting number of connections to PostgreSQL per IP (not per DB/user)?  (Heiko Wundram <modelnine@modelnine.org>)
List pgsql-general
On 1 Prosinec 2011, 13:47, Magnus Hagander wrote:
> On Thu, Dec 1, 2011 at 01:03, Tomas Vondra <tv@fuzzy.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


pgsql-general by date:

Previous
From: Jim Buttafuoco
Date:
Subject: Replication issue
Next
From: "Tomas Vondra"
Date:
Subject: Re: psql query gets stuck indefinitely