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

[Carsten Leonhardt]

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