# ovedr-centos10-postgres — Demo 1d's "golden image" as code. GENERATED by automation/tools/centos10_template.sh from # workload/centos10/user-data.yaml and workload/sql/*.sql: edit those, then regenerate. # # Deploy it from the console (Virtualization → Catalog → Template catalog → "CentOS Stream 10 + PostgreSQL 16 (class)"), # or from the command line: # oc process openshift//ovedr-centos10-postgres -p NAME=… -p VLAN=… -p IP=… -p OS_USER=… -p DB_USER=… -p DB_PASSWORD=… \ # | oc apply -n -f - apiVersion: template.openshift.io/v1 kind: Template metadata: name: ovedr-centos10-postgres namespace: openshift labels: app.kubernetes.io/part-of: ovedr template.kubevirt.io/type: vm os.template.kubevirt.io/centos-stream10: "true" workload.template.kubevirt.io/server: "true" flavor.template.kubevirt.io/medium: "true" annotations: openshift.io/display-name: "CentOS Stream 10 + PostgreSQL 16 (class)" description: >- The class's database server, built entirely in code: Red Hat's CentOS Stream 10 boot source, plus cloud-init that installs PostgreSQL 16 with its data and WAL on their own disks, the data-integrity toolkit, your Linux and database logins, and a heartbeat. Same result every time; nothing baked into an image. iconClass: icon-centos tags: kubevirt,virtualmachine,linux,postgresql openshift.io/provider-display-name: Paragon Micro lab template.kubevirt.io/provider: Paragon Micro lab name.os.template.kubevirt.io/centos-stream10: CentOS Stream 10 defaults.template.kubevirt.io/disk: rootdisk parameters: - { name: NAME, displayName: "VM name", required: true, description: "e.g. pg-s01-centos10" } - { name: VLAN, displayName: "VLAN", required: true, description: "your booking's VLAN, 22 + N (vcenter01 → 23)" } - { name: IP, displayName: "IP address", required: true, description: "a free address on that VLAN, e.g. 10.101.23.151 (150 + your student number)" } - { name: OS_USER, displayName: "Linux login", required: true, description: "e.g. student-01" } - { name: DB_USER, displayName: "Database login", required: true, description: "e.g. student_01 (underscore)" } - { name: DB_PASSWORD, displayName: "Password (Linux and database)", required: true, description: "your class password" } objects: # cloud-init's user-data rides in a Secret: KubeVirt refuses inline userData over 2048 bytes, and this is ~10 KB - apiVersion: v1 kind: Secret metadata: name: ${NAME}-cloudinit labels: { app: "${NAME}", ovedr/golden: centos10-postgres } type: Opaque stringData: userdata: | #cloud-config # ovedr-centos10-postgres: the customer's "golden image" as code (Demo 1d). Red Hat's CentOS Stream 10 cloud image # boots, and this file alone turns it into the class's database server: PostgreSQL 16 with its data and its WAL on # their own disks, the class's data-integrity toolkit, the student's Linux and database logins, and a heartbeat. # Nothing is baked into an image: delete the VM, deploy it again, and you get the same server. # # ${NAME}, ${OS_USER}, ${DB_USER} and ${DB_PASSWORD} are OpenShift Template parameters, filled in at deploy. # automation/tools/centos10_template.sh builds the Template from this file and workload/sql/*.sql (#@file lines). # Keep shell variables brace-free ($x, never with braces): the Template reads dollar-brace names as its parameters. hostname: ${NAME} users: - default - name: ${OS_USER} groups: [wheel] lock_passwd: false shell: /bin/bash ssh_pwauth: false # The two database disks, found by the serial the VM gives them, each with an xfs filesystem and a label. Mounted # with PostgreSQL's SELinux type, so the database may use them without relabelling. fs_setup: - { device: /dev/disk/by-id/virtio-pgdata, partition: none, filesystem: xfs, label: pgdata } - { device: /dev/disk/by-id/virtio-pgwal, partition: none, filesystem: xfs, label: pgwal } mounts: - [ LABEL=pgdata, /var/lib/pgsql/data, xfs, "defaults,nofail,context=system_u:object_r:postgresql_db_t:s0", "0", "2" ] - [ LABEL=pgwal, /var/lib/pgsql/wal, xfs, "defaults,nofail,context=system_u:object_r:postgresql_db_t:s0", "0", "2" ] packages: [ postgresql-server, qemu-guest-agent ] write_files: # the password, once, in a root-only file the setup reads and then removes (any character is safe in a block) - path: /root/.ovedr-password permissions: "0600" content: | ${DB_PASSWORD} # the class's database: the same schema and toolkit the Windows VMs run - path: /usr/local/share/ovedr/schema.sql content: | -- BCDR heartbeat schema. Run once as the postgres superuser: -- psql -U postgres -f schema.sql -- Database `bcdr`, owner `bcdr`. The writer inserts every 5 s; students insert named rows. CREATE ROLE bcdr LOGIN; -- password set by Install-Heartbeat.ps1 CREATE DATABASE bcdr OWNER bcdr; \connect bcdr CREATE TABLE heartbeat ( id bigserial PRIMARY KEY, ts timestamptz NOT NULL DEFAULT clock_timestamp(), -- wall clock at insert, not txn start writer text NOT NULL, -- 'auto' or a student account note text ); CREATE INDEX heartbeat_ts ON heartbeat (ts); ALTER TABLE heartbeat OWNER TO bcdr; -- Every move the class makes, for the proof table. CREATE TABLE move_log ( id bigserial PRIMARY KEY, ts timestamptz NOT NULL DEFAULT clock_timestamp(), step text NOT NULL, -- e.g. 'm4-before', 'm4-after' max_hb_id bigint, pg_started timestamptz NOT NULL DEFAULT pg_postmaster_start_time() ); ALTER TABLE move_log OWNER TO bcdr; - path: /usr/local/share/ovedr/integrity.sql content: | -- Data-integrity toolkit: what every student runs at every stage of every demo. -- Idempotent: Setup-SourceVm.ps1 applies it on each converge, as the postgres superuser: -- psql -U postgres -d bcdr -f integrity.sql -- -- SELECT * FROM ovedr_mark('student-03', 'd1-before-migration'); -- inject: one named, hash-chained row -- SELECT * FROM ovedr_load('student-03', 'd2-load', 500); -- inject: a burst of rows (a busy database) -- SELECT * FROM ovedr_verify('student-03'); -- read: are all my rows here, chain intact? -- SELECT * FROM ovedr_checksum(); -- read: one fingerprint of every mark -- -- Each row's sha = sha256(previous row's sha for that student ‖ student ‖ stage ‖ payload ‖ ts in UTC, µs). A row -- that is lost, altered or reordered breaks that student's chain, and ovedr_verify says where. \connect bcdr CREATE TABLE IF NOT EXISTS marks ( id bigserial PRIMARY KEY, ts timestamptz NOT NULL DEFAULT clock_timestamp(), student text NOT NULL, stage text NOT NULL, payload text NOT NULL DEFAULT '', prev_sha text NOT NULL, sha text NOT NULL ); CREATE INDEX IF NOT EXISTS marks_student ON marks (student, id); ALTER TABLE marks OWNER TO bcdr; CREATE OR REPLACE FUNCTION ovedr_mark(p_student text, p_stage text, p_payload text DEFAULT '') RETURNS TABLE (id bigint, ts timestamptz, stage text, sha text) LANGUAGE plpgsql AS $$ DECLARE prev text; t timestamptz := clock_timestamp(); BEGIN PERFORM pg_advisory_xact_lock(hashtext(p_student)); -- one chain per student, no races SELECT m.sha INTO prev FROM marks m WHERE m.student = p_student ORDER BY m.id DESC LIMIT 1; prev := coalesce(prev, 'genesis'); RETURN QUERY INSERT INTO marks AS m (ts, student, stage, payload, prev_sha, sha) VALUES (t, p_student, p_stage, p_payload, prev, encode(sha256(convert_to(prev || '|' || p_student || '|' || p_stage || '|' || p_payload || '|' || to_char(t AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.US'), 'UTF8')), 'hex')) RETURNING m.id, m.ts, m.stage, left(m.sha, 12); END $$; ALTER FUNCTION ovedr_mark(text, text, text) OWNER TO bcdr; CREATE OR REPLACE FUNCTION ovedr_load(p_student text, p_stage text, p_rows int DEFAULT 100) RETURNS TABLE (rows_written int, first_id bigint, last_id bigint, seconds numeric) LANGUAGE plpgsql AS $$ DECLARE a bigint; b bigint; t0 timestamptz := clock_timestamp(); BEGIN FOR i IN 1..p_rows LOOP SELECT o.id INTO b FROM ovedr_mark(p_student, p_stage, 'load ' || i || '/' || p_rows || ' ' || md5(random()::text)) o; IF i = 1 THEN a := b; END IF; END LOOP; RETURN QUERY SELECT p_rows, a, b, round(extract(epoch FROM clock_timestamp() - t0)::numeric, 2); END $$; ALTER FUNCTION ovedr_load(text, text, int) OWNER TO bcdr; CREATE OR REPLACE FUNCTION ovedr_verify(p_student text DEFAULT NULL) RETURNS TABLE (student text, marks bigint, last_stage text, last_ts timestamptz, chain_ok boolean, first_break bigint) LANGUAGE sql STABLE AS $$ WITH c AS ( SELECT m.*, lag(m.sha) OVER (PARTITION BY m.student ORDER BY m.id) AS want_prev, encode(sha256(convert_to(m.prev_sha || '|' || m.student || '|' || m.stage || '|' || m.payload || '|' || to_char(m.ts AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.US'), 'UTF8')), 'hex') AS want_sha FROM marks m WHERE p_student IS NULL OR m.student = p_student) SELECT c.student, count(*), (array_agg(c.stage ORDER BY c.id DESC))[1], max(c.ts), bool_and(c.sha = c.want_sha AND c.prev_sha = coalesce(c.want_prev, 'genesis')), min(c.id) FILTER (WHERE NOT (c.sha = c.want_sha AND c.prev_sha = coalesce(c.want_prev, 'genesis'))) FROM c GROUP BY c.student ORDER BY c.student $$; ALTER FUNCTION ovedr_verify(text) OWNER TO bcdr; CREATE OR REPLACE FUNCTION ovedr_checksum() RETURNS TABLE (marks bigint, last_id bigint, fingerprint text) LANGUAGE sql STABLE AS $$ SELECT count(*), max(id), left(md5(string_agg(sha, '' ORDER BY id)), 16) FROM marks $$; ALTER FUNCTION ovedr_checksum() OWNER TO bcdr; - path: /usr/local/share/ovedr/class.sql content: | -- the class role, the guest-tools report, and what a student may do (as on the Windows VMs) CREATE ROLE ovedr_class NOLOGIN; GRANT CONNECT ON DATABASE bcdr TO ovedr_class; \connect bcdr CREATE TABLE IF NOT EXISTS guest_tools (id bigserial PRIMARY KEY, ts timestamptz NOT NULL DEFAULT clock_timestamp(), platform text, vmtools text, qemu_ga text, virtio text); ALTER TABLE guest_tools OWNER TO bcdr; GRANT USAGE ON SCHEMA public TO ovedr_class; GRANT SELECT, INSERT ON marks, heartbeat TO ovedr_class; GRANT SELECT ON move_log, guest_tools TO ovedr_class; GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO ovedr_class; GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO ovedr_class; -- the student's own login, with the password read from the file above (psql quotes it) CREATE ROLE ${DB_USER} LOGIN PASSWORD :'pw' IN ROLE ovedr_class; - path: /usr/local/sbin/ovedr-pg-setup permissions: "0755" content: | #!/bin/bash # First boot: PostgreSQL on the data disk with its WAL on the WAL disk, open to the lab network, the class # database, the student's logins, and the heartbeat. Runs once (cloud-init runcmd). set -euo pipefail pw="$(head -n1 /root/.ovedr-password)" echo "${OS_USER}:$pw" | chpasswd chown postgres:postgres /var/lib/pgsql/data /var/lib/pgsql/wal chmod 700 /var/lib/pgsql/data /var/lib/pgsql/wal PGSETUP_INITDB_OPTIONS="--waldir=/var/lib/pgsql/wal/pg_wal --auth-local=peer --auth-host=scram-sha-256" \ postgresql-setup --initdb echo "listen_addresses = '*'" >> /var/lib/pgsql/data/postgresql.conf echo "host all all 10.0.0.0/8 scram-sha-256" >> /var/lib/pgsql/data/pg_hba.conf systemctl enable --now postgresql qemu-guest-agent runuser -u postgres -- psql -X -q -v ON_ERROR_STOP=1 -f /usr/local/share/ovedr/schema.sql runuser -u postgres -- psql -X -q -v ON_ERROR_STOP=1 -d bcdr -f /usr/local/share/ovedr/integrity.sql runuser -u postgres -- psql -X -q -v ON_ERROR_STOP=1 -v pw="$pw" -f /usr/local/share/ovedr/class.sql shred -u /root/.ovedr-password systemctl enable --now ovedr-heartbeat echo "OVEDR-READY $(hostname) postgresql $(runuser -u postgres -- psql -XAtc 'SHOW server_version')" # the heartbeat: one row every 5 s (gaps are outages), and a guest-tools report at start - path: /usr/local/sbin/ovedr-heartbeat permissions: "0755" content: | #!/bin/bash psql -X -q -d bcdr -c "INSERT INTO guest_tools (platform, vmtools, qemu_ga, virtio) VALUES ('$(systemd-detect-virt)', 'absent', '$(systemctl is-active qemu-guest-agent)', '$(lsmod | awk '/^virtio/ {print $1}' | sort | paste -sd, -)')" while :; do psql -X -q -d bcdr -c "INSERT INTO heartbeat (writer, note) VALUES ('auto', 'centos10')" || true sleep 5 done - path: /etc/systemd/system/ovedr-heartbeat.service content: | [Unit] Description=ovedr heartbeat: a row in bcdr.heartbeat every 5 seconds After=postgresql.service Requires=postgresql.service [Service] User=postgres ExecStart=/usr/local/sbin/ovedr-heartbeat Restart=always RestartSec=5 [Install] WantedBy=multi-user.target runcmd: - [ systemctl, daemon-reload ] - [ /usr/local/sbin/ovedr-pg-setup ] - apiVersion: kubevirt.io/v1 kind: VirtualMachine metadata: name: ${NAME} labels: app: ${NAME} ovedr/golden: centos10-postgres vm.kubevirt.io/template: ovedr-centos10-postgres vm.kubevirt.io/template.namespace: openshift spec: runStrategy: Always dataVolumeTemplates: - metadata: { name: "${NAME}" } # the system disk: a clone of Red Hat's CentOS Stream 10 image spec: sourceRef: { kind: DataSource, name: centos-stream10, namespace: openshift-virtualization-os-images } storage: { resources: { requests: { storage: 30Gi } } } - metadata: { name: "${NAME}-pgdata" } # PostgreSQL data, an empty disk (the cluster's default storage) spec: source: { blank: {} } storage: { resources: { requests: { storage: 10Gi } } } - metadata: { name: "${NAME}-pgwal" } # PostgreSQL WAL (the log), its own empty disk spec: source: { blank: {} } storage: { resources: { requests: { storage: 5Gi } } } template: metadata: labels: { kubevirt.io/domain: "${NAME}", ovedr/golden: centos10-postgres } annotations: { vm.kubevirt.io/os: centos-stream10, vm.kubevirt.io/workload: server, vm.kubevirt.io/flavor: medium } spec: domain: cpu: { cores: 2, sockets: 1, threads: 1 } memory: { guest: 4Gi } devices: disks: - { name: rootdisk, disk: { bus: virtio } } - { name: cloudinitdisk, disk: { bus: virtio } } - { name: pgdata, disk: { bus: virtio }, serial: pgdata } # /dev/disk/by-id/virtio-pgdata - { name: pgwal, disk: { bus: virtio }, serial: pgwal } # /dev/disk/by-id/virtio-pgwal interfaces: - { name: default, bridge: {}, model: virtio } # on your VLAN, at its own address rng: {} networks: - { name: default, multus: { networkName: "default/vmnet${VLAN}" } } terminationGracePeriodSeconds: 180 volumes: - { name: rootdisk, dataVolume: { name: "${NAME}" } } - { name: pgdata, dataVolume: { name: "${NAME}-pgdata" } } - { name: pgwal, dataVolume: { name: "${NAME}-pgwal" } } - name: cloudinitdisk cloudInitNoCloud: networkData: | version: 2 ethernets: enp1s0: # the virtio NIC's name in CentOS Stream 10 addresses: [ ${IP}/24 ] # 0.0.0.0/0, not "default": cloud-init 24.4 (CentOS 10) fails its network stage on "to: default" # ("Address default is not a valid ip address") and the VM falls back to a DHCP address routes: [ { to: 0.0.0.0/0, via: 10.101.${VLAN}.1 } ] nameservers: { addresses: [ 10.101.6.201, 10.101.6.202 ] } secretRef: { name: "${NAME}-cloudinit" } # the user-data, from the Secret above