Database Migration from 21.04 -> 22.04

Building from Source and Advanced Topics
1

pg_gvm=v22.4.0
notus_scanner=v22.4.0
gvmd=v22.4.0
openvas=v22.4.0
openvas_smb=v22.4.0
gvm_libs=v22.4.0
openvas_scanner=v22.4.0
gsa=v22.4.0
ospd_openvas=v22.4.0
python_gvm=v22.7.0
gvm_tools=v22.6.1

Codebase: GitHub - immauss/openvas: Containers for running the Greenbone Vulnerability Manager. Run as a single container with all services or separate single applications containers via docker-compose.

I’m working on rebuilding my container to 22.4. I have everything working quite well, as long as I build a fresh new Database. However, when I try to migrate from a 21.04 database, I get the below errors. This is with a clean fresh 21.04 database nothing but feeds synced. ( not targets, tasks, etc.)

root@6e5ab7738496:/var/log/gvm# su -c "gvmd --migrate " gvm 

(gvmd:1370): md manage-WARNING **: 12:08:52.810: sql_exec_internal: PQexec failed: ERROR:  function hosts_contains(text,text) is not a member of extension "pg-gvm"
DETAIL:  An extension is not allowed to replace an object that it does not own.
 (7)

(gvmd:1370): md manage-WARNING **: 12:08:52.810: sql_exec_internal: SQL: CREATE EXTENSION IF NOT EXISTS "pg-gvm"

(gvmd:1370): md manage-WARNING **: 12:08:52.810: sqlv: sql_exec_internal failed
root@6e5ab7738496:/var/log/gvm# !tail
tail -f gvmd.log 
md   main:MESSAGE:2022-08-19 12h08.19 utc:1355:    Greenbone Vulnerability Manager version 22.4.0~dev1 (DB revision 250)
md manage:MESSAGE:2022-08-19 12h08.19 utc:1356: check_db_versions: database version of database: 242
md manage:MESSAGE:2022-08-19 12h08.19 utc:1356: check_db_versions: database version supported by manager: 250
md   main:CRITICAL:2022-08-19 12h08.19 utc:1356: gvmd: database is wrong version

Any ideas ?

2

Bumping this.
I’m still stumped on this and looking for ideas on how to resolve.
@DeeAnn Can you help ?

Thank you,
Scott

3

Hi Scott,

Something to check is to make sure you’re migrating the database as the gvm user.

1 Like
4

Thanks @DeeAnn … I’m definitely using the gvm user. It fails very differently if I try to do it as root. :slight_smile:

5

Digging in I find:

Operational 21.4:

Extensions:

postgres=# \dx 
                 List of installed extensions
  Name   | Version |   Schema   |         Description          
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

Functions:

postgres=# \df public.*
                       List of functions
 Schema | Name | Result data type | Argument data types | Type 
--------+------+------------------+---------------------+------
(0 rows)

Operational 22.4 with clean fresh DB.

Extensions:

postgres=# \dx 
                 List of installed extensions
  Name   | Version |   Schema   |         Description          
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

Functions:

postgres=# \df
                       List of functions
 Schema | Name | Result data type | Argument data types | Type 
--------+------+------------------+---------------------+------
(0 rows)

Executing the command to add the extension from psql does not produce any errors.


postgres=# CREATE EXTENSION IF NOT EXISTS "pg-gvm"
postgres-# ;
CREATE EXTENSION
postgres=# \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description          
---------+---------+------------+------------------------------
 pg-gvm  | 22.4.0  | public     | Functions for GVMd
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language

postgres=# \df public.*
                                List of functions
 Schema |      Name      | Result data type |     Argument data types     | Type 
--------+----------------+------------------+-----------------------------+------
 public | hosts_contains | boolean          | text, text                  | func
 public | max_hosts      | integer          | text, text                  | func
 public | next_time_ical | integer          | text, bigint, text          | func
 public | next_time_ical | integer          | text, bigint, text, integer | func
 public | regexp         | boolean          | text, text                  | func
(5 rows)

I’m perplexed. It would seem that the running instances don’t have the extension registered.

I’ve tried removing the extension and then running the migration with the same result as well as running the migration after registering the extension manually

6

Yep, that seems weird. I’m bumping this to see if anyone has an idea.

1 Like
7

Not sure why it did not show in my previous data gathering, but rebuilt my image with a manually generated clean 22.4 DB and the functions and extension are listed. But migrations from 21.4 still fail with the same errors.

postgres=# \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description          
---------+---------+------------+------------------------------
 pg-gvm  | 22.4.0  | public     | Functions for GVMd
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

