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:

1) Adding the underscore in the name is not a good idea. The underscore is interpreted as wildcard in mysql usernames (see mysql usermanual). Using the clientname is also not a good idea for another reason, all mysql usernames are limited to a ovaerall length of 16 chars, so if you client has a long username, then he might not be able to add any mysql users or only mysql users with 1 char.

2) No, the user should always enter a name for the database. Thats why we made this error. We had it before that the username part was optional and it confused the users when they tried to add another database.

3) The password is needed to access this database in conjunction with the username of this database. There can not be duplicate usernames as ISPConfig prevents that. Every database shall have its own user and own password and ISPConfig enforces this for security reasons.

4) Using the client name and domain name in the usernames of webistes is not recommended. If you would change the username of the client or the domaoin of a website, all modern cms systems would stop working as they would loose the ability to access their files and write to the filesysrtem. This naming scheme can only be used if you lock the client name field and the website domain field so that it can not be changed after initial creation of the websites and clients. But I prefer that I'am able to change a domain name of a website instead of having to delete the whole website and client to be able to chnage the name.

Instead of changing the user- and group names of the websites, it might be a option if we provide some helper scripts for the daily admin work e.g. for chowning the files in a website to the website user and group that support the website domain as parameter and does the lookup of the correct user and group in the ispconfig database automatically like:

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?

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.

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.

Click to expand...

Feel free to implement it if you like. If it is disabled by default and the current default behaviour does not get changed, then I will integrate it in the main branch. We will also have to add some warnings so that administrators are ware that using the clientname as prefix might break their sites when they change a client.

Regarding unserscores in mysql users, this is not just related to searching, we had trhis problem in some older ispconfig 2 releases and it is a huge security problem as it allows access with phpmyadmin to databases that are not owned by the user. For example, see here:

This is not a bug. Mysql uses _ as the single-character wildcard, and % as
the multi-character wildcard in pattern matches. This is clearly explained in
the manual <http://dev.mysql.com/doc/mysql/en/grant.html>:

Note: the '_' and '%' wildcards are allowed when specifying database
names in GRANT statements that grant privileges at the global or database
levels. This means, for example, that if you want to use a '_' character
as part of a database name, you should specify it as '\_' in the GRANT
statement, to prevent the user from being able to access additional
databases matching the wildcard pattern; for example, GRANT ... ON
`foo\_bar`.* TO ....