Re: [HACKERS] dividing privileges for replication role.
Hi, Tom Thank you for comments. Tomonari Katsumata t.katsumata1...@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. 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
Re: [HACKERS] dividing privileges for replication role.
On Wed, Jan 23, 2013 at 5:46 PM, Tomonari Katsumata t.katsumata1...@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? -- Michael Paquier http://michael.otacoo.com
Re: [HACKERS] dividing privileges for replication role.
Hi, Michael 2013/1/23 Michael Paquier michael.paqu...@gmail.com On Wed, Jan 23, 2013 at 5:46 PM, Tomonari Katsumata t.katsumata1...@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? 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
Re: [HACKERS] dividing privileges for replication role.
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
Re: [HACKERS] dividing privileges for replication role.
Tomonari Katsumata t.katsumata1...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] dividing privileges for replication role.
On Sat, Jan 19, 2013 at 4:47 AM, Tomonari Katsumata t.katsumata1...@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 Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] dividing privileges for replication role.
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] dividing privileges for replication role.
On Sat, Jan 19, 2013 at 12:47 PM, Tomonari Katsumata t.katsumata1...@gmail.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
Re: [HACKERS] dividing privileges for replication role.
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
