SEO

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

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.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFEzf7uIB7bNG8LQkwRAjMaAJ9v6oxfj0ChtQIU7b1FOz AqCcbLpQCghTw9
szxjm2FeXNepYZ6e0JIiE+M=
=a0Bn
-----END PGP SIGNATURE-----

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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/s...CTION-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

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

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


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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/s...CTION-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

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/s...CTION-SETTINGS

Cheers,
Csaba.



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend