Private PopSQL Environment

PopSQL can be deployed on-premises or in your own private cloud environment. It will be professionally managed for you by the PopSQL team. We work with Release to deploy a private PopSQL environment seamlessly into your cloud environment. This setup is ideal for customers who want to have complete control over their data without the hassle of managing the operations.

Release manages the AWS integration for us, handling deployments in a way that's transparent to you. As a best practice, we recommend creating a separate AWS organization for your private PopSQL
environment.

Prerequisites for Self-Hosted Environments

For us to setup a private PopSQL environment, we require that you create and share credentials for the following:

  • 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. As mentioned above, we recommend creating a separate AWS organization for PopSQL environment. We require getting the credentials for these services before we can create your personalized link for Release for the next section.

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.

We will provide to you values for <server-url>, <presentation-url>, <desktop-app-url>, and <app-url> to be used in step 11.

Creating the buckets

  1. Navigate to the AWS S3 Console.

  2. Click the Create bucket button on the right side of your screen.

  3. 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.

  4. For the results and schema buckets, turn off the "Block all public access" checkbox. For "schema-reports", leave it enabled.

  5. You can leave the rest of the settings as default.

  6. After creating the bucket, click on their names to open them.

  7. Go to the permissions tab.

  8. Scroll down to "Bucket policy" and hit the "Edit" button.

  9. 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"
                   }
                }
          }
       ]
    }
    
  10. For the results and schema buckets, scroll down to the "Cross-origin resource sharing (CORS)" section and hit "Edit" button.

  11. 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
   }
]
  1. 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:

  1. Navigate to the IAM Console > Policies.
  2. Click the Create policy button.
  3. 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"
                ]
          }
       ]
    }
    
  4. Give the policy a name and any tags you want.
  5. Create the policy.

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

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

Finally, create a new user for the role:

  1. Navigate to the IAM console > Users.
  2. Hit Add users button.
  3. Give the user a name (e.g. popsql-service-account).
  4. Select Access key - Programmatic access under the access type settings and continue onto permissions.
  5. Select Attach existing policies directly and find the policy you created in the last step, and then hit Next: tags.
  6. Give any tags you want to the user, and Next: review.
  7. Ensure the details are correct, and hit Create user.
  8. On the next screen, click the Download .csv file button.
  9. 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.

  1. Open the Firebase console.
  2. Click on Create a project button.
  3. In the resulting create project window, give your project a name (eg. "PopSQL").
  4. On the next screen, disable analytics.
  5. After the project has been created, click Realtime Database on left sidebar.
  6. Click the Create Database button.
  7. In the modal popup, choose a location.
  8. Select "Start in locked mode" and hit Enable.
  9. After creating the database, hit the cog icon in upper left corner and go to Project settings.
  10. 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.
  11. Open APIs & Services > Credentials and click the copy icon for Browser key.
  12. Send us the generated JSON file from step 10 and value copied in step 11 in a secure fashion.

With the above done, our deploy process will handle setting up the database rules.

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
  • 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.

Setting up Release Deployments

For our managed-deployment option, Release hooks into your AWS account and handles the entire deployment process. During the setup process, you will authorize an AWS CloudFormation Template to create a role which is then used by Release to configure, deploy, run, and update your PopSQL environment.

The initial process to setup the CloudFormation integration will take 5 minutes or less, and then it will take around 30 minutes to deploy our application for the first time. Once you are setup, all upgrades are managed by PopSQL through Release. You shouldn't experience any downtime as we keep your environment upgraded to the latest version.

Creating Release AWS Integration

Step 1: We will send you a personalized link to log into your Release console.

Step 2: You will be directed to the AWS console where you will be prompted to accept and install a Cloudformation template. The template creates a role that generates the necessary services and infrastructure components. The created role has an “External ID” and source-trust relationship back to the Release account. Combining the External ID and the credentials from the trust relationship establishes a secret, safe way to authenticate and authorize both parties.

