swiyu-verifier-dbconfig (6747B)
1 #!/bin/bash 2 # This file is part of KyCH. 3 # Copyright (C) 2026 Taler Systems SA 4 # 5 # KyCH is free software; you can redistribute it and/or modify it under the 6 # terms of the GNU Affero General Public License as published by the Free 7 # Software Foundation; either version 3, or (at your option) any later version. 8 # 9 # KyCH is distributed in the hope that it will be useful, but WITHOUT ANY 10 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR 11 # A PARTICULAR PURPOSE. See the GNU Affero General Public License for more 12 # details. 13 # 14 # You should have received a copy of the GNU Affero General Public License 15 # along with KyCH; see the file COPYING. If not, see 16 # <http://www.gnu.org/licenses/> 17 # 18 # 19 # Create the PostgreSQL role and database for the SWIYU verifier and store the 20 # generated password where the service can read it. 21 # 22 # Unlike KyCH, the verifier reaches PostgreSQL through the JDBC driver, which 23 # cannot use peer authentication over a Unix socket -- hence a password, and 24 # hence this script rather than a plain createdb. The schema itself is not 25 # created here: the verifier runs its own Flyway migrations on every start. 26 # 27 # Error checking on 28 set -eu 29 30 RESET_DB=0 31 DBUSER= 32 DBNAME= 33 ENVFILE="/etc/swiyu-verifier/swiyu-verifier.env" 34 SECRETFILE="/etc/swiyu-verifier/secrets/db.env" 35 SERVICEUSER="swiyu-verifier" 36 37 while getopts 'c:d:hn:ru:' OPTION; do 38 case "$OPTION" in 39 c) 40 ENVFILE="$OPTARG" 41 ;; 42 d) 43 SECRETFILE="$OPTARG" 44 ;; 45 h) 46 echo 'Supported options:' 47 echo " -c FILENAME -- read the connection from FILENAME (default: $ENVFILE)" 48 echo " -d FILENAME -- write the password to FILENAME (default: $SECRETFILE)" 49 echo " -h -- print this help text" 50 echo " -n DBNAME -- database to create (default: from POSTGRES_JDBC)" 51 echo " -r -- reset database (dangerous, destroys all data)" 52 echo " -u DBUSER -- database role to create (default: from POSTGRES_USER)" 53 exit 0 54 ;; 55 n) 56 DBNAME="$OPTARG" 57 ;; 58 r) 59 RESET_DB="1" 60 ;; 61 u) 62 DBUSER="$OPTARG" 63 ;; 64 ?) 65 echo "Unrecognized command line option" 1>&2 66 exit 1 67 ;; 68 esac 69 done 70 71 # Take the port and the database name from the service's own configuration, so 72 # that this script and the service cannot end up talking to different servers: 73 # on a host whose PostgreSQL is not on the default port, a hard-coded default 74 # would silently set the database up in the wrong place, or in no place at all. 75 # 76 # POSTGRES_JDBC=jdbc:postgresql://host:port/dbname?params 77 jdbc=$(sed -n 's/^[[:space:]]*POSTGRES_JDBC=//p' "$ENVFILE" 2>/dev/null | tail -1) 78 JDBCHOST=localhost 79 PGPORT=5432 80 case "$jdbc" in 81 jdbc:postgresql://*) 82 rest=${jdbc#jdbc:postgresql://} 83 authority=${rest%%/*} 84 path=${rest#*/} 85 path=${path%%\?*} 86 JDBCHOST=${authority%%:*} 87 case "$authority" in 88 *:*) PGPORT=${authority##*:} ;; 89 esac 90 [ -n "$DBNAME" ] || DBNAME="$path" 91 ;; 92 *) 93 echo "No POSTGRES_JDBC in $ENVFILE; assuming localhost port 5432." 1>&2 94 ;; 95 esac 96 97 case "$JDBCHOST" in 98 localhost | 127.0.0.1 | ::1 | "") ;; 99 *) 100 echo "POSTGRES_JDBC names the server '$JDBCHOST', which is not this host." 1>&2 101 echo "Create the role and the database there by hand -- the verifier needs" 1>&2 102 echo "only an empty database owned by its role, since it migrates its own" 1>&2 103 echo "schema -- and put the password in $SECRETFILE." 1>&2 104 exit 1 105 ;; 106 esac 107 108 if [ -z "$DBUSER" ]; then 109 DBUSER=$(sed -n 's/^[[:space:]]*POSTGRES_USER=//p' "$ENVFILE" 2>/dev/null | tail -1) 110 fi 111 112 : "${DBNAME:=swiyu_verifier}" 113 : "${DBUSER:=swiyu_verifier}" 114 115 if [ "$(id -u)" -ne 0 ]; then 116 echo "This script must be run as root" 1>&2 117 exit 1 118 fi 119 120 if ! id postgres >/dev/null 2>&1; then 121 echo "Could not find 'postgres' user. Please install PostgreSQL first" 1>&2 122 exit 1 123 fi 124 125 # 32 bytes of base64 from the kernel's pool; no shell history, no temporary 126 # file, and never printed. 127 DBPASS=$(head -c 32 /dev/urandom | base64 | tr -d '\n=' | tr '+/' 'ab') 128 129 # The administrative work is done over the local socket as the postgres user, 130 # which is what peer authentication there expects. Only the port comes from 131 # the configuration: the service's own TCP connection with a password is a 132 # different matter, and asking the postgres superuser to authenticate over TCP 133 # would stop to prompt for a password that nobody is there to type. "sudo -i" 134 # resets the environment, so PGPORT is passed explicitly, and -w keeps psql 135 # from prompting in any case. 136 as_postgres() { 137 sudo -i -u postgres env PGPORT="$PGPORT" PGCONNECT_TIMEOUT=10 "$@" 138 } 139 140 # Up front, so that a server which is not running is reported as that and not 141 # as a series of puzzling failures further down. 142 if ! as_postgres psql -w -tAc 'SELECT 1' postgres >/dev/null 2>&1; then 143 echo "Cannot reach PostgreSQL on port $PGPORT as the postgres user over the" 1>&2 144 echo "local socket. Check that the server is running and that" 1>&2 145 echo "POSTGRES_JDBC in $ENVFILE names its port." 1>&2 146 exit 1 147 fi 148 149 echo "Setting up database user '$DBUSER'." 1>&2 150 if as_postgres psql -w -tAc "SELECT 1 FROM pg_roles WHERE rolname = '$DBUSER'" postgres | grep -q 1; then 151 echo "Database user '$DBUSER' already existed; setting a new password." 1>&2 152 fi 153 # Both branches in one statement, so the role ends up with the password we are 154 # about to write out either way. 155 as_postgres psql -w -q postgres <<SQL 156 DO \$\$ 157 BEGIN 158 IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = '$DBUSER') THEN 159 ALTER ROLE "$DBUSER" WITH LOGIN PASSWORD '$DBPASS'; 160 ELSE 161 CREATE ROLE "$DBUSER" WITH LOGIN PASSWORD '$DBPASS'; 162 END IF; 163 END 164 \$\$; 165 SQL 166 167 DO_CREATE=1 168 if as_postgres psql -w -lqtA -F: | cut -d: -f1 | grep -qx "$DBNAME"; then 169 if [ 1 = "$RESET_DB" ]; then 170 echo "Deleting existing database '$DBNAME'." 1>&2 171 if ! as_postgres dropdb "$DBNAME"; then 172 echo "Failed to delete existing database '$DBNAME'" 1>&2 173 exit 1 174 fi 175 else 176 echo "Database '$DBNAME' already exists, continuing anyway." 1>&2 177 DO_CREATE=0 178 fi 179 fi 180 181 if [ 1 = "$DO_CREATE" ]; then 182 echo "Creating database '$DBNAME' on port $PGPORT." 1>&2 183 if ! as_postgres createdb -O "$DBUSER" "$DBNAME"; then 184 echo "Failed to create database '$DBNAME'" 1>&2 185 exit 1 186 fi 187 fi 188 189 echo "Writing the generated password to $SECRETFILE." 1>&2 190 mkdir -p "$(dirname "$SECRETFILE")" 191 umask 027 192 cat >"$SECRETFILE" <<ENV 193 # Written by swiyu-verifier-dbconfig; read by swiyu-verifier.service. 194 # Keep this file mode 0640 and owned by root:$SERVICEUSER. 195 POSTGRES_PASSWORD=$DBPASS 196 ENV 197 chmod 0640 "$SECRETFILE" 198 if getent group "$SERVICEUSER" >/dev/null 2>&1; then 199 chown "root:$SERVICEUSER" "$SECRETFILE" 200 fi 201 202 echo "Database configuration finished. The schema itself is created by the" 1>&2 203 echo "service on its next start." 1>&2 204 205 exit 0