JSOPanel Get started
Help Center › Administrators › Allowing remote database connections

Allowing remote database connections

Let one client address connect to one customer database from outside the server. Switched off until you turn it on, and for administrators only.

By default every database engine on the server answers on the server itself only. A customer's website can reach its database, but nothing on the internet can. When a customer needs to connect from their own computer or from another service (a reporting tool, a desktop database client, an office system), you can open that one connection for them. Only an administrator can do this, and it is off until you switch it on.

How it works

Three things all have to be open before anyone can connect, and JSOPanel opens them together so they always agree:

  • the database engine listens beyond the server (one switch per engine, for MariaDB, PostgreSQL and MongoDB)
  • the engine accepts that one client address, with its own login and password
  • the firewall lets that address through to the engine's port

If a step fails, the earlier ones are undone and you are told why, so nothing is left half open. The firewall has to be switched on first; the page refuses to do anything while it is off.

The remote login is separate from the website's own. It is named after the website's login with _r added, it can reach one database and nothing else, and MariaDB and PostgreSQL allow it 20 connections at a time. Changing or removing it never touches the website's own login.

Steps

  1. Open Databases, then Remote access.
  2. Under Database engines, press Turn on for the engine the customer uses. The engine restarts, which briefly disconnects every site using it, so choose a quiet moment. Nobody can connect yet.
  3. Under Allow a client address, choose the engine, pick the database (start typing its name, for example acme_shop, and the databases created through JSOPanel for that engine are suggested), type the customer's IPv4 address, and a password of at least 12 characters. Make one beside the password writes a strong one for you.
  4. Press Allow this address. The address appears under Open connections.
  5. Send the customer your server's address, the port shown for the engine, the login shown in the list, the password and the database name.

To take access away, press Remove beside the address. Its connections are ended at once and its firewall rule is removed. Turning an engine off removes every remote login and rule for that engine.

What to tell the customer

They need five things: the server address, the port, the database name, the login (the one in your list, ending in _r) and the password. Ask them to turn on encryption, called SSL or TLS, in their client. The ports are 3306 for MariaDB, 5432 for PostgreSQL and 27017 for MongoDB, unless the list shows a different one.

  • MariaDB: in a client such as MySQL Workbench or DBeaver, use the server address and port 3306, the login and password, and set the SSL option to Require. From a command line: mariadb -h SERVER -u LOGIN -p --ssl DATABASE.
  • PostgreSQL: in pgAdmin or DBeaver, set SSL mode to require. From a command line: psql "host=SERVER port=5432 dbname=DATABASE user=LOGIN sslmode=require".
  • MongoDB: the address is mongodb://LOGIN:PASSWORD@SERVER:27017/DATABASE?authSource=DATABASE. Add &tls=true if the page shows encrypted connections as available for MongoDB.

Connections only work from the exact address you entered. If the customer's own address is not the one you allowed, they will see a timeout, not a login error, because the firewall never lets them reach the server.

Encryption

MariaDB and PostgreSQL logins are set to refuse a connection that is not encrypted, whenever the engine has encrypted connections set up. MongoDB can offer encryption but cannot insist on it for one login.

The Database engines table shows, for each engine, whether encrypted connections are set up. If one says No, the page refuses to open an address for it unless you tick I accept an unencrypted connection. In that case the password and the data cross the internet in the clear, and the list marks the address as unencrypted. Think hard before accepting that for anything that matters.

Good to know

  • One IPv4 address per entry, never a range, a name or an IPv6 address. A customer with several offices needs one entry per address.
  • Several addresses for one database share its password.
  • MongoDB is only opened if it is already set to require a login. Otherwise the page refuses.
  • Only databases created through JSOPanel can be opened. Deleting a database or its account removes its remote access automatically.
  • With PostgreSQL, tables a customer creates over a remote connection belong to the website's own login, so the website can still use them.
  • A customer whose address changes (a home connection, for example) needs the old address removed and the new one added.
  • After restoring a whole server from a backup, remove and allow each address again. The settings come back, the logins inside the databases do not.
  • On the server, jsopctl remote-db status shows what is open and jsopctl remote-db off closes everything.
Tip: if a customer only needs to look at or edit their data, point them to the built-in database browser (Adminer) on their Databases page instead. It needs nothing opened at all.