Обсуждение: dividing privileges for replication role.

On Sat, Jan 19, 2013 at 4:47 AM, Tomonari Katsumata
<t.katsumata1122@gmail.com> wrote:
> Hi,
>
> I made a patch to divide privileges for replication role.
>
> Currently(9.2), the privilege for replication role is
> true / false which means that standby server is able to
> connect to another server or not with the replication role.

Why is it better to do this with a privilege, rather than just using
pg_hba.conf? It doesn't represent an actual "permission level" after
all - it's just an administrative "flag" to say you can't connect.
Which AFAICS can just as easily be handled in pg_hba.conf? I guess I'm
missing something?

--Magnus HaganderMe: http://www.hagander.net/Work: http://www.redpill-linpro.com/

Katsumata-san,

> In this patch, I made below.
> a) adding new privileges for replication:"MASTER REPLICATION" and "CASCADE
> REPLICATION"
>    "MASTER REPLICATION":  Replication-connection to master server is only
> allowed
>    "CASCADE REPLICATION": Replication-connection to cascade server is only
> allowed
>    ("REPLICATION" already implemented means replication-connection to both
> servers is allowed)

This seems to me a case of making things more complicated for everyone
in order to satisfy a very narrow use-case.  It certainly doesn't seem
to me to do anything about the "accidental cycle" issue.  Am I missing
something?

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

<div class="moz-cite-prefix">On 01/19/2013 11:47 AM, Tomonari Katsumata wrote:<br /></div><blockquote
cite="mid:CAC55fYcoM-wc7PL-g-R5CjSMwaEOik1wHmOr7tA7iHqKJ=RvoQ@mail.gmail.com"type="cite"><div dir="ltr"><p>Hi,<p>I made
apatch to divide privileges for replication role.<br /></div></blockquote><br /> I've added your patch to the
commitfesttracking app for the post-9.3 release; see <a
href="https://commitfest.postgresql.org/action/patch_view?id=1072">https://commitfest.postgresql.org/action/patch_view?id=1072</a>
.<br/><br /> If it's convenient for you to keep that entry up to date with any revised patches you get and any reviews
peoplewrite that will be rather helpful. I'll keep an eye on it and update it when I see something change, but you're
payingattention to this one patch so you'll notice first.<br /><pre class="moz-signature" cols="72">-- Craig Ringer
             <a class="moz-txt-link-freetext"
href="http://www.2ndQuadrant.com/">http://www.2ndQuadrant.com/</a>PostgreSQLDevelopment, 24x7 Support, Training &
Services</pre>

<p>Hi, Magnus, Josh, Michael, Craig<p>Thank you for comments and registring to CommitFest.<p>>> I made a patch to
divideprivileges for replication role.<br />>><br />>> Currently(9.2), the privilege for replication role
is<br/>>> true / false which means that standby server is able to<br />>> connect to another server or not
withthe replication role.<br /> ><br />>Why is it better to do this with a privilege, rather than just using<br
/>>pg_hba.conf?It doesn't represent an actual "permission level" after<br />>all - it's just an administrative
"flag"to say you can't connect.<br /> >Which AFAICS can just as easily be handled in pg_hba.conf? I guess I'm<br
/>>missingsomething?<br />><br />You are right.<br />Handling with pg_hba.conf is an easy way.<p>But I think many
usersthink about switch over, so<br />the pg_hba.conf is same on master and standby.<br />it's not convinient that we
haveto rewrite pg_hba.conf<br />whenever switch over occurs.<p>In the other hand, using a privilege, although we have
toprepare<br />each roles before, we don't need to rewrite pg_hba.conf.<br />So I think it's better with a privilege
thanusing only pg_hba.conf<p>----<p>>> In this patch, I made below.<br />>> a) adding new privileges for
replication:"MASTERREPLICATION" and "CASCADE<br />>> REPLICATION"<br />>>    "MASTER REPLICATION": 
Replication-connectionto master server is only<br /> >> allowed<br />>>    "CASCADE REPLICATION":
Replication-connectionto cascade server is only<br />>> allowed<br />>>    ("REPLICATION" already
implementedmeans replication-connection to both<br /> >> servers is allowed)<br />><br />>This seems to me
acase of making things more complicated for everyone<br />>in order to satisfy a very narrow use-case.  It certainly
doesn'tseem<br />>to me to do anything about the "accidental cycle" issue.  Am I missing<br /> >something?<br
/>><br/>I agreed that it is a very narrow use-case and accidental thing.<p>But I think we should provide a kind of
methodto avoid it,<br />because it has been different of before release.<p>And I don't think it's complicated, because
"REPLICATION"and<br />"NOREPLICATION" do same behavior with before release.<p>----<p>>> a) adding new privileges
forreplication:"MASTER REPLICATION" and "CASCADE<br />>> REPLICATION"<br />>><br />>>    "MASTER
REPLICATION": Replication-connection to master server is only<br /> >> allowed<br />>>    "CASCADE
REPLICATION":Replication-connection to cascade server is only<br />>> allowed<br />>>    ("REPLICATION"
alreadyimplemented means replication-connection to both<br /> >> servers is allowed)<br />>><br />>This
doesnot really solve the case you reported because, as reported in<br />>your bug, you could still have each slave
connectingto each other using<br />>the privilege CASCADE REPLICATION. It makes even the privilege level more<br />
>complicated.<br/>><br />Yes, the patch can not solve the case at all.<br />I just added a parameter for
users.<br/>It is responsibility of users to connect with a right role.<p>>What would be necessary to solve your
problemwould be to have each standby<br />>being aware that it is connected to a unique master. This is not really
an<br/>>issue with privileges but more of something like having a standby scanning<br /> >its upper cluster node
treeand check if there is a master connected. While<br />>checking the cluster node tree, you will also need to be
awareif a node<br />>has already been found when you scanned it to be sure that the same node<br /> >has not been
scanned,what would mean that you are in a cycle.<br />><br />I think this is very complicated.<br />At least, now I
can'tsolve it...<p>If someday we can detect it, this kind of switch will be needed.<br />Because some users may need
thecyclic situation.<p>----<p>I'm not insisting to use replication-role, but<br />I want something to control this
behavior.<p>regards,<br/>------------<br />NTT Software Corporation<br />  Tomonari Katsumata<br /> 

