ISPConfig 3.1.13 and activation Let's Encrypt from customer

Discussion in 'ISPConfig 3 Priority Support' started by tritema, Oct 17, 2018.

  1. tritema

    tritema Member HowtoForge Supporter

    Dear,

    starting from this version (latest version available) it's looks like that arise a problem with Let's Encrypt activation, from a customer level access.
    Basically when a customer try to enable Let's Encrypt the job is not added in the job queue and nothing happens (or logged).
    But if I try to activate the same Let's Encrypt as admin, that's works like a charme, without any problems.
    The customers has the ability to activate Let's Encrypt (is controlled via template, and the template has Let's Encrypt rights).
    Any glue of what could happen here, and what to debug?

    Thank you.
     
  2. till

    till Super Moderator Staff Member ISPConfig Developer

    I guess what you see is not directly Let's encrypt related, the behaviour you describe is the admin site protection which exists in ISPConfig for many years now. The site must have been created by the admin user and not the client himself (or by admin and not using the login as client function). When an admin creates the site, then this site is protected from changes of the client, so only the admin can do things like enabling PHP or changing PHP mode or enabling let's encrypt, the client himself can not do that. If you unintentionally activated the admin protection for that site, then you can disable it by changing the value in the sys_perm_group field in web_domain database from 'ru' to 'riud' and changing the sys_userid from ID of the admin to the sys_userid of the client user.
     
  3. tritema

    tritema Member HowtoForge Supporter

    Thank's for the feedback. But I suppose that there is something in the middle.
    Let me explain.

    We configure the services inside ISPConfig, with the external API (basically we have a plugin that connect our WHMCS installation with ISPConfig, that use ISPConfig api system).

    So, due the API, the plugin connect ISPConfig via a remote user of ISPConfig itself. And the jobs run under that user.

    Moreover, we have seen that the Let's Encrypt trouble happen only in few customer, not all of them. And every hosting is created on the same way.

    In your opinion, what could be a good way to debug that one?
     
  4. till

    till Super Moderator Staff Member ISPConfig Developer

    Check the website permissions in the database as I described in post #2 and compare an affected website with a non affected site.
     
  5. tritema

    tritema Member HowtoForge Supporter

    Thank you.
    It was the control that I did. The result are pretty strange:
    I don't think that sys_perm_group is the source of all the problems.
    Because for example I have domain that has that configuration:

    Code:
    +------------+-------------+----------------+-----------------------------------+------------------------+
    | sys_userid | sys_groupid | sys_perm_group | domain                            | company_name           |
    |         XX |          XX | ru             | thedomain.com                     | THE RESELLER NAME      |
    +------------+-------------+----------------+-----------------------------------+------------------------+
    And if I log-in inside the ISPConfig, as the "THE RESELLER NAME" user, i can activate let's encrypt, without problems.
    Otherwise, I have a domain configurated in that way:

    Code:
    +------------+-------------+----------------+-----------------------------------+------------------------+
    | sys_userid | sys_groupid | sys_perm_group | domain                            | company_name           |
    |         YY |          YY | ru             | anotherdomain.com                 | THE CUSTOMER NAME      |
    +------------+-------------+----------------+-----------------------------------+------------------------+
    than if I try to activate the Let's Encrypt as "THE CUSTOMER NAME", the job is not added in the job queue.

    That sound pretty strange to me.
    Exist others check that i can verify?
     
  6. till

    till Super Moderator Staff Member ISPConfig Developer

    That's the admin protection that I described in my first post. The domain is locked so that the client is not allowed to change settings on the first tab. Set sys_perm_group to 'riud' and the client will be able to modify the site.
     
  7. tritema

    tritema Member HowtoForge Supporter

    Thank you, what I wasn't able to understand was that for "anotherdomain.com" on that example doesn't works. Otherwise for "
    thedomain.com" works. And the configuration of both domain was the same. By the way, correcting the permission and the
    sys_userid as your instruction, both are working correctly.
    I have made the change both on master db and the slave db of the server. Can you confirm please that we need to manually update the record from db on both master and slave?

    We use your external API to create the customer and create the web host. It's there something on the API that set as default "sys_userid" the admin id and the sys_perm_group "ru" instead "riud"?

    thank you
     
  8. tritema

    tritema Member HowtoForge Supporter

    Digging deeper in the code I see that the remote method "sites_web_domain_add" has a readonly parameter, that if "TRUE" set sys_userid=1 So I think that something trigger that on with TRUE, and put the website in readonly.

    So the question is now changed: I see that for any web_domain added here the sys_groupid is the sys_userid of the customer. So it's safe update (on master and slave) all the record that has sys_userid=1 with sys_userid=sys_groupid value? Something like that:

    update web_domain set sys_userid=sys_groupid where sys_userid!=sys_groupid and sys_userid=1;

    On master database and slave database?
     
    Last edited: Oct 19, 2018
  9. till

    till Super Moderator Staff Member ISPConfig Developer

    That's not the case. It might be that the numbers are the same by incident for some clients though. The sys_groupid is the groupid if the group of the client. And like I said, the nehaviour if a website is in protected mode or not is controlled by the sys_perm_group setting.
     
  10. tritema

    tritema Member HowtoForge Supporter

    Ok, starting from this quote:

    I need to do two query:

    - first change "ru" to "riud" on sys_perm_group field. And that's fine;
    - change sys_userid from ID of the admin to the sys_userid of the client user. That one, is more complex. Because on the table web_domain I have just "1" as sys_userid, so what kind of key i can use in order to find out the sys_userid of the user of that domain? Exist some kind of documentation that display the database relationship? otherwise I need to guess it...

    ty
     
    Last edited: Oct 19, 2018
  11. tritema

    tritema Member HowtoForge Supporter

    Debugging more about that part:
    without have the relation from web_domain and customer (with no sys_userid set), i have managed to use something like that
    in order to extract sys_userid (pseudo-code follows):

    Code:
    - i'm going to extract every domain that has sys_userid=1, and from there get the sys_groupid (limit 1 in order to analyze only 1 domain a time):
    
    $my_sys_groupid="select sys_groupid from web_domain where sys_userid=1 limit 1";
      
    - with that sys_groupid ($my_sys_groupid) of the user i'm going to extract from the table sys_group, the client_id of the customer:
    
    $my_client_id="select client_id from sys_group where groupid=$my_sys_groupid"
    
    - with the client_id ($my_client_id) i can extract the sys_userid of the customer:
    
    $my_sysuserid="select userid from sys_user where client_id=$my_clientid"
      
    - now that i have the sys_userid ($my_sysuserid) i can update the field on the table web_domain:
    
    $update_sysuserid="update web_domain set sys_userid=$my_sysuserid where sys_groupid=$my_sys_groupid;
    
    Basically the problem was extract the relation from the domain in web_domain table and the client sys_userid. Honestly i don't see others key different than groupid to use.

    That make sense for you?

    Sorry for bothering about it, but without documentation i need to guess something, and I need to be sure before to run the query, because i have some hundreds domains in that situation.

    Thank's for your time.
     
    Last edited: Oct 19, 2018
  12. till

    till Super Moderator Staff Member ISPConfig Developer

    You might try this SQL query to disable the admin protection for all sites:

    Code:
    UPDATE web_domain SET sys_userid = (select userid FROM sys_user WHERE default_group = web_domain.sys_groupid), sys_perm_group = 'riud' WHERE sys_groupid != 0;
    But try this on a test system first or do at least a full backup of the ispconfig database before you try it to be able to restore the original database in case that there are some side effects.
     
  13. tritema

    tritema Member HowtoForge Supporter

    Thank you that's sound pretty good.
    On monday i will try.

    Will be run on both master and slave server right?
     
  14. till

    till Super Moderator Staff Member ISPConfig Developer

    Yes, you have to run it on both servers.
     
  15. tritema

    tritema Member HowtoForge Supporter

    ok i'll try, and i'll update this thread after. Thank you
     
  16. tritema

    tritema Member HowtoForge Supporter

    Dear,

    after a complete backup of every database involved, i have tried that query.
    That's worked. So now user are be able to activate SSL service from his own panel.
    There is only a small think that i can't understand.

    Checking on the web_domain table of master server, everything is smooth. So the "sys_userid" value is correctly filled for the 100% of the domain involved. But on the slave database, most of the entry of the web_domain table have 0 as "sys_userid".
    I suppose that this is normal, because the table "sys_user" is empty on the slave server, due is filled only on the master database.

    However from the interface everything works smoothly.
    Any clue about it?

    Thank you.
     
  17. till

    till Super Moderator Staff Member ISPConfig Developer

    Yes, that happened because sys_user is empty. But it should be ok like that as the permission columns of the web_domain table are read on master only. So we probably could have skipped the query on the second server.
     
    tritema likes this.
  18. tritema

    tritema Member HowtoForge Supporter

    ok thank's for the support.
     

Share This Page