Re: Replication and user privileges

--------------E2BD4040F3021D5F190C38B4
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

On 2/26/2019 9:44 AM, shawn l.green wrote:
> Hello Jim,
>
> On 2/25/2019 7:29 PM, Jim wrote:
>> On 2/25/2019 5:46 PM, shawn l.green wrote:
>>> Hello Jim,
>>>
>>> On 2/25/2019 5:04 PM, Jim wrote:
>>>> I have a question about mysql replication. I believe I understand most
>>>> of it, but have a question about user privileges.
>>>>
>>>> I understand on the master, the replication user must have the
>>>> Repl_slave_priv privilege as described here:
>>>> https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_replication-slave 
>>>>
>>>>
>>>>
>>>>
>>>> My question is about what replication-related users and privileges 
>>>> must
>>>> exist on the slave.
>>>>
>>>> So, for example, if an insert on the master that is to be 
>>>> replicated is
>>>> performed by user 'abc' with proper insert permissions on the master,
>>>> does that same 'abc' user with same insert permissions need to 
>>>> exist on
>>>> the slave as well?
>>>>
>>>> In other words, what user is performing the replication operation 
>>>> on the
>>>> slave? I don't see any indication of users referenced in the bin logs
>>>> that I have examined on the master. Are user and privileges regarding
>>>> replicated queries irrelevant on the slave and that is handled all
>>>> internally via the replication thread with no regard to user 
>>>> privileges?
>>>>
>>>> Thank you.
>>>> Jim
>>>>
>>>
>>> Your final supposition is correct. All privileges were checked and
>>> verified on the master when the original command was executed. The
>>> Replication system on the slave is going to repeat that change as well
>>> as possible given the state of its copy of the data without regards to
>>> "who originally performed this change" on the upstream master.
>>>
>>> We do not store credentials in the Binary Log because they are not
>>> important to either of the purposes of the Binary Log
>>>
>>> * point-in-time recovery
>>> or
>>> * Replication (which is very much like an automated, continuous
>>> point-in-time recovery)
>>>
>>> ===
>>>
>>> That replication account you mentioned, on the master, is required to
>>> give a slave (and you could have several) enough rights to read the
>>> Binary Log and not much else. This allows you to create an account
>>> that can login from a remote location with the "least privileges"
>>> necessary to do its job. This minimizes your data's exposure should
>>> that account become compromised.
>>>
>>> Many other accounts could also have the REPL_SLAVE_PRIV privilege and
>>> any of those could be used by a slave to do the same job. However
>>> losing control over one of those more privileged accounts could pose a
>>> higher risk to your data.
>>>
>>>
>>
>> Thanks, Shawn. Your response confirms what I had assumed was happening.
>>
>> So bottom line... what I plan to do is strip the various
>> insert/update/delete privileges from appropriate db users on my slaves.
>> I had placed them there originally because I thought they would be
>> needed for the replicated queries, but not true based on your response.
>>
>> I only want the various mysql users used by my code to have select privs
>> on the slaves so that if somehow a slave was mistakenly written to via a
>> bug in my code, that write would fail and I would receive the error. The
>> slaves should only be used for selects and should never experience a 
>> write.
>>
>> That would make sense based on our discussion, correct?
>>
>> Thanks again.
>> Jim
>>
>
> As masters and slaves can exchange "positions" or "roles" (it depends 
> on how you like to mentally visualize the relationship) within a 
> replication graph in a failover situation, adding time to re-establish 
> actual permissions using GRANT commands to reset user accounts to 
> their old privileges may not be time you want to spend.
>
> A cleaner, simpler solution is to set the --super-read-only flag in 
> the server:
> https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_super_read_only 
>
>
> That way, you get the behavior you want (no writes to a read-only 
> slave) without forcing differences to the content of your privileges 
> tables within different nodes of your Replication setup.  Each node 
> will remain a transactionally consistent copy of all the others 
> (within the temporal limits of replication being an asynchronous 
> process).
>
> Yours,
>

Thanks, Shawn.

super-read-only looks perfect for what I want. I can keep my slaves with 
all the potential users needed to take over as master without risking 
unwanted writes.

Given how you read:
"If the |read_only| 
<https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_read_only> 
system variable is enabled, the server permits client updates only from 
users who have the |SUPER| 
<https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_super> 
privilege. If the |super_read_only| 
<https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_super_read_only> 
system variable is also enabled, the server prohibits client updates 
even from users who have |SUPER| 
<https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_super>."
One somewhat gets the impression that in order to enable 
super_read_only, one must also enable read_only.

However, based on:
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_read_only
I see:
"Setting |super_read_only| 
<https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_super_read_only> 
to |ON| implicitly forces |read_only| 
<https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_read_only> 
to |ON|. "
So that reinforces that one only needs set super_read_only.

Is it appropriate to configure this in my.cnf, eg:
super-read-only=ON

Thanks again for the incredibly helpful suggestion.

Jim


--------------E2BD4040F3021D5F190C38B4--