[pkg-bacula-devel] Bug#1100649: bacula-director-pgsql: Upgrade wants database extension but doesn't have permission

[Carsten Leonhardt]

Hi,

this doesn't seem to have been forwarded to the list, so FYI.

Probably it would be easiest have dbconfig-common first run that part of
the upgrade as superuser.

Regards

Carsten


-------------------- Start of forwarded message --------------------
Subject: Bug#1100649: bacula-director-pgsql: Upgrade wants database extension but doesn't have permission
From: Carsten Leonhardt <leo at debian.org>
To: Debian Bug Tracking System <submit at bugs.debian.org>
Date: Sun, 16 Mar 2025 16:01:36 +0100

Package: bacula-director-pgsql
Version: 15.0.2-1
Severity: important
File: /usr/share/dbconfig-common/data/bacula-director-pgsql/upgrade/pgsql/15.0.0

(See below for a workaround)

The upgrade contains instructions to install the postgres extension
"pg_trgm" if not already installed, but only the database superuser is
allowed to install it. The database upgrade fails and consequently the
packages upgrade fails too.

Some installations seem to already have the extension installed for the
bacula database. Maybe it depends on the age of first installation?

The error message:

ERROR: permission denied to create extension "pg_trgm" HINT: Must have CREATE privilege on current database to create this extension.
CONTEXT: SQL statement "CREATE EXTENSION pg_trgm" PL/pgSQL function inline_code_block line 11 at SQL statement


The code in question:

do $$
declare
  selected_ext pg_extension%rowtype;
begin  

  select 1 from pg_extension
    into selected_ext
  where extname = 'pg_trgm';
  
  if not found then
     CREATE EXTENSION pg_trgm;
  end if;
end $$;


Workaround:

Execute the following before the upgrade:

echo "CREATE EXTENSION pg_trgm;" | sudo -u postgres psql bacula

-------------------- End of forwarded message --------------------