gvmd restarts with error: relation “information_schema_catalog_name” does not exist

[jake-hoffer]

On 1 of our 8 test scanners (all running the latest stable release of gvm 10), gvmd restarts continuously, throwing the following error into our postgres log file:

2019-06-19 18:49:13.257 EDT [12032] root@gvmd STATEMENT:  SELECT table_name, column_name,       pg_get_serial_sequence (table_name, column_name)  FROM information_schema.columns  WHERE table_schema = 'public'    AND pg_get_serial_sequence (table_name, column_name)        IS NOT NULL;
2019-06-19 18:49:58.511 EDT [15421] root@gvmd ERROR:  relation "information_schema_catalog_name" does not exist

We tracked the source query down to /src/manage_pg.c line 3284, which contains this query:

SELECT table_name, column_name, pg_get_serial_sequence (table_name, column_name) FROM information_schema.columns WHERE table_schema = 'public' AND pg_get_serial_sequence (table_name, column_name) IS NOT NULL;

We dug into why this error occurred on this machine, and not on any others, and found that it came down to the way postgres plans the query. The call to pg_get_serial_sequence in the WHERE clause throws an error if evaluated before the table_schema = 'public' condition. In most cases, these conditions are evaluated in the order they appear, and pg_get_serial_sequence is only called on things in the public schema. In some cases, however, postgres for some reason decides to evaluate pg_get_serial_sequence first, and chokes on its inability to identify a schema for which it does not have a search path (ie not public).

We rewrote the query into one that always works by using a precomputed CTE to trick postgres' query planner into evaluating the conditions in the desired order:

WITH serial_sequences AS (SELECT table_name, column_name, pg_get_serial_sequence (table_name, column_name) p FROM information_schema.columns WHERE table_schema = 'public') SELECT * FROM serial_sequences WHERE p IS NOT NULL;

Would someone please double-check our suggestion and consider incorporating this (or similar) into forthcoming releases? Thank you!

[asmaack]

After a "sudo reboot", I'm getting the same/similar issue on one of our scanners.

gvmd 8.0.2 will not start and throws the same error:

md manage:WARNING:2019-08-05 10h17.12 utc:1443: sql_exec_internal: SQL: SELECT table_name, column_name,       pg_get_serial_sequence (table_name, column                            _name)  FROM information_schema.columns  WHERE table_schema = 'public'    AND pg_get_serial_sequence (table_name, column_name)        IS NOT NULL;
> md manage:WARNING:2019-08-05 10h17.12 utc:1443: next: sql_exec_internal failed
> md   main:MESSAGE:2019-08-05 10h19.30 utc:1458:    Greenbone Vulnerability Manager version 8.0.2 (GIT revision ee8861e4-gvmd-8.0) (DB revision 205)
> md manage:   INFO:2019-08-05 10h19.30 utc:1458:    Getting scanners.
> md manage:WARNING:2019-08-05 10h19.31 utc:1458: sql_exec_internal: PQexec failed: ERROR:  relation "information_schema_catalog_name" does not exist

I have no working knowledge of Postgres, but it looks like the relation in question is in fact non existent:

gvmd=> \dt information_schema_catalog_name
Did not find any relation named "information_schema_catalog_name".
gvmd=>

[mattmundell]

Thanks for the well explained report.

[d98ama]

Does anyone have a workaround for this (that doesn't involve patching the code)?

If one exists and could be shared, that would be much appreciated!

[asmaack]

Our workaround was to follow the "Handy script to drop all data" from https://github.com/greenbone/gvmd/blob/gvmd-8.0/doc/postgres-HOWTO, then do a sync of the scan server (greenbone-nvt-sync, greenbone-certdata-sync, greenbone-scapdata-sync), create users by hand, and finally import scan configs (and portlist configs) from backups.

[bjoernricks]

Should be fixed with #642

[jake-hoffer]

Thank you for the fix!