Like all databases servers, MySQL (and PostgreSQL) uses it's own set of usernames and passwords, as well as it's own system of permissions management to restrit/grant access to set databases, as well as the tables within and the commands that can be run on those databases/tables.
However, there is a unique element within database servers - it's not only the username and password that grants you access to the server, but also the name of the computer (the hostname) that you use to connect from. By default, this is set to '%', meaning 'from anywhere'.
What makes this feature interesting is two-fold: First, a username can be specified more than once in the users table, as each hostname and username pair require their own entry, so a username can have a different password when connecting from a different hostname (however, a user cannot have two different passwords for the same hostname value).
Secondly, as each hostname and username pair have their own entry, so they have their own set of permissions - in theory, the same username (with or without the same password) can have different permissions for access. So, for example, you can say that when a user is not connecting directly from the server, they cannot delete any data.
Obviously, this makes the whole system of managing permissions on the database server much more difficult.
How does Plesk create a database user?
As a result, Plesk have simplified the system and so detailed control over this permissions management system is not available. Instead, when it creates a user for a database there are two set defaults:
- All users have their hostname value hard-coded to localhost, allowing them to only connect to the database server as long as they are on the same computer as the server is on; and
- All users have SELECT, INSERT, UPDATE, DELETE, INDEX, ALTER, CREATE and DROP permissions granted to all the tables within the database the user is created for (but no others). In terms of CREATE and DROP, the permission is not applied to the database itself, so they cannot create any more or delete any other databases on the server, just tables, views, triggers, etc (where supported) within the database.
So can we connect remotely?
In short, no.
As Plesk has hard-coded the all the hostname values to localhost, the database server will not allow anyone to connect to the server using any username and password from any remote computer. Given the therefore redundant nature of the ability remotely log in to the database server (as it will reject any attempt to authenticate), we've disabled the ability to connect through the firewall.
This reduced the risk of a bug with or DDoS attack on MySQL or PostgreSQL compromising the server and hence our customer's data. However, as we do have SSH enabled on all our servers, it is possible to establish an SSH tunnel, which will make it appear like the database server is in fact on your computer to the program.
For further details on this, please see the following Knowledgebase article:
How can I mange the database remotely with using an SSH tunnel?
If you want to manage your database, but don't like the idea of establishing an SSH tunnel, or for any other reason are unable, you can either install your own copy of a web-based utility (for example phpMyAdmin or phpPgAdmin), or using the modified phpMyAdmin (or phpPgAdmin for PostgreSQL) interface available in Plesk (by clicking on the WebAdmin icon under each database).
