[pkg-bacula-devel] Bug#1108272: bacula-director-pgsql: update_postgresql_tables script fails due to bad shell quoting

[Wouter Verhelst]

Package: bacula-director-pgsql
Version: 15.0.3-1~bpo12+1
Severity: important
X-Debbugs-Cc: wouter at debian.org

Hi,

I installed the bacula director from backports, so that my laptop (which
runs unstable) can still be backed up to my server (which runs stable).

After the upgrade to bacula 15, I noticed that my director was no longer
running. Checking the logs, I found the following:

24-jun 18:00 bacula-dir JobId 0: Fatal error: Version error for database "bacula". Wanted 1026, got 1024
24-jun 18:00 bacula-dir JobId 0: Fatal error: Could not open Catalog "MyCatalog", database "bacula".
24-jun 18:00 bacula-dir JobId 0: Fatal error: Version error for database "bacula". Wanted 1026, got 1024
24-jun 18:00 bacula-dir ERROR TERMINATION

This surprised me, because obviously there's a script to handle this. So
I ran it manually, and received errors about SQL syntax where it said
"do <number>".

Checking the script, I quickly found the problem. Here's a patch that
solved it for me:

--- update_postgresql_tables.orig	2025-06-24 18:14:43.055571381 +0200
+++ update_postgresql_tables	2025-06-24 18:15:04.866426330 +0200
@@ -642,7 +642,7 @@
 
 -- Need to add postgresql-contrib to the dependency list
 
-do $$
+do \$\$
 declare
   selected_ext pg_extension%rowtype;
 begin  
@@ -654,7 +654,7 @@
   if not found then
      CREATE EXTENSION pg_trgm;
   end if;
-end $$;
+end \$\$;
 
 CREATE INDEX meta_emailowner on MetaEmail (EmailTenant, EmailOwner);
 CREATE INDEX meta_emailtime on MetaEmail (EmailTime);

the "$$" is meant to be passed to the postgresql server, but
unfortunately it's written unquoted in a here document, which is a
context where the shell does variable expansion.  The result is that the
$$ part gets replaced by the shell into its PID, resulting in a line "do
<PID>", which is not a valid way to quote PL/PGSQL code (the thing that
is being attempted here).

Adding backslashes to avoid that variable expansion resolved the issue
for me.

I don't know whether this problem exists in trixie or unstable too, but
thought I'd mention it.

-- System Information:
Debian Release: 12.11
  APT prefers stable-updates
  APT policy: (500, 'stable-updates'), (500, 'stable-security'), (500, 'stable')
Architecture: amd64 (x86_64)

Kernel: Linux 6.1.0-34-amd64 (SMP w/1 CPU thread; PREEMPT)
Locale: LANG=nl_BE.UTF-8, LC_CTYPE=nl_BE.UTF-8 (charmap=UTF-8), LANGUAGE not set
Shell: /bin/sh linked to /usr/bin/dash
Init: systemd (via /run/systemd/system)
LSM: AppArmor: enabled

Versions of packages bacula-director-pgsql depends on:
ii  bacula-common-pgsql                       15.0.3-1~bpo12+1
ii  dbconfig-pgsql                            2.0.24
ii  debconf [debconf-2.0]                     1.5.82
ii  postgresql-client                         15+248
ii  postgresql-client-13 [postgresql-client]  13.11-0+deb11u1
ii  postgresql-client-15 [postgresql-client]  15.13-0+deb12u1

Versions of packages bacula-director-pgsql recommends:
ii  postgresql  15+248

Versions of packages bacula-director-pgsql suggests:
pn  gawk  <none>

-- debconf information:
* bacula-director-pgsql/pgsql/app-pass: (password omitted)
  bacula-director-pgsql/pgsql/admin-pass: (password omitted)
* bacula-director-pgsql/app-password-confirm: (password omitted)
  bacula-director-pgsql/password-confirm: (password omitted)
  bacula-director-pgsql/pgsql/changeconf: false
  bacula-director-pgsql/db/dbname: bacula
  bacula-director-pgsql/pgsql/method: Unix socket
* bacula-director-pgsql/dbconfig-reinstall: false
  bacula-director-pgsql/pgsql/authmethod-user: ident
  bacula-director-pgsql/pgsql/admin-user: postgres
  bacula-director-pgsql/install-error: abort
  bacula-director-pgsql/pgsql/manualconf:
  bacula-director-pgsql/upgrade-error: abort
  bacula-director-pgsql/pgsql/no-empty-passwords:
  bacula-director-pgsql/passwords-do-not-match:
  bacula-director-pgsql/remove-error: abort
  bacula-director-pgsql/purge: false
  bacula-director-pgsql/pgsql/authmethod-admin: ident
  bacula-director-pgsql/internal/reconfiguring: false
  bacula-director-pgsql/upgrade-backup: false
* bacula-director-pgsql/dbconfig-upgrade: false
  bacula-director-pgsql/db/app-user: bacula at localhost
  bacula-director-pgsql/internal/skip-preseed: true
  bacula-director-pgsql/dbconfig-remove: true
  bacula-director-pgsql/remote/newhost:
  bacula-director-pgsql/remote/port:
  bacula-director-pgsql/missing-db-package-error: abort
  bacula-director-pgsql/database-type: pgsql
  bacula-director-pgsql/remote/host: localhost
* bacula-director-pgsql/dbconfig-install: true