Operating Caliach Vision across the Internet

Version Relevance: V5

Issue: Can I logon to Caliach Vision over the Internet and be secure and keep the database secure?

Background: Is it possible to connect Caliach Vision across the Internet directly to your live PostgreSQL server database? And can this be done securely?

Jul 24th, 2018

Feedback: Yes, there are options one of which is using PostgreSQL supports of SSL (Secure Socket Layer) connections and this article will explains how it can be achieved. It is not easy, so only attempt it if you have good computer skills. Please note that throughout this article the word server means the computer on which PostgreSQL's server is running and the word client means the user's computer or device that is accessing the database on the server.

Internet Access Options

  1. Terminal Server - Running Caliach Vision on your server using Remote Desktop.
  2. Connecting Caliach Vision to the database over the Internet through a VPN (Virtual Private Network).
  3. Connecting Caliach Vision to the database over the Internet directly.
  4. Connecting Caliach Vision to the database over the Internet using SSL security.

By way of example, lets imagine you are a Sales Rep traveling the world with your laptop and want to regularly go in to your HQ Caliach Vision to book orders, raise quotes, check prices and report your progress. With that in mind the following table is a summary the pros and cons of each of the options to let you reliably work on your travels:

No Summary Setup Advantages Drawbacks
1 Caliach Vision runs on the server and you operate it on the server in a virtual window that mimics a desktop. Easy with Windows 2012 Server, a secure VPN and good network parformance. Your IT department or support provider can implement this. Each potential remote user needs their own Caliach Vision installation on the server. 1. Very convenient.

2. Client computer does not need a Caliach Vision installation, as that is on the server.

3. Full access to database and Server Folder resources.

4. With an appropriate VPN you can be very secure.
1. Some places (and ISPs) prevent access to VPNs, so you can get blocked (e.g China).

2. Because you are on the server, export/import is to the server file system not your local file system.

3. Local printing can be problematic.

4. Operation of Caliach Vision windows is less responsive and can be irritating on poor networks. You can also get drop-outs and re-connections.

5. You are typically locked into using Microsoft Windows servers only.
2 With Caliach Vision on your laptop, with which you work normally in the office over the LAN (local area network), when you are away you connect to your LAN through a VPN and become part of that LAN. You can operate Caliach Vision normally with all traffic passing through the VPN. You need to setup a VPN. Database access for remote clients using Caliach Vision is unchanged. Unless the VPN can handle it, you need a local Server Folder to fall back on. 1. Pretty easy to set up.

2. Secure, if VPN is secure.

3. You still can interact in Caliach Vision with your local file system.

4. Operation of Caliach Vision windows is normal with only slower database interaction on poor networks.

5. Genarally less irritating user experience than 1. above.
1. Some places (and ISPs) prevent access to VPNs, so you can get blocked (e.g China).

2. Typically, you don't have access to your normal Server Folder, so you need to have a full local Caliach Vision instalation and/or obtain a copy of the Server Folder when things change like, license (Extras\Caliach.inf), customisation (ProgUser.db), updates (ProgSyst.db), etc

3. Large volume data manipulation and reporting can be slower because data is traveling two-way across the Internet, in and out.
3 With Caliach Vision on your laptop with which you work normally in the office over the LAN (local area network), when you are away you connect to the database server diectly over the Internet. This is really the same as 2. above but without the VPN. No change to the client, other than Logon Settings. The PostgreSQL server needed to be stup to accept connections from external IP addresses, as opposed to just within your LAN. 1. Very easy to setup.

2. Generally, it works everywhere regarless of country or ISP restrictions.

3. You still can interact in Caliach Vision with your local file system.

4. Operation of Caliach Vision windows is normal with only slower database interaction on poor networks.

5. Genarally less irritating user experience than 1. above.
1. This is the best performing approach, but is very insecure and leaves your PostgreSQL server vulnerable to malicious access.

2. You don't have access to your normal Server Folder, so you need to have a full local Caliach Vision instalation and/or obtain a copy of the Server Folder when things change like, license (Extras\Caliach.inf), customisation (ProgUser.db), updates (ProgSyst.db), etc

