get_targets query slow - Githubissues

Expected behavior

When editing a task in gsa, it calls the cmd get_targets which ultimately results in gvmd issuing a sql query. This should return the available targets for the dropdown relatively quickly.

Actual behavior

The get_targets command builds an unreasonably slow query. In testing against an instance with ~600 targets, the query can take over 5 minutes, at which point it tends to get cancelled by gsa, making the dropdown unusable. With scans running, I've seen this query take over 15 minutes to return.

Steps to reproduce

Add a large amount of targets/credentials
Edit a task

GVM versions

This has been noticed since at least version 8, but is still present in the most recent releases. I am currently reproducing with the following:

gsa: (gsad --version) 21.4.1 gvm: (gvmd --version) 21.4.2 openvas-scanner: (openvas --version) 21.4.1 gvm-libs: 21.4.1

Environment

Operating system:

Linux 3a45fdc8949c 5.4.72-microsoft-standard-WSL2 #1 SMP Wed Oct 28 23:40:43 UTC 2020 x86_64 GNU/Linux
Distributor ID: Debian
Description:    Debian GNU/Linux 10 (buster)
Release:        10
Codename:       buster

Installation method / source: (packages, source installation) This is being tested currently in docker, although it also exists when installed directly in linux. Pacakages are being checked out from the tagged versions in git and built according to the build directions

Logfiles

I added in a print under https://github.com/greenbone/gvmd/blob/v21.4.2/src/sql_pg.c#L488 that dumps the query when the targets table is being queried like so:

if (strstr(stmt->sql, " FROM targets ")) {
  printf("\n\n\n\n\n\n%s\n", stmt->sql);
}

The query that gets generated from get_targets in 21.4.2 looks something like this:

WITH permissions_subject AS (SELECT *
                                 FROM permissions
                                 WHERE subject_location = 0
                                   AND ((subject_type = 'user' AND subject = (SELECT id
                                                                                  FROM users
                                                                                  WHERE users.uuid = '3f098b34-3caa-4b7d-91ad-008c114cbbeb')) OR
                                        (subject_type = 'group' AND subject IN (SELECT DISTINCT "group"
                                                                                    FROM group_users
                                                                                    WHERE "user" = (SELECT id
                                                                                                        FROM users
                                                                                                        WHERE users.uuid = '3f098b34-3caa-4b7d-91ad-008c114cbbeb'))) OR
                                        (subject_type = 'role' AND subject
                                            IN (SELECT DISTINCT role
                                                    FROM role_users
                                                    WHERE "user" = (SELECT id
                                                                        FROM users
                                                                        WHERE users.uuid = '3f098b34-3caa-4b7d-91ad-008c114cbbeb'))))),
     super_on_users AS (SELECT DISTINCT *
                            FROM (SELECT resource
                                      FROM permissions_subject
                                      WHERE name = 'Super'
                                        AND resource_type = 'user'
                                  UNION
                                  SELECT "user"
                                      FROM role_users
                                      WHERE role IN (SELECT resource
                                                         FROM permissions_subject
                                                         WHERE name = 'Super' AND resource_type = 'role')
                                  UNION
                                  SELECT "user"
                                      FROM group_users
                                      WHERE "group" IN (SELECT resource
                                                            FROM permissions_subject
                                                            WHERE name = 'Super'
                                                              AND resource_type = 'group')) AS all_users)