postgres=# \df
                                List of functions
 Schema |      Name      | Result data type |     Argument data types     | Type 
--------+----------------+------------------+-----------------------------+------
 public | hosts_contains | boolean          | text, text                  | func
 public | max_hosts      | integer          | text, text                  | func
 public | next_time_ical | integer          | text, bigint, text          | func
 public | next_time_ical | integer          | text, bigint, text, integer | func
 public | regexp         | boolean          | text, text                  | func
8

UPDATE:
The error message makes me think it has something to do with ownership of the functions within postgres. ( Things owned by postgres user instead of gvm.) since gvmd is using the “gvm” user in the database and it doesn’t seem to be able to make the changes.

I tried altering the DB to change the ownership of the “hosts_contains(text,text)” function to the gvm user, I expected to get an error for a new function that needed to be changed, but still failed with the same error.

SO … I modified my 21.4 image to add the extensions for uuid-ossp and pgcrypto as the gvm user in the database vs the postgres user. Thinking maybe there was an underlying ‘something’ that was getting created with the postgres user vs the gvm user.

I still get the same error.

I’m relatively certain this has something to do with how the original DB is being created, but I just dont’ understand either postgress or the gvmd database schema well enough to figure where the problem is. If I someone who understand the DB schema better could give me an idea what’s wrong at that level, I could probably figure out how to resolve on my end.

Thanks,
Scott

9

OK … After many trials and failures…
.
.
Success !!!
Though I had to take the nuclear option.
I assumed that if I dropped all the functions/extensions/operators/view etc …
that a “gvmd -m” would recreate them all.
I was “almost” right.

I created a long list of sql commands to drop everything, and the first time I tried to migrate, it failed because it could not find the “task_severity” function. So I started what I feared would be a long and painful journey of commenting out what caused failures and trying again.

But luckily, that was the only function that caused an issue, and the very next attempt WORKED !!

gvmd -m completed successfully in about 10 minutes.

Now I still need to do some testing to make sure it really works.

However … @DeeAnn … I feel like there may be a bug here. Shouldn’t the gvmd --migrate be able to handle this better?

I also feel like there should be a better option than the nuclear option.

1 Like
10

For anyone that wants the full nuclear option:

