Connecting a Database#

You can connect as many databases to Beekeeper as you like.

Because we support more than just 'databases', I'll refer to them ad 'datastores' from here on out.

Supported Datastores#

Beekeeper supports a range of SQL and NoSQL datastores.

  • MySQL
  • Postgres
  • Redshift
  • Microsoft SQL Server
  • Apache Hive
  • Spark SQL

General Requirements for Beekeeper#

  • Your database must be accessible from Beekeeper's servers. Generally this means it must have a publically accessible IP address.
  • Your database must allow network connections over TCP. In some databases (like postgres) you must enable this explicitly.
  • Your database must be online running.

Heroku Users - Stop Here!

If your database is hosted on Heroku Postgres, use our Heroku Plugin to connect instantly with full SSL security.

Best Practices#

  • Create a read-only database user with restricted access to only the analytics tables you need to report on
  • Enable SSL for your database server
  • Connect Beekeeper to your database with SSL and allow Beekeeper to verify your server certificate
  • Whitelist the Beekeeper IP in your firewall: 54.235.132.133

Walkthrough#

  1. Create a Read-only database user for Beekeeper to use
  2. (recommended) White-list Beekeeper's public IP in your firewall
  3. (recommended) Configure SSL
  4. Add your database configuration to Beekeeper

Create a Read-Only User#

In your database create a read-only user for Beekeeper to use. This allows Beekeeper to connect and read information but not edit or delete information. By giving Beekeeper its own user account it also makes it easy for you to remove Beekeeper's access should you ever need to. The method varies by database, but below are some common commands:

MySQL#

GRANT SELECT ON database_name.schema_name TO 'beekeeper'@'%';

PostgreSQL#

GRANT USAGE ON SCHEMA public TO beekeeper;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO beekeeper;

Also check our our guide to configuring PostgreSQL for read-only analytics users

Microsoft SQL Server#

GRANT SELECT table TO beekeeper;

Amazon Redshift#

GRANT USAGE ON SCHEMA public TO beekeeper;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO beekeeper;

Whitelist Beekeeper's IP#

By whitelisting our IP address you can limit public access to your database to only our machines. The way you do this is different depending on where your datastore is hosted. We have some platform specific guides below.

You will need to configure your firewall to allow incoming connections to your database server from the Beekeeper IP Address:

# Beekeeper's IP
54.235.132.133

Encrypt with SSL#

Most databases allow a secure connection between the client and the server over SSL. This provides a secure encrypted connection between Beekeeper and your database.

Most databases provided by web hosts have SSL enabled by default (for example: Heroku, Amazon, Azure).

There are two ways to enable SSL:

  • With Certificate Verification
  • Without Certificate Verification

With Certificate Verification#

If your database is hosted by Amazon RDS or Heroku then we support your database out of the box.

If you are hosted on another platform contact support to register your certificate and we will add it to our certificate repository.

Without a Certificate#

If you don't know how to get your certificate, that's ok. Connections will still be encrypted, but negotiating the initial connection is slightly less secure. You can simply disable Certificate checking in Beekeeper by checking the Do not verify SSL Certificate checkbox in Beekeeper.

Configuring Beekeeper#

In the Beekeeper App navigate to Settings -> Datastores > Add. You will need to provide the hostname/IP, port, and credentials for connecting to your database

Here is a screenshot from the application. When you submit your credentials Beekeeper will test the connection to make sure it was successful. It will then talk to your database and retrieve a list of all tables and columns within the database. You can then interact with the schema in our SQL query workbench.

New Datastore Screenshot

Beekeeper relies on the JDBC protocol to connect to your database. Depending on your database vendor, SSL-specific JDBC parameters will be turned on and off automatically by Beekeeper. If you wish you may override the JDBC URL using the custom JDBC URL option at the bottom of the page.

Platform Guides#

Amazon Web Services (AWS) - RDS or EC2#

The best way to whitelist an IP address in AWS is to create a new security group that you can assign to either EC2 instances or RDS instances.

RDS Configuration

Note that RDS instances must be configured as 'publically accessible' to allow any kind of IP whitelisting, regardless of security group.

For RDS create a database security group and add Beekeepers CIDR/IP:

54.235.132.133/32

Next Steps#

Write some SQL using the Beekeeper SQL Workbench.