Ok.. so that is the actual problem. Other queries also use "user" instead of
user and the latest in the repo has it fixed:
/openvas9-3/gvm-7.0.3/src/manage_sql.c
init_iterator (&iterator,
"SELECT max(severity)"
" FROM report_counts"
" WHERE report = %llu"
" AND override = %d"
" AND user = (SELECT id FROM users WHERE uuid = '%s')"
" AND min_qod = %d"
" AND (end_time = 0 or end_time >= m_now ());",
report, overrides, current_credentials.uuid, min_qod);
https://raw.githubusercontent.com/greenbone/gvm/master/src/manage_sql.c
init_iterator (&iterator,
"SELECT max(severity)"
" FROM report_counts"
" WHERE report = %llu"
" AND override = %d"
" AND \"user\" = (SELECT id FROM users WHERE uuid = '%s')"
" AND min_qod = %d"
" AND (end_time = 0 or end_time >= m_now ());",
report, overrides, current_credentials.uuid, min_qod);
So.. a few things are broken in the versions for download at
http://www.openvas.org/install-source.html -_-
Thijs Stuurman
Quality & Security | KPN Internedservices B.V.
[email protected]<mailto:[email protected]> |
[email protected]<mailto:[email protected]>
T: +31(0)299476185 | M: +31(0)624366778
PGP Key-ID: 0x16ADC048 (https://pgp.surfnet.nl/)
Fingerprint: 2EDB 9B42 D6E8 7D4B 6E02 8BE5 6D46 8007 16AD C048
W: https://www.internedservices.nl<https://www.internedservices.nl/> | L:
https://nl.linkedin.com/in/thijsstuurman
Van: Openvas-discuss <[email protected]> Namens Thijs
Stuurman
Verzonden: vrijdag 31 augustus 2018 13:10
Aan: [email protected]
Onderwerp: Re: [Openvas-discuss] PQexec failed: ERROR: operator does not exist:
name = integer
I haven't figured out what changed but I see what is wrong.
The table still looks the same and the query didn't change in the source
between the two versions:
./openvas9/openvas-manager-7.0.1/src/manage_sql.c
init_iterator (&iterator,
"SELECT max(severity)"
" FROM report_counts"
" WHERE report = %llu"
" AND override = %d"
" AND user = (SELECT id FROM users WHERE uuid = '%s')"
" AND min_qod = %d"
" AND (end_time = 0 or end_time >= m_now ());",
report, overrides, current_credentials.uuid, min_qod);
./openvas9-3/gvm-7.0.3/src/manage_sql.c
init_iterator (&iterator,
"SELECT max(severity)"
" FROM report_counts"
" WHERE report = %llu"
" AND override = %d"
" AND user = (SELECT id FROM users WHERE uuid = '%s')"
" AND min_qod = %d"
" AND (end_time = 0 or end_time >= m_now ());",
report, overrides, current_credentials.uuid, min_qod);
Table is defined as:
CREATE TABLE public.report_counts (
id integer NOT NULL,
report integer,
"user" integer,
severity numeric,
count integer,
override integer,
end_time integer,
min_qod integer
);
Seems to me, the column user doesn't exist because its "user" or user is now a
reserved word since.. not sure:
tasks=> SELECT max(severity) FROM report_counts WHERE report = 3913 AND
override = 1 AND asdfuser = (SELECT id FROM users WHERE uuid =
'aa9e7cb5-3ad9-41fd-95d5-e15716067b20') AND min_qod = 70 AND (end_time = 0 or
end_time >= m_now ());
ERROR: column "asdfuser" does not exist
LINE 1: ...t_counts WHERE report = 3913 AND override = 1 AND asdfuser =...
^
tasks=> SELECT max(severity) FROM report_counts WHERE report = 3913 AND
override = 1 AND user = (SELECT id FROM users WHERE uuid =
'aa9e7cb5-3ad9-41fd-95d5-e15716067b20') AND min_qod = 70 AND (end_time = 0 or
end_time >= m_now ());
ERROR: operator does not exist: name = integer
LINE 1: ...nts WHERE report = 3913 AND override = 1 AND user = (SELECT ...
^
HINT: No operator matches the given name and argument type(s). You might need
to add explicit type casts.
tasks=> SELECT max(severity) FROM report_counts WHERE report = 3913 AND
override = 1 AND "user" = (SELECT id FROM users WHERE uuid =
'aa9e7cb5-3ad9-41fd-95d5-e15716067b20') AND min_qod = 70 AND (end_time = 0 or
end_time >= m_now ());
max
------
10.0
(1 row)
Now it's tempting to change the source and recompile but I rather find out
where and when this broke; why isn't anyone else running in to this?
Thijs Stuurman
Quality & Security | KPN Internedservices B.V.
[email protected]<mailto:[email protected]> |
[email protected]<mailto:[email protected]>
T: +31(0)299476185 | M: +31(0)624366778
PGP Key-ID: 0x16ADC048 (https://pgp.surfnet.nl/)
Fingerprint: 2EDB 9B42 D6E8 7D4B 6E02 8BE5 6D46 8007 16AD C048
W: https://www.internedservices.nl<https://www.internedservices.nl/> | L:
https://nl.linkedin.com/in/thijsstuurman
Van: Openvas-discuss
<[email protected]<mailto:[email protected]>>
Namens Thijs Stuurman
Verzonden: vrijdag 31 augustus 2018 11:39
Aan:
[email protected]<mailto:[email protected]>
Onderwerp: [Openvas-discuss] PQexec failed: ERROR: operator does not exist:
name = integer
OpenVAS discuss,
I had some performance issues so I decided to reinstall OpenVAS on all my nodes
from:
openvas-libraries-9.0.1.tar.gz
openvas-manager-7.0.1.tar.gz
openvas-scanner-5.1.1.tar.gz
to:
openvas-libraries-9.0.3.tar.gz
openvas-manager-7.0.3.tar.gz
v5.1.3.tar.gz
(openvas-scanner tar.gz name is wrong but that's how it gets downloaded from
the site).
All good, setup everything again; using my existing database (tried openvasmd
-migrate, does nothing).
Slaves up and running, CA certs replaced etc'.
Now, everything runs very well but when a scan ends there is an SQL error:
"""
event task:MESSAGE:2018-08-31 11h30.40 CEST:16331: Status of task domain_ka -
officeclient (8fb8bc4a-aa53-480a-a4f2-0967864c313f) has changed to Done
md manage:WARNING:2018-08-31 11h30.40 CEST:16331: sql_exec_internal: PQexec
failed: ERROR: operator does not exist: name = integer
LINE 1: ...nts WHERE report = 4469 AND override = 1 AND user = (SELECT ...
^
HINT: No operator matches the given name and argument type(s). You might need
to add explicit type casts.
(7)
md manage:WARNING:2018-08-31 11h30.40 CEST:16331: sql_exec_internal: SQL:
SELECT max(severity) FROM report_counts WHERE report = 4469 AND override = 1
AND user = (SELECT id FROM users WHERE uuid =
'aa9e7cb5-3ad9-41fd-95d5-e15716067b20') AND min_qod = 70 AND (end_time = 0 or
end_time >= m_now ());
md manage:WARNING:2018-08-31 11h30.40 CEST:16331: next: sql_exec_internal failed
md manage:WARNING:2018-08-31 11h30.40 CEST:16331: manage_cleanup_process_error:
Error exit, setting running task to Internal Error
event task:MESSAGE:2018-08-31 11h30.40 CEST:16331: Status of task domain_ka -
officeclient (8fb8bc4a-aa53-480a-a4f2-0967864c313f) has changed to Internal
Error
"""
Looking at my database, "'aa9e7cb5-3ad9-41fd-95d5-e15716067b20'" is the UUID
for the Admin user which I use to start the task:
"""
cat tasks.sql | grep aa9e7cb5-3ad9-41fd-95d5-e15716067b20
1 aa9e7cb5-3ad9-41fd-95d5-e15716067b20 \N admin \N
"""
I don't think I have changed enough to suddenly cause this error so I am not
sure where to even begin on this one.
Everything runs fine, the job results are ok .. but it cannot go from 100% to
status Done on a task because of this error.
Anyone who has an idea or experienced this before?
Thijs Stuurman
Quality & Security | KPN Internedservices B.V.
[email protected]<mailto:[email protected]> |
[email protected]<mailto:[email protected]>
T: +31(0)299476185 | M: +31(0)624366778
PGP Key-ID: 0x16ADC048 (https://pgp.surfnet.nl/)
Fingerprint: 2EDB 9B42 D6E8 7D4B 6E02 8BE5 6D46 8007 16AD C048
W: https://www.internedservices.nl<https://www.internedservices.nl/> | L:
https://nl.linkedin.com/in/thijsstuurman
_______________________________________________
Openvas-discuss mailing list
[email protected]
https://lists.wald.intevation.org/cgi-bin/mailman/listinfo/openvas-discuss