3. Large volume data manipulation and reporting can be slower because data is traveling two-way across the Internet, in and out. But less so than with VPN.
4 The same as 3. above but adds SSL (Secure Socket Layer) encription to connection and interaction with your server. Not easy for an amature to setup. The system uses public-key private-key server-client end-to-end encription, much like WhatsApp, which so irritates intellegence organisations. See below for setup details. 1. Very secure for client and server with point-to-point verification of both with certificates and highest standard encription.

2. Generally, it works everywhere regarless of country or ISP restrictions.

3. You still can interact in Caliach Vision with your local file system.

4. Operation of Caliach Vision windows is normal with only slower database interaction on poor networks.

5. Genarally less irritating user experience than 1. above.
1. Hard to setup - see below.

2. You don't have access to your normal Server Folder, so you need to have a full local Caliach Vision instalation and/or obtain a copy of the Server Folder when things change like, license (Extras\Caliach.inf), customisation (ProgUser.db), updates (ProgSyst.db), etc

3. Large volume data manipulation and reporting can be slower because data is traveling two-way across the Internet, in and out. But without the blocking vulnerability of VPN.

Alternative simplistic view of the 4 approaches is as follows:

Issue 1
Remote Desktop
2
Connect over VPN
3
Connect Direct
4
Connect with SSL
Additional hardware/software needed Yes Yes No No
Setup by IT dept. or service Standard Standard Simple Difficult
Requires local client Server Folder No Yes Yes Yes
Security for Server Good Good Bad Excellent
Security for Data Good Good Bad Excellent
Can have local client printing problems Yes No No No
Can have local client file system issues Yes No No No
User operating exparience Poor Fair Good Fair

All these approaches require your server to be permenently connected to the Internet with a fixed IP address and high performance line, preferably fibre. It will preferably have the same upload and download speed and low latency (especially with Remote Desktop which tends to send lots of small packets). You should get advice from your ISP.

 

Remote Desktop into your Server

This approach is very standard in the corporate world. You may already use it, but if not talk to your IT provider. The only thing you (and they) need to know is that for Caliach Vision each user going into Remote Desktop must have their own installation of Caliach Vision (and Omnis). This is because of printing issues and personalisation of the Caliach Vision work space.

Here are some useful tips:

  • The RDP server does not need to be the same as the PostgreSQL database server, in fact there are merrits to having different machines on a LAN so that workload can be shared and it adds protection to your database.
  • You are ill-advised to use Email with Remote Desktop without excellent anti-malware protection.
  • If you have many remote users be careful not to overload the server. Each user is doing everything on the server and if PostgreSQL is co-located, things could slow.
  • If users complain of Caliach Vision being sticky you need to improve your Internet connection performance, particularly latency (use Ping to check), because RDP sends allot of small packets back and forth between client and server. For instance, typing into a field echoes each keystroke the client makes with a tiny redraw by the server. Lists can be slow to redraw if maximized to full screen, menus can pause before dropping, hierarchical menus can pause before appearing, context menus can be sticky, etc. This is all because the visual image the client sees has to be transmitted across the Internet, maybe with the added burden of VPN encapsulation and encryption.

Data connection over VPN

In this case you simply run Caliach Vision on your remote computer connecting to your office LAN (local area network) as though you were sitting in your office. The traffic going between your machine and the PostgreSQL server, though, goes over a VPN (Virtual Private Network) which is typically safe point-to-point and encrypted.

A VPN is typically setup on a firewall, so implementing it requires hardware and software investment. Contact your IT support service or a competent IT professional.

Typically, there is no changes needed on the client computer Caliach Vision installation. But when remote you won't have access to the Server Folder you normally do. This means you need to have a full Caliach Vision installation, or better keep a synchronized copy of the folder on your machine for Caliach Vision to fall back on.

Data connection direct without VPN

