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

On Sat, Jan 19, 2013 at 4:47 AM, Tomonari Katsumata
<t(dot)katsumata1122(at)gmail(dot)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 Hagander
Me: 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

On Sat, Jan 19, 2013 at 12:47 PM, Tomonari Katsumata <
t(dot)katsumata1122(at)gmail(dot)com> wrote:

> 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 does not really solve the case you reported because, as reported in
your bug, you could still have each slave connecting to each other using
the privilege CASCADE REPLICATION. It makes even the privilege level more
complicated.

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.
--
Michael Paquier
http://michael.otacoo.com

On 01/19/2013 11:47 AM, Tomonari Katsumata wrote:
>
> Hi,
>
> I made a patch to divide privileges for replication role.
>

I've added your patch to the commitfest tracking app for the post-9.3
release; see https://commitfest.postgresql.org/action/patch_view?id=1072 .

If it's convenient for you to keep that entry up to date with any
revised patches you get and any reviews people write that will be rather
helpful. I'll keep an eye on it and update it when I see something
change, but you're paying attention to this one patch so you'll notice
first.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Hi, Magnus, Josh, Michael, Craig

Thank you for comments and registring to CommitFest.

>> 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?
>
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.
So I think it's better with a privilege than using only pg_hba.conf

----

>> 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?
>
I agreed that it is a very narrow use-case and accidental thing.

But I think we should provide a kind of method to avoid it,
because it has been different of before release.

And I don't think it's complicated, because "REPLICATION" and
"NOREPLICATION" do same behavior with before release.

----

>> 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 does not really solve the case you reported because, as reported in
>your bug, you could still have each slave connecting to each other using
>the privilege CASCADE REPLICATION. It makes even the privilege level more
>complicated.
>
Yes, the patch can not solve the case at all.
I just added a parameter for users.
It is responsibility of users to connect with a right role.

>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.
>
I think this is very complicated.
At least, now I can't solve it...

If someday we can detect it, this kind of switch will be needed.
Because some users may need the cyclic situation.

----

I'm not insisting to use replication-role, but
I want something to control this behavior.

regards,
------------
NTT Software Corporation
Tomonari Katsumata

Tomonari Katsumata <t(dot)katsumata1122(at)gmail(dot)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

Hi, Tom

Thank you for comments.

> Tomonari Katsumata <t(dot)katsumata1122(at)gmail(dot)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.
>
Yes, I'm thinking changing settings on the master and the settings are
propagating to all 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.
>
I understand that the approach in my patch(using pribileges for controlling
its behavior) does not match the policy.

But I'm still thinking I should put a something to controle
the behavior.

Then, how about to add a new option "standby_mode" to Database Connection
Control Functions like application_name.

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

regards,
--------
NTT Software Corporation
Tomonari Katsumata

On Wed, Jan 23, 2013 at 5:46 PM, Tomonari Katsumata <
t(dot)katsumata1122(at)gmail(dot)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?
--
Michael Paquier
http://michael.otacoo.com

Hi, Michael
2013/1/23 Michael Paquier <michael(dot)paquier(at)gmail(dot)com>

>
>
> On Wed, Jan 23, 2013 at 5:46 PM, Tomonari Katsumata <
> t(dot)katsumata1122(at)gmail(dot)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?
>
>
No, I'm not thinking about solving the cyclic problem directly.
It is too difficult for me.
And the goal of my patch is adding some selections to avoid it for users.
--------
NTT Software Corporation
Tomonari Katsumata