Slony1 PostgreSQL Replication - Security with slony

Author

Security with slony

Roger Lucas

2006-01-21, 1:23 pm

All,

I posted a similar query to the Slony mailing list back in November, and got
a very clear reply from Jan, but having thought about the problem some more
(and having lots of issues with our current MySQL replication solution), I
wondered if there was a different way of looking at the problem and finding
a Postgres+Slony solution....

Here is the summary:

We have a network with multiple geographic sites, some of which are machines
in racks at ISPs. We are very concerned about security and are working on
the principle that, even with our best efforts, one of the machines in the
network will get compromised at some point by a malicious user. They will
then have complete access to the machine including all the accounts and
passwords on it.

With slony running with full postgres super-user privileges on each node,
our concern is that this malicious user could then reconfigure the
master-slave relationships across the network then send out a sequence of
update or delete operations to corrupt or destroy all the databases across
the entire network. We would, of course, have regular backups, but
restoring them would be a lot of hassle and take time. During the
intervening period, the databases would be unavailable, which would be very
"inconvenient".

To try to prevent the above scenario from being possible, I was wondering if
it was possible to reduce the privileges that the Slony user had on each
node in the database. Clearly, the Slony user would need write privileges
on any slave table that it was replicating data to, but would the Slony user
need write privileges to a table that it was simply reading for replication
to a different node? If we could reduce the privileges of the Slony user to
a custom set of privileges on each node, then even if the Slony network was
compomised as described above, the Slony user would not have the privileges
to corrupt all the tables across the entire network. The original master
tables would still be intact.

Does this above make sense, and can anyone offer assistance on this?

Ideally, I am looking for information on what privileges and commands Slony
needs for the configuration and replication operations for the master and
slave nodes in the system. I can go through the code, but that is going to
take some time, so I was hoping that someone might know the answers or point
me to some more detailed documentation.

Thanks in advance,

Roger

Andrew Sullivan

2006-01-23, 7:24 am

On Sat, Jan 21, 2006 at 05:27:40PM -0000, Roger Lucas wrote:
> Ideally, I am looking for information on what privileges and commands Slony
> needs for the configuration and replication operations for the master and
> slave nodes in the system. I can go through the code, but that is going to
> take some time, so I was hoping that someone might know the answers or point
> me to some more detailed documentation.

I've sometimes thought that it would be possible to split things up
this way:

1. A super-user role for setup and configuration.
2. A regular user for normal operation.

The problem would be that, as soon as any operation involving tables
happened, you'd need some way of automatically promoting the "regular
user" to superuser status. So this would turn out actually to be no
security at all. It's like giving limited sudo access to some user,
but one of the commands is "vi". Since the user can escape to the
shell, you've effectively just given them the keys anyway, although
at one level of indirection.

Your better bet would be to use the log shipping mechanism available
in 1.1. It solves this problem for you.

A

--
Andrew Sullivan | ajs-oaT0K0jot5/q2IAV+ODieA@public.gmane.org
This work was visionary and imaginative, and goes to show that visionary
and imaginative work need not end up well.
--Dennis Ritchie

Roger Lucas

2006-01-23, 7:24 am

Hi Andrew,

Thanks for your reply. I have looked at log shipping in detail, but our
situation makes it inappropriate (I think). I will explain a bit more about
our network:

Our primary (and secure) server holds the master configuration tables.
These change every so often, but changes must be propagated ASAP to the
multiple remote sites (which are considered partially secure).

The remote sites are busy and each site has its own unique namespace where
it writes log and state information. We pull these namespaces back to our
primary site so that we have all the log and state information locally.

The network topology is very stable. Once we have set up the node
master-slave topologies we want to:
- Absolutely lock this configuration in concrete
- Absolutely ensure that slony can never write to the master "primary"
namespace

Looking at the slony documentation, it suggests that slonik would not be run
very often in the above scenario, so we could use ssltunnels to link the
sites for the configuration commands and then remove these tunnels to
prevent the node configuration from changing. Firewalling of all incoming
SSL tunnels to the master node would prevent the slaves from triggering any
changes by themselves. This is an excellent start.

I would have no problem explicitly granting access to specific tables to
allow slonik to change its configuration should I need to reconfigure the
network for any reason and then revoking these permissions after the action
was complete. The key here is that the super user which grants the
privileges would have a unique password on each node, so no malicious user
could use the password information gleaned from one node to change the
privileges on a different node. To allow the tables to be locked down in
this way, I would need to know which tables slony needs write access to and
under what conditions.

I appreciate your comment about "vi" below. I think that the above scenario
is different, however, as I don't care if a malicious user completely wrecks
a particular node. I am assuming that if they break into a node, then they
can and will do this anyhow. By making each node have a unique set of
passwords, however, they cannot use their knowledge from one node to change
the configuration of a different node - their passwords etc just won't let
them log into the other node to make the privilege changes required to allow
slonik to reconfigure the network topology.

I hope the above makes sense and appreciate your thoughts/suggestions.

Best regards,

Roger

