Skip to main content

DB Connection

There are multiple tools that allow to connect to our databases on the market. Currently, most of us use either pgAdmin or DBeaver (recommended for Linux users).

Basically, setup requires SSH connection to bastion host and establishing DB connection through SSH tunnel.

General SSH connection​

Our DBs are available only in the private networks of our GCP projects. That is why you need SSH access to the Bastion host and from it you will be able to connect to DBs.

The Bastion host is only accessible through Identity-Aware Proxy.

Here is how you can SSH into the Bastion host:

QA environment:

gcloud compute ssh qa-bastion --tunnel-through-iap

Production environment

gcloud compute ssh qa-bastion --tunnel-through-iap

How to access database instance locally​

You can create a SSH tunnel to forward Cloud-SQL instances to your local machines.

  1. Check the private IP address of the database you want to access. You can find this information in the GCP console or use gcloud if you know the name of the instance:
gcloud sql instances describe [INSTANCE_NAME] --format="get(ipAddresses.ipAddress)"

  1. Create the SSH tunnel and forward the connection port of the database locally.
gcloud compute ssh qa-bastion \
--tunnel-through-iap \
-- -NL 5432:[DATABASE IP]:5432

If you need to access all the databases in order to run the API server locally, you can leverage tmux and start 3 tunnel using the following script:

#!/bin/sh
tmux new-session -d 'echo start identitidb && gcloud compute ssh qa-bastion --project archipelo-qa --zone us-central1-c --tunnel-through-iap -- -NL 5433:10.176.0.7:5432'
tmux split-window -v 'echo start ardb      && gcloud compute ssh qa-bastion --project archipelo-qa --zone us-central1-c --tunnel-through-iap -- -NL 5432:10.100.1.7:5432'
tmux split-window -h 'echo start analytics && gcloud compute ssh qa-bastion --project archipelo-qa --zone us-central1-c --tunnel-through-iap -- -NL 5435:10.100.1.13:5432'

DB connection information​

For QA and Production DB connection information is stored in KeyBase.

  • QA: keybase://team/archipelo/QA/configuration.txt
  • Prod: keybase://team/archipelo/Production%20PostgreSQL/configuration.txt

Note This is read-only access - if you need write access, please contact @zaibon.

For Dev environment:

  1. You can find IP address of your DB here
  2. Logins: identitydb for Identity DB and ardb for Data DB
  3. DB passwords are stored here - look for ardb-WORKSPACE-password and db-WORKSPACE-identity-identitydb secrets