On Thu, Dec 1, 2011 at 01:03, Tomas Vondra <tv@fuzzy.cz> wrote:
> On 29.11.2011 23:38, Merlin Moncure wrote:
>> On Tue, Nov 29, 2011 at 7:49 AM, Heiko Wundram <modelnine@modelnine.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.
>
> I'd like to have logon triggers too, but I don't think that's the right
> solution for this problem. For example the logon triggers would be
> called after forking the backend, which means overhead.
>
> The connection limits should be checked when creating the connection
> (validation username/password etc.), before creating the backend.
>
> 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..
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/