Re: 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(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Rod Taylor <pg(at)rbt(dot)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 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.

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(at)rbt(dot)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.
--

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: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.

--

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(at)dunslane(dot)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

Ühel kenal päeval, E, 2006-07-31 kell 09:52, kirjutas Tom Lane:
> Andrew Dunstan <andrew(at)dunslane(dot)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

On Mon, 2006-07-31 at 09:52 -0400, Tom Lane wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)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(at)dunslane(dot)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 solutions since 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(at)dunslane(dot)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!