Streaming replication as a separate permissions
Here's a patch that changes walsender to require a special privilege
for replication instead of relying on superuser permissions. We
discussed this back before 9.0 was finalized, but IIRC we ran out of
time. The motivation being that you really want to use superuser as
little as possible - and since being a replication slave is a read
only role, it shouldn't require the maximum permission available in
the system.
Obviously the patch needs docs and some system views updates, which I
will add later. But I wanted to post what I have so far for a quick
review to confirm whether I'm on the right track or not... How it
works should be rather obvious - adds a "WITH
REPLICATION/NOREPLICATION" to the create and alter role commands, and
then check this when a connection attempts to start the walsender.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
Attachments:
repl_role.patchtext/x-patch; charset=US-ASCII; name=repl_role.patchDownload+90-27
Magnus Hagander <magnus@hagander.net> writes:
Here's a patch that changes walsender to require a special privilege
for replication instead of relying on superuser permissions. We
discussed this back before 9.0 was finalized, but IIRC we ran out of
time. The motivation being that you really want to use superuser as
little as possible - and since being a replication slave is a read
only role, it shouldn't require the maximum permission available in
the system.
Maybe it needn't require "max" permissions, but one of the motivations
for requiring superusernesss was to prevent Joe User from sucking every
last byte of data out of your database (and into someplace he could
examine it at leisure). This patch opens that barn door wide, because
so far as I can see, it allows anybody at all to grant the replication
privilege ... or revoke it, thereby breaking your replication setup.
I think only superusers should be allowed to change the flag.
regards, tom lane
On Thu, Dec 23, 2010 at 10:15 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Magnus Hagander <magnus@hagander.net> writes:
Here's a patch that changes walsender to require a special privilege
for replication instead of relying on superuser permissions. We
discussed this back before 9.0 was finalized, but IIRC we ran out of
time. The motivation being that you really want to use superuser as
little as possible - and since being a replication slave is a read
only role, it shouldn't require the maximum permission available in
the system.Maybe it needn't require "max" permissions, but one of the motivations
for requiring superusernesss was to prevent Joe User from sucking every
last byte of data out of your database (and into someplace he could
examine it at leisure). This patch opens that barn door wide, because
so far as I can see, it allows anybody at all to grant the replication
privilege ... or revoke it, thereby breaking your replication setup.
I think only superusers should be allowed to change the flag.
I haven't looked at the patch yet, but I think we should continue to
allow superuser-ness to be *sufficient* for replication - i.e.
superusers will automatically have the replication privilege just as
they do any other - and merely allow this as an option for when you
want to avoid doing it that way.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
I haven't looked at the patch yet, but I think we should continue to
allow superuser-ness to be *sufficient* for replication - i.e.
superusers will automatically have the replication privilege just as
they do any other - and merely allow this as an option for when you
want to avoid doing it that way.
I don't particularly mind breaking that. If we leave it as-is, we'll
be encouraging people to use superuser accounts for things that don't
need that, which can't be good from a security standpoint.
BTW, is it possible to set things up so that a REPLICATION account
can be NOLOGIN, thereby making it really hard to abuse for other
purposes? Or does the login privilege check come too soon?
regards, tom lane
On Thu, Dec 23, 2010 at 10:54 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
I haven't looked at the patch yet, but I think we should continue to
allow superuser-ness to be *sufficient* for replication - i.e.
superusers will automatically have the replication privilege just as
they do any other - and merely allow this as an option for when you
want to avoid doing it that way.I don't particularly mind breaking that. If we leave it as-is, we'll
be encouraging people to use superuser accounts for things that don't
need that, which can't be good from a security standpoint.
And if we break it, we'll be adding an additional, mandatory step to
make replication work that isn't required today. You might think
that's OK, but I think the majority opinion is that it's already
excessively complex.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Thu, Dec 23, 2010 at 16:15, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Magnus Hagander <magnus@hagander.net> writes:
Here's a patch that changes walsender to require a special privilege
for replication instead of relying on superuser permissions. We
discussed this back before 9.0 was finalized, but IIRC we ran out of
time. The motivation being that you really want to use superuser as
little as possible - and since being a replication slave is a read
only role, it shouldn't require the maximum permission available in
the system.Maybe it needn't require "max" permissions, but one of the motivations
for requiring superusernesss was to prevent Joe User from sucking every
last byte of data out of your database (and into someplace he could
examine it at leisure). This patch opens that barn door wide, because
so far as I can see, it allows anybody at all to grant the replication
privilege ... or revoke it, thereby breaking your replication setup.
I think only superusers should be allowed to change the flag.
That was certainly not intentional - and doesn't work that way for me
at least, unless I broke it right before I submitted it.
oh hang on.. Yeah, it's allowing anybody *that has CREATE ROLE*
privilege to do it, I think. And I agree that's wrong and should be
fixed. But I can't see it allowing anybody at all to do it - am I
misreading the code?
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
On Thu, Dec 23, 2010 at 16:57, Robert Haas <robertmhaas@gmail.com> wrote:
On Thu, Dec 23, 2010 at 10:54 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
I haven't looked at the patch yet, but I think we should continue to
allow superuser-ness to be *sufficient* for replication - i.e.
superusers will automatically have the replication privilege just as
they do any other - and merely allow this as an option for when you
want to avoid doing it that way.I don't particularly mind breaking that. If we leave it as-is, we'll
be encouraging people to use superuser accounts for things that don't
need that, which can't be good from a security standpoint.And if we break it, we'll be adding an additional, mandatory step to
make replication work that isn't required today. You might think
that's OK, but I think the majority opinion is that it's already
excessively complex.
Most of the people I run across in the real world are rather surprised
how *easy* it is to set up, and not how complex. And tbh, the only
complexity complaints I've heard there are about the requirement to
start/backup/stop to get it up and running. I've always told everybody
to create a separate account to do it, and not heard a single comment
about that.
That said, how about a compromise in that we add the replication flag
by default to the initial superuser when it's created? That way, it's
at least possible to remove it if you want to. Would that address your
complexity concern?
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
Magnus Hagander <magnus@hagander.net> writes:
On Thu, Dec 23, 2010 at 16:15, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I think only superusers should be allowed to change the flag.
That was certainly not intentional - and doesn't work that way for me
at least, unless I broke it right before I submitted it.
oh hang on.. Yeah, it's allowing anybody *that has CREATE ROLE*
privilege to do it, I think. And I agree that's wrong and should be
fixed. But I can't see it allowing anybody at all to do it - am I
misreading the code?
Ah, sorry, yeah there are probably CREATE ROLE privilege checks
somewhere upstream of here. I was expecting to see a privilege check
added by the patch itself, and did not, so I complained.
regards, tom lane
Magnus Hagander <magnus@hagander.net> writes:
On Thu, Dec 23, 2010 at 16:57, Robert Haas <robertmhaas@gmail.com> wrote:
On Thu, Dec 23, 2010 at 10:54 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I don't particularly mind breaking that. �If we leave it as-is, we'll
be encouraging people to use superuser accounts for things that don't
need that, which can't be good from a security standpoint.
And if we break it, we'll be adding an additional, mandatory step to
make replication work that isn't required today. �You might think
that's OK, but I think the majority opinion is that it's already
excessively complex.
Most of the people I run across in the real world are rather surprised
how *easy* it is to set up, and not how complex. And tbh, the only
complexity complaints I've heard there are about the requirement to
start/backup/stop to get it up and running. I've always told everybody
to create a separate account to do it, and not heard a single comment
about that.
FWIW, it seems unreasonable to me to expect that we will not be breaking
any part of a 9.0 replication configuration over the next release or
two. We *knew* we were shipping a rough version that would require
refinements, and this is one of the planned refinements.
That said, how about a compromise in that we add the replication flag
by default to the initial superuser when it's created? That way, it's
at least possible to remove it if you want to. Would that address your
complexity concern?
It does nothing to address my security concern. I want to discourage
people from using superuser accounts for this, full stop.
regards, tom lane
On 12/23/2010 08:59 PM, Magnus Hagander wrote:
On Thu, Dec 23, 2010 at 16:57, Robert Haas<robertmhaas@gmail.com> wrote:
On Thu, Dec 23, 2010 at 10:54 AM, Tom Lane<tgl@sss.pgh.pa.us> wrote:
Robert Haas<robertmhaas@gmail.com> writes:
I haven't looked at the patch yet, but I think we should continue to
allow superuser-ness to be *sufficient* for replication - i.e.
superusers will automatically have the replication privilege just as
they do any other - and merely allow this as an option for when you
want to avoid doing it that way.I don't particularly mind breaking that. If we leave it as-is, we'll
be encouraging people to use superuser accounts for things that don't
need that, which can't be good from a security standpoint.And if we break it, we'll be adding an additional, mandatory step to
make replication work that isn't required today. You might think
that's OK, but I think the majority opinion is that it's already
excessively complex.Most of the people I run across in the real world are rather surprised
how *easy* it is to set up, and not how complex. And tbh, the only
complexity complaints I've heard there are about the requirement to
start/backup/stop to get it up and running. I've always told everybody
to create a separate account to do it, and not heard a single comment
about that.
I agree - people I talked to are fairly surprised on us not using a
dedicated replication role but are surprised at the complexity of
actually initializing the replication (mostly the "we cannot do a base
backup over the replication connection" missfeature)
Stefan
On 12/23/10 7:49 AM, Robert Haas wrote:
I haven't looked at the patch yet, but I think we should continue to
allow superuser-ness to be *sufficient* for replication - i.e.
superusers will automatically have the replication privilege just as
they do any other - and merely allow this as an option for when you
want to avoid doing it that way.
Yes. Currently I already create a separate "replicator" superuser just
so that I can simply track which connections belong to replication. It
would be great if it could make the "replicator" user less than a superuser.
If we still make it possible for "postgres" to replicate, then we don't
add any complexity to the simplest setup.
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
Josh Berkus <josh@agliodbs.com> writes:
If we still make it possible for "postgres" to replicate, then we don't
add any complexity to the simplest setup.
Well, that's one laudable goal here, but "secure by default" is another
one that ought to be taken into consideration.
regards, tom lane
On 12/23/10 2:21 PM, Tom Lane wrote:
Josh Berkus <josh@agliodbs.com> writes:
If we still make it possible for "postgres" to replicate, then we don't
add any complexity to the simplest setup.Well, that's one laudable goal here, but "secure by default" is another
one that ought to be taken into consideration.
I don't see how *not* granting the superuser replication permissions
makes things more secure. The superuser can grant replication
permissions to itself, so why is suspending them by default beneficial?
I'm not following your logic here.
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
Josh Berkus <josh@agliodbs.com> writes:
On 12/23/10 2:21 PM, Tom Lane wrote:
Well, that's one laudable goal here, but "secure by default" is another
one that ought to be taken into consideration.
I don't see how *not* granting the superuser replication permissions
makes things more secure. The superuser can grant replication
permissions to itself, so why is suspending them by default beneficial?
I'm not following your logic here.
Well, the reverse of that is just as true: if we ship it without
replication permissions on the postgres user, people can change that if
they'd rather not create a separate role for replication. But I think
we should encourage people to NOT do it that way. Setting it up that
way by default hardly encourages use of a more secure arrangement.
regards, tom lane
* Josh Berkus (josh@agliodbs.com) wrote:
On 12/23/10 2:21 PM, Tom Lane wrote:
Well, that's one laudable goal here, but "secure by default" is another
one that ought to be taken into consideration.I don't see how *not* granting the superuser replication permissions
makes things more secure. The superuser can grant replication
permissions to itself, so why is suspending them by default beneficial?
I'm not following your logic here.
The point is that the *replication* role can't grant itself superuser
privs. Having the replication role compromised isn't great, but if that
role is *also* a superuser, then the whole database server could be
compromised. Encouraging users to continue to configure remote systems
with the ability to connect as a superuser when it's not necessary is a
bad idea.
One compromise would be to:
a) let superusers be granted the replication permission
b) have pg_dump assume that superusers have that permission when dumping
from a version which pre-dates the replication grant
c) have pg_upgrade assume the superuser has that permission when
upgrading
d) *not* grant replication to the default superuser
A better alternative, imv, would be to just have a & d, and mention in
the release notes that users *should* create a dedicated replication
role which is *not* a superuser but *does* have the replication grant,
but if they don't want to change their existing configurations, they can
just grant the replication privilege to whatever role they're currently
using.
Thanks,
Stephen
On 12/23/10 2:33 PM, Stephen Frost wrote:
A better alternative, imv, would be to just have a & d, and mention in
the release notes that users *should* create a dedicated replication
role which is *not* a superuser but *does* have the replication grant,
but if they don't want to change their existing configurations, they can
just grant the replication privilege to whatever role they're currently
using.
Well, if we really want people to change their behavior then we need to
make it easy for them:
1) have a replication permission
2) *by default* create a replication user with the replication
permission when we initdb.
3) have an example line for a replication connection by the replication
user in the default pg_hba.conf (commented out).
4) change all our docs and examples to use that replication user.
If using the replication user is easier than any other path, people
will. If it's harder, they won't.
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
Josh Berkus <josh@agliodbs.com> writes:
On 12/23/10 2:33 PM, Stephen Frost wrote:
A better alternative, imv, would be to just have a & d, and mention in
the release notes that users *should* create a dedicated replication
role which is *not* a superuser but *does* have the replication grant,
but if they don't want to change their existing configurations, they can
just grant the replication privilege to whatever role they're currently
using.
Well, if we really want people to change their behavior then we need to
make it easy for them:
1) have a replication permission
2) *by default* create a replication user with the replication
permission when we initdb.
Yeah, I could see doing that ... the entry would be wasted if you're not
doing any replication, but one wasted catalog entry isn't much.
However, it'd be a real good idea for that role to be NOLOGIN if it's
there by default.
regards, tom lane
* Josh Berkus (josh@agliodbs.com) wrote:
1) have a replication permission
Right, that's what this patch is about.
2) *by default* create a replication user with the replication
permission when we initdb.
I'm not entirely sure about this one.. I'm not against it but I'm also
not really 'for' it. :)
3) have an example line for a replication connection by the replication
user in the default pg_hba.conf (commented out).
Sure.
4) change all our docs and examples to use that replication user.
I don't have a problem with that.
Thanks,
Stephen
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
However, it'd be a real good idea for that role to be NOLOGIN if it's
there by default.
Definitely. I'd also suggest we WARN if someone creates a situation
where a role has both replication and login, and maybe prevent it
altogether, it's just a bad idea..
Thanks,
Stephen
On Thu, Dec 23, 2010 at 23:44, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Josh Berkus <josh@agliodbs.com> writes:
On 12/23/10 2:33 PM, Stephen Frost wrote:
A better alternative, imv, would be to just have a & d, and mention in
the release notes that users *should* create a dedicated replication
role which is *not* a superuser but *does* have the replication grant,
but if they don't want to change their existing configurations, they can
just grant the replication privilege to whatever role they're currently
using.Well, if we really want people to change their behavior then we need to
make it easy for them:1) have a replication permission
2) *by default* create a replication user with the replication
permission when we initdb.Yeah, I could see doing that ... the entry would be wasted if you're not
doing any replication, but one wasted catalog entry isn't much.However, it'd be a real good idea for that role to be NOLOGIN if it's
there by default.
That shouldn't be too hard - I'll look at making the patch do that to
make sure it *isn't* that hard ;)
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
