Cloud SQL for PostgreSQL

Oppsett av instanser med terraform

SKIP har laget to terraform-moduler (cloud_sql og cloud_sql_config) for å gjøre det enkelt å sette opp nye Cloud SQL-instanser i GCP.

Dokumentasjon for hvordan modulene brukes, finnes på wiki-siden til terraform-modules. Spesielt guiden for hvordan bruke terraform-modules-repoet er relevant.

cloud_sql modulen

For mer utfyllende dokumentasjon se cloud_sql wiki

module "cloudsql_instance_prod" {
  source        = "git@github.com:kartverket/terraform-modules.git/?ref=cloud_sql/v0.15.0"
  env           = "prod"
  instance_name = "foo-db"
  instance_tier = "db-custom-1-3840" # 1 vCPU og 3.75 GB RAM, default er db-f1-micro som er veldig liten og ikke anbefalt for produksjon
  project_id    = "<team-prod-id>"
}

cloud_sql_config modulen og konfigurering av brukere

For mer utfyllende dokumentasjon se cloud_sql_config wiki

Denne modulen er laget for konfigurasjon av Postgres-instanser. Vi har laget denne for å gjøre konfigurering av databaser så enkelt som mulig for dere og for å unngå "click-ops". Det er noen ting dere bør tenke over før dere tar denne i bruk:

1. Den bør bare brukes på en ny instans. Å importere eksisterende databaser, brukere og skjemaer er noe vi fraråder.
2. Feil bruk av denne modulen kan slette brukere, secrets og hele databasen inkludert all data. Sjekk alltid PLAN før du applyer.
3. Vær sikker på at migreringene dine er kompatible med modulen med hensyn til privileges.

Eksempel config:

module "cloudsql_config" {
  source            = "git@github.com:kartverket/terraform-modules.git/?ref=cloud_sql_config/v0.7.0"
  gcp_instance_name = module.cloudsql_test.cloud_sql_instance_name
  gcp_project_id    = module.cloudsql_test.gcp_project_id
  env               = "prod"
  databases = {
    "backstage" = {
      name            = "backstage"
      owner           = "backstage"
      extensions      = ["pgcrypto", "postgis"]
      prevent_destroy = true
      # Denne variabelen må IKKE endres uten at dere er klare til å migrere state manuelt.
      schemas = [
        {
          name = "backstage"
          migration_user = {
            name = "backstage_migrater" # migration_user blir eier av skjemaet som opprettes
          }
          application_user = {
            name = "backstage_app" # application user får CRUD privilegier
          }
          misc_users = [
            {
              name       = "readonly"
              privileges = ["SELECT"]
            }
          ]
        },
        {
          name         = "opencost"
          unified_user = true
          migration_user = {
            name = "opencost_migrater" # ignoreres, fordi vi har unified_user = true, men den må settes likevel
          }
          application_user = {
            name       = "opencost_app"
            privileges = ["SELECT", "UPDATE"] # ignoreres, app user blir owner av skjemaet fordi unified_user er true
          }
          misc_users = [
            {
              name       = "readonly"
              privileges = ["SELECT"]
            }
          ]
        }
      ]
    }
  }
}

For hver bruker så vil modulen generere opp et klient sertifikat og en privatnøkkel, disse legges i GSM. Den private nøkkelen legges i to formater; PEM og PK8. Vi har erfart at JDBC ikke liker PEM, så i dette tilfellet så bør du bruke PK8-nøkkelen i stedet for.

Bruk av CloudSQL

Koble til fra applikasjon kjørende på SKIP

Når du skal bruke instansen fra SKIP så må du gjøre noen få modifikasjoner til applikasjonsmanifestet ditt.

Det første du må gjøre er å hente ut secrets.

Terraform modulen vil generere opp og legge inn alle secrets du trenger for å koble til databasen i Google Secret Manager i prosjektet du har valgt.

Kjenner du ikke til GSM og ExternalSecrets anbefaler vi å lese Hente hemmeligheter fra hemmelighetshvelv først.

For å hente ut disse så må du lage to ExternalSecret, en for sertifikater og en for passord/brukernavn, her er et eksempel:

apiVersion: external-secrets.io/v1
kind: ExternalSecret
metadata:
  name: my-app-db-client-password # Denne brukes for å hente ut password fra json secreten i gsm.
  namespace: my-app-namespace
spec:
  refreshInterval: 1h
  secretStoreRef:
    name: gsm
    kind: SecretStore
  target:
    name: my-app-db-user-password # Navnet på Kubernetes Secret som opprettes
    creationPolicy: Owner
  data:
    - remoteRef:
        conversionStrategy: Default
        decodingStrategy: None
        key: cloudsql-myinstance-myuser # Navnet på GSM secret
        metadataPolicy: None
        property: password
      secretKey: password

--- 

