Repository navigation
130 lines (121 loc) · 6.92 KB
/
Copy pathprovision-postgres.yml
File metadata and controls
130 lines (121 loc) · 6.92 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
name: Provision PostgreSQL
# Idempotent and safe to leave in the repo / re-run at any time.
# Provisions a PostgreSQL instance dedicated to this project alone --
# each project on this VM gets its own isolated database, not a shared
# one (a shared instance would couple this project's DB uptime/
# maintenance to whoever else depends on it, working against the
# fault-isolation/independent-deploy principle this whole service is
# already built around -- see README's own rationale for why this
# talks to CKAN over its Action API instead of running in-process).
#
# Generates its own password directly on the VM, only the first time
# it's actually needed (no GitHub secrets round-trip required), and
# never overwrites an existing .env/database/role. Exists so this
# project stays redeployable: if its Postgres were ever lost (VM
# rebuild, disk issue), re-running this workflow stands it back up
# from the docker-compose.yml this repo actually commits, rather than
# depending on a one-time action nobody could reproduce.
on:
workflow_dispatch:
jobs:
provision:
runs-on: [self-hosted, loader]
steps:
- name: Confirm docker compose is available
run: sudo docker compose version
- name: Sync the compose file from this repo's own committed copy
run: |
sudo mkdir -p /opt/data-projects/loader-data-gov-gr-postgres
sudo chown -R localadmin:localadmin /opt/data-projects/loader-data-gov-gr-postgres
sudo -u localadmin cp \
/opt/data-projects/loader-data-gov-gr/deploy/postgres/docker-compose.yml \
/opt/data-projects/loader-data-gov-gr-postgres/docker-compose.yml
- name: Generate the superuser password, only if not already provisioned
run: |
if [ -f /opt/data-projects/loader-data-gov-gr-postgres/.env ]; then
echo "Already provisioned -- .env exists, leaving it untouched."
else
pw=$(openssl rand -base64 24)
sudo -u localadmin tee /opt/data-projects/loader-data-gov-gr-postgres/.env > /dev/null <<EOF
POSTGRES_PASSWORD=$pw
EOF
sudo chmod 600 /opt/data-projects/loader-data-gov-gr-postgres/.env
echo "Generated a fresh superuser password."
fi
- name: Start (or confirm already running) the container
run: |
cd /opt/data-projects/loader-data-gov-gr-postgres
sudo docker compose up -d
echo "--- waiting for healthy ---"
for i in $(seq 1 24); do
status=$(sudo docker inspect --format='{{.State.Health.Status}}' loader-postgres 2>/dev/null || echo "starting")
echo "status: $status"
if [ "$status" = "healthy" ]; then break; fi
sleep 2
done
sudo docker ps --filter name=loader-postgres
- name: Verify the resource caps actually took effect
run: |
echo "--- memory limit (bytes; expect 2147483648 for 2g) ---"
sudo docker inspect loader-postgres --format='{{.HostConfig.Memory}}'
echo "--- CPU quota (NanoCpus; expect 1000000000 for 1.0 cpu) ---"
sudo docker inspect loader-postgres --format='{{.HostConfig.NanoCpus}}'
- name: Ensure loader's own database/role exist (idempotent either way)
run: |
cd /opt/data-projects/loader-data-gov-gr-postgres
if sudo grep -q '^LOADER_HARVEST_DB_URL=' /etc/loader-data-gov-gr.env 2>/dev/null; then
echo "loader's own DB URL is already provisioned -- confirming the role/db still exist, not regenerating the password."
sudo docker compose exec -T postgres psql -U postgres -c \
"SELECT rolname FROM pg_roles WHERE rolname = 'loader_harvest_app'; SELECT datname FROM pg_database WHERE datname = 'loader_harvest';"
# Self-correcting: an earlier run of this same workflow wrote
# a bare postgresql:// scheme, which makes SQLAlchemy default
# to the psycopg2 dialect regardless of what's installed --
# this project installs psycopg (v3). Confirmed the hard way
# (/admin/harvest-db/health 503'd with "No module named
# 'psycopg2'"). Fix forward rather than leave it broken.
sudo sed -i 's#^LOADER_HARVEST_DB_URL=postgresql://#LOADER_HARVEST_DB_URL=postgresql+psycopg://#' \
/etc/loader-data-gov-gr.env
# Self-correcting: Postgres 15+ no longer grants CREATE/USAGE
# on the "public" schema to PUBLIC by default, so
# GRANT ALL PRIVILEGES ON DATABASE alone isn't enough --
# confirmed the hard way (a real harvest-now 500'd with
# "psycopg.errors.InsufficientPrivilege: permission denied
# for schema public" the first time HarvestBase tried to
# CREATE TABLE). Re-grant on every run; it's a no-op once
# already granted.
sudo docker compose exec -T postgres psql -U postgres -d loader_harvest -c \
"GRANT ALL ON SCHEMA public TO loader_harvest_app;"
else
app_pw=$(openssl rand -base64 24)
sudo docker compose exec -T postgres psql -U postgres <<EOF
SELECT 'CREATE DATABASE loader_harvest' WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'loader_harvest')\gexec
DO \$\$
BEGIN
IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'loader_harvest_app') THEN
CREATE ROLE loader_harvest_app LOGIN PASSWORD '$app_pw';
END IF;
END
\$\$;
GRANT ALL PRIVILEGES ON DATABASE loader_harvest TO loader_harvest_app;
EOF
# Postgres 15+ default: "public" schema grants CREATE/USAGE
# to the owner only, not to PUBLIC -- must be granted
# explicitly to the app role, on the target database itself
# (not the "postgres" maintenance DB the block above ran
# against).
sudo docker compose exec -T postgres psql -U postgres -d loader_harvest -c \
"GRANT ALL ON SCHEMA public TO loader_harvest_app;"
sudo tee -a /etc/loader-data-gov-gr.env > /dev/null <<EOF
LOADER_HARVEST_DB_URL=postgresql+psycopg://loader_harvest_app:$app_pw@127.0.0.1:5432/loader_harvest
EOF
echo "Provisioned loader_harvest / loader_harvest_app for the first time."
fi
- name: Confirm the stored connection string actually still works
run: |
url=$(sudo grep '^LOADER_HARVEST_DB_URL=' /etc/loader-data-gov-gr.env | cut -d= -f2-)
sudo docker exec -e PGPASSWORD="$(echo "$url" | sed -E 's#.*://[^:]+:([^@]+)@.*#\1#')" loader-postgres \
psql -U loader_harvest_app -d loader_harvest -h 127.0.0.1 -c "SELECT current_user, current_database();"
- name: Confirm the env file still has exactly the expected keys
run: |
echo "--- resulting file (values redacted by GitHub, keys only) ---"
sudo sed 's/=.*/=<redacted>/' /etc/loader-data-gov-gr.env