> -----Original Message-----
> From: slony1-general-bounces- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org [mailto:slony1-general-
> bounces- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org] On Behalf Of Andrew Sullivan
> Sent: 23 January 2006 12:03
> To: slony1-general- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org
> Subject: Re: [Slony1-general] Security with slony
>
> On Sat, Jan 21, 2006 at 05:27:40PM -0000, Roger Lucas wrote:
> Slony
> and
> to
> point
>
> I've sometimes thought that it would be possible to split things up
> this way:
>
> 1. A super-user role for setup and configuration.
> 2. A regular user for normal operation.
>
> The problem would be that, as soon as any operation involving tables
> happened, you'd need some way of automatically promoting the "regular
> user" to superuser status. So this would turn out actually to be no
> security at all. It's like giving limited sudo access to some user,
> but one of the commands is "vi". Since the user can escape to the
> shell, you've effectively just given them the keys anyway, although
> at one level of indirection.
>
> Your better bet would be to use the log shipping mechanism available
> in 1.1. It solves this problem for you.
>
> A
>
> --
> Andrew Sullivan | ajs-oaT0K0jot5/q2IAV+ODieA@public.gmane.org
> This work was visionary and imaginative, and goes to show that visionary
> and imaginative work need not end up well.
> --Dennis Ritchie
> ____________________
____________________
_______
> Slony1-general mailing list
> Slony1-general- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org
> http://gborg.postgresql.org/mailman.../slony1-general

Andrew Sullivan

2006-01-23, 11:25 am

On Mon, Jan 23, 2006 at 12:30:53PM -0000, Roger Lucas wrote:
> The network topology is very stable. Once we have set up the node
> master-slave topologies we want to:
> - Absolutely lock this configuration in concrete
> - Absolutely ensure that slony can never write to the master "primary"
> namespace

I think that requirement 2 is unreasonable. That's like saying you
want to make sure that the VACUUM user can never write in the primary
namespace.

If I were designing such a system, I'd do it with two steps: node A
replicates to node B, and node B chains down to everything else.
There should be no listen paths from A to {C. . . Z}.

But it sounds like what you need is a functional host-based ACL
system grafted on top. That is, events of type T can come only from
nodes {some set of nodes}, and other events of type T' can come from,
&c.

>
> Looking at the slony documentation, it suggests that slonik would not be run
> very often in the above scenario, so we could use ssltunnels to link the
> sites for the configuration commands and then remove these tunnels to
> prevent the node configuration from changing. Firewalling of all incoming

No, you seem to misunderstand. Slonik injects events, just like any
other kinds of event, into the replica stream.

> privileges on a different node. To allow the tables to be locked down in
> this way, I would need to know which tables slony needs write access to and
> under what conditions.

The problem is that administration commands need to be able to write
on the system catalogue. So you _have_ to use a superuser.

But I still don't see why log shipping won't work for you. All you
really need to make it work is very small changesets.

A

--
Andrew Sullivan | ajs-oaT0K0jot5/q2IAV+ODieA@public.gmane.org
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism.
--Brad Holland

Roger Lucas

2006-01-23, 11:25 am

Hi Andrew,

Thanks for your reply.

> -----Original Message-----
> From: Andrew Sullivan [mailto:ajs-oaT0K0jot5/q2IAV+ODieA@public.gmane.org]
> Sent: 23 January 2006 15:46
> To: Roger Lucas
> Cc: 'Andrew Sullivan'; slony1-general- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org
> Subject: Re: [Slony1-general] Security with slony
>
> On Mon, Jan 23, 2006 at 12:30:53PM -0000, Roger Lucas wrote:
>
> I think that requirement 2 is unreasonable. That's like saying you
> want to make sure that the VACUUM user can never write in the primary
> namespace.
>
> If I were designing such a system, I'd do it with two steps: node A
> replicates to node B, and node B chains down to everything else.
> There should be no listen paths from A to {C. . . Z}.
>
> But it sounds like what you need is a functional host-based ACL
> system grafted on top. That is, events of type T can come only from
> nodes {some set of nodes}, and other events of type T' can come from,
> &c.
>

That would be ideal... but I cannot find it in existance already.

> run
> incoming
>
> No, you seem to misunderstand. Slonik injects events, just like any
> other kinds of event, into the replica stream.

That confuses me, as I had read the information below to imply that the
network configuration commands (i.e. control messages from the
"administrative workstation" that set up the topology of the replication
network) were sent "out of band" using different links which could then be
shut down when not required.

http://linuxfinances.info/info/plainpaths.html
("ADMIN CONNINFO" section at the top)

I don't want to generate too much noise on this mailing list as it is
relatively low volume, however, so I'll dig through the code to try to
understand its workings in more detail rather than ask more questions.

Best regards,

Roger

Christopher Browne

2006-01-23, 11:25 am

Roger Lucas wrote:

>Hi Andrew,
>
>Thanks for your reply.
>
>
>
>
>That would be ideal... but I cannot find it in existance already.
>
>
>
>
>That confuses me, as I had read the information below to imply that the
>network configuration commands (i.e. control messages from the
>"administrative workstation" that set up the topology of the replication
>network) were sent "out of band" using different links which could then be
>shut down when not required.
>
>http://linuxfinances.info/info/plainpaths.html
>("ADMIN CONNINFO" section at the top)
>
>
The *requests* are set out in a potentially "out of band" fashion, using
slonik.

But some of the processing takes place using the slon process (e.g. -
the event processor).

For instance, when a node is set up as a subscriber to a set, you'd use
slonik to submit the "subscribe set" request.