Step 2a: By default, Release creates its role with the SuperUser permission set. You can optionally use a Permission Boundary on the role to limit what it can do. A minimal Policy Boundary is given below. Replace <account_id> with your AWS account id.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": "iam:UpdateRole",
            "Resource": "arn:aws:iam::<account_id>:role/release/*"
        },
        {
            "Sid": "VisualEditor1",
            "Effect": "Allow",
            "Action": [
               "autoscaling:CreateAutoScalingGroup",
               "autoscaling:DescribeAutoScalingGroups",
               "autoscaling:DescribeScalingActivities",
               "autoscaling:UpdateAutoScalingGroup",

               "cloudformation:CreateStack",
               "cloudformation:DescribeStacks",

               "dynamodb:BatchGetItem",

               "ec2:AllocateAddress",
               "ec2:AssociateAddress",
               "ec2:AssociateRouteTable",
               "ec2:AssociateSubnetCidrBlock",
               "ec2:AttachInternetGateway",
               "ec2:AttachNetworkInterface",
               "ec2:AttachVolume",
               "ec2:AttachVpnGateway",
               "ec2:AuthorizeSecurityGroupEgress",
               "ec2:AuthorizeSecurityGroupIngress",
               "ec2:CreateRoute",
               "ec2:CreateInternetGateway",
               "ec2:CreateLaunchTemplate",
               "ec2:CreateNatGateway",
               "ec2:CreateNetworkInterface",
               "ec2:CreateRouteTable",
               "ec2:CreateSecurityGroup",
               "ec2:CreateSubnet",
               "ec2:CreateTags",
               "ec2:CreateVolume",
               "ec2:CreateVpc",
               "ec2:CreateVpnGateway",
               "ec2:DescribeAccountAttributes",
               "ec2:DescribeAddresses",
               "ec2:DescribeImages",
               "ec2:DescribeLaunchTemplates",
               "ec2:DescribeLaunchTemplateVersions",
               "ec2:DescribeNatGateways",
               "ec2:DescribeRegions",
               "ec2:DescribeRouteTables",
               "ec2:DescribeSecurityGroups",
               "ec2:DescribeSubnets",
               "ec2:DescribeVpcs",
               "ec2:ModifySubnetAttribute",
               "ec2:ModifyVpcAttribute",
               "ec2:RevokeSecurityGroupEgress",
               "ec2:RevokeSecurityGroupIngress",
               "ec2:RunInstances",

               "ecr:BatchCheckLayerAvailability",
               "ecr:BatchGetImage",
               "ecr:CompleteLayerUpload",
               "ecr:DescribeRepositories",
               "ecr:GetAuthorizationToken",
               "ecr:InitiateLayerUpload",
               "ecr:PutImage",
               "ecr:UploadLayerPart",

               "eks:CreateAddon",
               "eks:CreateCluster",
               "eks:DescribeAddon",
               "eks:DescribeAddonVersions",
               "eks:DescribeCluster",
               "eks:TagResource",

               "elasticfilesystem:CreateMountTarget",
               "elasticfilesystem:CreateFileSystem",
               "elasticfilesystem:CreateTags",

               "elasticloadbalancing:AddTags",
               "elasticloadbalancing:CreateLoadBalancer",
               "elasticloadbalancing:CreateLoadBalancerListeners",

               "iam:CreatePolicy",
               "iam:CreateRole",
               "iam:TagPolicy",
               "iam:TagRole",

               "kms:CreateAlias",
               "kms:CreateGrant",
               "kms:CreateKey",
               "kms:DescribeKey",
               "kms:Decrypt",
               "kms:Encrypt",
               "kms:ListAliases",

               "s3:CreateBucket",

               "servicequotas:ListServiceQuotas",

               "sts:GetCallerIdentity"
            ],
            "Resource": "*"
        }
    ]
}

After Release has done the initial installation, the policy can then be further restricted using the ARNs of these new resources.

Step 3: You can then switch back to the Release console. You will be asked to select an AWS region for the resources to deploy in.

Then, you will be asked to select a CIDR block that does not conflict with any existing VPCs you might have. The ones shown in the dropdown are not currently used in your account.

Step 4: Last step is to click install. You will receive an email once everything is ready!

Ongoing Access

Once the role is created, the following steps occur whenever Release needs to access your account.

  1. Software running in the Release environment acquires a local role credential.
  2. The software requests a Security Token Service (STS) Assume Role credential for the remote customer role. The IAM role is confirmed by AWS on the customer side by verifying the source account and External ID supplied during creation. The IAM role is confirmed by AWS to have enough permissions to execute the command AWS creates a temporary set of credentials (between 15 minutes and several hours, depending on the configuration).
  3. AWS returns a token that is used to execute API calls in the remote account. If the credentials are about to expire, the software can renew the credentials by asking for a new set.
  4. The software uses the temporary credentials to execute commands against the customer account.
  5. All transactions, identities, and API calls are logged in Cloudtrail in the respective account.

Removing Access

You can break the remote access at any time with any of the following unilateral
actions:

  • Deleting or restricting the policy attached to the role
  • Deleting the IAM role.
  • Deleting the Cloudformation template (which deletes the role and any other artifacts related to the installation).
  • Denying and revoking any tokens that were issued to the IAM role.

Keep in mind that these actions are irreversible and would require re-installing the AWS integration steps for Release to regain access.

Connecting to your AWS Database

To allow your database to be accessed by the PopSQL deployment, we recommend you utilize VPC peering or a Transit Gateway. Otherwise, an alternative option would be to get the IP address of the server instance, and whitelist that, similar to how you would use the public PopSQL application. You can get the IP address of your instance by looking at the ELB that was created for your application.


Did this page help you?
Cookie settings