MySQL Database does not permit non-localhost entry, codeworkout user cannot connect remotely.

[tycrowe]

To preface, I setup my instance of CodeWorkout following the ReadMe guide provided. (Using Vagrant) After successfully uploading and testing a few exercises, I thought I'd give the database a go and see the internals. After following the ReadMe's guide on connecting to the active MySQL server, I noticed there were some flaws. Note the following is not meant for production, just for testing purposes only.

Firstly, if the host isn't connecting from within the vagrant itself, no remote host will be able to access the database. This is due to this line in /etc/mysql/my.cnf: binding-address: 127.0.0.1 which will only allow same-server connections and thus no MySQL Workbench connections from other remote hosts. Changing the binding-address: to 0.0.0.0 (all interfaces) will do the trick. Another limiting line is just above, skip-external-locking, commenting this out will allow external hosts to connect to the database. (It's recommended this to be re-enabled for production. More information here.)

Secondly, the provided user codeworkout cannot connect from remote hosts. To fix this, one must mysql -u root -p (password is default root), and give the codeworkout user full privileges (on or from specific remote connections, the following command allows any) GRANT ALL PRIVILEGES ON *.* TO 'codeworkout'@'%';.

Lastly, and this more for convenience sake, but it should be outlined that vagrant users must port forward 3306 in the VagrantFile for the remote connecting to work. config.vm.network "forwarded_port", guest: 3306, host: 3306. Not really that important but again, convenience sake.

That should be everything I found, let me know if I'm wrong about something. I'd like to reiterate that I'm just attempting to get everything up and running in a development environment so I can understand the project better.

Thanks!

[ayaankazerouni]

These sound about right. I haven't actually tried to connect to the development database from a remote host before.

Just checking: is this documentation bug you're reporting? Or were you just asking if your steps sounded okay?

[s-edwards]

Note that this issue has nothing to do with CodeWorkout, and is instead about the mysql configuration. It is typical on many systems to disallow remote connections to mysql to prevent potential attacks on the db. Common practice is to use ssh tunneling via an authorized account on the server to connect to mysql, which allows direct external access to be shut off entirely but still allows authorized users to connect remotely.

The port forwarding and additional connection permissions you suggest, while convenient for developers, are a security risk if you're considering a production environment. If you use ssh tunneling instead, all remote connections are actually local anyway, and such issues go away.

But, then again, the vagrant setup in the repo is only used by a couple of our dev members for development and is not intended for production. If you're trying to run a production instance, you'll most likely want to set it up differently in order to support a more realistic number of concurrent users. Please do not take the vagrant instructions in the readme as suggestions for a production deployment, because that's not what they were written for.

[ayaankazerouni]

This should no longer be an issue with the Docker setup now prescribed in the README. Closing this issue.