SELECT id,
       uuid,
       name,
       comment,
       iso_time(creation_time),
       iso_time(modification_time),
       creation_time                                                                                                AS created,
       modification_time                                                                                            AS modified,
       (SELECT name
            FROM users AS inner_users
            WHERE inner_users.id = targets.owner)                                                                   AS _owner,
       owner,
       hosts,
       target_credential(id, 0, CAST('ssh' AS TEXT)),
       target_login_port(id, 0, CAST('ssh' AS TEXT))                                                                AS ssh_port,
       target_credential(id, 0, CAST('smb' AS TEXT)),
       port_list,
       0,
       0,
       (SELECT uuid FROM port_lists WHERE port_lists.id = port_list),
       (SELECT name FROM port_lists WHERE port_lists.id = port_list)                                                AS port_list,
       0,
       exclude_hosts,
       reverse_lookup_only,
       reverse_lookup_unify,
       alive_test,
       target_credential(id, 0, CAST('esxi' AS TEXT)),
       0,
       target_credential(id, 0, CAST('snmp' AS TEXT)),
       0,
       target_credential(id, 0, CAST('elevate' AS TEXT)),
       0,
       allow_simultaneous_ips,
       (SELECT name
            FROM credentials
            WHERE credentials.id =
                  target_credential(targets.id, 0, CAST('ssh' AS TEXT)))                                            AS ssh_credential,
       (SELECT name
            FROM credentials
            WHERE credentials.id =
                  target_credential(targets.id, 0, CAST('smb' AS TEXT)))                                            AS smb_credential,
       (SELECT name
            FROM credentials
            WHERE credentials.id =
                  target_credential(targets.id, 0, CAST('esxi' AS TEXT)))                                           AS esxi_credential,
       (SELECT name
            FROM credentials
            WHERE credentials.id =
                  target_credential(targets.id, 0, CAST('snmp' AS TEXT)))                                           AS snmp_credential,
       (SELECT name
            FROM credentials
            WHERE credentials.id =
                  target_credential(targets.id, 0, CAST('elevate' AS TEXT)))                                        AS ssh_elevate_credential,
       hosts,
       max_hosts(hosts, exclude_hosts)                                                                              AS ips
    FROM targets
    WHERE ((targets.owner = (SELECT id FROM users WHERE users.uuid = '3f098b34-3caa-4b7d-91ad-008c114cbbeb')) OR
           EXISTS(SELECT * FROM permissions_subject WHERE name = 'Super' AND (resource = 0)) OR
           targets.owner IN (SELECT * FROM super_on_users) OR EXISTS(SELECT id
                                                                         FROM permissions_subject
                                                                         WHERE resource = targets.id
                                                                           AND resource_type = 'target'
                                                                           AND resource_location = 0
                                                                           AND (t())))
    ORDER BY LOWER(name) ASC
    LIMIT 5000 OFFSET 0;

Running this against a server with 618 targets and 505 credentials, this query took 4m1s to complete.

The slowness seems to be due to the subqueries fetching credentials at the end. By changing these to LEFT JOINs, the query speed is substantially improved, and as far as I can tell returns the same data:

WITH permissions_subject AS (SELECT *
                                 FROM permissions
                                 WHERE subject_location = 0
                                   AND ((subject_type = 'user' AND subject = (SELECT id
                                                                                  FROM users
                                                                                  WHERE users.uuid = '3f098b34-3caa-4b7d-91ad-008c114cbbeb')) OR
                                        (subject_type = 'group' AND subject IN (SELECT DISTINCT "group"
                                                                                    FROM group_users
                                                                                    WHERE "user" = (SELECT id
                                                                                                        FROM users
                                                                                                        WHERE users.uuid = '3f098b34-3caa-4b7d-91ad-008c114cbbeb'))) OR
                                        (subject_type = 'role' AND subject
                                            IN (SELECT DISTINCT role
                                                    FROM role_users
                                                    WHERE "user" = (SELECT id
                                                                        FROM users
                                                                        WHERE users.uuid = '3f098b34-3caa-4b7d-91ad-008c114cbbeb'))))),
     super_on_users AS (SELECT DISTINCT *
                            FROM (SELECT resource
                                      FROM permissions_subject
                                      WHERE name = 'Super'
                                        AND resource_type = 'user'
                                  UNION
                                  SELECT "user"
                                      FROM role_users
                                      WHERE role IN (SELECT resource
                                                         FROM permissions_subject
                                                         WHERE name = 'Super'
                                                           AND resource_type = 'role')
                                  UNION
                                  SELECT "user"
                                      FROM group_users
                                      WHERE "group" IN (SELECT resource
                                                            FROM permissions_subject
                                                            WHERE name = 'Super'
                                                              AND resource_type = 'group')) AS all_users)