This is effectively running the connection from Caliach Vision directly with your server without any point-to-point security. But it is simple to setup:

  1. You need your natwork to be open to public TCP traffic, at least to your server PostgreSQL port.
  2. You need to set the PostgreSQL server internal connection firewall to be configured to accept connections from the public Internet, or at least the IP address of remote users. To do this you must edit the entries in you PostgreSQL ...data\pg_hba.conf file. Add in an entry (line) to the end in the white-space delimmited form:
    host       DATABASE  USER  ADDRESS  md5

    It must start with host and where DATABASE is replace that with the name of the database access is permitted to. This can be the word all or a comma-seperated list of database names, which should include postgres e.g. postgres,clariant,clariantplay. USER is replaced similarly by all or a list of PostgreSQL users and/or roles, as in your Database Logon Settings, e.g. clariantuser,postgres. ADDRESS is replaced by the permitted client IP address with bit-mask. So 172.146.5.1/24 means any attempted connection from clients with an IP in the range 172.146.5.1 to 172.146.5.255, /32 would nail it down to just a single IP address. The line ends with md5, being the password encryption method.
  3. You can add as many lines as you like with alternative ADDRESS entries for different IP ranges.
  4. To make this change effective, restart the PostgresSQL service. if it doesn't restart without error, go back and undo your changes.
  5. If it doesn't work, you can comment out the entry by adding a # to the begining of the line you added, until you work out your mistake.

Data connection direct with SSL without VPN

This is much the same in operation to the previous approach but takes advantage of the SSL connection capabilities in the PostgreSQL server and client libraries. It uses public-key private-key encryption and client-server trusted certification to verify that the client and server are trusted. For more insite see the PostgreSQL documentation. There is also an Omnis Technical Note that covers the implementation from which you can learn more.

Negotiating the connection verifies to the client that the server can be trusted and to the server that the client can be trusted. Once connection is established all communications are then encrypted using a bullet-proof cypher. The communications over TCP network protocol are thus safe and secure over a public network (the Internet).

