Thread: Connection limit and Superuser
It appears that the superuser does not have connection limit enforcement. I think this should be changed. Slony in particular does not need more than N connections but does require being a super user. --
On Mon, Jul 31, 2006 at 08:47:38AM -0400, Rod Taylor wrote: > It appears that the superuser does not have connection limit > enforcement. I think this should be changed. So if some admin process goes awry and uses up all the connection slots, how does the admin get in to see what's happening? If there's a limit you're not really superuser, are you? > Slony in particular does not need more than N connections but does > require being a super user. Maybe someone should look into enabling slony to not run as a superuser? Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Rod Taylor <pg@rbt.ca> writes:
> It appears that the superuser does not have connection limit
> enforcement. I think this should be changed.
If you're superuser, you are not subject to access restrictions,
by definition. I cannot imagine any scenario under which the
above would be a good idea. (Hint: it would be more likely to
lock out manual admin connections than Slony.)
regards, tom lane
On Mon, 2006-07-31 at 15:00, Martijn van Oosterhout wrote: > On Mon, Jul 31, 2006 at 08:47:38AM -0400, Rod Taylor wrote: > > It appears that the superuser does not have connection limit > > enforcement. I think this should be changed. > > So if some admin process goes awry and uses up all the connection > slots, how does the admin get in to see what's happening? If there's a > limit you're not really superuser, are you? I thought there is a limit for super-users too... citation from: http://www.postgresql.org/docs/8.1/static/runtime-config-connection.html#RUNTIME-CONFIG-CONNECTION-SETTINGS max_connections (integer) Determines the maximum number of concurrent connections to the database server.The default is typically 100, but may be less if your kernel settings will not support it (as determined during initdb). This parameter can only be set at server start. Increasing this parameter may cause PostgreSQLto request more System V shared memory or semaphores than your operating system's default configurationallows. See Section 16.4.1 for information on how to adjust those parameters, if necessary. superuser_reserved_connections (integer) Determines the number of connection "slots" that are reserved for connections by PostgreSQL superusers. At most max_connections connections can ever be active simultaneously. Whenever the number of active concurrent connections is at least max_connections minus superuser_reserved_connections,new connections will be accepted only for superusers. The default valueis 2. The value must be less than the value of max_connections. This parameter can only be set at server start. Cheers, Csaba.
Martijn van Oosterhout wrote: > On Mon, Jul 31, 2006 at 08:47:38AM -0400, Rod Taylor wrote: > >> It appears that the superuser does not have connection limit >> enforcement. I think this should be changed. >> > > So if some admin process goes awry and uses up all the connection > slots, how does the admin get in to see what's happening? If there's a > limit you're not really superuser, are you? > > >> Slony in particular does not need more than N connections but does >> require being a super user. >> > > Maybe someone should look into enabling slony to not run as a > superuser? > > > That was my initial reaction to this suggestion. But then I realised that it might well make sense to have a separate connection-limited superuser for Slony purposes (or any other special purpose) alongside an unlimited superuser. If we were restricted to having just one superuser I would be much more inclined to agree with you. Perhaps if this suggestion were to be adopted it could be argued that the superuser reserved connection slots should be kept only for superusers that are not connection-limited. cheers andrew
On Mon, 2006-07-31 at 09:06 -0400, Tom Lane wrote: > Rod Taylor <pg@rbt.ca> writes: > > It appears that the superuser does not have connection limit > > enforcement. I think this should be changed. > > If you're superuser, you are not subject to access restrictions, > by definition. I cannot imagine any scenario under which the > above would be a good idea. (Hint: it would be more likely to > lock out manual admin connections than Slony.) If you don't want an admin user to have a connection limit, give them "-1" or no connection limit. Anyway, you're right that Slony should not require superuser status but at the moment that is rather tricky to accomplish since it wants to muck about in the system catalogues, use pg_cancel_backend, among other things. --
On Mon, 2006-07-31 at 15:07 +0200, Csaba Nagy wrote: > On Mon, 2006-07-31 at 15:00, Martijn van Oosterhout wrote: > > On Mon, Jul 31, 2006 at 08:47:38AM -0400, Rod Taylor wrote: > > > It appears that the superuser does not have connection limit > > > enforcement. I think this should be changed. > > > > So if some admin process goes awry and uses up all the connection > > slots, how does the admin get in to see what's happening? If there's a > > limit you're not really superuser, are you? > > I thought there is a limit for super-users too... citation from: > http://www.postgresql.org/docs/8.1/static/runtime-config-connection.html#RUNTIME-CONFIG-CONNECTION-SETTINGS Sorry for not being more specific. I was speaking about ALTER ROLE WITH CONNECTION LIMIT. --
Nevermind, I realized now that you're talking about a different setting. > I thought there is a limit for super-users too... citation from: > http://www.postgresql.org/docs/8.1/static/runtime-config-connection.html#RUNTIME-CONFIG-CONNECTION-SETTINGS Cheers, Csaba.
On Mon, 2006-07-31 at 15:00 +0200, Martijn van Oosterhout wrote: > On Mon, Jul 31, 2006 at 08:47:38AM -0400, Rod Taylor wrote: > > It appears that the superuser does not have connection limit > > enforcement. I think this should be changed. > > So if some admin process goes awry and uses up all the connection > slots, how does the admin get in to see what's happening? If there's a > limit you're not really superuser, are you? Work this one through. If an admin process goes awry and uses up all the connection slots it has reached max_connections AND used superuser_reserved_connections as well. This means an admin cannot get in to see what is happening. That's what happens today. I would much prefer that Superuser 'a' reaches WITH CONNECTION LIMIT for user 'a' and superuser 'b' can get in to see what is happening. > > Slony in particular does not need more than N connections but does > > require being a super user. > > Maybe someone should look into enabling slony to not run as a > superuser? > > Have a nice day, --
Andrew Dunstan <andrew@dunslane.net> writes:
> Martijn van Oosterhout wrote:
>> Maybe someone should look into enabling slony to not run as a
>> superuser?
> That was my initial reaction to this suggestion. But then I realised
> that it might well make sense to have a separate connection-limited
> superuser for Slony purposes (or any other special purpose) alongside an
> unlimited superuser.
Actually, the real question in my mind is why Slony can't be trusted
to use the right number of connections to start with. If you don't
trust it that far, what are you doing letting it into your database as
superuser to start with?
As for "connection-limited superuser", if you can't do ALTER USER SET
on yourself then you aren't a superuser, so any such restriction is
illusory anyway.
regards, tom lane
On Mon, 2006-07-31 at 09:52 -0400, Tom Lane wrote: > Andrew Dunstan <andrew@dunslane.net> writes: > > Martijn van Oosterhout wrote: > >> Maybe someone should look into enabling slony to not run as a > >> superuser? > > > That was my initial reaction to this suggestion. But then I realised > > that it might well make sense to have a separate connection-limited > > superuser for Slony purposes (or any other special purpose) alongside an > > unlimited superuser. > > Actually, the real question in my mind is why Slony can't be trusted > to use the right number of connections to start with. If you don't > trust it that far, what are you doing letting it into your database as > superuser to start with? I generally try to apply reasonable restrictions on all activities that take place on my systems unless the machine was dedicated for that task (in which case the limitations are those of the machine). When things go wrong, and they almost always do eventually, these types of restrictions ensure that only the one process grinds to a halt instead of the entire environment. Cron jobs are another area that are frequently implemented incorrectly. Implementing checks to see if it is already running is overlooked enough that I would like to restrict them as well. This is less important since roles now allow multiple users to take ownership of a relation; less jobs that need to run as a superuser. --
Tom Lane wrote: >Andrew Dunstan <andrew@dunslane.net> writes: > > >>Martijn van Oosterhout wrote: >> >> >>>Maybe someone should look into enabling slony to not run as a >>>superuser? >>> >>> > > > >>That was my initial reaction to this suggestion. But then I realised >>that it might well make sense to have a separate connection-limited >>superuser for Slony purposes (or any other special purpose) alongside an >>unlimited superuser. >> >> > >Actually, the real question in my mind is why Slony can't be trusted >to use the right number of connections to start with. If you don't >trust it that far, what are you doing letting it into your database as >superuser to start with? > >As for "connection-limited superuser", if you can't do ALTER USER SET >on yourself then you aren't a superuser, so any such restriction is >illusory anyway. > > > As a protection against malice, yes. I think Rod was more interested in some protection against stupidity. Maybe the real answer is that Slony should connect as a non-superuser and call security definer functions for the privileged things it needs to do. cheers andrew
> > As a protection against malice, yes. I think Rod was more interested in > some protection against stupidity. > > Maybe the real answer is that Slony should connect as a non-superuser > and call security definer functions for the privileged things it needs > to do. Wouldn't that break Slony's ability to connect to older postgresql versions and replicate? Joshua D. Drake > > cheers > > andrew > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutionssince 1997 http://www.commandprompt.com/
Joshua D. Drake wrote: > >> >> As a protection against malice, yes. I think Rod was more interested >> in some protection against stupidity. >> >> Maybe the real answer is that Slony should connect as a non-superuser >> and call security definer functions for the privileged things it >> needs to do. > > > Wouldn't that break Slony's ability to connect to older postgresql > versions and replicate? > I don't know anything of Slony's internals, but I don't see why older versions should matter - Postgres has had security definer functions for every release that Slony supports. Maybe I'm missing something ... cheers andrew
andrew@dunslane.net (Andrew Dunstan) writes:
> Joshua D. Drake wrote:
>
>>
>>>
>>> As a protection against malice, yes. I think Rod was more
>>> interested in some protection against stupidity.
>>>
>>> Maybe the real answer is that Slony should connect as a
>>> non-superuser and call security definer functions for the
>>> privileged things it needs to do.
>>
>>
>> Wouldn't that break Slony's ability to connect to older postgresql
>> versions and replicate?
>>
>
> I don't know anything of Slony's internals, but I don't see why older
> versions should matter - Postgres has had security definer functions
> for every release that Slony supports. Maybe I'm missing something ...
Most of Slony-I's activities don't require superuser access. The
usual thing that's running are SYNC events, and those merely require
write access to some internal Slony-I tables and write access to the
replicated tables on the subscribers.
The functions that do need superuser access are (basically)- subscribe set (needs to alter system tables)- execute
script(ditto)
The trouble is that you in effect need to have that superuser up and
ready for action at any time in case it's needed, and it being that
needful, we basically use it all the time.
Perhaps it's worth looking at shoving the superuser stuff into
SECURITY DEFINER functions; that may be worth considering
post-1.2.0...
--
output = reverse("gro.gultn" "@" "enworbbc")
http://cbbrowne.com/info/multiplexor.html
Wow! Windows now can do everything using shared library DLLs, just
like Multics did back in the 1960s! Maybe someday they'll discover
separate processes and pipes, which came out in the 1970s!
Ühel kenal päeval, E, 2006-07-31 kell 09:52, kirjutas Tom Lane: > Andrew Dunstan <andrew@dunslane.net> writes: > > Martijn van Oosterhout wrote: > >> Maybe someone should look into enabling slony to not run as a > >> superuser? > > > That was my initial reaction to this suggestion. But then I realised > > that it might well make sense to have a separate connection-limited > > superuser for Slony purposes (or any other special purpose) alongside an > > unlimited superuser. > > Actually, the real question in my mind is why Slony can't be trusted > to use the right number of connections to start with. If you don't > trust it that far, what are you doing letting it into your database as > superuser to start with? This has probably nothing to do withs slony. One way tos shut out users from postgresqls backend is to cut all connections in a way that a smart client sees (maybe by sending keepalives), but backend does not (it times out after some TCP timeout, which by default is in about 2.5hours). BTW, sometimes this does happen by itself in case of long enough connections. In such a case the client will likely establish new connection(s), and if the whole process happens many times, then the backend runs out of connections. > As for "connection-limited superuser", if you can't do ALTER USER SET > on yourself then you aren't a superuser, so any such restriction is > illusory anyway. I guess they want protection against accidentally using up all connections, not to have a way for competing superusers to locking each other out; -- ---------------- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com