[HACKERS] 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. -- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Connection limit and Superuser
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. signature.asc Description: Digital signature
Re: [HACKERS] Connection limit and Superuser
Rod Taylor [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Connection limit and Superuser
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 PostgreSQL to request more System V shared memory or semaphores than your operating system's default configuration allows. 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 value is 2. The value must be less than the value of max_connections. This parameter can only be set at server start. Cheers, Csaba. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Connection limit and Superuser
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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Connection limit and Superuser
On Mon, 2006-07-31 at 09:06 -0400, Tom Lane wrote: Rod Taylor [EMAIL PROTECTED] 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. -- ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Connection limit and Superuser
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. -- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Connection limit and Superuser
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. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Connection limit and Superuser
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, -- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Connection limit and Superuser
Andrew Dunstan [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Connection limit and Superuser
On Mon, 2006-07-31 at 09:52 -0400, Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] 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. -- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Connection limit and Superuser
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Connection limit and Superuser
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 solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Connection limit and Superuser
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Connection limit and Superuser
[EMAIL PROTECTED] (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! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Connection limit and Superuser
Ühel kenal päeval, E, 2006-07-31 kell 09:52, kirjutas Tom Lane: Andrew Dunstan [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
