Connecting data sources

Connecting data sources can be done either through the dashboard, or through SQL commands.

• Connecting data sources using the dashboard
• Connecting data sources using SQL commands
• Securing connections with SSH tunnels
  • Creating and using SSH tunnels using the dashboard
  • Creating and using SSH tunnels using SQL commands

Connecting data sources using the dashboard

The deployment overview page displays a table showing all data sources that are currently connected to that deployment. Adding a new data source can be done by clicking the Add data source button.

A new dialog will pop up showing all supported data sources.

Clicking one of the data source types will take you to the next screen where you will need to provide details on how to connect.

In our example, we picked Postgres. We are then asked to provide a Name, Host, Port, Database, User, and Password. (See Postgres data source for more information on these fields.)

Submitting this form will validate the connection. If there’s an issue with connecting, an error message will be displayed.

If everything succeeds, a confirmation message will be displayed, and the data source is now able to be queried from within your deployment.

Connecting data sources using SQL commands

Alternatively, data sources can be added using CREATE EXTERNAL TABLE or CREATE EXTERNAL DATABASE.

To add a Postgres data source using the CREATE EXTERNAL DATABASE command.

glare=> CREATE EXTERNAL DATABASE external_db
    FROM postgres
    OPTIONS (
        host = 'my.postgres.host',
        port = '5432',
        user = 'glaredb',
        password = 'password',
        database = 'glaredb_test',
    );

When we submit this command, validation is done to ensure we can properly connect. Any errors connecting will be returned, for example:

ERROR:  External database validation failed: Failed to connect to Postgres
instance: error connecting to server: Connection refused (os error 111)

If everything validates and no errors are returned, the data source is then available to query from within the deployment.

Securing connections with SSH tunnels

Currently only MySQL and Postgres data sources support SSH tunnels

For scenarios whereby a data source does not have a public access point, GlareDB can use a tunnel to make the connection. SSH tunnels are specified per deployment, and can be reused by multiple data sources for that deployment.

Creating and using SSH tunnels using the dashboard

1. Go to the deployment for which the SSH tunnel will be created

2. Go to the Settings tab and select SSH tunnels

   

3. Click New tunnel and add the host, port and user for the SSH server that will be used to connect to the data source

   

4. After creating the tunnel a public key will be presented. Add this key to your SSH server (for example ~/.ssh/authorized_keys)

   

Creating and using SSH tunnels using SQL commands

1. Create the tunnel

   CREATE TUNNEL example_tunnel FROM ssh OPTIONS (
      host = '1.2.3.4',
      port = '22',
      user = 'example_ssh_user'
   );
   

2. Get the public key and add it to your SSH server

   SELECT public_key FROM glare_catalog.ssh_keys
      WHERE ssh_tunnel_name = 'example_tunnel';
   

3. Create the data source with the tunnel

   CREATE EXTERNAL DATABASE my_pg
      FROM postgres
      TUNNEL example_tunnel
      OPTIONS ( connection_string = '<connection-string>' );