Thread: dividing privileges for replication role.
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.
This management and cascading replication make a strange behavior.
Because cascading replication is able to connect to another standby server,
we can see the cyclic situation.
This behavior has been discussed on Hackers-list(1),
but the conclusion was that's difficult to detect the situation.
(1) http://www.postgresql.org/message-id/50D12E8F.8000808@agliodbs.com
And then, I've reported a Bug-list(2) about this.
In this discussion, an idea that controlling
replication-connection with GUC parameter or privileges on
replication role comes up.
I think these can not avoid cyclic situation but will make some help for
DBA.
(2) http://www.postgresql.org/message-id/E1TtVvj-0004B3-2Z@wrigleys.postgresql.org
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)
b) addin above options in createuser command
--master-replication
--cascade-replication
c) dumping pg_authid.rolreplication value in pg_dumpall
is changed by server version like this:
from 9.1
true -> master-replication
false -> noreplication
from 9.2
true -> replication(master & cascade)
false -> noreplication
I've not write any documents and tests for this yet,
but I want any comments whether this change is needed or not.
regards,
---------
NTT Software Corporation
Tomonari Katsumata
Attachment
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
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)
What would be necessary to solve your problem would be to have each standby being aware that it is connected to a unique master. This is not really an issue with privileges but more of something like having a standby scanning its upper cluster node tree and check if there is a master connected. While checking the cluster node tree, you will also need to be aware if a node has already been found when you scanned it to be sure that the same node has not been scanned, what would mean that you are in a cycle.
--
http://michael.otacoo.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 />
ex:primary_conninfo = 'port=5432 standby_mode=master-cascade'
primary_conninfo = 'port=5432 standby_mode=master-only'
primary_conninfo = 'port=5432 standby_mode=cascade-only'I think it will be able to do same control with privilege controlling.
And it won't be contrary to the policy(no data is stored in system catalogs).Because it is corner-case, I should not do anything about this?
(Am I concerning too much?)
Solving the cyclic problem?
Michael Paquier
http://michael.otacoo.com
On Wed, Jan 23, 2013 at 5:46 PM, Tomonari Katsumata <t.katsumata1122@gmail.com> wrote:ex:primary_conninfo = 'port=5432 standby_mode=master-cascade'
primary_conninfo = 'port=5432 standby_mode=master-only'
primary_conninfo = 'port=5432 standby_mode=cascade-only'I think it will be able to do same control with privilege controlling.
And it won't be contrary to the policy(no data is stored in system catalogs).Because it is corner-case, I should not do anything about this?
(Am I concerning too much?)Just curious, but... What is your primary goal with this patch?
Solving the cyclic problem?
And the goal of my patch is adding some selections to avoid it for users.