Remote Postgresql Server

Hi all

I’m using Virtualmin on a few separate virtual machines now, and have one of them configured as a database server.

So, I’ve successfully configured MySQL to run on the remote system and for Virtualmin on the web server to control the remote databases on the database server. However, I’ve had less luck with the PostgreSQL set up.

On the database server I’ve added a remote_user with all permissions and configured allowed hosts for the web server, tied to the remote_user. On the web server, I’ve configured the admin login details to match the remote_user from the database server, and configured the host and port numbers to match.

When I head to Webmin > PostgreSQL on the web server it simply says:
PostgreSQL is not running on your system - database list could not be retrieved.

Now I’m a little stumped as to where to look in order to troubleshoot the issue. The logs for postgresql aren’t showing anything helpful, and I don’t know where to go next.

Any ideas would be more than welcome.

You need to configure the PostgreSQL module in Webmin to connect to a remote database host. This can be done by clicking on the Module Config link, at Webmin > Servers -> PostgreSQL, and in the “System configuration” section setting the “PostgreSQL host to connect to” field.

Thanks Jamie

I have already configured the module using the instructions in your post, and used a custom port for it also. In the firewall, I’ve allowed all connections from my apache IP for the time-being to ensure I have no issues there.

In pg_hba.conf on the db server, I have these lines:
hostssl all my-username-here my-remote-ip-here trust
host all my-username-here my-remote-ip-here trust

I’ve tried 3 options for the last option in these entries (password, md5 and trust) - none of these work.

When I try to view the Postgres module in Webmin on the remote apache server, I get the message:

SQL select * from pg_database order by datname failed : no pg_hba.conf entry for host “my-remote-ip-here”, user “my-username-here”, database “template1”, SSL on

I’ve tried the same via command line and it returns 2 errors; the one above plus another with SSL off at the end instead.

The error outputs make me think that the networking part is all OK as the remote server can get to read the postgres hba.conf file. But somehow the permissions aren’t matched and I’m unsure how to get those talking to each other properly.

Any ideas on what I could try next?

Hey all

This issue has been resolved. It related to a mis-configured SSL option, ie switched on for 1 server but not the other. Also there was a problem defining a hostssl access permission when ssl wasn’t enabled.

Anyhow, all sorted for me now.

Thanks again Jamie for your comment.