Installation guide

This guide is for the platform team installing Cloud Certainty SQLark in their AWS account. The install is one CloudFormation stack (the hub). Every other account whose Athena workgroups and Glue Data Catalog SQLark should reach gets a small role stack (the spoke). With IAM Identity Center or another SAML IdP, the hub takes two steps: deploy the stack, configure the SAML application, then update the stack. For the short version, see Getting started.

#Before you start

• Region. Any Region with Amazon Athena, AWS Glue, AWS Lambda, Amazon API Gateway (HTTP APIs), Amazon DynamoDB, Amplify Hosting, Amazon Cognito, AWS License Manager and Amazon ECS on Fargate. The templates are always loaded from the vendor's public bucket in us-east-1 (CloudFormation accepts a template URL from any Region), and all product code comes from the container image. The hub's Region doesn't limit which Regions you can query: list them in LocalRegions and in each connection.
• Account. Deploy the hub in the account that subscribed on AWS Marketplace: the licence is delivered to that account through AWS License Manager. To run the hub in another account of your organisation, distribute the licence to it with a License Manager grant first.
• Permissions. You need permission to create the resources in the stack: IAM roles (some have fixed names), Lambda, ECS, EC2 security groups, Cognito, API Gateway, Amplify, DynamoDB, S3, Athena, SNS, EventBridge, KMS and CloudWatch Logs.
• Network. A VPC with at least one subnet with outbound HTTPS access, for the one-off installer task (see Networking). Nothing else runs in your VPC.
• Identity. Decide how users will sign in: IAM Identity Center, another SAML 2.0 IdP (Entra ID, Okta and others), an OIDC IdP, or Cognito-managed users. SAML IdPs other than IAM Identity Center, and OIDC, need the Team plan or above.
• Your data. Note the S3 buckets your Athena tables read, the result buckets of the workgroups you already use, and any customer managed KMS keys on that data. SQLark may only use what you list (see Data access).

#1. Subscribe

1. Open SQLark in AWS Marketplace and choose Continue to Subscribe. Choose the plan and the contract length (Team is the base plan; Business and Enterprise are add-ons to it) and accept the offer. AWS License Manager now holds your licence.
2. Choose Continue to Configuration, select the fulfilment option and the Region, then open the CloudFormation launch link from the usage instructions.

#2. Launch the hub stack

In the CloudFormation console, fill in the parameters (see the reference). Acknowledge "AWS CloudFormation might create IAM resources with custom names" and create the stack. Creation takes about 10 minutes.

The quick-create link, with the release version filled in:

https://console.aws.amazon.com/cloudformation/home#/stacks/quickcreate?templateURL=https://cloudcertainty-sqlark-us-east-1.s3.us-east-1.amazonaws.com/pro/<version>/main.yaml&stackName=sqlark

From the AWS CLI (--region is the Region you deploy to):

aws cloudformation create-stack --region <your-region> --stack-name sqlark \
  --template-url https://cloudcertainty-sqlark-us-east-1.s3.us-east-1.amazonaws.com/pro/<version>/main.yaml \
  --parameters file://params.json \
  --capabilities CAPABILITY_IAM CAPABILITY_NAMED_IAM

params.json lists the parameters as [{"ParameterKey": "VpcId", "ParameterValue": "vpc-..."}, ...].

#What happens during creation

The product's code (the Lambda functions and the web app) ships inside the subscription-protected container image, not as public downloads. Early in stack creation:

1. CloudFormation creates a private S3 bucket in your account (output ArtifactsBucketName; all public access blocked, encrypted, TLS only).
2. The ArtifactInstall custom resource starts one Fargate task from the product image in your InstallerSubnetIds. The task checks your AWS Marketplace licence in AWS License Manager, verifies every file of the code bundle against the SHA-256 checksums in the image, and copies it into the bucket under the release's version. It runs for about a minute at 0.25 vCPU and 512 MB, with a read-only root filesystem and a security group without inbound rules.
3. The Lambda functions and the web app are then created from that bucket.
4. With CreateWorkgroup = Enabled (the default), the stack also creates the SQLark workgroup (named after NamePrefix, so sqlark by default) and its results bucket <NamePrefix>-results-<account>-<region> (see The SQLark workgroup).