SELECT targets.id,
       targets.uuid,
       targets.name,
       targets.comment,
       iso_time(targets.creation_time),
       iso_time(targets.modification_time),
       targets.creation_time                                         AS created,
       targets.modification_time                                     AS modified,
       (SELECT name
            FROM users AS inner_users
            WHERE inner_users.id = targets.owner)                    AS _owner,
       targets.owner,
       hosts,
       target_credential(targets.id, 0, CAST('ssh' AS TEXT)),
       target_login_port(targets.id, 0, CAST('ssh' AS TEXT))         AS ssh_port,
       target_credential(targets.id, 0, CAST('smb' AS TEXT)),
       port_list,
       0,
       0,
       (SELECT uuid FROM port_lists WHERE port_lists.id = port_list),
       (SELECT name FROM port_lists WHERE port_lists.id = port_list) AS port_list,
       0,
       exclude_hosts,
       reverse_lookup_only,
       reverse_lookup_unify,
       alive_test,
       target_credential(targets.id, 0, CAST('esxi' AS TEXT)),
       0,
       target_credential(targets.id, 0, CAST('snmp' AS TEXT)),
       0,
       target_credential(targets.id, 0, CAST('elevate' AS TEXT)),
       0,
       allow_simultaneous_ips,
       cred_ssh.name                                                 AS ssh_credential,
       cred_smb.name                                                 AS smb_credential,
       cred_esxi.name                                                AS esxi_credential,
       cred_snmp.name                                                AS snmp_credential,
       cred_elevate.name                                             AS ssh_elevate_credential,
       hosts,
       max_hosts(hosts, exclude_hosts)                               AS ips
    FROM targets
             LEFT JOIN targets_login_data tld_ssh ON targets.id = tld_ssh.target AND tld_ssh.type = 'ssh'
             LEFT JOIN credentials cred_ssh ON cred_ssh.id = tld_ssh.credential
             LEFT JOIN targets_login_data tld_smb ON targets.id = tld_smb.target AND tld_smb.type = 'smb'
             LEFT JOIN credentials cred_smb ON cred_smb.id = tld_smb.credential
             LEFT JOIN targets_login_data tld_esxi ON targets.id = tld_esxi.target AND tld_esxi.type = 'esxi'
             LEFT JOIN credentials cred_esxi ON cred_esxi.id = tld_esxi.credential
             LEFT JOIN targets_login_data tld_snmp ON targets.id = tld_snmp.target AND tld_snmp.type = 'snmp'
             LEFT JOIN credentials cred_snmp ON cred_snmp.id = tld_snmp.credential
             LEFT JOIN targets_login_data tld_elevate
                       ON targets.id = tld_elevate.target AND tld_elevate.type = 'elevate'
             LEFT JOIN credentials cred_elevate ON cred_elevate.id = tld_elevate.credential
    WHERE ((targets.owner = (SELECT id FROM users WHERE users.uuid = '3f098b34-3caa-4b7d-91ad-008c114cbbeb')) OR
           EXISTS(SELECT * FROM permissions_subject WHERE name = 'Super' AND (resource = 0)) OR
           targets.owner IN (SELECT * FROM super_on_users) OR EXISTS(SELECT id
                                                                         FROM permissions_subject
                                                                         WHERE resource = targets.id
                                                                           AND resource_type = 'target'
                                                                           AND resource_location = 0
                                                                           AND (t())))
    ORDER BY LOWER(targets.name) ASC
    LIMIT 5000 OFFSET 0;

This query instead returns in 859ms.

I added a little blurb into the sql_exec_internal function to look for this particular query and modify it into one using the LEFT JOINs and the task edit screen response time is massively improved.

I'm not sure where this query is generated, so please let me know if I should be opening this issue on a different project, or if this is not feasible to resolve due to how it is generated and should continue patching as needed.

greenbone / gvmd