That turns into a "SUBSCRIBE_SET" event, which slon passes along. And
it's slon, doing event processing, which has to alter the tables in that
replication set, hiding any existing triggers, and adding in the ones
that Slony-I wants.

As a result, the component that needs to be trusted-as-superuser is the
slon event processor.

>I don't want to generate too much noise on this mailing list as it is
>relatively low volume, however, so I'll dig through the code to try to
>understand its workings in more detail rather than ask more questions.
>
>
I suppose this is displays that the splitting of work across slon/slonik
hasn't been done with a view to trying to get as many bits as possible
to be achievable with consciously minimized access privileges. The
assumption was always that slon/slonik would need to operate as a
superuser because some parts require that.

It would be an interesting analysis to determine which parts of that
truly need superuser access and which don't, with a view to minimizing
access privileges. That would lead to some fairly significant changes,
I'm sure. I imagine be necessary to shift certain changes over to be
done by Slonik, or for there to be some sort of "superuser event queue"
that would block "normal events" until someone logged on as a superuser
to process the "super events."

An open question is whether the effort would actually help. I'm more
than a little skeptical of that. But given a Schneier style "Attack
Tree" <http://www.schneier.com/paper-attacktrees-ddj-ft.html> or other
decision tree that maps this against a model of someone's security
requirements, I'll bet interesting things could fall out.

Jim C. Nasby

2006-01-23, 1:24 pm

On Mon, Jan 23, 2006 at 04:48:14PM -0000, Roger Lucas wrote:
>
> That confuses me, as I had read the information below to imply that the
> network configuration commands (i.e. control messages from the
> "administrative workstation" that set up the topology of the replication
> network) were sent "out of band" using different links which could then be
> shut down when not required.

I'm wondering if it would be possible to add the idea of 'administrative
command'. My understanding is that very few things require superuser
access, so it might be possible to put more stringent requirements on
their use. It's much more likely that these commands do not need to run
without human intervention, so (for example), it might be far more
practical for these commands to require the user to supply a password to
gain access to the SU account on all the nodes; or perhaps the commands
are run from a secured workstation that has the proper credentials in
~/.pgpass. I realize that the requirement for these commands to happen
as part of the replication stream (in the correct order with everything
else) complicates this, but it should be possible to allow for a
SU-enabled slon process to connect to remote machines via an SU account
to process those admin commands and then be shut-down again after they
were finished.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby-D/ iDPWeZeLdl57MIdRCFDg
@public.gmane.org
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Andrew Sullivan

2006-01-23, 1:24 pm

On Mon, Jan 23, 2006 at 04:48:14PM -0000, Roger Lucas wrote:
>
> That would be ideal... but I cannot find it in existance already.

Nope, this'd be a feature that would have to be built. I haven't
thought it through, but it seems to me to be at least logically
possible.

> That confuses me, as I had read the information below to imply that the
> network configuration commands (i.e. control messages from the
> "administrative workstation" that set up the topology of the replication
> network) were sent "out of band" using different links which could then be
> shut down when not required.

Well, that's the design of the example case that Chris sent, but
there's nothing in principle to prevent these commands from being
issued on the same box as slon is running. All you'd need is the
configuration tool (slonik) and a way of connecting to some node as
superuser. You've already posited that some of your nodes are less
secure than your main origin system. But since those systems need to
be able to send confirmations back, they'll have to be able to talk
to the origin postmaster. Which means you could issue slonik from
them.

A

--
Andrew Sullivan | ajs-oaT0K0jot5/q2IAV+ODieA@public.gmane.org
When my information changes, I alter my conclusions. What do you do sir?
--attr. John Maynard Keynes

Christopher Browne

2006-01-23, 1:24 pm

"Jim C. Nasby" <jnasby-D/ iDPWeZeLdl57MIdRCFDg
@public.gmane.org> writes:
> On Mon, Jan 23, 2006 at 04:48:14PM -0000, Roger Lucas wrote:
>
> I'm wondering if it would be possible to add the idea of
> 'administrative command'. My understanding is that very few things
> require superuser access, so it might be possible to put more
> stringent requirements on their use. It's much more likely that
> these commands do not need to run without human intervention, so
> (for example), it might be far more practical for these commands to
> require the user to supply a password to gain access to the SU
> account on all the nodes; or perhaps the commands are run from a
> secured workstation that has the proper credentials in ~/.pgpass. I
> realize that the requirement for these commands to happen as part of
> the replication stream (in the correct order with everything else)
> complicates this, but it should be possible to allow for a
> SU-enabled slon process to connect to remote machines via an SU
> account to process those admin commands and then be shut-down again
> after they were finished.

Well, that seems a *less* interesting question to me than you might
think.

As far as I am concerned, the only "non-administrative event" (minor
distinction from "command") is Slony-I's own "SYNC" event.

Every other event is "administrative," whether it needs superuser
access or not.

There's a boatload of Slonik commands; they are all "administrative,"
every one of them.

You'll need to run Slonik when:
- Servers go unstable and require special maintenance
- Software changes, thereby changing schemas
- New servers come along

Those are definitely "out of the ordinary" events.

On the average day, when Slony-I is operating, you don't need to use
any of them. For the average TLD registry, we don't need to fiddle
with database schemas even as often as once a month, so any day when
we run Slonik represents a pretty unusual day. And that is doubtless
true for most Slony-I users.

