0

I have two RDS mysql servers running, one is the main (master) rds server and the other is the read-only slave, which was created through the RDS console.

How would I create a new user account for the read-only machine, that would not have access to the main master machine? For example, when I run the following command:

CREATE USER 'foo'@'localhost' IDENTIFIED BY 'password';

I get:

The MySQL server is running with the --read-only option so it cannot execute this statement

I would like the user to have access to the following:

  • YES (read-only): site-read.cyrnzp...
  • NO (master): site-provisioned.cyrnzp...

How would this be done then?

John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
David542
  • 104,438
  • 178
  • 489
  • 842
  • Does `super_read_only` exist on RDS and is it this that is preventing the modification? Might be able to disable that temporarly – danblack Sep 18 '18 at 01:36

1 Answers1

0

Edit: Won't work on AWS RDS as sql_log_bin is privileged

Easiest way would be to create it on the master CREATE USER foo@localhost. This will replicate to the slave creating a user there as well.

Then to remove access on the master, SET SESSION sql_log_bin=0; DROP USER foo@localhost. The sql_log_bin=0 means that all statements in the same session aren't written to the binary log. All other server activity on different sessions is still replicated.

The result of this is the slave server won't see the DROP USER statement, which means the foo user is still there on the slave, but not on the master server.

danblack
  • 12,130
  • 2
  • 22
  • 41
  • could you please explain what's going on here with the above queries? And how then would you be able to re-enable the normal replicating behavior? – David542 Sep 18 '18 at 01:41
  • thanks, and then I suppose after the `drop user` command you would once against `set session sql_log_bin=1` ? – David542 Sep 18 '18 at 02:03
  • @danblack - RDS is a managed service. Have you asked AWS if this is supported? This may work until AWS changes something (restore, failover, etc.). I don't think that AWS supports doing this. – John Hanley Sep 18 '18 at 05:31
  • I haven't contacted AWS support. [create/drop user](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_MySQL.html) seems to be allowed. I've found no documentation about restrictions on `sql_log_bin`. There's also [IAM](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.html) that I haven't looked at. – danblack Sep 18 '18 at 06:14
  • @danblack your suggestions here will not work with RDS. `@@sql_log_bin` is a [privileged](https://dev.mysql.com/doc/refman/5.6/en/system-variable-privileges.html) session variable. Users `@localhost` are also not usable on RDS, because the local machine is not accessible. – Michael - sqlbot Sep 18 '18 at 08:08
  • I apologize. Edited to reflect this. Not deleted yes to prevent someone else answering the same way. – danblack Sep 18 '18 at 09:10
  • @danblack thank you for that courteous response. The solution, if you'd like to test it out and write it up, is to change `read_only` in the RDS parameter group of the replica from its default value to 0, make the change to add the user on the replica, and then change it back again. The default, as I recall, is a macro that makes the value true on replicas and false otherwise. (Requires non-default parameter group; changing a running instance to use a new parameter group causes a reboot, but modifying this parameter in the already-applied group should not... ymmv.) – Michael - sqlbot Sep 18 '18 at 09:25
  • ...Or, as you suggested, use IAM auth. Create the user on the master but in IAM policies, only give this user/role permission on the replica. – Michael - sqlbot Sep 18 '18 at 09:26
  • @Michael-sqlbot if I make that change on the read-only replica and update it -- would it eventually revert from the RDS natural sync process (for example, is there a maintenance window that syncs everything?) to being, again, an exact read-replica of the master server? – David542 Sep 18 '18 at 19:51