Issue 820 in tungsten-replicator: Replicator should support secured MySQL installations

[tungsten-...@googlecode.com]

Status: Accepted
Owner: robert.h...@continuent.com
CC: linas.vi...@continuent.com, giuseppe...@continuent.com
Labels: Type-Enhancement Priority-High FoundIn-2.2.0

New issue 820 by mc.br...@continuent.com: Replicator should support secured
MySQL installations
http://code.google.com/p/tungsten-replicator/issues/detail?id=820

1. To which tool/application/daemon will this feature apply?

Core replicator and tpm

2. Describe the feature in general

Currently the replicator expects to have SUPER or REPLICATION CLIENT grants
on the MySQL server it is running on. On some installations where the
database is locked down it is not possible to grant these.

There should be a mode or setting to allow starting without this option if
possible.

3. Describe the feature interface

Disable the strict checking of GRANT checking
Ensure the documentation coves how to manually start replication by
defining the start event number

4. Give an idea (if applicable) of a possible implementation

Remove the tpm checking
Remove the TR using a binary log switch to the MySQL server to reset and
determine position

5. Describe pros and cons of this feature.

Would allow installation in locked down environments

6. Notes

--
You received this message because this project is configured to send all
issue notifications to this address.
You may adjust your notification preferences at:
https://code.google.com/hosting/settings

[tungsten-...@googlecode.com]

Comment #1 on issue 820 by mc.br...@continuent.com: Replicator should

support secured MySQL installations
http://code.google.com/p/tungsten-replicator/issues/detail?id=820

With these permissions for tungsten user:

+---------------------------------------------------------------------------------------------------------+
| Grants for
tungsten@%
|
+---------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tungsten'@'%' IDENTIFIED BY
PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `tungsten_alpha`.*
TO 'tungsten'@'%' |
+---------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Going ONLINE with 'trepctl online', the following error is reported:

Operation failed: Online operation failed (Unable to connect to DBMS:
url=jdbc:mysql:thin://tr-ms1:13306/tungsten_alpha?createDB=true
message=Access denied; you need (at least one of) the SUPER privilege(s)
for this operation)


Checking the log, it's the SQL_LOG_BIN statement that causes the disconnect:

37 Connect tungsten@tr-ms1 on
37 Query CREATE DATABASE IF NOT EXISTS tungsten_alpha
37 Query USE tungsten_alpha
37 Query SET SQL_LOG_BIN=0
36 Quit

[tungsten-...@googlecode.com]

Comment #2 on issue 820 by robert.h...@continuent.com: Replicator should

support secured MySQL installations
http://code.google.com/p/tungsten-replicator/issues/detail?id=820

Good point. This is occurring because in cluster environments we update
the trep_commit_seqno table and need to do this without having transactions
hit the log. (Heartbeats are another matter--we do want these to be
logged.)

A simple answer would be to have a non-privileged master. This cannot be
easily used in clustering but for pure replication topologies would do the
job.

Another level of privileges involve granting REPLICATION CLIENT. We should
in theory be able to operate without this but you would need to read the
binlog directly from storage and also set the master start point
explicitly. We may want to distinguish this as a separate setting.

[tungsten-...@googlecode.com]

Comment #3 on issue 820 by scott.wi...@smartsheet.com: Replicator should
support secured MySQL installations
https://code.google.com/p/tungsten-replicator/issues/detail?id=820

In a direct pipeline with a MySQL server, the grants that I needed* were:
REPLICATION SLAVE, REPLICATION CLIENT, SUPER, RELOAD

* My MySQL-fu is weak, so it is entirely possible that the grants I'm using
are a superset of the actually required grants. It still seems like a
better set than the "grant all the things" approach recommended by the
documentation.