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
andschema
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
andschema
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 selectS3
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 hitNext: 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 theGenerate 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.
Updated 11 months ago