Create functions.
115 static int created = 0;
121 if (
sql_int (
"SELECT count (*) FROM pg_available_extensions" 122 " WHERE name = 'uuid-ossp' AND installed_version IS NOT NULL;")
125 g_warning (
"%s: PostgreSQL extension uuid-ossp required", __FUNCTION__);
131 sql (
"SET role dba;");
133 sql (
"CREATE OR REPLACE FUNCTION max_hosts (text, text)" 135 " AS '%s/openvasmd/pg/libmanage-pg-server', 'sql_max_hosts'" 137 OPENVAS_LIB_INSTALL_DIR);
139 sql (
"CREATE OR REPLACE FUNCTION level_max_severity (text, text)" 140 " RETURNS double precision" 141 " AS '%s/openvasmd/pg/libmanage-pg-server', 'sql_level_max_severity'" 143 OPENVAS_LIB_INSTALL_DIR);
145 sql (
"CREATE OR REPLACE FUNCTION level_min_severity (text, text)" 146 " RETURNS double precision" 147 " AS '%s/openvasmd/pg/libmanage-pg-server', 'sql_level_min_severity'" 149 OPENVAS_LIB_INSTALL_DIR);
151 sql (
"CREATE OR REPLACE FUNCTION next_time (integer, integer, integer)" 153 " AS '%s/openvasmd/pg/libmanage-pg-server', 'sql_next_time'" 155 OPENVAS_LIB_INSTALL_DIR);
157 sql (
"CREATE OR REPLACE FUNCTION next_time (integer, integer, integer, text)" 159 " AS '%s/openvasmd/pg/libmanage-pg-server', 'sql_next_time'" 161 OPENVAS_LIB_INSTALL_DIR);
163 sql (
"CREATE OR REPLACE FUNCTION next_time (integer, integer, integer, text, integer)" 165 " AS '%s/openvasmd/pg/libmanage-pg-server', 'sql_next_time'" 167 OPENVAS_LIB_INSTALL_DIR);
169 sql (
"CREATE OR REPLACE FUNCTION severity_matches_ov (double precision," 172 " AS '%s/openvasmd/pg/libmanage-pg-server', 'sql_severity_matches_ov'" 175 OPENVAS_LIB_INSTALL_DIR);
177 sql (
"CREATE OR REPLACE FUNCTION valid_db_resource_type (text)" 179 " AS '%s/openvasmd/pg/libmanage-pg-server', 'sql_valid_db_resource_type'" 181 OPENVAS_LIB_INSTALL_DIR);
183 sql (
"CREATE OR REPLACE FUNCTION regexp (text, text)" 185 " AS '%s/openvasmd/pg/libmanage-pg-server', 'sql_regexp'" 187 OPENVAS_LIB_INSTALL_DIR);
189 if (
sql_int (
"SELECT count(*) FROM pg_operator" 190 " WHERE oprname = '?~#';")
193 sql (
"CREATE OPERATOR ?~#" 194 " (PROCEDURE = regexp, LEFTARG = text, RIGHTARG = text);");
201 if (
sql_int (
"SELECT EXISTS (SELECT * FROM information_schema.tables" 202 " WHERE table_catalog = '%s'" 203 " AND table_schema = 'public'" 204 " AND table_name = 'meta')" 208 sql (
"CREATE OR REPLACE FUNCTION resource_name (text, text, integer)" 209 " RETURNS text AS $$" 212 " execute_name text;" 215 " WHEN NOT valid_db_resource_type ($1)" 216 " THEN RAISE EXCEPTION 'Invalid resource type argument: %', $1;" 219 " THEN RETURN (SELECT 'Note for: '" 222 " WHERE nvts.uuid = notes.nvt)" 226 " THEN RETURN (SELECT 'Note for: '" 229 " WHERE nvts.uuid = notes_trash.nvt)" 232 " WHEN $1 = 'override'" 234 " THEN RETURN (SELECT 'Override for: '" 237 " WHERE nvts.uuid = overrides.nvt)" 240 " WHEN $1 = 'override'" 241 " THEN RETURN (SELECT 'Override for: '" 244 " WHERE nvts.uuid = overrides_trash.nvt)" 245 " FROM overrides_trash" 247 " WHEN $1 = 'report'" 248 " THEN RETURN (SELECT (SELECT name FROM tasks WHERE id = task)" 251 " CASE (SELECT end_time FROM tasks" 254 " ELSE (SELECT end_time::text" 255 " FROM tasks WHERE id = task)" 259 " WHEN $1 = 'result'" 260 " THEN RETURN (SELECT (SELECT name FROM tasks WHERE id = task)" 262 " || (SELECT name FROM nvts WHERE oid = nvt)" 265 " CASE (SELECT end_time FROM tasks" 268 " ELSE (SELECT end_time::text" 269 " FROM tasks WHERE id = task)" 274 " THEN RETURN (SELECT name FROM tasks WHERE uuid = $2);" 276 " THEN EXECUTE 'SELECT name FROM ' || $1 || 's" 280 " RETURN execute_name;" 281 " WHEN $1 NOT IN ('nvt', 'cpe', 'cve', 'ovaldef', 'cert_bund_adv'," 282 " 'dfn_cert_adv', 'report', 'result', 'user')" 283 " THEN EXECUTE 'SELECT name FROM ' || $1 || 's_trash" 287 " RETURN execute_name;" 291 "$$ LANGUAGE plpgsql;");
296 sql (
"CREATE OR REPLACE FUNCTION report_progress_active (integer)" 297 " RETURNS integer AS $$" 300 " report_task integer;" 301 " task_target integer;" 302 " target_hosts text;" 303 " target_exclude_hosts text;" 306 " maximum_hosts integer;" 307 " total_progress integer;" 308 " report_host record;" 309 " dead_hosts integer;" 313 " report_task := (SELECT task FROM reports WHERE id = $1);" 314 " task_target := (SELECT target FROM tasks WHERE id = report_task);" 315 " IF task_target IS NULL THEN" 316 " target_hosts := NULL;" 317 " target_exclude_hosts := NULL;" 319 " FROM tasks WHERE id = report_task)" 321 " target_hosts := (SELECT hosts FROM targets_trash" 322 " WHERE id = task_target);" 323 " target_exclude_hosts := (SELECT exclude_hosts FROM targets_trash" 324 " WHERE id = task_target);" 326 " target_hosts := (SELECT hosts FROM targets" 327 " WHERE id = task_target);" 328 " target_exclude_hosts := (SELECT exclude_hosts FROM targets" 329 " WHERE id = task_target);" 331 " IF target_hosts IS NULL THEN" 334 " maximum_hosts := max_hosts (target_hosts, target_exclude_hosts);" 335 " IF maximum_hosts = 0 THEN" 338 " FOR report_host IN SELECT current_port, max_port" 339 " FROM report_hosts WHERE report = $1" 341 " IF report_host.max_port = -1 THEN" 343 " dead_hosts := dead_hosts + 1;" 344 " ELSEIF report_host.max_port IS NOT NULL" 345 " AND report_host.max_port != 0" 347 " progress := (report_host.current_port * 100)" 348 " / report_host.max_port;" 349 " ELSIF report_host.current_port IS NULL" 350 " OR report_host.current_port = 0" 356 " total := total + progress;" 358 " IF (maximum_hosts - dead_hosts) > 0 THEN" 359 " total_progress := total / (maximum_hosts - dead_hosts);" 361 " total_progress := 0;" 363 " IF total_progress = 0 THEN" 365 " ELSIF total_progress = 100 THEN" 368 " RETURN total_progress;" 370 "$$ LANGUAGE plpgsql;");
372 sql (
"CREATE OR REPLACE FUNCTION order_inet (text)" 373 " RETURNS text AS $$" 375 " IF $1 ~ '^[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}$' THEN" 377 " || to_char (split_part ($1, '.', 1)::integer, 'fm000')" 379 " || to_char (split_part ($1, '.', 2)::integer, 'fm000')" 381 " || to_char (split_part ($1, '.', 3)::integer, 'fm000')" 383 " || to_char (split_part ($1, '.', 4)::integer, 'fm000');" 388 "$$ LANGUAGE plpgsql" 391 sql (
"CREATE OR REPLACE FUNCTION order_message_type (text)" 392 " RETURNS integer AS $$" 394 " IF $1 = 'Security Hole' THEN" 396 " ELSIF $1 = 'Security Warning' THEN" 398 " ELSIF $1 = 'Security Note' THEN" 400 " ELSIF $1 = 'Log Message' THEN" 402 " ELSIF $1 = 'Debug Message' THEN" 404 " ELSIF $1 = 'Error Message' THEN" 410 "$$ LANGUAGE plpgsql" 413 sql (
"CREATE OR REPLACE FUNCTION order_port (text)" 414 " RETURNS integer AS $$" 416 " IF $1 ~ '^[0-9]+' THEN" 417 " RETURN CAST (substring ($1, '^[0-9]+') as integer);" 418 " ELSIF $1 ~ '^[^0-9]* \\([0-9]+/' THEN" 419 " RETURN CAST (substring ($1, '^[^0-9]* \\(([0-9]+)/') as integer);" 424 "$$ LANGUAGE plpgsql" 427 sql (
"CREATE OR REPLACE FUNCTION order_role (text)" 428 " RETURNS text AS $$" 430 " IF $1 = 'Admin' THEN" 436 "$$ LANGUAGE plpgsql" 439 sql (
"CREATE OR REPLACE FUNCTION order_threat (text)" 440 " RETURNS integer AS $$" 442 " IF $1 = 'High' THEN" 444 " ELSIF $1 = 'Medium' THEN" 446 " ELSIF $1 = 'Low' THEN" 448 " ELSIF $1 = 'Log' THEN" 450 " ELSIF $1 = 'Debug' THEN" 452 " ELSIF $1 = 'False Positive' THEN" 454 " ELSIF $1 = 'None' THEN" 460 "$$ LANGUAGE plpgsql" 463 sql (
"CREATE OR REPLACE FUNCTION severity_to_type (double precision)" 464 " RETURNS text AS $$" 466 " IF $1 IS NULL THEN" 469 " RETURN 'Log Message';" 471 " RETURN 'False Positive';" 473 " RETURN 'Debug Message';" 475 " RETURN 'Error Message';" 476 " ELSIF $1 > 0.0 AND $1 <= 10.0 THEN" 479 " RAISE EXCEPTION 'Invalid severity score given: %', $1;" 482 "$$ LANGUAGE plpgsql" 485 sql (
"CREATE OR REPLACE FUNCTION iso_time (seconds integer)" 486 " RETURNS text AS $$" 489 " user_offset interval;" 492 " coalesce ((SELECT tz_override FROM current_credentials)," 493 " (SELECT timezone FROM users" 494 " WHERE uuid = (SELECT uuid" 495 " FROM current_credentials)));" 497 " user_offset := age (now () AT TIME ZONE user_zone," 498 " now () AT TIME ZONE 'UTC');" 499 " EXCEPTION WHEN invalid_parameter_value THEN" 500 " user_zone = 'UTC';" 506 " WHEN user_zone IS NULL" 507 " OR EXTRACT (EPOCH FROM user_offset) = 0" 508 " THEN to_char (to_timestamp ($1) AT TIME ZONE 'UTC'," 510 " || to_char (to_timestamp ($1) AT TIME ZONE 'UTC'," 512 " ELSE to_char (to_timestamp ($1) AT TIME ZONE user_zone," 514 " || to_char (to_timestamp ($1) AT TIME ZONE user_zone," 516 " || CASE WHEN (extract (epoch FROM user_offset) > 0)" 517 " THEN '+' ELSE '' END" 518 " || to_char (extract (hours FROM user_offset)::integer," 521 " || to_char (abs (extract (minutes FROM user_offset)" 526 "$$ LANGUAGE plpgsql;");
528 sql (
"CREATE OR REPLACE FUNCTION days_from_now (seconds integer)" 529 " RETURNS integer AS $$" 533 " diff := age ( to_timestamp( seconds ), now() );" 537 " WHEN diff < interval '0 seconds'" 539 " ELSE date_part( 'day', diff )" 542 "$$ LANGUAGE plpgsql" 545 sql (
"CREATE OR REPLACE FUNCTION uniquify (type text, proposed_name text," 546 " owner integer, suffix text)" 547 " RETURNS text AS $$" 549 " number integer := 1;" 550 " candidate text := '';" 551 " separator text := ' ';" 552 " unique_candidate boolean;" 554 " IF type = 'user' THEN separator := '_'; END IF;" 555 " candidate := proposed_name || suffix || separator || number::text;" 557 " EXECUTE 'SELECT count (*) = 0 FROM ' || type || 's" 559 " AND ((owner IS NULL) OR (owner = $2))'" 560 " INTO unique_candidate" 561 " USING candidate, owner;" 562 " EXIT WHEN unique_candidate;" 563 " number := number + 1;" 564 " candidate := proposed_name || suffix || separator || number::text;" 568 "$$ LANGUAGE plpgsql;");
570 sql (
"CREATE OR REPLACE FUNCTION create_index (schema_name text," 574 " RETURNS void AS $$" 576 " IF (SELECT count(*) = 0 FROM pg_indexes" 577 " WHERE schemaname = lower (schema_name)" 578 " AND tablename = lower (table_name)" 579 " AND indexname = lower (index_name))" 581 " EXECUTE 'CREATE INDEX ' || index_name" 582 " || ' ON ' || table_name || ' (' || columns || ');';" 585 "$$ LANGUAGE plpgsql;");
587 sql (
"CREATE OR REPLACE FUNCTION create_index (index_name text," 590 " RETURNS void AS $$" 592 " PERFORM create_index ('public', index_name, table_name, columns);" 594 "$$ LANGUAGE plpgsql;");
596 sql (
"CREATE OR REPLACE FUNCTION user_has_super_on_resource (arg_type text, arg_id integer)" 597 " RETURNS boolean AS $$" 606 " EXISTS (SELECT * FROM permissions" 607 " WHERE name = ''Super''" 609 " AND ((resource = 0)" 611 " OR ((resource_type = ''user'')" 612 " AND (resource = (SELECT ' || $1 || 's.owner" 613 " FROM ' || $1 || 's" 616 " OR ((resource_type = ''role'')" 618 " IN (SELECT DISTINCT role" 621 " = (SELECT ' || $1 || 's.owner" 622 " FROM ' || $1 || 's" 625 " OR ((resource_type = ''group'')" 627 " IN (SELECT DISTINCT \"group\"" 630 " = (SELECT ' || $1 || 's.owner" 631 " FROM ' || $1 || 's" 632 " WHERE id = $2)))))" 634 " AND ((subject_type = ''user''" 636 " = (SELECT id FROM users" 639 " FROM current_credentials)))" 640 " OR (subject_type = ''group''" 642 " IN (SELECT DISTINCT \"group\"" 650 " FROM current_credentials))))" 651 " OR (subject_type = ''role''" 653 " IN (SELECT DISTINCT role" 661 " FROM current_credentials))))))'" 662 " USING arg_type, arg_id" 666 "$$ LANGUAGE plpgsql;");
668 sql (
"CREATE OR REPLACE FUNCTION user_owns (arg_type text, arg_id integer)" 669 " RETURNS boolean AS $$" 677 " WHEN arg_type = 'nvt'" 678 " OR arg_type = 'cve'" 679 " OR arg_type = 'cpe'" 680 " OR arg_type = 'ovaldef'" 681 " OR arg_type = 'cert_bund_adv'" 682 " OR arg_type = 'dfn_cert_adv'" 684 " WHEN user_has_super_on_resource (arg_type, arg_id)" 686 " WHEN arg_type = 'result'" 688 " WHEN EXISTS (SELECT * FROM results, reports" 689 " WHERE results.id = arg_id" 690 " AND results.report = reports.id" 691 " AND ((reports.owner IS NULL)" 693 " = (SELECT id FROM users" 696 " FROM current_credentials)))))" 698 " ELSE RETURN false;" 700 " WHEN arg_type = 'task'" 702 " WHEN EXISTS (SELECT * FROM tasks" 705 " AND ((owner IS NULL)" 707 " = (SELECT id FROM users" 710 " FROM current_credentials)))))" 712 " ELSE RETURN false;" 716 " 'SELECT EXISTS (SELECT * FROM ' || $1 || 's" 718 " AND ((owner IS NULL)" 719 " OR (owner = (SELECT id FROM users" 720 " WHERE users.uuid = (SELECT uuid" 721 " FROM current_credentials))))'" 722 " USING arg_type, arg_id" 727 "$$ LANGUAGE plpgsql;");
731 sql (
"CREATE OR REPLACE FUNCTION t () RETURNS boolean AS $$" 736 sql (
"CREATE OR REPLACE FUNCTION m_now () RETURNS integer AS $$" 737 " SELECT extract (epoch FROM now ())::integer;" 741 sql (
"CREATE OR REPLACE FUNCTION common_cve (text, text)" 742 " RETURNS boolean AS $$" 744 " SELECT EXISTS (SELECT trim (unnest (string_to_array ($1, ',')))" 746 " SELECT trim (unnest (string_to_array ($2, ','))));" 751 sql (
"CREATE OR REPLACE FUNCTION cpe_title (text)" 752 " RETURNS text AS $$" 753 " SELECT title FROM scap.cpes WHERE uuid = $1;" 758 sql (
"CREATE OR REPLACE FUNCTION cpe_title (text)" 759 " RETURNS text AS $$" 760 " SELECT null::text;" 764 sql (
"CREATE OR REPLACE FUNCTION hosts_contains (text, text)" 765 " RETURNS boolean AS $$" 768 " IN (SELECT trim (unnest (string_to_array ($1, ','))));" 772 sql (
"CREATE OR REPLACE FUNCTION make_uuid () RETURNS text AS $$" 773 " SELECT uuid_generate_v4 ()::text AS result;" 776 sql (
"CREATE OR REPLACE FUNCTION tag (text, text) RETURNS text AS $$" 778 " SELECT split_part (unnest, '=', 2)" 779 " FROM unnest (string_to_array ($1, '|'))" 780 " WHERE split_part (unnest, '=', 1) = $2;" 783 if (
sql_int (
"SELECT EXISTS (SELECT * FROM information_schema.tables" 784 " WHERE table_catalog = '%s'" 785 " AND table_schema = 'public'" 786 " AND table_name = 'meta')" 790 sql (
"CREATE OR REPLACE FUNCTION report_active (integer)" 791 " RETURNS boolean AS $$" 794 " WHEN (SELECT scan_run_status FROM reports" 795 " WHERE reports.id = $1)" 796 " IN (SELECT unnest (ARRAY [%i, %i, %i, %i, %i, %i," 810 sql (
"CREATE OR REPLACE FUNCTION report_progress (integer)" 811 " RETURNS integer AS $$" 816 " WHEN (SELECT slave_task_uuid FROM reports WHERE id = $1)" 818 " THEN (SELECT slave_progress FROM reports WHERE id = $1)" 819 " WHEN report_active ($1)" 820 " THEN report_progress_active ($1)" 825 sql (
"CREATE OR REPLACE FUNCTION dynamic_severity ()" 826 " RETURNS boolean AS $$" 828 " SELECT CAST (value AS integer) = 1 FROM settings" 829 " WHERE name = 'Dynamic Severity'" 830 " AND ((owner IS NULL)" 831 " OR (owner = (SELECT id FROM users" 834 " FROM current_credentials))))" 835 " ORDER BY coalesce (owner, 0) DESC LIMIT 1;" 838 sql (
"CREATE OR REPLACE FUNCTION current_severity (real, text)" 839 " RETURNS double precision AS $$" 840 " SELECT coalesce ((CASE WHEN $1 > " G_STRINGIFY (
SEVERITY_LOG)
841 " THEN (SELECT CAST (cvss_base" 842 " AS double precision)" 844 " WHERE nvts.oid = $2)" 850 #define OVERRIDES_SQL(severity_sql) \ 852 " ((SELECT overrides.new_severity" \ 854 " WHERE overrides.nvt = results.nvt" \ 855 " AND ((overrides.owner IS NULL)" \ 856 " OR (overrides.owner =" \ 857 " (SELECT id FROM users" \ 858 " WHERE users.uuid" \ 860 " FROM current_credentials))))" \ 861 " AND ((overrides.end_time = 0)" \ 862 " OR (overrides.end_time >= m_now ()))" \ 863 " AND (overrides.task = results.task" \ 864 " OR overrides.task = 0)" \ 865 " AND (overrides.result = results.id" \ 866 " OR overrides.result = 0)" \ 867 " AND (overrides.hosts is NULL" \ 868 " OR overrides.hosts = ''" \ 869 " OR hosts_contains (overrides.hosts," \ 871 " AND (overrides.port is NULL" \ 872 " OR overrides.port = ''" \ 873 " OR overrides.port = results.port)" \ 874 " AND severity_matches_ov" \ 875 " (" severity_sql ", overrides.severity)" \ 876 " ORDER BY overrides.result DESC," \ 877 " overrides.task DESC," \ 878 " overrides.port DESC," \ 879 " overrides.severity ASC," \ 880 " overrides.creation_time DESC" \ 885 if (current_db_version >= 147)
886 sql (
"CREATE OR REPLACE FUNCTION report_severity (report integer," 887 " overrides integer," 889 " RETURNS double precision AS $$" 891 " WITH max_severity AS (SELECT max(severity) AS max" 892 " FROM report_counts" 897 " AND (end_time = 0 or end_time >= m_now ()))" 899 " WHEN EXISTS (SELECT max FROM max_severity)" 900 " AND (SELECT max FROM max_severity) IS NOT NULL" 901 " THEN (SELECT max::double precision FROM max_severity)" 902 " WHEN dynamic_severity () AND $2::boolean" 906 (
"current_severity (results.severity," 909 " WHERE results.report = $1" 910 " AND results.qod >= $3)" 911 " WHEN dynamic_severity ()" 913 " THEN (SELECT max (CASE" 914 " WHEN results.type IS NULL" 916 " ELSE current_severity" 917 " (results.severity, results.nvt)" 920 " WHERE results.report = $1" 921 " AND results.qod >= $3)" 926 " WHERE results.report = $1" 927 " AND results.qod >= $3)" 929 " ELSE (SELECT max (CASE" 930 " WHEN results.type IS NULL" 932 " ELSE results.severity" 935 " WHERE results.report = $1" 936 " AND results.qod >= $3)" 940 sql (
"CREATE OR REPLACE FUNCTION report_host_count (report integer)" 941 " RETURNS bigint AS $$" 942 " SELECT count (DISTINCT id) FROM report_hosts" 943 " WHERE report_hosts.report = $1;" 946 sql (
"CREATE OR REPLACE FUNCTION report_result_host_count (report integer," 948 " RETURNS bigint AS $$" 949 " SELECT count (DISTINCT id) FROM report_hosts" 950 " WHERE report_hosts.report = $1" 951 " AND EXISTS (SELECT * FROM results" 952 " WHERE results.host = report_hosts.host" 953 " AND results.qod >= $2)" 956 sql (
"CREATE OR REPLACE FUNCTION severity_class ()" 957 " RETURNS text AS $$" 959 " SELECT value FROM settings" 960 " WHERE name = 'Severity Class'" 961 " AND ((owner IS NULL)" 962 " OR (owner = (SELECT id FROM users" 963 " WHERE users.uuid = (SELECT uuid" 964 " FROM current_credentials))))" 965 " ORDER BY coalesce (owner, 0) DESC LIMIT 1;" 969 if (current_db_version >= 147)
970 sql (
"CREATE OR REPLACE FUNCTION" 971 " report_severity_count (report integer, overrides integer," 972 " min_qod integer, level text)" 973 " RETURNS bigint AS $$" 975 " WITH severity_count AS (SELECT sum (count) AS total" 976 " FROM report_counts" 981 " or end_time >= m_now ())" 983 " BETWEEN level_min_severity" 984 " ($4, severity_class ())" 985 " AND level_max_severity" 986 " ($4, severity_class ())))" 988 " WHEN EXISTS (SELECT total FROM severity_count)" 989 " AND (SELECT total FROM severity_count) IS NOT NULL" 990 " THEN (SELECT total FROM severity_count)" 991 " WHEN dynamic_severity () AND $2::boolean" 993 " THEN (SELECT count (*)" 995 " WHERE results.report = $1" 996 " AND results.qod >= $3" 998 (
"current_severity (results.severity," 1000 " BETWEEN level_min_severity" 1001 " ($4, severity_class ())" 1002 " AND level_max_severity" 1003 " ($4, severity_class ())))" 1004 " WHEN dynamic_severity ()" 1006 " THEN (SELECT count (*)" 1008 " WHERE results.report = $1" 1009 " AND results.qod >= $3" 1011 " WHEN results.type IS NULL" 1013 " ELSE current_severity (results.severity," 1016 " BETWEEN level_min_severity ($4, severity_class ())" 1017 " AND level_max_severity" 1018 " ($4, severity_class ())))" 1021 " THEN (SELECT count (*)" 1023 " WHERE results.report = $1" 1024 " AND results.qod >= $3" 1026 " BETWEEN level_min_severity ($4, severity_class ())" 1027 " AND level_max_severity" 1028 " ($4, severity_class ())))" 1030 " ELSE (SELECT count (*)" 1032 " WHERE results.report = $1" 1033 " AND results.qod >= $3" 1035 " WHEN results.type IS NULL" 1037 " ELSE results.severity" 1039 " BETWEEN level_min_severity ($4, severity_class ())" 1040 " AND level_max_severity" 1041 " ($4, severity_class ())))" 1043 "$$ LANGUAGE SQL;");
1045 sql (
"CREATE OR REPLACE FUNCTION task_last_report (integer)" 1046 " RETURNS integer AS $$" 1048 " SELECT id FROM reports WHERE task = $1 AND scan_run_status = %u" 1049 " ORDER BY date DESC LIMIT 1;" 1053 sql (
"CREATE OR REPLACE FUNCTION task_second_last_report (integer)" 1054 " RETURNS integer AS $$" 1056 " SELECT id FROM reports WHERE task = $1 AND scan_run_status = %u" 1057 " ORDER BY date DESC LIMIT 1 OFFSET 1;" 1061 if (current_db_version >= 147)
1063 sql (
"CREATE OR REPLACE FUNCTION task_severity (integer, integer," 1065 " RETURNS double precision AS $$" 1068 " WHEN (SELECT target IS NULL OR target = 0" 1069 " FROM tasks WHERE id = $1)" 1070 " THEN CAST (NULL AS double precision)" 1072 " (SELECT report_severity ((SELECT id FROM reports" 1074 " AND scan_run_status = %u" 1075 " ORDER BY date DESC" 1076 " LIMIT 1 OFFSET 0), $2, $3))" 1081 sql (
"CREATE OR REPLACE FUNCTION task_trend (integer, integer, integer)" 1082 " RETURNS text AS $$" 1085 " last_report integer;" 1086 " second_last_report integer;" 1087 " severity_a double precision;" 1088 " severity_b double precision;" 1095 " threat_a integer;" 1096 " threat_b integer;" 1100 " WHEN (SELECT count(*) <= 1 FROM reports" 1102 " AND scan_run_status = %u)" 1103 " THEN RETURN ''::text;" 1105 " WHEN NOT EXISTS (SELECT uuid FROM current_credentials)" 1106 " OR (SELECT uuid = '' FROM current_credentials)" 1107 " THEN RETURN ''::text;" 1109 " WHEN (SELECT run_status = %u OR target = 0" 1110 " FROM tasks WHERE id = $1)" 1111 " THEN RETURN ''::text;" 1115 " last_report := task_last_report ($1);" 1116 " second_last_report := task_second_last_report ($1);" 1117 " severity_a := report_severity (last_report, $2, $3);" 1118 " severity_b := report_severity (second_last_report, $2, $3);" 1119 " IF severity_a > severity_b THEN" 1120 " RETURN 'up'::text;" 1121 " ELSIF severity_b > severity_a THEN" 1122 " RETURN 'down'::text;" 1125 " high_a := report_severity_count (last_report, $2, $3," 1127 " high_b := report_severity_count (second_last_report, $2, $3," 1129 " medium_a := report_severity_count (last_report, $2, $3," 1131 " medium_b := report_severity_count (second_last_report, $2, $3," 1133 " low_a := report_severity_count (last_report, $2, $3," 1135 " low_b := report_severity_count (second_last_report, $2, $3," 1137 " IF high_a > 0 THEN" 1139 " ELSIF medium_a > 0 THEN" 1141 " ELSIF low_a > 0 THEN" 1146 " IF high_b > 0 THEN" 1148 " ELSIF medium_b > 0 THEN" 1150 " ELSIF low_b > 0 THEN" 1156 " IF threat_a > threat_b THEN" 1157 " RETURN 'up'::text;" 1158 " ELSIF threat_b > threat_a THEN" 1159 " RETURN 'down'::text;" 1162 " IF high_a > 0 THEN" 1163 " IF high_a > high_b THEN" 1164 " RETURN 'more'::text;" 1165 " ELSIF high_a < high_b THEN" 1166 " RETURN 'less'::text;" 1168 " RETURN 'same'::text;" 1170 " IF medium_a > 0 THEN" 1171 " IF medium_a > medium_b THEN" 1172 " RETURN 'more'::text;" 1173 " ELSIF medium_a < medium_b THEN" 1174 " RETURN 'less'::text;" 1176 " RETURN 'same'::text;" 1178 " IF low_a > 0 THEN" 1179 " IF low_a > low_b THEN" 1180 " RETURN 'more'::text;" 1181 " ELSIF low_a < low_b THEN" 1182 " RETURN 'less'::text;" 1184 " RETURN 'same'::text;" 1186 " RETURN 'same'::text;" 1188 "$$ LANGUAGE plpgsql;",
1194 sql (
"CREATE OR REPLACE FUNCTION run_status_name (integer)" 1195 " RETURNS text AS $$" 1200 " THEN 'Delete Requested'" 1201 " WHEN $1 = %i OR $1 = %i" 1202 " THEN 'Ultimate Delete Requested'" 1211 " WHEN $1 = %i OR $1 = %i OR $1 = %i" 1212 " THEN 'Stop Requested'" 1215 " ELSE 'Internal Error'" 1232 if (
sql_int (
"SELECT EXISTS (SELECT * FROM information_schema.tables" 1233 " WHERE table_catalog = '%s'" 1234 " AND table_schema = 'public'" 1235 " AND table_name = 'permissions')" 1238 sql (
"CREATE OR REPLACE FUNCTION user_can_everything (text)" 1239 " RETURNS boolean AS $$" 1243 " SELECT count(*) > 0 FROM permissions" 1244 " WHERE resource = 0" 1245 " AND ((subject_type = 'user'" 1247 " = (SELECT id FROM users" 1248 " WHERE users.uuid = $1))" 1249 " OR (subject_type = 'group'" 1251 " IN (SELECT DISTINCT \"group\"" 1253 " WHERE \"user\" = (SELECT id" 1257 " OR (subject_type = 'role'" 1259 " IN (SELECT DISTINCT role" 1261 " WHERE \"user\" = (SELECT id" 1265 " AND name = 'Everything';" 1266 "$$ LANGUAGE SQL;");
1268 sql (
"CREATE OR REPLACE FUNCTION group_concat_pair (text, text, text)" 1269 " RETURNS text AS $$" 1271 " WHEN $1 IS NULL OR $1 = ''" 1273 " ELSE $1 || $3 || $2" 1278 sql (
"DROP AGGREGATE IF EXISTS group_concat (text, text);");
1280 sql (
"CREATE AGGREGATE group_concat (text, text)" 1281 " (sfunc = group_concat_pair," 1283 " initcond = '');");
1285 if (
sql_int (
"SELECT EXISTS (SELECT * FROM information_schema.tables" 1286 " WHERE table_catalog = '%s'" 1287 " AND table_schema = 'public'" 1288 " AND table_name = 'meta')" 1292 sql (
"CREATE OR REPLACE FUNCTION severity_in_level (double precision," 1294 " RETURNS boolean AS $$" 1295 " SELECT CASE (SELECT value FROM settings" 1296 " WHERE name = 'Severity Class'" 1297 " AND ((owner IS NULL)" 1298 " OR (owner = (SELECT id FROM users" 1301 " FROM current_credentials))))" 1302 " ORDER BY coalesce (owner, 0) DESC LIMIT 1)" 1304 " THEN (CASE lower ($2)" 1321 " THEN (CASE lower ($2)" 1325 " THEN $1 >= 0.0 AND $1 < 4.0" 1327 " THEN $1 >= 0.0 AND $1 < 4.0" 1348 "$$ LANGUAGE SQL;");
1350 sql (
"CREATE OR REPLACE FUNCTION severity_to_level (text, integer)" 1351 " RETURNS text AS $$" 1353 " WHEN $1::double precision = " G_STRINGIFY (
SEVERITY_LOG)
1355 " WHEN $1::double precision = " G_STRINGIFY (
SEVERITY_FP)
1356 " THEN 'False Positive'" 1361 " WHEN $1::double precision > 0.0" 1362 " AND $1::double precision <= 10.0" 1363 " THEN (SELECT CASE" 1366 " WHEN severity_in_level ($1::double precision," 1369 " WHEN severity_in_level ($1::double precision," 1372 " WHEN severity_in_level ($1::double precision," 1377 " ELSE 'Internal Error'" 1382 sql (
"CREATE OR REPLACE FUNCTION severity_to_level (double precision," 1384 " RETURNS text AS $$" 1389 " THEN 'False Positive'" 1394 " WHEN $1 > 0.0 AND $1 <= 10.0" 1395 " THEN (SELECT CASE" 1398 " WHEN severity_in_level ($1, 'high')" 1400 " WHEN severity_in_level ($1, 'medium')" 1402 " WHEN severity_in_level ($1, 'low')" 1406 " ELSE 'Internal Error'" 1411 if (current_db_version >= 147)
1412 sql (
"CREATE OR REPLACE FUNCTION task_threat_level (integer, integer," 1414 " RETURNS text AS $$" 1416 " SELECT severity_to_level (task_severity ($1, $2, $3), 0);" 1421 if (
sql_int (
"SELECT (EXISTS (SELECT * FROM information_schema.tables" 1422 " WHERE table_catalog = '%s'" 1423 " AND table_schema = 'public'" 1424 " AND table_name = 'credentials_data')" 1425 " AND EXISTS (SELECT * FROM information_schema.tables" 1426 " WHERE table_catalog = '%s'" 1427 " AND table_schema = 'public'" 1428 " AND table_name = 'credentials_trash_data'))" 1432 sql (
"CREATE OR REPLACE FUNCTION credential_value (integer, integer, text)" 1433 " RETURNS text AS $$" 1437 " (SELECT value FROM credentials_trash_data" 1438 " WHERE credential = $1 AND type = $3)" 1440 " (SELECT value FROM credentials_data" 1441 " WHERE credential = $1 AND type = $3)" 1443 "$$ LANGUAGE SQL;");
1446 if (
sql_int (
"SELECT (EXISTS (SELECT * FROM information_schema.tables" 1447 " WHERE table_catalog = '%s'" 1448 " AND table_schema = 'public'" 1449 " AND table_name = 'targets_login_data')" 1450 " AND EXISTS (SELECT * FROM information_schema.tables" 1451 " WHERE table_catalog = '%s'" 1452 " AND table_schema = 'public'" 1453 " AND table_name = 'targets_trash_login_data'))" 1457 sql (
"CREATE OR REPLACE FUNCTION target_credential (integer, integer, text)" 1458 " RETURNS integer AS $$" 1462 " (SELECT credential FROM targets_trash_login_data" 1463 " WHERE target = $1 AND type = $3)" 1465 " (SELECT credential FROM targets_login_data" 1466 " WHERE target = $1 AND type = $3)" 1468 "$$ LANGUAGE SQL;");
1470 sql (
"CREATE OR REPLACE FUNCTION trash_target_credential_location (integer, text)" 1471 " RETURNS integer AS $$" 1472 " SELECT credential_location FROM targets_trash_login_data" 1473 " WHERE target = $1 AND type = $2" 1474 "$$ LANGUAGE SQL;");
1476 sql (
"CREATE OR REPLACE FUNCTION target_login_port (integer, integer, text)" 1477 " RETURNS integer AS $$" 1481 " (SELECT port FROM targets_trash_login_data" 1482 " WHERE target = $1 AND type = $3)" 1484 " (SELECT port FROM targets_login_data" 1485 " WHERE target = $1 AND type = $3)" 1487 "$$ LANGUAGE SQL;");
1490 sql (
"CREATE OR REPLACE FUNCTION lower (integer)" 1491 " RETURNS integer AS $$" #define LOCATION_TRASH
Location of a constituent of a trashcan resource.
int sql_int(char *sql,...)
Get a particular cell from a SQL query, as an int.
void sql(char *sql,...)
Perform an SQL statement, retrying if database is busy or locked.
#define OVERRIDES_SQL(severity_sql)
int manage_db_version()
Return the database version of the actual database.
const char * sql_database()
Return name of current database.
#define LOCATION_TABLE
Location of a constituent of a trashcan resource.
int manage_scap_loaded()
Check whether SCAP is available.