It wouldn't be an outrageous idea to have more stringent user
capabilities requirements for the user that is used for running
Slonik.

Unfortunately, this turns out to be a pretty banal result, because it
does not suffice to "make slonik run special."

The trouble with that idea is that a lot of the "administrative" work
is done indirectly, by the slon processes. The slon also needs
"superuser" access because much of the Slonik work gets passed on to
the slon.

The average SYNC event (which is the only non-administrative event
that there is!!!) doesn't need superuser access for the slon. But the
slon-based processing of every other event *potentially* means that
every slon needs superuser access in order to invoke those other
events.
--
"cbbrowne","@","ca.afilias.info"
<http://dba2.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)

Roger Lucas

2006-01-23, 1:24 pm

Perhaps the question could be put a different way:

"How can we make minimal changes to Slony so that administrative events can
be disabled for particular Postgres users?"

Perhaps the slony processes around the system could be given the credentials
for a restricted user and thus could not send administrative events (apart
from SYNC). When something goes wrong then the sysadmin can provide the
credentials for a privileged user and make any required changes. Upon
completion, the sysadmin restores the credentials for the user with
restricted privileges.

> -----Original Message-----
> From: Christopher Browne [mailto:cbbrowne-swQf4SbcV9C7WVzo/KQ3Mw@public.gmane.org]
> Sent: 23 January 2006 19:01
> To: Jim C. Nasby
> Cc: Roger Lucas; slony1-general- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org
> Subject: Re: [Slony1-general] Security with slony
>
> "Jim C. Nasby" <jnasby-D/ iDPWeZeLdl57MIdRCFDg
@public.gmane.org> writes:
>
> Well, that seems a *less* interesting question to me than you might
> think.
>
> As far as I am concerned, the only "non-administrative event" (minor
> distinction from "command") is Slony-I's own "SYNC" event.
>
> Every other event is "administrative," whether it needs superuser
> access or not.
>
> There's a boatload of Slonik commands; they are all "administrative,"
> every one of them.
>
> You'll need to run Slonik when:
> - Servers go unstable and require special maintenance
> - Software changes, thereby changing schemas
> - New servers come along
>
> Those are definitely "out of the ordinary" events.
>
> On the average day, when Slony-I is operating, you don't need to use
> any of them. For the average TLD registry, we don't need to fiddle
> with database schemas even as often as once a month, so any day when
> we run Slonik represents a pretty unusual day. And that is doubtless
> true for most Slony-I users.
>
> It wouldn't be an outrageous idea to have more stringent user
> capabilities requirements for the user that is used for running
> Slonik.
>
> Unfortunately, this turns out to be a pretty banal result, because it
> does not suffice to "make slonik run special."
>
> The trouble with that idea is that a lot of the "administrative" work
> is done indirectly, by the slon processes. The slon also needs
> "superuser" access because much of the Slonik work gets passed on to
> the slon.
>
> The average SYNC event (which is the only non-administrative event
> that there is!!!) doesn't need superuser access for the slon. But the
> slon-based processing of every other event *potentially* means that
> every slon needs superuser access in order to invoke those other
> events.
> --
> "cbbrowne","@","ca.afilias.info"
> <http://dba2.int.libertyrms.com/>
> Christopher Browne
> (416) 673-4124 (land)

Andrew Sullivan

2006-01-23, 8:24 pm

On Mon, Jan 23, 2006 at 07:10:40PM -0000, Roger Lucas wrote:
> Perhaps the slony processes around the system could be given the credentials
> for a restricted user and thus could not send administrative events (apart
> from SYNC). When something goes wrong then the sysadmin can provide the
> credentials for a privileged user and make any required changes. Upon
> completion, the sysadmin restores the credentials for the user with
> restricted privileges.

That was sort of what I had in mind for an acl approach. Every node
gets its own user. That way, you just have a list of things that can
originate from any given user. Now, you want your slonik commands to
come from a particular workstation, and always be injected at one
(and only one) node? Then you configure the ACL on every node except
the injection site to refuse such commands, and you configure the
nodes to accept such commands coming only from this or that node.

This has several problems I can think of. You'll need to be
perfectly certain of your listen paths. I can think of all sorts of
ways this might break. I also don't think it'd be a trivial amount
of work to hack in (and an even less-trivial job to make it anything
other than easy to circumvent). But on the back of a napkin it looks
implementable. I'd be interested to see a real proposal.

A

--
Andrew Sullivan | ajs-oaT0K0jot5/q2IAV+ODieA@public.gmane.org
Information security isn't a technological problem. It's an economics
problem.
--Bruce Schneier

Roger Lucas

2006-01-23, 8:24 pm

OK

Since I'm the one pushing for it and I don't really know Slony, it will take
me some time before I can put together a solid proposal for what could be
done. I'll take the action, however, and see what I can come up with.

If there is any internal documentation on how Slony works then please pass
it my way. I'm dredging with Google to get my hands on everything I can
however, so if all documentation is in out there somewhere then I'll
probably find it (or rather, Google will probably find it...).

If I have the occasional question, would you like me to mail you directly or
keep the mailing list on full copy ?

- R

