Access phpmyadmin on separate server

Discussion in 'Installation/Configuration' started by rsteuer, Dec 14, 2017.

  1. rsteuer

    rsteuer Member

    We have a separate server for web and separate for databases. The issue we're running into is that users cannot access phpmyadmin for database administration, as phpmyadmin is trying to access the database on the webserver rather than the database serer. Is there a way to send phpmyadmin requests to the database backend?

    servers were configured with Debian 8 following the install manual. Thanks
     
  2. ahrasis

    ahrasis Well-Known Member

    I think you may need to modify PMA config.inc.php like what was being done in here.
     
    Last edited: Sep 16, 2020
  3. till

    till Super Moderator Staff Member ISPConfig Developer

    There are two solutions, either configure phpmyadmin as ahrasis pointed out or in case you have a phpmyadmin on each database server, then you can use the [SERVERNAME] placeholder in the PHPMyAdmin URL in ISPConfig, this placehlder gets replaced with the correct mysql server name automatically.
     
    ahrasis likes this.
  4. rsteuer

    rsteuer Member

    Thanks to both for the reply.
    Till, from ISPConfig panel, if a user clicks on the phpmyadmin icon, the site name is replaced by the local server name. For instance, if the site is xyz.com and they click on the icon, the browser is trying to take them to dbserver.ourdomain.com/phpmyadmin, which fails because we do not publish the db server through our firewall. Is there any way to change this so that we do not need to publish our database server?
    Thank you
     
  5. till

    till Super Moderator Staff Member ISPConfig Developer

    PHPMyAdmin can connect remotely to a server, so you can install PHPMyAdmin at any location that is accessible, then set the link in ISPConfig under System > Interface config to that location and finally configure phpmyadmin in it's config file to connect to your DB server. That's basically the config that ahrasis suggested (see link in his post).
     
  6. rsteuer

    rsteuer Member

    Thanks for the info. I am not able to get phpmyadmin to accept connections. What I've done so far is:
    1. added servername/phpmyadmin to System | System Config (also added check to Link to phpmyadmin in DB list)
    2. on the webserver, changed the IP in /etc/phpMyAdmin/config.inc.php to point to the dbserver
    3. commented out the bind addr in my.cnf
    I could not locate /etc/httpd/conf.d/phpMyAdmin.conf (or /etc/apache2/...) to make the change suggested.
    When attempting to access domain.com/phpmyadmin, I receive a prompt for credentials, but the login fails. I'm assuming the requests are being sent to the phpmyadmin directory on the webserver, not the db server because I can login to phpmyadmin on the db server without issue.

    Any suggestions?
     
  7. ahrasis

    ahrasis Well-Known Member

    If you are using ISPConfig, you don't need to make any changes to "phpmyadmin.conf" because none exist and I think normally phpmyadmin is under ISPConfig vhost while the permissions to access its web page are already granted by default.

    If your config.inc.php has been set correctly and apache / nginx has been restarted accordingly, reasons on why you cannot access it may probably be seen from the logs of the servers.
     
  8. till

    till Super Moderator Staff Member ISPConfig Developer

    domain.tld/phpmyadmin is the local phpmyadmin and not the Db server of course. The setting in ISPConfig configures the phpmyadmin button in the database list and you have to use [SERVERNAME] and not servername there. Enter into the phpmyadmin URL field

    http://[SERVERNAME]/phpmyadmin

    and when you click on the phpmyadmin button in ISPConfig, the phpmyadmin of the database server will be opened.
     
  9. rsteuer

    rsteuer Member

    Hi @ahrasis,
    I was going to change the phpmyadmin.conf based on the link you provided, starting with "We have to edit the vi /etc/httpd/conf.d/phpMyAdmin.conf file". But, since I couldn't locate such a file, nothing was actually changed.

    @till, I will make the change suggested and let you know.

    Thanks to both of you.
     
  10. Chris_UK

    Chris_UK Member HowtoForge Supporter

    The link by ahrasis is no longer active, these are the steps I took under ubuntu 18.04

    ssh into your database and run:
    Code:
    mysql -uroot -p
    CREATE USER 'phpmyadmin'@'<ispconfighostname>' IDENTIFIED BY '<your password>';
    GRANT ALL privileges on *.* TO '<your dbuser>'@'<your ISPCHOST>' IDENTIFIED BY '<your password';
    flush privileges;
    
    ssh into your ispc host server
    Code:
    nano /etc/phpmyadmin/config.inc.php
    //Find the comment that says Authentication type:
    //Edit the host to match the hostname or ipaddress of your server, if local and cannot be accessed outside of your network, you //will likely be better to just use the ipaddress for simplicity.
    ctrl + x and save.
    
    You shouldn't need to reboot or restart any services, you should be able to access the remote database from your ispconfig panels phpmyadmin installation.

    For each client web server you will need to repeat the steps using the webservers specifics if you want to allow them to login from their own phpmyadmin installation, you will also need to restrict the databases the user can access so dont user *.* instead use 'databasename'.*

    The issue I have found with this is that even when the remote server is set the user is not created for the hostname.

    You will note the two bold lines, they were what I added manually, everything else was added by ispc automatically, Note there is no hostname under host in the automatic entries. This prevented me from connecting, hence i needed to intervene and make my own entries.
    This was what prompted me to intervene:
    #1045 - Access denied for user 'c1test'@'server1' (using password: YES)

    Code:
    MariaDB [mysql]> select user, host, password from user;
    +------------+---------------+-------------------------------------------+
    | user       | host          | password                                  |
    +------------+---------------+-------------------------------------------+
    | root       | localhost     | *hash |
    | ispconfig  | localhost     | *hash |
    | phpmyadmin | server1       | *hash |
    | c1test   | server1       | *hash |
    | c1test     | 192.168.1.61  | *hash |
    | c1test     | ***.***.***,*** | *hash |
    | c1test     | localhost     | *hash |
    +------------+---------------+-------------------------------------------+
    
    One important thing to note however, upon changing a mysql user password with this there was no change in the mysql table to reflect this, I had to open and save the database in ISPConfig. Clearly this isn't good, a check should be performed on password update to see if the user is assigned to a database. it should just be a local check that can be added to the job queue if an update needs completing.

    NOTE: if you set a domain db1.server.com instead of the ip in config.inc.php, the fqdn hostname will be needed eg: user @ web.server.com if you use the ip, just the partial hostname is used web


    Sorry for the long post again.
     
    Last edited: Sep 22, 2019
  11. ahrasis

    ahrasis Well-Known Member

    Chris_UK likes this.
  12. Chris_UK

    Chris_UK Member HowtoForge Supporter

    I appreciate the reply with the new link to the tutorial.

    However, I believe it shouldn't be needed, I think that ISPC should be handling all of this, maybe through some interactive shell script or through the expert installation steps.

    As an aside:
    I can only access the other vhosts by ipaddress because i'm running vSphere ESXI behind a dhcp server so I only have one public IP address (home network).

    I am aware this is not the normal use case for ISPC but on the same note, i doubt that its a such an edge case that it would not benefit from a little look into to way the users are added to the remote server.

    In any case what currently occurs is three (in my case as i added the local and public ip under the configs) users are added to the database, but none of these are correct for this configuration. the user hosts as you saw in my previous reply include localhost.

    I am not even sure why localhost is added on the remote server because it would only be useful from a ssh or direct server access and to be blunt:

    There is no way in hell anybody should be getting shell access to the database server save for the system administrators who would never need to use this "localhost" user because they already have root to access all databases if really needed, end blunt ;p

    As i previously mentioned, the FQDN is not passed during the connection, just the local part of the hostname when the connection to the remote database server is made using just an IP address. I have a feeling that this is because there is no hosts file check to be made when using the IP address. And as I think on this more, its possible this is not even an ISPC issue directly but a phpmyadmin one, that said its part of the overall ISPC setup with a remote db so ispc could make the changes with a "patch" if needed.

    I have taken a look at the git but to be fair without some extensive digging into the source I would not know where to make these changes in my installation. I probably would not want to do this anyway because it would prevent me from updating until it was applied into the source.
     
    Last edited: Sep 22, 2019
  13. till

    till Super Moderator Staff Member ISPConfig Developer

    There is no patch needed and also no need to create a phpmyadmin user or whatever, the localhost user where you don't understand what it is needed for is the option to use phpmyadmin on the database server and I explained in post #8 of this thread how to configure ISPConfig to redirect the user to the correct phpmyadmin instance on the db server.
     
  14. Chris_UK

    Chris_UK Member HowtoForge Supporter

    Sorry till, I must have missed that reply. I will go back over the thread and read it more thoroughly. It still does not resolve my issue as the connections are over local IPS which adds the ip to the database as the host but the user is the hostname eg: web1 web2 I had to add these manually.
     
  15. ahrasis

    ahrasis Well-Known Member

    I think the tutorial that added extra config for PMA is, on the other hand, meant for those who want to remotely access their db servers from only one PMA installation.

    So it really depends on each administrator's preference.
     
    Last edited: Sep 26, 2019
  16. till

    till Super Moderator Staff Member ISPConfig Developer

    It resolves your issue as phpmyadmin connects trough localhost on each node and localhost is added automatically for the user.
     
  17. Chris_UK

    Chris_UK Member HowtoForge Supporter

    I am going to simplify my setup here to try to clarify what I mean and why it doesn't.
    I have one public ip address, I have now gone with nginx to reverse proxy, so i added nginx to a dedicated vhost. (You don't need to know that in this scenario but just wanted you to know I have moved past what I am explaining here).

    So, one public IP, I pointed this (forwarded ports) to one web server.
    In the same network I have a database host, its dedicated to only database functions with nothing other than that, no web, phpmyadmin, dns, mail etc.

    This is a shared database server for all websites, hosts.

    Of course each web host will have its own phpmyadmin, because it solves a problem of having a global phpmyadmin server, although that would be doable under the right situation.

    So what I have is a web server with phpmyadmin and database server without it. And complicating things is that phpmyadmin interface is loaded over a local ip, not the public one.

    So here's what happens. ISPC adds the appropriate users for a user that would be from a public host/ip, thats great in most cases, but for me it fails because the local ip means that there is no matching user when i connect, so i get [email protected] instead of [email protected]

    The actual message i got was that permission was denied for [email protected] I manually added the [email protected] and I got connected (yes phpmyadmin one wasnt needed).

    Honestly i'm not sure if the situation is due to using vms over a local network or just using local netwok altogether but its unavoidable (or was until i reverse proxied) in my situation.

    As an aside, I want to put in a feature request, where would I do this?
     
  18. till

    till Super Moderator Staff Member ISPConfig Developer

    Chris_UK likes this.
  19. till

    till Super Moderator Staff Member ISPConfig Developer

    Under System > server config in ISPConfig, which IP do you have there for the web and db server, the internal or external one? My guess is that you might use a wrong IP there. ISPConfig automatically allows access from the webserver where the website of the database resides to the database, so when you installed phpmyadmin on your web server and not db server, then that's fine as well as access ios granted automatically. If you get a wrong IP in the database user, then this wrong IP must be set under System > server config of the webserver.
     
    Last edited: Sep 26, 2019
  20. Chris_UK

    Chris_UK Member HowtoForge Supporter

    I have the internal IP on all servers. But with the reverse proxy in place I suppose I would be better to set the public ip for all the servers now?
     

Share This Page