This reqires a number of personalised files to be stored on the server and client that provide certificates and keys for the system to work. You also need to change two server configuration files and adjust your Caliach Vision Logon Settting to activate such connections:

  1. You need to generate the server.key, server.crt and root.crt files for the PostgreSQL server to use. This is possible, using OpenSSL, or you can obtain them from Caliach's consulting service.
  2. You need to generate the postgresql.key, postgresql.crt and root.crt files for the clients to use. This is possible, using OpenSSL, or you can obtain them from Caliach's consulting service.
  3. These self-signed files are cryptograhically locked together and so are unique to your installation. They rely on you having a ping-able server domain or IP address (e.g. caliach.com) Once created and installed they typically last for a year and confirm the safe and secure end-to-end identities of that server and clients connecting to it. The following information is needed to create them:
    country=            e.g. GB
    state=              e.g. Devon
    locality=           e.g. Barnstaple
    organization=       e.g. Caliach Ltd
    organizationalunit= e.g. Vision
    email=              e.g. chrisross@caliach.com
    commonname=         e.g. www.caliach.com or IP address of server
  4. Tip: We found that to make these work we had to add C:\OpenSSL\ to the PATH system Environment Variable and to create a OPENSSL_CONF user Environment Variable and make that C:\OpenSSL\openssl.cnf
  5. A batch file script was written to carry out the steps needed to create these files.
  6. The PostgreSQL server files server.key, server.crt and root.crt should be copied to the PostgreSQL installation data folder (this location can vary with the installation. You can find out where it is using the following statement within PostgreSQL:
    SELECT setting FROM pg_settings WHERE name = 'data_directory'
  7. The client files postgresql.key, postgresql.crt and root.crt need to be copied to C:\Users\YourName\AppData\Roaming\postgresql folder with appropriate substitution of YourName.
  8. With the files taken care of, we now need to change the PostgreSQL server configuration files to turn on SSL. Edit the C:\Program Files\PostgreSQL\10\data\postgresql.conf file and find the entry ssl which should be set on. It is also useful to turn on some extra logging. Summary of changes:
    ssl = on
    log_connections = on
    log_disconnections = on
    log_hostname = on
  9. You also need to put in an entry in the C:\Program Files\PostgreSQL\10\data\pg_hba.conf file (see PostegreSQL docs. for more info):
    hostssl    all             all             110.78.184.189/32       cert clientcert=1
    hostssl    all             all             all       cert clientcert=1
    where 110.78.184.189/32 singles out one specific address. You can replace that with the word all to cover any IP4 address (as in the second entry in the example above). This is safe because a connection will only be allowed if the SSL negotiation is a success.
  10. Restart the PostgreSQL service on the server to apply the changes. Now the server is ready.
  11. The client setup requires a bit of skullduggery! This is because Omnis do not distribute SSL-capable PostgreSQL client libraries for license reasons, so Caliach cannot also. You need to borrow these from your pgAdmin 4 installation. Find the pgAdmin 4 program files which are typically in C:\Program Files\PostgreSQL\10\pgAdmin 4 then go into the bin folder and select and copy the following 5 files:
    libpq.dll
    ssleay32.dll
    libeay32.dll
    libintl-8.dll
    libiconv-2.dll
    Then navigate to the Omnis root directory, typically, C:\CaliachVision-5.10\OmnisRuntime-x64 and paste these files in, overwriting the existing libpq.dll file. Now Omnis will support SSL connections.
  12. Finally, we need to tell Caliach Vision to use an SSL connection when it connects to PostgreSQL. To do this change the File -- Preferences - Database Logon Settings (or click on the Change Settings button on the Logon window). Under the Advanced tab pane enter and enter a line sslmode with Value require. This can take other values; verify-ca, for example. (See PostgreSQL docs.) sslmode is the only nessecary setting to make SSL work for a single server database connection - see later for situations where you may have many servers you want to connect to.
    Caliach Vision Logon Settings for SSL
  13. You are now ready to logon using SSL. Assuming everything works you can see that the connection was successfully SSL by spotting a -SSL at the end of the Omnis DAM line on the About Caliach Vision window:
    Caliach Vision About window with SSL
  14. You can also see it reported in the Omnis Trace Log with entries
    Logon 1 with SSL success with version=version, type=SSL Connect.
    Logon 2 with SSL success with version=version, type=SSL Connect.
  15. You can also check the server end by looking at the PostgreSQL logs, typically in C:\Program Files\PostgreSQL\10\data\log where a log entry of this nature will be shown of a connection received, authorized (with SSL details) and finally a disconnection when the user logoff takes place:
    2018-07-24 02:41:45.048 UTC [2420] LOG:  connection received: host=110.78.184.189 port=64703
    2018-07-24 02:41:46.369 UTC [2420] LOG:  connection authorized: user=postgres database=postgres SSL enabled (protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384, compression=off)
    2018-07-24 02:41:46.612 UTC [2420] LOG:  disconnection: session time: 0:00:01.567 user=postgres database=postgres host=110.78.184.189 port=64703
    
When you have More than One Server

Say you you are running Caliach Vision is three locations around the world. One in the UK, one in Hong Kong and one in the US. You can't duplicate the server.key, server.crt and root.crt files on each of the three servers because at least the Common Name is different for the server certificates. You need need to make different sets of certificate and key files for both server and client for each server you have. Therefore any client Caliach Vision user needs to use one of 3 certificate/key sets when connecting to a particular server. So we can't simply put the files in the default location (C:\Users\YourName\AppData\Roaming\postgresql) as in step 7. above. You have to set up a folder structure somewhere convenient which holds all three sets, then tell the connection library libpq.dll which ones to use for each alternative connection. This is achieved by adding 3 more options to your Database Connection Settings: The sslcert, sslkey and sslrootcert options are used for this, shown below. Note that the paths should be relatively short and use the Unix-like seperator / rather than the normal Windows \. This is because it seems that somewhere along the line if the resulting options string is too long, the connection will fail. So keep the folder names short, and of course they should contain no spaces or special characters. (This was accepted by Omnis as Fault ST/*P/092 on O$8.1.3 where the connection string is limited to 255 characters.)
Caliach Vision Logon Settings for SSL multi-server

Chris Ross - Senior Consultant