TrigpointingUK - API

Source code for TrigpointingUK API.

The current TrigpointingUK website uses outdated PHP code, which does not meet modern security expectations, and which relies on AWS functionality due to be deprecated in August 2022. This repo houses the development of a more cloud native codebase.

Running locally

First start the Google Cloud SQL Proxy on your local machine, pointing to the T:UK CloudSQL instance:

# Start sql proxy using the same socket path that CloudRun uses...
./cloud_sql_proxy --dir=/cloudsql --instances=trigpointinguk:europe-west1:trigpointing-679c4ef1=unix:/cloudsql/trigpointinguk:europe-west1:trigpointing-679c4ef1

# Connect with a postgres username/password...
psql "host=/cloudsql/trigpointinguk:europe-west1:trigpointing-679c4ef1 dbname=tme user=postgres"


#...or in hope of things to come...
./cloud_sql_proxy -enable_iam_login --dir=/cloudsql --instances=trigpointinguk:europe-west1:trigpointing-679c4ef1=unix:/cloudsql/trigpointinguk:europe-west1:trigpointing-679c4ef1
# Connect with a GCP user...
gcloud auth login admin@trigpointing.uk
psql "host=/cloudsql/trigpointinguk:europe-west1:trigpointing-679c4ef1 dbname=tme user=admin@trigpointing.uk"
# Connect as a Service Account... (NB not supported by Cloud Run!)
gcloud iam service-accounts keys create --iam-account=api-tme@trigpointinguk.iam.gserviceaccount.com - | gcloud auth activate-service-account --key-file -
psql "host=/cloudsql/trigpointinguk:europe-west1:trigpointing-679c4ef1 dbname=tme user=api-tme@trigpointinguk.iam"

Local PSQL client

Option 1

Cloud Run -> Serverless VPC Connector -> VPC -> Cloud SQL

VPC Connector costs money for an instance

Option 2

Cloud Run -> Cloud SQL Proxy -> Cloud SQL

Local testing: ./cloud_sql_proxy --dir=/cloudsql --instances=trigpointinguk:europe-west1:trigpointing-6b5de36a=unix:/cloudsql/trigpointinguk:europe-west1:trigpointing-6b5de36a

With POSTGRES_SOCKET=/cloudsql/trigpointinguk:europe-west1:trigpointing-6b5de36a

Option 3

Cloud Run -> direct ssl connection -> Cloud SQL

Tried once and failed - that's why Google wrote the proxy!

Option 4

Cloud Run -> unencrypted -> Cloud SQL

Poor security

Test results - Main branch

License and copyright

Copyright (C) 2021-2022  Ian Harris

This program is free software: you can redistribute it and/or modify
it under the terms of the GNU Affero General Public License as published
by the Free Software Foundation, version 3.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU Affero General Public License for more details.

You should have received a copy of the GNU Affero General Public License
along with this program.  If not, see <https://www.gnu.org/licenses/agpl-3.0.txt>.

TrigpointingUK/api