0

I have a MariaDB 10.2.14 server running on a CentOS server (10.0.0.201) and a jobs server with various Java processes running on another CentOS server (10.0.0.193). MariaDB is configured such that the root user can only connect from localhost - the various job apps have their own users to connect with.

One of the jobs is currently running and takes quite a while - it moves a lot of data using an insert-select statement. If I do a show processlist in the database it tells me the connection has come from localhost and is running as root:

| 58684 | root             | localhost:57486  | db_name              | Query       |  23420 | Sending data ...

A netstat on the db server shows the connection with that client port is actually established from the jobs server:

netstat -n | grep 57486
tcp        0      0 10.0.0.201:3306         10.0.0.193:57486        ESTABLISHED

There is no job running on the DB server (it doesn't even have Java installed). Other connections from the job server show as I'd expect - with the remote IP and the application user name. Is anyone able to explain why this might be happening? Is the MariaDB server somehow proxying the remote request? Why would it be running as root?

3
  • 1
    Maybe the job in question uses some procedure created using DEFINER=`root@localhost` ?
    – Akina
    May 15 '18 at 4:50
  • 1
    @Akina the job does use a stored proc with definer=root@localhost. I didn't realise the behaviour of the definer attribute, so I got to learn something today! Post this as an answer and I'll accept it.
    – Doughnuts
    May 17 '18 at 0:05
  • Post this as an answer and I'll accept it. It was my assumption, not knowledge. Please confirm that change DEFINER attribute of the procedure (alone!) solved Your problem to be sure it's really solution, not happenstance.
    – Akina
    May 17 '18 at 4:43
0

I would expect a connection to show as coming from localhost if a remote user was querying a table that has the federated, federatedX, connect or spider storage engines (or possible other similar storage engines), and the table was configured to connect as the root user to the local MariaDB instance rather than a remote one.

You can find the storage engine used for a particular table with:

SHOW TABLE STATUS WHERE Name='your_table_name';

You can also find tables using a particular storage engine with:

SHOW TABLE STATUS WHERE Engine='name_of_engine';

You could also do something like:

SHOW TABLE STATUS WHERE Engine IN ('FEDERATED', 'FEDERATEDX', 'CONNECT', 'SPIDER');
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.