> -----Original Message-----
> From: slony1-general-bounces- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org [mailto:slony1-general-
> bounces- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org] On Behalf Of Andrew Sullivan
> Sent: 23 January 2006 19:41
> To: slony1-general- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org
> Subject: Re: [Slony1-general] Security with slony
>
> On Mon, Jan 23, 2006 at 07:10:40PM -0000, Roger Lucas wrote:
> credentials
> (apart
>
> That was sort of what I had in mind for an acl approach. Every node
> gets its own user. That way, you just have a list of things that can
> originate from any given user. Now, you want your slonik commands to
> come from a particular workstation, and always be injected at one
> (and only one) node? Then you configure the ACL on every node except
> the injection site to refuse such commands, and you configure the
> nodes to accept such commands coming only from this or that node.
>
> This has several problems I can think of. You'll need to be
> perfectly certain of your listen paths. I can think of all sorts of
> ways this might break. I also don't think it'd be a trivial amount
> of work to hack in (and an even less-trivial job to make it anything
> other than easy to circumvent). But on the back of a napkin it looks
> implementable. I'd be interested to see a real proposal.
>
> A
>
> --
> Andrew Sullivan | ajs-oaT0K0jot5/q2IAV+ODieA@public.gmane.org
> Information security isn't a technological problem. It's an economics
> problem.
> --Bruce Schneier
> ____________________
____________________
_______
> Slony1-general mailing list
> Slony1-general- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org
> http://gborg.postgresql.org/mailman.../slony1-general

Andrew Sullivan

2006-01-23, 8:24 pm

On Mon, Jan 23, 2006 at 07:56:20PM -0000, Roger Lucas wrote:
> OK
>
> Since I'm the one pushing for it and I don't really know Slony, it will take
> me some time before I can put together a solid proposal for what could be
> done. I'll take the action, however, and see what I can come up with.
>

My suggestion is to break your proposal into two sections:

1. The problem set you're trying to solve.
2. The way you think it can be solved.

These are not tightly coupled, so they can be treated separately.

> If there is any internal documentation on how Slony works then please pass
> it my way. I'm dredging with Google to get my hands on everything I can
> however, so if all documentation is in out there somewhere then I'll
> probably find it (or rather, Google will probably find it...).

As far as I know, all the documentation that there is is liked from
the main gborg page. That includes the design documents. Other than
that, you likely have to read the code and the comments therein.

> If I have the occasional question, would you like me to mail you directly or
> keep the mailing list on full copy ?

I think you'd better mail the list. Anyone who has ever looked at
the sorry thing that passes for code from my fingers will tell you
that I'm a completely awful programmer.

A

--
Andrew Sullivan | ajs-oaT0K0jot5/q2IAV+ODieA@public.gmane.org
The whole tendency of modern prose is away from concreteness.
--George Orwell

Roger Lucas

2006-02-03, 7:24 am

Hi Andrew and all,

Sorry for the delay in my reply, but things have been a little busy here
recently....

Here is my summary in for the security problem that I am trying to solve.
An outline proposal for a method to resolve it follows. I apologise in
advance for the recap of information from older mails, but hopefully this
mail can be used as a single point of reference going forward.

Security Problem
----------------

At present, all Slony daemons run with postgres SuperUser privileges on
their connection to their local node. In addition, the daemons include
login information in plain text for postgres accounts with SuperUser
privileges on all other nodes that the daemons must communicate between.
This allows them to reconfigure the other nodes automatically then the
replication hierarchy changes.

Unfortunately, these privileges are preserved during normal operation, which
has no requirements for such high privileges (just write permissions on some
tables and read permissions on some others).

This means that if the security of any Slony node is compromised at any
time, then the attacker not only has access to the local SuperUser password
(which they may be able to get in many ways having compromised the machine),
but they have been given all the SuperUser passwords to other machines in
the Slony replication system. They can then open connections to other
databases in the system using these SuperUser logins and then recover data
from from, change the data or even delete the data completely.

There is even the risk that once an attacker has gained access to a Postgres
database with SuperUser privileges, they could leverage these privileges to
access other files on the system allowing the security breach to be
worsened. Hence, compromising one machine with Slony running may allow the
attacker to quickly compromise every machine on the network which has Slony
running.

To me, this represents an unacceptable security risk.

I accept that the Slony daemon may require SuperUser (or more precisely, a
significant set of privileges) occasionally, but I cannot see strong
justification for leaving these privileges permanently granted when they are
not required. This goes against all recommendations I have ever seen on how
to develop and maintain secure systems.

I feel that there must be a way to reduce the privileges of a Slony daemon
while it is running "normally" and not in the process of reconfiguring the
replication hierarchy. Then, even if a machine has been compromised, its
ability to wreak havoc through the rest of the system is dramatically
reduced.

Outline of recommendation
-------------------------

I suggest that a set of "reconfiguration" and "operation" privileges are
determined. A specific slony-daemon account would then be created which the
slony daemons use when logging in to other nodes. Under normal operation
this account is only given the "operation" privileges. These lock the
daemon down to only being able to read tables that need replication or are
used to manage the replication and only being able to write to tables that
are targets of replication data or are used to maintain the replication
state.

When an administrator wishes to reconfigure the replication hierarchy, which
would be a relatively rare event, then they can explictly log in to
appropriate Postgres databases and temporarily grant the slony-daemon user
the "reconfiguration" privileges before starting the reconfiguration of the
replication hierarchy.

Once the replication has been reconfigured, a "release privileges" action
could be performed to drop the Slony postgres users back to an appropriate
privilege level for the new network. If possible, the Slony daemon would
automatically determine what their minimal privilege requirements from
studying its replication needs and can then release all but these
privileges. This would then then it to work normally during replication.

There is still a security risk when the replication hierarchy is
reconfigured, but since these events are rare, and the administrator may
perform additional security checks before performing the replication
hierarchy changes, this would give a dramatic reduction to the security
risks present in the system.

I would welcome comments and suggestions on this proposal.

Best regards,

Roger

Rod Taylor

2006-02-03, 11:24 am

> When an administrator wishes to reconfigure the replication hierarchy, which

> would be a relatively rare event, then they can explictly log in to

I'm going to argue against the assumption that this is a rare event. In
fact, if you are replicating a partitioned table the reconfiguration
will occur once per partition creation which could be quite frequent
(say hourly on several structures).

I will also argue against the assumption that it is an administrator
doing the reconfiguration by hand rather than an automated toolkit.

Of course, creation of an inherited table requires ownership of the
parent and create privileges -- not full superuser access.

--

Jan Wieck

2006-02-03, 11:24 am

In the end, an attacker can fake events on the compromised node that
will be executed by the slon deamons against their local databases with
all the rights that are required. Therefore the outline below is just a
complification of an already very complex system. Unless you want to
introduce a fine grained node-event-permission system you don't gain
anything. And I don't see an easy way to tell if such event really
originated on that permitted node in a cascaded environment. Which means
you probably want to have every event digitally signed?

Jan

On 2/3/2006 7:12 AM, Roger Lucas wrote:
> Hi Andrew and all,
>
> Sorry for the delay in my reply, but things have been a little busy here
> recently....
>
> Here is my summary in for the security problem that I am trying to solve.
> An outline proposal for a method to resolve it follows. I apologise in
> advance for the recap of information from older mails, but hopefully this
> mail can be used as a single point of reference going forward.
>
> Security Problem
> ----------------
>
> At present, all Slony daemons run with postgres SuperUser privileges on
> their connection to their local node. In addition, the daemons include
> login information in plain text for postgres accounts with SuperUser
> privileges on all other nodes that the daemons must communicate between.
> This allows them to reconfigure the other nodes automatically then the
> replication hierarchy changes.
>
> Unfortunately, these privileges are preserved during normal operation, which
> has no requirements for such high privileges (just write permissions on some
> tables and read permissions on some others).
>
> This means that if the security of any Slony node is compromised at any
> time, then the attacker not only has access to the local SuperUser password
> (which they may be able to get in many ways having compromised the machine),
> but they have been given all the SuperUser passwords to other machines in
> the Slony replication system. They can then open connections to other
> databases in the system using these SuperUser logins and then recover data
> from from, change the data or even delete the data completely.
>
> There is even the risk that once an attacker has gained access to a Postgres
> database with SuperUser privileges, they could leverage these privileges to
> access other files on the system allowing the security breach to be
> worsened. Hence, compromising one machine with Slony running may allow the
> attacker to quickly compromise every machine on the network which has Slony
> running.
>
> To me, this represents an unacceptable security risk.
>
> I accept that the Slony daemon may require SuperUser (or more precisely, a
> significant set of privileges) occasionally, but I cannot see strong
> justification for leaving these privileges permanently granted when they are
> not required. This goes against all recommendations I have ever seen on how
> to develop and maintain secure systems.
>
> I feel that there must be a way to reduce the privileges of a Slony daemon
> while it is running "normally" and not in the process of reconfiguring the
> replication hierarchy. Then, even if a machine has been compromised, its
> ability to wreak havoc through the rest of the system is dramatically
> reduced.
>
>
> Outline of recommendation
> -------------------------
>
> I suggest that a set of "reconfiguration" and "operation" privileges are
> determined. A specific slony-daemon account would then be created which the
> slony daemons use when logging in to other nodes. Under normal operation
> this account is only given the "operation" privileges. These lock the
> daemon down to only being able to read tables that need replication or are
> used to manage the replication and only being able to write to tables that
> are targets of replication data or are used to maintain the replication
> state.
>
> When an administrator wishes to reconfigure the replication hierarchy, which
> would be a relatively rare event, then they can explictly log in to
> appropriate Postgres databases and temporarily grant the slony-daemon user
> the "reconfiguration" privileges before starting the reconfiguration of the
> replication hierarchy.
>
> Once the replication has been reconfigured, a "release privileges" action
> could be performed to drop the Slony postgres users back to an appropriate
> privilege level for the new network. If possible, the Slony daemon would
> automatically determine what their minimal privilege requirements from
> studying its replication needs and can then release all but these
> privileges. This would then then it to work normally during replication.
>
> There is still a security risk when the replication hierarchy is
> reconfigured, but since these events are rare, and the administrator may
> perform additional security checks before performing the replication
> hierarchy changes, this would give a dramatic reduction to the security
> risks present in the system.
>
> I would welcome comments and suggestions on this proposal.
>
> Best regards,
>
> Roger
>
> ____________________
____________________
_______
> Slony1-general mailing list
> Slony1-general- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org
> http://gborg.postgresql.org/mailman.../slony1-general

--
#===================
====================
====================
===========#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#===================
====================
=========== JanWieck- bwPqjjyvM7QAvxtiuMwx
3w@public.gmane.org #

Roger Lucas

2006-02-03, 11:24 am

Hi Rod,

Thanks for your comments.

Just for clarification, do you mean that you are creating a completely new
partitioned table every few hours and running "slon" to configure its
replication across the different nodes?

Even if the above is true, and as you pointed out at the bottom of your
post, Slony still does not need full superuser access.

- Roger

> -----Original Message-----
> From: Rod Taylor [mailto:pg-pBEMHqKcNc4@public.gmane.org]
> Sent: 03 February 2006 15:51
> To: Roger Lucas
> Cc: 'Andrew Sullivan'; slony1-general- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org
> Subject: Re: [Slony1-general] Security with slony
>
> which
>
> I'm going to argue against the assumption that this is a rare event. In
> fact, if you are replicating a partitioned table the reconfiguration
> will occur once per partition creation which could be quite frequent
> (say hourly on several structures).
>
> I will also argue against the assumption that it is an administrator
> doing the reconfiguration by hand rather than an automated toolkit.
>
> Of course, creation of an inherited table requires ownership of the
> parent and create privileges -- not full superuser access.
>
> --

Rod Taylor

2006-02-03, 11:24 am

On Fri, 2006-02-03 at 16:00 +0000, Roger Lucas wrote:
> Hi Rod,
>
> Thanks for your comments.
>
> Just for clarification, do you mean that you are creating a completely new
> partitioned table every few hours and running "slon" to configure its
> replication across the different nodes?

It's closer to once every 10 minutes since there are numerous structures
which are frequently partitioned.

> Even if the above is true, and as you pointed out at the bottom of your
> post, Slony still does not need full superuser access.

Nor do I run Slony with superuser access. It has access only to the
schemas and tables necessary to carry out replication.

Of course, if you replicated the entire database, then that would be
nearly equivalent to super-user access.

>
>
--

Roger Lucas

2006-02-03, 1:24 pm

> -----Original Message-----
> From: Jan Wieck [mailto:JanWieck- bwPqjjyvM7QAvxtiuMwx
3w@public.gmane.org]
> Sent: 03 February 2006 15:58
> To: Roger Lucas
> Cc: 'Andrew Sullivan'; slony1-general- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org
> Subject: Re: [Slony1-general] Security with slony
>
> In the end, an attacker can fake events on the compromised node that
> will be executed by the slon deamons against their local databases with
> all the rights that are required. Therefore the outline below is just a
> complification of an already very complex system. Unless you want to
> introduce a fine grained node-event-permission system you don't gain
> anything. And I don't see an easy way to tell if such event really
> originated on that permitted node in a cascaded environment. Which means
> you probably want to have every event digitally signed?
>

I must be missing something.

As I understand it, all slon daemons log into the appropriate Postgres
databases using the login and password that is indicated in the STORE_PATH()
line in the slonik script. If the username given does not have superuser
access then the slon daemon will not get superuser access, slon just gets
the rights for that username.

If that is true, then I should be able to reduce the rights on the slon user
so that it only has (for example) write access to the tables that it is
replicating to, read access for the tables that it is replicating from,
read/write access to the slony configuration tables, and no access to
anything else. Hence, if an attacker gains access to a node, even if they
can reconfigure the replication hierarchy, the other machines in the Slony
network still have the appropriate user restrictions in the Postgres
databases to stop the attacker from doing too much damage. For example, if
the slon user did not have write permissions on a master table that should
only be replicated from (and should never be replicated to), then that table
could never be corrupted by slon, even if the attacker tried to get slon to
write to it in some way.

Is the above correct?

Thanks,
Roger.

Jan Wieck

2006-02-03, 1:24 pm

On 2/3/2006 12:34 PM, Roger Lucas wrote:

>
> I must be missing something.
>
> As I understand it, all slon daemons log into the appropriate Postgres
> databases using the login and password that is indicated in the STORE_PATH()
> line in the slonik script. If the username given does not have superuser
> access then the slon daemon will not get superuser access, slon just gets
> the rights for that username.

No slon process ever updates any of the remote databases at all. They
only ever insert, update and delete against the nodes local database. If
it makes you feel better, you can put another user into the path info
and set that user up for read only access of the slony schema. It should
still work.

But keep in mind that all slon daemons simply do whatever they are told
in the events they receive. So even without remote superuser access, an
attacker can do almost all the damage he wants by simply injecting
events into the compromised database. The events will be read by the
slon daemons of the other nodes and they then do the damage on behalf of
the attacker.

Jan

>
> If that is true, then I should be able to reduce the rights on the slon user
> so that it only has (for example) write access to the tables that it is
> replicating to, read access for the tables that it is replicating from,
> read/write access to the slony configuration tables, and no access to
> anything else. Hence, if an attacker gains access to a node, even if they
> can reconfigure the replication hierarchy, the other machines in the Slony
> network still have the appropriate user restrictions in the Postgres
> databases to stop the attacker from doing too much damage. For example, if
> the slon user did not have write permissions on a master table that should
> only be replicated from (and should never be replicated to), then that table
> could never be corrupted by slon, even if the attacker tried to get slon to
> write to it in some way.
>
> Is the above correct?
>
> Thanks,
> Roger.
>
> ____________________
____________________
_______
> Slony1-general mailing list
> Slony1-general- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org
> http://gborg.postgresql.org/mailman.../slony1-general

--
#===================
====================
====================
===========#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#===================
====================
=========== JanWieck- bwPqjjyvM7QAvxtiuMwx
3w@public.gmane.org #

Roger Lucas

2006-02-03, 1:24 pm

> STORE_PATH()
> superuser
> gets
>
> No slon process ever updates any of the remote databases at all. They
> only ever insert, update and delete against the nodes local database. If
> it makes you feel better, you can put another user into the path info
> and set that user up for read only access of the slony schema. It should
> still work.
>
> But keep in mind that all slon daemons simply do whatever they are told
> in the events they receive. So even without remote superuser access, an
> attacker can do almost all the damage he wants by simply injecting
> events into the compromised database. The events will be read by the
> slon daemons of the other nodes and they then do the damage on behalf of
> the attacker.
>

Yes, but each slon daemon on each node still has to log into the local
Postres database to perform the insert/update/delete/whatever actions that
it sees arriving as events from the other databases, and for this it needs a
username+password. Hence, each slon daemon is logging in with an
appropriate set of restricted privileges for its node. Malicious events are
can still inserted on a remote compromised node, and get copied over to the
local node. When the slon daemon tries (in good faith) to perform the
appropriate commands that it believes are necessary to replicate the events
in its node, the user the slon is logged in as may not have the rights to
perform the command. Postgres itself will therefore reject the command.

This isn't a perfect solution. A table that is a target of a replication
can still be maliciously be over-written. On the other hand, a table that
is only ever replicated from (and never replicated to) should never need
write permission granted to the slon user. Hence, even if malicious events
are injected, (e.g. explicit delete of data in the table) and the local slon
daemon tries to replicate it, then this command will be rejected on the
master database, thus protecting the true source of the data. The tables on
other databases which were replication targets can still be corrupted, but
you don't suffer the risk of total data loss that you would have otherwise.

Jan Wieck

2006-02-03, 1:24 pm

On 2/3/2006 1:43 PM, Roger Lucas wrote:

>
>
> Yes, but each slon daemon on each node still has to log into the local
> Postres database to perform the insert/update/delete/whatever actions that
> it sees arriving as events from the other databases, and for this it needs a
> username+password. Hence, each slon daemon is logging in with an
> appropriate set of restricted privileges for its node. Malicious events are
> can still inserted on a remote compromised node, and get copied over to the
> local node. When the slon daemon tries (in good faith) to perform the
> appropriate commands that it believes are necessary to replicate the events
> in its node, the user the slon is logged in as may not have the rights to
> perform the command. Postgres itself will therefore reject the command.
>
> This isn't a perfect solution. A table that is a target of a replication
> can still be maliciously be over-written. On the other hand, a table that
> is only ever replicated from (and never replicated to) should never need
> write permission granted to the slon user. Hence, even if malicious events
> are injected, (e.g. explicit delete of data in the table) and the local slon
> daemon tries to replicate it, then this command will be rejected on the
> master database, thus protecting the true source of the data. The tables on
> other databases which were replication targets can still be corrupted, but
> you don't suffer the risk of total data loss that you would have otherwise.

Slon needs superuser access including catalog update permission on the
local database. Otherwise it can neither perform the commands required
to subscribe to a set, nor could it ever do anything like failover or
switchover.

Jan

--
#===================
====================
====================
===========#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#===================
====================
=========== JanWieck- bwPqjjyvM7QAvxtiuMwx
3w@public.gmane.org #

Rod Taylor

2006-02-03, 1:24 pm

> Slon needs superuser access including catalog update permission on the
> local database. Otherwise it can neither perform the commands required
> to subscribe to a set, nor could it ever do anything like failover or
> switchover.

With PostgreSQL 8.1 it appears you can shove Slony into a ROLE with
ownership of tables and schemas it replicates instead of a superuser
account.

The exception to this seems to be the various system catalogue updates
like the index detachment during COPY.
--

Jim C. Nasby

2006-02-07, 3:24 am

On Fri, Feb 03, 2006 at 11:12:45AM -0500, Rod Taylor wrote:
> Nor do I run Slony with superuser access. It has access only to the
> schemas and tables necessary to carry out replication.

Can you provide any details about that? As I understand it based on the
docs, slon needs to connect as a superuser...

> Of course, if you replicated the entire database, then that would be
> nearly equivalent to super-user access.

Not hardly, especially if any of the machines in the cluster have
multiple databases. Heck, just the ability for a superuser to create new
user accounts means it's an extremely powerful tool for a cracker.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby-D/ iDPWeZeLdl57MIdRCFDg
@public.gmane.org
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Rod Taylor

2006-02-07, 11:24 am

On Tue, 2006-02-07 at 00:05 -0600, Jim C. Nasby wrote:
> On Fri, Feb 03, 2006 at 11:12:45AM -0500, Rod Taylor wrote:
>
> Can you provide any details about that? As I understand it based on the
> docs, slon needs to connect as a superuser...

Before 8.1 that was the only way that Slony could create and manage
triggers, truncate tables, and do all of the other work necessary.

With 8.1 you can setup co-ownership of tables, schemas, and other
structures through the use of ROLEs. So, setup the Slony user as an
owner of the structures so that it has permissions to do what it needs
to do.

There are a few bits of Slony code (direct manipulation of system
catalogues) you will need to remove or work around as well.

--

Sponsored Links