apiVersion: external-secrets.io/v1
kind: ExternalSecret
metadata:
  name: database-certs
spec:
  secretStoreRef:
    kind: SecretStore
    name: gsm
  data:
    - secretKey: server.crt
      remoteRef:
        key: cloudsql-<instansnavn>-ca-certificate
    - secretKey: client.crt
      remoteRef:
        key: cloudsql-<instansnavn>-<bruker>-client-certificate
        
    ### client.key i PEM, fungerer for de fleste
    - secretKey: client.key
      remoteRef:
        key: cloudsql-<instansnavn>-<bruker>-client-key
    
    ### VISST DU TRENGER PK8 (JDBC kan kreve dette) - Bare ta med ÈN key, ikke begge
    - secretKey: client.pk8
      remoteRef:
        decodingStrategy: Base64 # Må være med for pk8
        key: cloudsql-<instansnavn>-<bruker>-client-key-pk8

Nå har du hentet alle hemmelighetene du trenger, og kan bruke disse i skiperator manifestet ditt:

apiVersion: skiperator.kartverket.no/v1alpha1
kind: Application
metadata:
  name: minapp
spec:
  image: ghcr.io/kartverket/minapp
  port: 8080
  replicas: 2
  accessPolicy:
    outbound:
      external:
      - host: <instansnavn>-db-<env>  # Velg selv hva du vil kalle denne, så lenge den er unik
        ip: 10.x.x.x # Privat IP-adresse til databasen, den finner du i GCP
        ports:
          - name: sql
            port: 5432
            protocol: TCP
  env:  ## DISSE env VERDIENE ER EKSEMPLER, OG MÅ JUSTERES FOR HVER APPLIKASJON
    - name: POSTGRES_DB
      value: minapp
    - name: POSTGRES_USER
      value: minappbrukernavn
    - name: POSTGRES_PASSWORD
      valueFrom:
        secretKeyRef:
          key: db_password
          name: minapp-hemmligheter
    - name: PGSSLCA
      value: /app/db-certs/server.crt
    - name: PGSSLKEY
      value: /app/db-certs/client.key
    - name: PGSSLCERT
      value: /app/db-certs/client.crt
  filesFrom:
  - mountPath: /app/db-certs
    secret: database-certs

Skiperator vil nå:

• lage en NetworkPolicy som gir applikasjonen tilgang til databasen
• 'mounte' sertifikatene inn i filsystemet til poden, slik applikasjonen kan bruke de til å koble til databasen
• laste inn passord hemmeligheten som en env variabel inn i poden, slik applikasjonen kan koble til databasen

Koble til utenfor SKIP

Når man kobler til databasen fra en applikasjon kjørende på SKIP går man direkte mot den private IP-adressen.

Hvis man har behov for å koble til databasen direkte som utvikler, gjøres dette enten via cloud-sql-proxy eller via Cloud SQL Studio i GCP-konsollet.

Tilkobling via cloud-sql-proxy gjøres på denne måten:

1. Elever tilgang med PAM entitlement cloudsql-user i prosjektet hvor databasen ligger
2. Last ned cloudsql-proxy
3. gcloud auth application-default login
4. ./cloud-sql-proxy --private-ip <connection-name> --auto-iam-authn -- connection name finner du på sql instansen i GCP
5. psql -d admin -h localhost -U admin

Du må være på Kartverkets nettverk for å få tilgang, selv med cloud sql proxy. Man kan ikke koble til fra egen klient uten proxy. Du trenger ikke å bruke SSL sertifikater når du kobler til via proxy.

Alternativt kan man også koble seg til databasen via Cloud SQL Studio i GCP-konsollet for å gjøre enklere spørringer og undersøkelser.

1. Elever tilgang med PAM entitlement cloudsql-user i prosjektet hvor databasen ligger
2. Gå til Secret Manager og hent ut brukernavn/passord for brukeren du vil logge på som
3. Gå til Cloud SQL i GCP-konsollet, velg instansen og trykk på "Cloud SQL Studio" hvor du logger inn med brukernavn og passord.

Bruke CloudSQL fra Java applikasjoner

Skal du bruke CloudSQL fra Java applikasjoner må du lage til ExternalSecrets og konfigurere skiperator som ovenfor, men bruk pk8-nøkkel i stedet for vanlig pem-nøkkel. Det skal være nok å konfigurere en connection string som ser noe slik ut postgresql://<privat-ip>:5432/<database-navn>?sslmode=require&sslrootcert=/app/db-certs/server.crt&sslcert=/app/db-certs/client.crt&sslkey=/app/db-certs/client.pk8

Alternativt kan man også bruke en Cloud Sql Auth Proxy connector, men da vil man få litt dårligere ytelse. Bruker man en connector så slipper man å bruke certs, men man må åpne for port 3307 mot databasen i access policies i skiperator manifestet.