DROP EXTENSION IF EXISTS  pgcrypto CASCADE;
DROP EXTENSION IF EXISTS  "uuid-ossp" CASCADE;
DROP VIEW IF EXISTS  result_new_severities CASCADE;
DROP VIEW IF EXISTS  result_new_severities_dynamic CASCADE;
DROP VIEW IF EXISTS  result_new_severities_static CASCADE;
DROP VIEW IF EXISTS  result_overrides CASCADE;
DROP VIEW IF EXISTS  tls_certificate_source_origins CASCADE;
DROP VIEW IF EXISTS  vulns CASCADE;
DROP FUNCTION IF EXISTS certificate_iso_time(bigint)  CASCADE;
DROP FUNCTION IF EXISTS group_concat_pair(text,text,text)  CASCADE;
DROP FUNCTION IF EXISTS cpe_title(text)  CASCADE;
DROP FUNCTION IF EXISTS common_cve(text,text)  CASCADE;
DROP FUNCTION IF EXISTS create_index(text,text,text,text)  CASCADE;
DROP FUNCTION IF EXISTS gvmd_user()  CASCADE;
DROP FUNCTION IF EXISTS credential_value(integer,integer,text)  CASCADE;
DROP FUNCTION IF EXISTS hosts_contains(text,text)  CASCADE;
DROP FUNCTION IF EXISTS dynamic_severity()  CASCADE;
DROP FUNCTION IF EXISTS current_severity(real,text)  CASCADE;
DROP FUNCTION IF EXISTS create_index(text,text,text)  CASCADE;
DROP FUNCTION IF EXISTS order_inet(text)  CASCADE;
DROP FUNCTION IF EXISTS next_time_ical(text,text)  CASCADE;
DROP FUNCTION IF EXISTS iso_time(bigint)  CASCADE;
DROP FUNCTION IF EXISTS m_now()  CASCADE;
DROP FUNCTION IF EXISTS order_port(text)  CASCADE;
DROP FUNCTION IF EXISTS order_role(text)  CASCADE;
DROP FUNCTION IF EXISTS order_threat(text)  CASCADE;
DROP FUNCTION IF EXISTS make_uuid()  CASCADE;
DROP FUNCTION IF EXISTS next_time_ical(text,text,integer)  CASCADE;
DROP FUNCTION IF EXISTS regexp(text,text)  CASCADE;
DROP FUNCTION IF EXISTS quote_ident_split(text)  CASCADE;
DROP FUNCTION IF EXISTS max_hosts(text,text)  CASCADE;
DROP FUNCTION IF EXISTS lower(integer)  CASCADE;
DROP FUNCTION IF EXISTS quote_ident_list(text)  CASCADE;
DROP FUNCTION IF EXISTS level_min_severity(text,text)  CASCADE;
DROP FUNCTION IF EXISTS severity_matches_ov(double precision,double precision)  CASCADE;
DROP FUNCTION IF EXISTS resource_name(text,text,integer)  CASCADE;
DROP FUNCTION IF EXISTS report_active(integer)  CASCADE;
DROP FUNCTION IF EXISTS report_result_host_count(integer,integer)  CASCADE;
DROP FUNCTION IF EXISTS severity_class()  CASCADE;
DROP FUNCTION IF EXISTS run_status_name(integer)  CASCADE;
DROP FUNCTION IF EXISTS severity_in_level(double precision,text)  CASCADE;
DROP FUNCTION IF EXISTS report_progress(integer)  CASCADE;
DROP FUNCTION IF EXISTS severity_in_levels(double precision,text[])  CASCADE;
DROP FUNCTION IF EXISTS report_host_count(integer)  CASCADE;
DROP FUNCTION IF EXISTS severity_to_level(double precision,integer)  CASCADE;
DROP FUNCTION IF EXISTS trash_target_credential_location(integer,text)  CASCADE;
DROP FUNCTION IF EXISTS user_has_super_on_resource(text,integer)  CASCADE;
DROP FUNCTION IF EXISTS task_threat_level(integer,integer,integer)  CASCADE;
DROP FUNCTION IF EXISTS target_login_port(integer,integer,text)  CASCADE;
DROP FUNCTION IF EXISTS t()  CASCADE;
DROP FUNCTION IF EXISTS task_last_report(integer)  CASCADE;
DROP FUNCTION IF EXISTS target_credential(integer,integer,text)  CASCADE;
DROP FUNCTION IF EXISTS try_exclusive_lock(regclass)  CASCADE;
DROP FUNCTION IF EXISTS task_second_last_report(integer)  CASCADE;
DROP FUNCTION IF EXISTS severity_to_type(double precision)  CASCADE;
DROP FUNCTION IF EXISTS uniquify(text,text,integer,text)  CASCADE;
DROP FUNCTION IF EXISTS user_has_access_uuid(text,text,text,integer)  CASCADE;
DROP FUNCTION IF EXISTS user_can_everything(text)  CASCADE;
DROP FUNCTION IF EXISTS iso_time(bigint,text)  CASCADE;
DROP FUNCTION IF EXISTS vts_verification_str()  CASCADE;
DROP FUNCTION IF EXISTS days_from_now(bigint)  CASCADE;
DROP FUNCTION IF EXISTS vuln_results(text,bigint,bigint,text)  CASCADE;
DROP FUNCTION IF EXISTS vuln_results_exist(text,bigint,bigint,text)  CASCADE;
DROP FUNCTION IF EXISTS level_max_severity(text,text)  CASCADE;
DROP FUNCTION IF EXISTS task_trend(integer,integer,integer)  CASCADE;
DROP FUNCTION IF EXISTS severity_to_level(text,integer)  CASCADE;
DROP FUNCTION IF EXISTS report_severity(integer,integer,integer)  CASCADE;
DROP FUNCTION IF EXISTS report_severity_count(integer,integer,integer,text)  CASCADE;
DROP FUNCTION IF EXISTS user_owns(text,integer)  CASCADE;
DROP AGGREGATE IF EXISTS group_concat(text,text) CASCADE;
DROP FUNCTION IF EXISTS regexp(text,text) CASCADE; 
DROP FUNCTION IF EXISTS group_concat_pair(text,text,text)  CASCADE;

It should also live here for the foreseeable future.

https://github.com/immauss/openvas/blob/22.4.0/scripts/21.4-to-22.4-prep.sql

-Scott

2 Likes
11

Cool! And yep, as far as I know it should migrate cleanly and I’m glad you found what it tripped on. I’ll pass this on internally and thank you :slight_smile:

12

Hi,

Thanks for the information you provided.

I was able to use a slightly less nuclear option by dropping only :

DROP FUNCTION IF EXISTS hosts_contains(text,text) CASCADE;
DROP FUNCTION IF EXISTS max_hosts(text,text) CASCADE;
DROP FUNCTION IF EXISTS regexp(text,text) CASCADE;

which were the error resulting of installing the extension using :
CREATE EXTENSION “pg-gvm”;

Regards.

1 Like