If the installer task fails, stack creation fails and rolls back. The error message gives the task's exit code and its log stream (log group /<NamePrefix>/installer); see Troubleshooting.

#3. Connect your identity provider

Until SamlMetadataUrl is set, the portal falls back to Cognito sign-in, which has no users. Nobody can sign in at that point, so the stack is safe in that state.

#4. First sign-in

Open the PortalUrl output and sign in as BootstrapAdminEmail. The first account that signs in with that (verified) email becomes the bootstrap administrator and stays bound to it (see the admin guide).

The local connection to the hub's own account exists from the start. It uses the stack's local role, which may do what LocalAccessLevel allows, in the Regions of LocalRegions. Administrators see every workgroup and database there. Other users see nothing until an administrator grants them access (admin guide).

#Data access

The local role and every spoke role are the upper limit of what SQLark can do in an account. Athena reads the Glue Data Catalog, your table data and KMS keys with the credentials of the person running the query, so the role must allow them, and SQLark then narrows each session to that person's grants.

The roles have no IAM, CloudFormation or DynamoDB permissions, and the local role is explicitly denied SQLark's own artifacts bucket, so SQLark can't change its own stack.

#The SQLark workgroup

With CreateWorkgroup = Enabled, the hub stack (and each spoke stack) creates an Athena workgroup made for SQLark:

The workgroup is created without grants: in Administration › Grants, give your teams access to it. The results bucket is kept when the stack is deleted, because it may still hold query results; delete it yourself when you no longer need it (and before you create a stack with the same NamePrefix in that Region again).

You can also use your existing workgroups. Their result location must be in ResultBucketNames (or results must be managed by Athena), and enforcing the workgroup configuration is recommended.

#5. Deploy the spoke role in other accounts

spoke-role.yaml creates one role, /sqlark/<RoleName>, that only the hub's API role (stack output SpokeTrustedPrincipal) can assume, only with your external ID, and only with a user's email as the source identity, and optionally a SQLark workgroup with its results bucket. Its template URL is the stack output SpokeTemplateUrl:

https://cloudcertainty-sqlark-us-east-1.s3.us-east-1.amazonaws.com/pro/<version>/spoke-role.yaml

The role is the upper limit of what SQLark can do in that account; grants narrow it per user or group but never widen it. IAM is global, so create the role once per account. For a SQLark workgroup in another Region of the same account, deploy the template again in that Region with CreateRole = Disabled and the same WorkgroupName. The stack's outputs are RoleArn (enter it in the connection), AccessLevel (choose the same level for the connection), WorkgroupName and ResultsBucketName.

#A single account

cat > spoke-params.json <<'EOF'
[
  {"ParameterKey": "TrustedPrincipal", "ParameterValue": "<SpokeTrustedPrincipal output>"},
  {"ParameterKey": "ExternalId", "ParameterValue": "<a secret of at least 16 characters>"},
  {"ParameterKey": "AccessLevel", "ParameterValue": "Query"},
  {"ParameterKey": "DataBucketNames", "ParameterValue": "*"}
]
EOF
aws cloudformation create-stack --region eu-west-1 --stack-name sqlark-spoke \
  --template-url https://cloudcertainty-sqlark-us-east-1.s3.us-east-1.amazonaws.com/pro/<version>/spoke-role.yaml \
  --parameters file://spoke-params.json --capabilities CAPABILITY_NAMED_IAM

#Every account of an AWS Organization (StackSets)

With service-managed StackSets, new accounts in the target OUs get the role automatically. Use one external ID for the whole StackSet, and enter that same external ID for every connection you create for those accounts.

