Skip to content

MetaMiner Database SSL

Encrypt the connection between the MetaMiner Server and the MetaMiner Database (MMDB), and limit which hosts may connect to the embedded database.

How it works

The MMDB is PostgreSQL: either the embedded database installed with MetaMiner (service InfolytikPostgres, port 49614) or an external PostgreSQL server. You turn on SSL in two places:

  1. On the database server, so it accepts encrypted connections.
  2. In the MetaMiner Administrator (MMA), so the MetaMiner Server (MMS) connects with SSL.

With SSL on, the MMS verifies the database certificate: it must chain to a CA the MMS trusts, and its name must match the Host in the MMDB configuration.

Before you begin

  • A server certificate for the database host, issued by your CA, in PEM format (server.crt), and its private key in PEM format without a passphrase (server.key). The embedded database starts automatically as a service and cannot prompt for a passphrase.
  • The certificate of the CA that issued it, in PEM format.
  • A maintenance window: the MetaMiner Server and the database restart.

Steps

  1. Stop the MetaMiner Server service: net stop metaminer.
  2. Copy server.crt and server.key to the database folder C:\Program Files\Infolytik\MetaMiner\server\data\mmdb\. Limit read access on server.key to administrators and the database service account.

  3. Edit server\data\mmdb\postgresql.conf and set:

    server\data\mmdb\postgresql.conf (excerpt)
    ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

    postgresql.conf with SSL settings

  4. Restart the database service: net stop InfolytikPostgres then net start InfolytikPostgres.

  5. Continue with Turn on SSL in MMA.
  1. Ask your database administrator to turn on SSL on the PostgreSQL server, following the PostgreSQL documentation.
  2. Ask for hostssl rules in pg_hba.conf for the MetaMiner Server host.
  3. Continue with Turn on SSL in MMA.

Trust the database CA on the MetaMiner Server

The MMS uses the PostgreSQL JDBC driver, which reads the trusted CA from root.crt in the postgresql folder of the Windows profile that runs the program.

  1. Save your CA certificate in PEM format as root.crt.
  2. Copy it to the profile of the account that runs the MetaMiner Server service. For the default SYSTEM account: C:\Windows\System32\config\systemprofile\AppData\Roaming\postgresql\root.crt.
  3. Copy it also to %APPDATA%\postgresql\root.crt of the Windows user who runs MMA, so that MMA can validate the connection.

TODO: confirm the root.crt locations on a live install

Source: PostgreSQL JDBC driver defaults (sslmode=verify-full when SSL is on, root certificate in %APPDATA%\postgresql\root.crt). Verify with the service running as SYSTEM and with MMA, then remove this note.

Turn on SSL in MMA

  1. In MMA, click Configure MMDB.
  2. Set Host to the name on the database certificate.
  3. Tick SSL next to the port.
  4. Click Validate and Close. MMA validates the connection and closes the dialog when it succeeds.
  5. Start the MetaMiner Server.

Configure MMDB dialog with SSL

Image of the Configure MMDB dialog in the current release with the SSL check box ticked next to Port

../images/security/mma-configure-mmdb-ssl.png

Restrict which hosts can connect

The embedded database listens on the network so that reporting tools can read the MMDB directly. Allow only the hosts that need it.

  1. Open server\data\mmdb\pg_hba.conf.
  2. Find the comment # Infolytik added for outside connections and the rules below it.
  3. Replace the address in each rule with the hosts or subnets you allow, for example 10.20.30.0/24. To require SSL from those hosts, change host to hostssl.
  4. Keep the comment line. MetaMiner adds its default rules only when that line is missing.
  5. Restart the InfolytikPostgres service, or reload it with pg_ctl reload.
server\data\mmdb\pg_hba.conf (example)
# Infolytik added for outside connections
hostssl all             all             10.20.30.0/24           md5

Also restrict port 49614 in the Windows firewall. See Network Exposure.

Verify

  1. In MMA, Configure MMDB > Validate and Close succeeds with SSL ticked.
  2. After the MetaMiner Server starts, the MetaMiner Client connects and shows data.
  3. Optional, with psql on the server: SELECT ssl, version FROM pg_stat_ssl JOIN pg_stat_activity USING (pid) WHERE application_name = 'INFOLYTIK-METAMINER'; shows t for the MetaMiner Server's connections.

Troubleshooting

Validation fails after ticking SSL
  • The database does not have SSL on. Check ssl = on and the database log in server\data\mmdb\logs.
  • The certificate name does not match Host. Use the name on the certificate.
  • root.crt is missing or holds the wrong CA for the account that validates.
The database service does not start after editing postgresql.conf

Check the file names in ssl_cert_file and ssl_key_file, that the key has no passphrase, and the log in server\data\mmdb\logs.

Next steps