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


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

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

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

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

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

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


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