Managing PostgreSQL Databases with Luminate

Products

Secure Access Cloud

Issue/Introduction

Resolution

Luminate Security offers a Software-as-a-Service application access platform that allows users to connect to any kind of application hosted on any datacenter platform without the need to have a direct network connection. For basic explanation about Luminate and how it works, please refer to this article.

Luminate allows Database Administrators to connect to PostgreSQL databases hosted in company data centers using various methods. This article will explain the required configuration steps and the resulting end-user experience.

PostgreSQL Databases can be managed using different types of tools:

Command-line tools, such as psql, or jpgAgent
Web UI tools, such as omniDB
Applications for Microsoft Windows, Mac OS X or Linux, such as pgAdmin or sqlMaestro

This article explains how use any of the above (or similar) tools.

The below diagram depicts the components of the system:

The basic assumption is that there is no network connectivity between the DBA's PC and the corporate datacenter and that the access is being done via Luminate Security.

Managing using command-line tools (psql, jpgAgent, ...)

Command-line tools for managing PostgreSQL databases require direct direct network access using PostgreSQL frontend/backend protocol (TCP Port 5432), therefore the recommended approach is to deploy these tools on a machine inside the corporate data center (for example, on an SSH Bastion Server in the diagram above, or on another dedicated SSH machine, accessible via Luminate directly or by a Bastion server).

Luminate supports native SSH access to hosts inside the corporate data center. In order to configure such access, please have a Luminate administrator refer to this article.

The end-user experience of accessing hosts inside corporate data center via SSH for end-users (in this case, Database Administrators) is explained in this article.

Schematically, the work session will look like the below:

The user will initiate an SSH Session using an SSH Client, authenticating either using an Access Token copied from the Luminate User Portal or via a Luminate RSA Key (also issued via a Luminate User Portal).

Managing using Web UI tools

Web-based database management tools for PostgreSQL (such as omniDB) require direct network access using PostgreSQL frontend/backend protocol (TCP Port 5432), therefore the recommended approach is to deploy these tools on a machine inside the corporate data center (or inside a relevant network segment with the ability to initiate direct TCP connections to the database servers), and then access their Web UI via Luminate from any allowed location. The end-users' experience (those of Database Administrators) will be 100% transparent, as they will have to log-in using Web Browser to a web application exposed via Luminate with their corporate credentials, and then get native access to the Web UI.

Schematically, the work session will look like the below:

The user will connect to the web application directly from a browser running on any device allowed by the policy, authenticate using the corporate identity and use it transparently.

Managing using Windows / Mac OS X / Linux (Desktop) clients

Various Windows / Mac OS X / Linux desktop applications exist for managing PostgreSQL Databases. Luminate allows application-agnostic secure access to PostgreSQL Servers hosted in the corporate datacenters. The configuration and the procedure for access may be different, depending on the ability of the desktop application the DBA uses to support SSH Tunneling.

Following desktop applications support connection to PostgreSQL Database Server via SSH Tunneling:

pgAdmin 3 (pgAdmin 4 doesn't supported it yet, as of version 2.1 from January 2018, although the development team promises to add this functionality in 2.x train)
PostgreSQL Maestro

For applications that currently do not support SSH Tunneling, the DBA will need to run a local SSH Client to create a tunnel with port mapping prior to opening a connection (something that clients that do support tunneling perform automatically and transparently).

Accessing using applications that support SSH Tunneling

This type of access is the most convenient for the DBA accessing the database. The precondition that needs to be allowed by the Luminate Administrator is an SSH Server configured in Luminate Administration Portal that has TCP access to the relevant PostgreSQL Servers. In order to configure this, please refer to this article.

The access session will be completely transparent for the DBA, according to the schema below.

The below process describes how to define PostgreSQL Server connections in pgAdmin 3 / PostgreSQL Maestro in order to connect via SSH Tunnel:

Get the details of the SSH Server / Bastion that will be used for connection from the Luminate User Portal

a. Log in to the Luminate User portal using your web browser at the following address: https://<your company tenant>.luminatesec.com

b. Choose the relevant SSH Server from the list of the applications (this should be the SSH Server / Bastion that has connectivity to the PostgreSQL Servers inside the corporate datacenter)

c. Copy the "Host Name for SSH Client" and "User Name for SSH Client" fields from the UI:

2. Configure the Server Connection

Below is the connection configuration window for SQL Maestro client:

Please use the fields copied in the previous step to configure Host name and User name fields. The port number should remain 22. If you are using Luminate Access Token, you should copy it to the Password field (in most clients, when leaving this field empty, the client will prompt for the password during the connection, in this case, you should choose Keyboard interactive, as the Access Tokens are changing all the time). If you are using Luminate RSA Key, you should download the file from the Luminate User Portal and provide the path to the file in this dialog.

Below is the connection configuration for pgAdmin 3 client:

In this dialog, Tunnel host and Username should be filled according to the data copied from the Luminate User Portal, the Tunnel port should be set to 22. If an Access Token is being used, both Identity file and Password/Passphrase fields should be left empty (and the client will prompt for password / Access Token) during the connection. When using a Luminate RSA Key download from the Luminate User Portal, it should be provided in the Identity file field.

For each PostgreSQL Server / Database, these settings should be set in the SSH Tunnel section of the configuration, while the real database / server details should be set in the database connection section.

Accessing using applications that don't SSH Tunneling

In order to connect desktop applications that don't support built-in SSH Tunneling, the user should take the following steps:

1. Create an SSH Tunnel from a local port to the PostgreSQL Database Server (using any SSH Client, such as, but not limited to OpenSSH, MobaXterm, SecureCRT, Putty)

2. Connect the desktop application to the local port

Creating the tunnel

The steps in the Luminate User Portal should be exactly the same. The configuration of the SSH Tunnel should be done like the following:

ssh <User Name for SSH Client>@<Host Name for SSH Client>.<Luminate Tenant Name>.luminatesec.com -L <LOCAL PORT>:<DATABASE SERVER ADDRESS>:<DATABASE SERVER PORT>

The <DATABASE SERVER ADDRESS> should be either an IP address or a DNS for the PostgreSQL Server, accessible from the SSH Server/Bastion used for the connection.

The <DATABASE SERVER PORT> should be the standard TCP port, 5432 in case of a standard PostgreSQL configuration.

For more details on using native desktop applications with Luminate, please refer to this article.