Access phpmyadmin on separate server

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

 

I think you may need to modify PMA config.inc.php like what was being done in here.

 

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

 

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?

 

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.

 

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.

 

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.

 

Sorry, but I think there are a lot of other similar tutorials out there; one of which is from howtoforge tutorials itself: https://www.howtoforge.com/managing...min-installation-using-ssl-encryption-p2/amp/

 

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.

 

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.

 

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.

 

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 user@hostname instead of [email protected]

The actual message i got was that permission was denied for user@host. I manually added the user@host 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?

 

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?