# From the management account or a delegated StackSets administrator.
aws cloudformation create-stack-set --region us-east-1 --stack-set-name sqlark-spoke \
  --template-url https://cloudcertainty-sqlark-us-east-1.s3.us-east-1.amazonaws.com/pro/<version>/spoke-role.yaml \
  --parameters file://spoke-params.json \
  --capabilities CAPABILITY_NAMED_IAM \
  --permission-model SERVICE_MANAGED \
  --auto-deployment Enabled=true,RetainStacksOnAccountRemoval=false

# One Region for the role (IAM is global): the Region where your data and workgroup are.
aws cloudformation create-stack-instances --region us-east-1 --stack-set-name sqlark-spoke \
  --deployment-targets OrganizationalUnitIds=ou-abcd-11111111 --regions eu-west-1

For SQLark workgroups in more Regions, create a second StackSet with CreateRole = Disabled and deploy it to those Regions. Service-managed StackSets don't deploy to the management account; use a normal stack there if SQLark should reach it. To change AccessLevel later, update the StackSet, then the access level of the connections.

#Add the connection

In Administration › Connections › Add connection, enter an id (used in links, it can't be changed later), a name, the account ID, the access level, the spoke stack's RoleArn, the external ID and the Regions. Choose Test: SQLark assumes the role and lists the workgroups and databases in each Region. See the admin guide.

#Parameter reference

#Networking

Only the installer task runs in your VPC, for about a minute at install and at each upgrade. It needs outbound HTTPS (443) to:

• Amazon ECR in us-east-1, to pull the image from the AWS Marketplace registry (cross-Region, so this needs internet egress: a NAT gateway or public subnets);
• AWS License Manager in your Region, for the licence check;
• Amazon S3 in your Region, to write the code into the artifacts bucket (a gateway endpoint works);
• CloudWatch Logs in your Region, for its log stream.

Its security group has no inbound rules and one outbound rule, TCP 443. The Lambda functions don't run in your VPC; they reach Athena, Glue and STS in your accounts over AWS's public endpoints. Athena itself reads your S3 data inside AWS.

#Encryption and KMS

Without KmsKeyArn, the DynamoDB tables, the log groups, the update SNS topic, the artifacts bucket and the SQLark results bucket use AWS owned keys and SSE-S3, and the SQLark workgroup encrypts results with SSE-S3.

With KmsKeyArn, your key encrypts all of them, and the SQLark workgroup encrypts its results with SSE-KMS under it. The key must be a symmetric customer managed key in the stack's Region (give its ARN, not an alias), and its key policy must:

• let the account's IAM policies grant its use (the default key policy statement for the account root does this). The stack's API role uses it only through DynamoDB; the local role only through S3, to write and read query results;
• allow logs.<region>.amazonaws.com to use it for the stack's log groups;
• allow sns.amazonaws.com to use it for the update topic.

The identity that creates or updates the stack also needs kms:Decrypt on the key, because Lambda reads the function code from the encrypted artifacts bucket with the deploying identity's permissions.

The key protects SQLark's own data and the SQLark workgroup's results. Your tables keep their own encryption; list their keys in DataKmsKeyArns.

#Stack outputs

#Updating

Each release is a new template; there is no version parameter. To upgrade, update the stack with the new release's template (Update stack › Replace current template) and keep every other parameter value. See the upgrading guide.

To change a setting such as LocalAccessLevel, LocalRegions, DataBucketNames or SamlMetadataUrl, update the stack with the current template and change only that parameter.

#Deleting

1. Delete the hub stack. The DynamoDB table (<NamePrefix>-table) is retained on purpose, with its settings, grants, saved queries, dashboards, history and audit log, and so is the SQLark results bucket. Everything else is deleted: the cache table (with the usage counters), the Cognito user pool (Cognito-only users are lost), the artifacts bucket, the SQLark workgroup and the log groups. A new stack with the same NamePrefix can't be created while the retained table or results bucket exists.

   To delete the table when you no longer need it, turn off its deletion protection, then delete it. Empty and delete the results bucket the same way.

2. Delete the spoke stacks or the StackSets in the other accounts (their results buckets are kept too).

3. Delete the SAML or OIDC application in your identity provider.

#Troubleshooting