wan
5d1f641c61
status convention: print one line, exit 0 OK / 1 DRIFT / 2 unknown (e.g. needs root). sysmig status now shows [applied ✓/✗/?] per migration and exits 1 on drift. DB checks fall back to peer auth when non-root.
62 lines
2.2 KiB
Bash
62 lines
2.2 KiB
Bash
#!/usr/bin/env bash
|
|
# 008-create-db-iwanhae - Create the personal database `iwanhae` owned by
|
|
# the admin role from 007, so plain `psql` works without -d.
|
|
# up: CREATE DATABASE iwanhae OWNER iwanhae (skip if it already exists)
|
|
# down: DROP DATABASE - refuses while it contains user objects (data safety)
|
|
set -euo pipefail
|
|
|
|
DB=iwanhae
|
|
ROLE=iwanhae
|
|
|
|
db_exists() {
|
|
runuser -u postgres -- psql -v ON_ERROR_STOP=1 -tAc \
|
|
"SELECT 1 FROM pg_database WHERE datname='$DB'" | grep -q 1
|
|
}
|
|
|
|
user_object_count() {
|
|
runuser -u postgres -- psql -v ON_ERROR_STOP=1 -d "$DB" -tAc \
|
|
"SELECT count(*) FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
|
|
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
|
|
AND c.relkind IN ('r', 'p', 'v', 'm', 'S', 'f')" | tr -d ' '
|
|
}
|
|
|
|
case "${1:-}" in
|
|
up)
|
|
if db_exists; then
|
|
echo " database $DB already exists - skipping"
|
|
else
|
|
runuser -u postgres -- psql -v ON_ERROR_STOP=1 -qc \
|
|
"CREATE DATABASE $DB OWNER $ROLE"
|
|
echo " database $DB created, owner $ROLE"
|
|
fi
|
|
;;
|
|
status)
|
|
if [[ $EUID -eq 0 ]]; then
|
|
q() { runuser -u postgres -- psql -d postgres -v ON_ERROR_STOP=1 -tAc "$1"; }
|
|
elif psql -d postgres -tAc 'SELECT 1' >/dev/null 2>&1; then
|
|
q() { psql -d postgres -v ON_ERROR_STOP=1 -tAc "$1"; }
|
|
else
|
|
echo "? needs root or peer DB access to verify"
|
|
exit 2
|
|
fi
|
|
row=$(q "SELECT pg_get_userbyid(datdba) FROM pg_database WHERE datname='$DB'")
|
|
[[ -n $row ]] || { echo "DRIFT database $DB does not exist"; exit 1; }
|
|
[[ $row == "$ROLE" ]] || { echo "DRIFT database $DB owner=$row (want $ROLE)"; exit 1; }
|
|
echo "OK database $DB exists, owner $ROLE"
|
|
;;
|
|
down)
|
|
if ! db_exists; then
|
|
echo " database $DB does not exist - nothing to do"
|
|
exit 0
|
|
fi
|
|
if [[ $(user_object_count) -gt 0 ]]; then
|
|
echo "refusing to drop $DB - it contains user objects (data would be lost)" >&2
|
|
echo "drop it manually if you really want: runuser -u postgres -- psql -c 'DROP DATABASE $DB WITH (FORCE)'" >&2
|
|
exit 1
|
|
fi
|
|
runuser -u postgres -- psql -v ON_ERROR_STOP=1 -qc \
|
|
"DROP DATABASE $DB WITH (FORCE)" # FORCE: kick lingering psql sessions
|
|
echo " database $DB dropped (was empty)"
|
|
;;
|
|
esac
|