Monitorering og alarmering

Fungerer bare dersom dere bruker høyere versjon enn 0.9.1 av cloud_sql modulen.

Vi har laget et dashboard, sammen med DBAene, for monitorering av CloudSQL databasene, som kan finne her. I tillegg så finnes også Googles standard dashboard og metrikker som man kan finne inne på CloudSQL ressursen i GCP consolen.

For alarmering så brukes grafana-alerts repoet som for alle andre type alerts. Her har vi utviklet en cloud_sql_alerts modul som gir dere et sett med standard alarmer. Metrikker vi baserer oss på blir hentet ut ved hjelp av sql_exporter, disse metrikkene er hentet ut på grunnlag av SQL-spørringer som DBAene har predefinert. Ønskes det andre metrikker så ta kontakt med dem.

Det er også tilgjengelig et sett med standardmetrikker fra Google gjennom grafana, for å bruke disse så gå inn i explore view i grafana. Velg Google Cloud Monitoring som datasource, og velg prosjektet ditt og Cloudsql som service. Se på cloud_sql_alerts modulen dersom du ønsker å se hvordan de kan brukes i en alert.

Backup og katastrofehåndtering

Backup

CloudSQL er en google managed løsning av postgres, og det betyr også at det har et innebygd backup system, og håndteres i gcp console. Dette systemet tar automatisk backup av databasen din, og lagrer disse i 7 dager som standard. Hvis du har behov for å bevare backups lengre enn dette kan det konfigureres med en variabel til terraform-modulen, ref: input_retained_backups Backupen er inkrementel og man har Point-in-time recovery tilgjengelig. Vi anbefaler at du leser gjennom Google sin dokumentasjon for å forstå hvordan backup fungerer i CloudSQL.

Katastrofehåndtering

Google Cloud SQL har innebygd failover, og det betyr at dersom primærinstansen din går ned, så vil en av de tilgjengelige replicaene ta over. Dette må konfigureres i terraform, ved bruk av availability_type variabelen, default på denne er ZONAL som betyr at du ikke får en secondary instans. I produksjon er det anbefalt å ha en secondary instans, og da må availability_type settes til REGIONAL i terraform. Les mer her: Google sin dokumentasjon

Audit-logging

Audit-logging for CloudSQL (og i GCP forøvrig) er delt opp i ulike deler:

• "Admin Activity"-logger er innebygd i GCP og loggfører alle administrative handlinger som gjøres på CloudSQL instansen, for eksempel opprettelse av nye databaser, endring av konfigurasjon og så videre.
• "Data Access"-logger inneholder mer detaljert informasjon om lesing og skriving av data på ressursnivå. På SKIP er disse skrudd på for alle prosjekter, men man kan konfigurere nivået av hva som logges gjennom et parameter til en PostgreSQL-extension som heter pgaudit. Som standard logges statements av typen "role,ddl,misc_set". Se wikien for mer informasjon om parameteret.

For å kunne lese audit-logget i prosjektet må man elevere tilganger ved å aktivere PAM entitlement audit-log-viewer i prosjektet hvor databasen ligger. Når dette er gjort kan man gå til Logs Explorer og filtrere på loggtypen man ønsker å se:

   projects/PROJECT_ID/logs/cloudaudit.googleapis.com%2Factivity
   projects/PROJECT_ID/logs/cloudaudit.googleapis.com%2Fdata_access
   projects/PROJECT_ID/logs/cloudaudit.googleapis.com%2Fsystem_event
   projects/PROJECT_ID/logs/cloudaudit.googleapis.com%2Fpolicy

Viktig å huske på

Max connections

I enkelte situasjoner kan hele CloudSQL-instansen bli utilgjengelig, og man vil motta følgende feilmelding:
HTTPError 409: Operation failed because another operation was already in progress. Try your request after the current operation is complete.

Basert på erfaring skyldes dette som regel at det er åpnet for mange samtidige connections mot databasen.
For eksempel, dersom max_connections-variabelen i Terraform-modulen er satt til 100, og man har to applikasjoner som hver bruker 30 connections og kjører med to replikas, vil det totalt bli 120 connections – i tillegg til noen system-connections (typisk 2–3).

I slike tilfeller kan instansen bli utilgjengelig: man kan verken restarte den eller gjøre konfigurasjonsendringer.

Løsning:
Skaler ned alle applikasjoner (sett replicas til 0), og vent opptil én time. Når connections er frigjort, kan man justere max_connections-verdien.

Anbefaling:
Implementer connection pooling i applikasjonene. Hver enkelt connection øker belastningen på databaseinstansen, og unødvendig høyt antall tilkoblinger er både ineffektivt og kostbart.
Legg også til denne alarmmodulen i grafana-alerts, da får dere alarmer når connections nærmer seg maks.