UIDs, GIDs, and Database Naming

As I'm coming up to speed on ISPConfig3, I have a couple of questions:

1. I see in System > Interface Config the prefix for the Database name and the Database user. I changed the Database name prefix to:

[CLIENTNAME]_
​

I added the trailing underscore as a convention so to help avoid namespace collisions (as long as usernames are never assigned with underscores). For example, with usernames abc and abcdef, the database abcdefghi could have been created by either user without this naming convention.

2. I have the Database user prefix set to be:

[CLIENTNAME]
​

When I try to create a database without any additional characters typed in the Database user field, I get an error. This field should be optional. Do you agree?

3. What is the purpose of the Database password field? As far as I know, passwords are not assigned to databases, but instead to users. If that is what is meant here, I still see an issue: what if the database user already exists? I think the Database user should be renamed to be Database user password. Also, it appears that I cannot create two databases with the same database user. If this is so, I think this is a bug. It is reasonable for a user to have more than database.

4. I see UIDs and GIDs such as:

web1:client1
web2:client1
web3:client1
​

for each of the websites that are created. I would much rather have a more mnemonic naming. I'd like [CLIENTNAME] to be the GID. And, I'd like the UID to be something like [CLIENTNAME]_[DOMAINNAME] where [DOMAINNAME] is the actual domain name with the period (".") replaced by an underscore ("_"). Then, instead of the above, for the username "fatbear" and for the domains domain1.com, domain2.com, and domain3.com, you'd have:

fatbear_domain1_com:fatbear
fatbear_domain2_com:fatbear
fatbear_domain3_com:fatbear
​

Yes, it is verbose, but it is also guaranteed to be unique, and it makes it much easier to verify correctness when doing operations on a server.

How can I modify ISPConfig (either via the GUI, or by modifying the code) to enable this naming convention for UIDs and GIDs?

 

Thanks for the reply. First, let me describe some "use cases" where it is reasonable for there to be multiple databases owned by a single MySQL user:

1. Revision Management

A given user updates a database regularly and needs to take snapshots at certain points. It is not reasonable to create a new MySQL user for each snapshot and this unnecessarily complicates restores as well.

2. Staged Deployments

Many of our customers develop their websites not directly in the www domain. They use something like stage.domainname.com for development. When they want to cut over the stage website to the www website, they run a deployment script. Again, it unnecessarily complicates deployment to require that these databases have different MySQL users.

3. Administration

From a system backup perspective, it is useful to know which databases are associated with a client. Overwhelmingly, I use the GID as the MySQL username. This makes it much easier for the user as well. The password management is controlled at one point in the control panel. And, when the user updated their client (GID) password, having it propagated automatically to the MySQL user makes administration much easier for customers and administrators alike.

4. Applications that use multiple databases

Some of our clients have applications that use multiple databases within a single web application. I can't go into specific details, but the present configuration for these customers is that the databases have the same MySQL user name.

My opinion is that "informed choice" is a good goal for UI design. While a control panel might want to influence certain best practices, it is a reasonable request to have multiple databases for the same MySQL user.

So, the question is: can this be done as a feature, or does it need to be done as a hack?

 

On the matter of Unix user names and group names and MySQL usernames:

Using the Unix GID as the MySQL username (and keeping the passwords of the client and the MySQL user sync'd) is my preference for ease of administration. I prefer highly mnemonic user and group names because:

1. It helps me to spot errors as I look at files in the Unix filesystem.

2. Such names are easier for customers (and me) to remember.

3. It appeals to customer's vanity (the acme customer would much rather see and use "acme" instead of "client23" when logging in, ftp'ing files, etc.)

To address your concerns:

1. Note that my preference is to have the Unix GID be the MySQL username for an account. This implies that the GID should be restricted to a certain number of characters less than 16. It all depends on how much variability one would want for MySQL usernames that have a GID prefix. This can be a settable parameter. But, let's say, by default, the GID length is 10. That would still leave 6 characters for MySQL "sub" users.

2. The UID is used for other Unix services (such as mail, web, and ftp), but not for MySQL.

Again, is there any reason to not allow a user of ISPConfig to not choose this approach if they want to? It does satisfy all technical requirements. Again, it may be reasonable for ISPConfig to try to influence the user's behavior. But, if the system administrator prefers to adopt naming such as I've described above, I say the control panel should let them.

Lastly, regarding the "_" issue for the database name.... It is useful to be able to just look at the database name and know which MySQL user it belongs to. In the example I previously gave (with users abc and abcdef and the database name abcdefghi), you can't tell which user owns the database named abcdefghi by simple inspection of the database name. Some character, not permitted as part of the database name prefix (the MySQL user name), would guarantee the ability to identify the database name's user by simple inspection. Note that the "_" is not special as is indicated in Section 8.2 Schema Object Names of the MySQL documentation. It is only when the underscore is used for searching that problems arise. But, that being said, I do agree that it might be best to avoid needing to escape underscores when doing searching. So, we could use other character that is not going to be allowed as a GID. I think the period (".") or the hyphen ("-") might be better choices based on your concern.

Again, this comes down to: can this be a feature, or will this require hacking of the codebase. Obviously, I prefer this to be a feature.

 

I quite agree. Whatever we do, it needs to support backward compatibility. I'm sure this is something that we can do.

 

Thanks, Till, for the encouragement and willingness to include choices for the user. I will do this, and will be guided by your experience in doing it in a way that you find acceptable.

And, thanks for the additional reference regarding the underscore. You've improved my knowledge on this topic!