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

On Tue, Nov 29, 2011 at 7:49 AM, Heiko Wundram <modelnine(at)modelnine(dot)org> wrote:
> Hello!
>
> Sorry for that subscribe post I've just sent, that was bad reading on my
> part (for the subscribe info on the homepage).
>
> Anyway, the title says it all: is there any possibility to limit the number
> of connections that a client can have concurrently with a PostgreSQL-Server
> with "on-board" means (where I can't influence which user/database the
> clients use, rather, the clients mostly all use the same user/database, and
> I want to make sure that a single client which runs amok doesn't kill
> connectivity for other clients)? I could surely implement this with a proxy
> sitting in front of the server, but I'd rather implement this with
> PostgreSQL directly.
>
> I'm using (and need to stick with) PostgreSQL 8.3 because of the frontend
> software in question.
>
> Thanks for any hints!

I think the (hypothetical) general solution for these types of
problems is to have logon triggers. It's one of the (very) few things
I envy from SQL Server -- see here:
http://msdn.microsoft.com/en-us/library/bb326598.aspx.

Barring the above, if you can trust the client to call a function upon
connection I'd just do that and handle the error on the client with a
connection drop. Barring *that*, I'd be putting my clients in front of
pgbouncer with some patches to the same to get what I needed
(pgbouncer is single threaded making firewally type features quite
easy to implement in an ad hoc fashion).

merlin