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.
     
  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

    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 at 12:40 AM
  11. ahrasis

    ahrasis Well-Known Member

    Chris_UK likes this.
  12. Chris_UK

    Chris_UK Member

    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 at 3:12 PM

Share This Page