Setup Prerequisites

Overview

To setup a private PopSQL environment, the following are required to be setup:

AWS S3 for storing schemas and run results
Google Firebase for real-time features
SMTP for sending emails (optional)

Please see below for details on setting up each of the above. If using a fully-managed deploy, we will ask that you provide us some details for being able to access each of these within PopSQL. These are required before we can deploy PopSQL. If using a self-managed deploy, then you would put these details in your environment yourself.

AWS S3

We utilize AWS S3 to store run results, fetched cloud schema reports, and troubleshooting reports, with each contained in its own bucket. As such, we require that you manually create three buckets for PopSQL to use, as well as provide a security credential that can access and manage those buckets.

If using fully-managed deploy, we will provide to you exact values for <server-url>, <presentation-url>, <desktop-app-url>, and <app-url> to be used in step 11.

Creating the buckets

Navigate to the AWS S3 Console.
Click the Create bucket button on the right side of your screen.
Create 3 S3 buckets with the following names, <company>-popsql-results, <company>-popsql-schema, and <company>-popsql-schema-reports), selecting the same region for all of them.
For the results and schema buckets, turn off the "Block all public access" checkbox. For "schema-reports", leave it enabled.
You can leave the rest of the settings as default.
After creating the bucket, click on their names to open them.
Go to the permissions tab.
Scroll down to "Bucket policy" and hit the "Edit" button.

In the Policy textarea, paste the following, editing "<bucket-name>" with the actual name of the bucket, and then hit "Save". All three buckets have the same policy.

{
   "Version": "2012-10-17",
   "Statement": [
      {
            "Sid": "AllowSSLRequestsOnly",
            "Effect": "Deny",
            "Principal": "*",
            "Action": "s3:*",
            "Resource": [
               "arn:aws:s3:::<bucket-name>",
               "arn:aws:s3:::<bucket-name>/*"
            ],
            "Condition": {
               "Bool": {
                  "aws:SecureTransport": "false"
               }
            }
      }
   ]
}

For the results and schema buckets, scroll down to the "Cross-origin resource sharing (CORS)" section and hit "Edit" button.
In the textarea, paste the following JSON, replacing <server-url>, <presentation-url>, <desktop-app-url>, and <webapp-url> with values that we give you.

Results:

[
   {
      "AllowedHeaders": [
         "*"
      ],
      "AllowedMethods": [
         "GET"
      ],
      "AllowedOrigins": [
         "<server-url>",
         "<presentation-url>"
      ],
      "ExposeHeaders": [],
      "MaxAgeSeconds": 3000
   },
   {
      "AllowedHeaders": [
         "*"
      ],
      "AllowedMethods": [
         "GET",
         "PUT"
      ],
      "AllowedOrigins": [
         "<web-app-url>",
         "<desktop-app-url>"
      ],
      "ExposeHeaders": [],
      "MaxAgeSeconds": 3000
   }
]

Schema:

[
   {
      "AllowedHeaders": [
         "*"
      ],
      "AllowedMethods": [
         "GET"
      ],
      "AllowedOrigins": [
         "<web-app-url>",
         "<desktop-app-url>"
      ],
      "ExposeHeaders": [],
      "MaxAgeSeconds": 3000
   }
]

Send us the name of the three buckets, as well as the region they were created in.

Generating the security credential

The credentials that PopSQL requires should be made as narrow as possible, and as such, we only need access to the three S3 buckets above. To accomplish this, we will walk you through creating a custom policy, creating a new IAM user, attaching the policy to that user, and then generating security credentials for that user.

To create the policy:

Navigate to the IAM Console > Policies.
Click the Create policy button.

Click the JSON tab, and enter the following into the input (replace the resource names with whatever names you used for the buckets):

{
   "Version": "2012-10-17",
   "Statement": [
      {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": [
               "s3:DeleteObjectTagging",
               "s3:PutObject",
               "s3:GetObject",
               "s3:ListBucketMultipartUploads",
               "s3:DeleteObjectVersion",
               "s3:PutObjectVersionTagging",
               "s3:ListBucket",
               "s3:PutObjectTagging",
               "s3:DeleteObjectVersionTagging",
               "s3:DeleteObject",
               "s3:ListMultipartUploadParts"
            ],
            "Resource": [
               "arn:aws:s3:::<company>-popsql-results",
               "arn:aws:s3:::<company>-popsql-schema",
               "arn:aws:s3:::<company>-popsql-schema-reports"
            ]
      }
   ]
}

Give the policy a name and any tags you want.
Create the policy.

Next, we need to create a role for that policy:

Navigate to the IAM Console > Roles.
Click the Create role button.
Make sure the type of entity is AWS service and select S3 as the use-case
Attach the policy you created in the last step.
Give the role a name and hit Create role button.

Finally, create a new user for the role (skip final steps for self-managed deploy):

Navigate to the IAM console > Users.
Hit Add users button.
Give the user a name (e.g. popsql-service-account).
Select Access key - Programmatic access under the access type settings and continue onto permissions.
Select Attach existing policies directly and find the policy you created in the last step, and then hit Next: tags.
Give any tags you want to the user, and Next: review.
Ensure the details are correct, and hit Create user.
On the next screen, click the Download .csv file button.
Securely send us the CSV file.

Firebase

We utilizes Firebase for its database and cloud functions to enable realtime features within the application. To enable this integration, please follow the steps below. Note, you will need to be using an account that has Firebase console access for your organization.

The steps to setup Firebase are (skip final steps for self-managed deploy):

Open the Firebase console.
Click on Create a project button.
In the resulting create project window, give your project a name (eg. "PopSQL").
On the next screen, disable analytics.
After the project has been created, click Realtime Database on left sidebar.
Click the Create Database button.
In the modal popup, choose a location.
Select "Start in locked mode" and hit Enable.
After creating the database, hit the cog icon in upper left corner and go to Project settings.
Under project settings, go to Service accounts, and then click the Generate new private key button for the Firebase Admin SDK, and save the generated JSON file to your computer.
Open APIs & Services > Credentials and click the copy icon for Browser key.
Send us the generated JSON file from step 10 and value copied in step 11 in a secure fashion.

As a final step, a JSON file of security database rules will be applied against firebase. If using our fully-managed option, this will be handled for you, otherwise this is a manual step you will need to take.

We store these user details in Firebase:

Version of the app they on
OAuth token last updated at

And for queries, we store:

Connection ID the query uses
Created timestamp
Database it uses
Query Name
Query text
User ID of creator
Can others edit it
Can others view it
List of User IDs that are currently viewing the query, along with metadata around that (e.g. Location of their cursor in the editor. This powers the collaboration effects of simultaneous query editing.)

SMTP

The PopSQL environment utilizes email to send out results of scheduled queries and dashboards. We recommend using SendGrid, but any SMTP service should work. Once you've setup your service, please provide us the details (address, port, username, password) to send emails on your behalf. If you'd like to skip this step, we are happy to send emails through our SendGrid setup.