Tomonari Katsumata <t.katsumata1122@gmail.com> writes:
>> Why is it better to do this with a privilege, rather than just using
>> pg_hba.conf?

> You are right.
> Handling with pg_hba.conf is an easy way.

> But I think many users think about switch over, so
> the pg_hba.conf is same on master and standby.
> it's not convinient that we have to rewrite pg_hba.conf
> whenever switch over occurs.

> In the other hand, using a privilege, although we have to prepare
> each roles before, we don't need to rewrite pg_hba.conf.

That sounds good, but if the behavior is controlled by a privilege
(ie, it's stored in system catalogs) then it's impossible to have
different settings on different slave servers --- or indeed to change
the settings locally on a slave at all.  You can only change settings
on the master and let the change replicate to all the slaves.  Quite
aside from whether you want to manage things like that, what happens if
your master has crashed and you find you need to change the settings on
the way to getting a slave to take over?

The crash-recovery worry is one of the main reasons that things like
pg_hba.conf aren't stored in system catalogs already.  It's not always
convenient to need a running server before you can change the settings.
        regards, tom lane

<p>Hi, Tom<p>Thank you for comments.<p>> Tomonari Katsumata <<a
href="mailto:t.katsumata1122@gmail.com">t.katsumata1122@gmail.com</a>>writes:<br />> >> Why is it better to
dothis with a privilege, rather than just using<br />> >> pg_hba.conf?<br /> > <br />> <br />> >
Youare right.<br />> > Handling with pg_hba.conf is an easy way.<br />> <br />> > But I think many users
thinkabout switch over, so<br />> > the pg_hba.conf is same on master and standby.<br /> > > it's not
convinientthat we have to rewrite pg_hba.conf<br />> > whenever switch over occurs.<br />> <br />> > In
theother hand, using a privilege, although we have to prepare<br />> > each roles before, we don't need to
rewritepg_hba.conf.<br /> > <br />> <br />> That sounds good, but if the behavior is controlled by a
privilege<br/>> (ie, it's stored in system catalogs) then it's impossible to have<br />> different settings on
differentslave servers --- or indeed to change<br /> > the settings locally on a slave at all.  You can only change
settings<br/>> on the master and let the change replicate to all the slaves. <br />> <br />Yes, I'm thinking
changingsettings on the master and the settings are<br /> propagating to all slaves.<p>> Quite aside from whether
youwant to manage things like that, what happens if<br />> your master has crashed and you find you need to change
thesettings on<br />> the way to getting a slave to take over?<br />> <br /> > The crash-recovery worry is one
ofthe main reasons that things like<br />> pg_hba.conf aren't stored in system catalogs already.  It's not always<br
/>>convenient to need a running server before you can change the settings.<br /> > <br />I understand that the
approachin my patch(using pribileges for controlling<br />its behavior) does not match the policy.<p>But I'm still
thinkingI should put a something to controle<br />the behavior.<p>Then, how about to add a new option "standby_mode" to
DatabaseConnection <br />Control Functions like application_name.<p>ex:<br />  primary_conninfo = 'port=5432
standby_mode=master-cascade'<br/>  primary_conninfo = 'port=5432 standby_mode=master-only'<br />  primary_conninfo =
'port=5432standby_mode=cascade-only'<p>I think it will be able to do same control with privilege controlling.<br />And
itwon't be contrary to the policy(no data is stored in system catalogs).<p>Because it is corner-case, I should not do
anythingabout this?<br />(Am I concerning too much?)<p><br />regards,<br />--------<br />NTT Software Corporation<br
/> Tomonari Katsumata<br />