OpenVAS Manager  7.0.3~git
manage_pg.c
Go to the documentation of this file.
1 /* OpenVAS Manager
2  * $Id$
3  * Description: Manager Manage library: PostgreSQL specific Manage facilities.
4  *
5  * Authors:
6  * Matthew Mundell <matthew.mundell@greenbone.net>
7  *
8  * Copyright:
9  * Copyright (C) 2014 Greenbone Networks GmbH
10  *
11  * This program is free software; you can redistribute it and/or
12  * modify it under the terms of the GNU General Public License
13  * as published by the Free Software Foundation; either version 2
14  * of the License, or (at your option) any later version.
15  *
16  * This program is distributed in the hope that it will be useful,
17  * but WITHOUT ANY WARRANTY; without even the implied warranty of
18  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
19  * GNU General Public License for more details.
20  *
21  * You should have received a copy of the GNU General Public License
22  * along with this program; if not, write to the Free Software
23  * Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA.
24  */
25 
26 #include "sql.h"
27 #include "manage_sql.h"
28 #include "manage_utils.h"
29 #include "manage_acl.h"
30 
31 #undef G_LOG_DOMAIN
32 
35 #define G_LOG_DOMAIN "md manage"
36 
37 
38 /* Session. */
39 
45 void
46 manage_session_init (const char *uuid)
47 {
48  sql ("CREATE TEMPORARY TABLE IF NOT EXISTS current_credentials"
49  " (id SERIAL PRIMARY KEY,"
50  " uuid text UNIQUE NOT NULL,"
51  " tz_override text);");
52  sql ("DELETE FROM current_credentials;");
53  if (uuid)
54  sql ("INSERT INTO current_credentials (uuid) VALUES ('%s');", uuid);
55 }
56 
62 void
63 manage_session_set_timezone (const char *timezone)
64 {
65  sql ("SET SESSION TIME ZONE '%s';", timezone);
66  return;
67 }
68 
69 
70 /* Helpers. */
71 
77 int
78 manage_db_empty ()
79 {
80  return sql_int ("SELECT EXISTS (SELECT * FROM information_schema.tables"
81  " WHERE table_catalog = '%s'"
82  " AND table_schema = 'public'"
83  " AND table_name = 'meta')"
84  " ::integer;",
85  sql_database ())
86  == 0;
87 }
88 
89 
90 /* SQL functions. */
91 
100 void
101 sql_rename_column (const char *old_table, const char *new_table,
102  const char *old_name, const char *new_name)
103 {
104  return;
105 }
106 
112 int
113 manage_create_sql_functions ()
114 {
115  static int created = 0;
116  int current_db_version = manage_db_version ();
117 
118  if (created)
119  return 0;
120 
121  if (sql_int ("SELECT count (*) FROM pg_available_extensions"
122  " WHERE name = 'uuid-ossp' AND installed_version IS NOT NULL;")
123  == 0)
124  {
125  g_warning ("%s: PostgreSQL extension uuid-ossp required", __FUNCTION__);
126  return -1;
127  }
128 
129  /* Functions in C. */
130 
131  sql ("SET role dba;");
132 
133  sql ("CREATE OR REPLACE FUNCTION max_hosts (text, text)"
134  " RETURNS integer"
135  " AS '%s/openvasmd/pg/libmanage-pg-server', 'sql_max_hosts'"
136  " LANGUAGE C;",
137  OPENVAS_LIB_INSTALL_DIR);
138 
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'"
142  " LANGUAGE C;",
143  OPENVAS_LIB_INSTALL_DIR);
144 
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'"
148  " LANGUAGE C;",
149  OPENVAS_LIB_INSTALL_DIR);
150 
151  sql ("CREATE OR REPLACE FUNCTION next_time (integer, integer, integer)"
152  " RETURNS integer"
153  " AS '%s/openvasmd/pg/libmanage-pg-server', 'sql_next_time'"
154  " LANGUAGE C;",
155  OPENVAS_LIB_INSTALL_DIR);
156 
157  sql ("CREATE OR REPLACE FUNCTION next_time (integer, integer, integer, text)"
158  " RETURNS integer"
159  " AS '%s/openvasmd/pg/libmanage-pg-server', 'sql_next_time'"
160  " LANGUAGE C;",
161  OPENVAS_LIB_INSTALL_DIR);
162 
163  sql ("CREATE OR REPLACE FUNCTION next_time (integer, integer, integer, text, integer)"
164  " RETURNS integer"
165  " AS '%s/openvasmd/pg/libmanage-pg-server', 'sql_next_time'"
166  " LANGUAGE C;",
167  OPENVAS_LIB_INSTALL_DIR);
168 
169  sql ("CREATE OR REPLACE FUNCTION severity_matches_ov (double precision,"
170  " double precision)"
171  " RETURNS boolean"
172  " AS '%s/openvasmd/pg/libmanage-pg-server', 'sql_severity_matches_ov'"
173  " LANGUAGE C"
174  " IMMUTABLE;",
175  OPENVAS_LIB_INSTALL_DIR);
176 
177  sql ("CREATE OR REPLACE FUNCTION valid_db_resource_type (text)"
178  " RETURNS boolean"
179  " AS '%s/openvasmd/pg/libmanage-pg-server', 'sql_valid_db_resource_type'"
180  " LANGUAGE C;",
181  OPENVAS_LIB_INSTALL_DIR);
182 
183  sql ("CREATE OR REPLACE FUNCTION regexp (text, text)"
184  " RETURNS boolean"
185  " AS '%s/openvasmd/pg/libmanage-pg-server', 'sql_regexp'"
186  " LANGUAGE C;",
187  OPENVAS_LIB_INSTALL_DIR);
188 
189  if (sql_int ("SELECT count(*) FROM pg_operator"
190  " WHERE oprname = '?~#';")
191  == 0)
192  {
193  sql ("CREATE OPERATOR ?~#"
194  " (PROCEDURE = regexp, LEFTARG = text, RIGHTARG = text);");
195  }
196 
197  sql ("RESET role;");
198 
199  /* Functions in pl/pgsql. */
200 
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')"
205  " ::integer;",
206  sql_database ()))
207  {
208  sql ("CREATE OR REPLACE FUNCTION resource_name (text, text, integer)"
209  " RETURNS text AS $$"
210  /* Get the name of a resource by its type and ID. */
211  " DECLARE"
212  " execute_name text;"
213  " BEGIN"
214  " CASE"
215  " WHEN NOT valid_db_resource_type ($1)"
216  " THEN RAISE EXCEPTION 'Invalid resource type argument: %', $1;"
217  " WHEN $1 = 'note'"
218  " AND $3 = " G_STRINGIFY (LOCATION_TABLE)
219  " THEN RETURN (SELECT 'Note for: '"
220  " || (SELECT name"
221  " FROM nvts"
222  " WHERE nvts.uuid = notes.nvt)"
223  " FROM notes"
224  " WHERE uuid = $2);"
225  " WHEN $1 = 'note'"
226  " THEN RETURN (SELECT 'Note for: '"
227  " || (SELECT name"
228  " FROM nvts"
229  " WHERE nvts.uuid = notes_trash.nvt)"
230  " FROM notes_trash"
231  " WHERE uuid = $2);"
232  " WHEN $1 = 'override'"
233  " AND $3 = " G_STRINGIFY (LOCATION_TABLE)
234  " THEN RETURN (SELECT 'Override for: '"
235  " || (SELECT name"
236  " FROM nvts"
237  " WHERE nvts.uuid = overrides.nvt)"
238  " FROM overrides"
239  " WHERE uuid = $2);"
240  " WHEN $1 = 'override'"
241  " THEN RETURN (SELECT 'Override for: '"
242  " || (SELECT name"
243  " FROM nvts"
244  " WHERE nvts.uuid = overrides_trash.nvt)"
245  " FROM overrides_trash"
246  " WHERE uuid = $2);"
247  " WHEN $1 = 'report'"
248  " THEN RETURN (SELECT (SELECT name FROM tasks WHERE id = task)"
249  " || ' - '"
250  " || (SELECT"
251  " CASE (SELECT end_time FROM tasks"
252  " WHERE id = task)"
253  " WHEN 0 THEN 'N/A'"
254  " ELSE (SELECT end_time::text"
255  " FROM tasks WHERE id = task)"
256  " END)"
257  " FROM reports"
258  " WHERE uuid = $2);"
259  " WHEN $1 = 'result'"
260  " THEN RETURN (SELECT (SELECT name FROM tasks WHERE id = task)"
261  " || ' - '"
262  " || (SELECT name FROM nvts WHERE oid = nvt)"
263  " || ' - '"
264  " || (SELECT"
265  " CASE (SELECT end_time FROM tasks"
266  " WHERE id = task)"
267  " WHEN 0 THEN 'N/A'"
268  " ELSE (SELECT end_time::text"
269  " FROM tasks WHERE id = task)"
270  " END)"
271  " FROM results"
272  " WHERE uuid = $2);"
273  " WHEN $1 = 'task'"
274  " THEN RETURN (SELECT name FROM tasks WHERE uuid = $2);"
275  " WHEN $3 = " G_STRINGIFY (LOCATION_TABLE)
276  " THEN EXECUTE 'SELECT name FROM ' || $1 || 's"
277  " WHERE uuid = $1'"
278  " INTO execute_name"
279  " USING $2;"
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"
284  " WHERE uuid = $1'"
285  " INTO execute_name"
286  " USING $2;"
287  " RETURN execute_name;"
288  " ELSE RETURN NULL;"
289  " END CASE;"
290  " END;"
291  "$$ LANGUAGE plpgsql;");
292 
293  created = 1;
294  }
295 
296  sql ("CREATE OR REPLACE FUNCTION report_progress_active (integer)"
297  " RETURNS integer AS $$"
298  /* Calculate the progress of an active report. */
299  " DECLARE"
300  " report_task integer;"
301  " task_target integer;"
302  " target_hosts text;"
303  " target_exclude_hosts text;"
304  " progress integer;"
305  " total integer;"
306  " maximum_hosts integer;"
307  " total_progress integer;"
308  " report_host record;"
309  " dead_hosts integer;"
310  " BEGIN"
311  " total := 0;"
312  " dead_hosts := 0;"
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;"
318  " ELSIF (SELECT target_location = " G_STRINGIFY (LOCATION_TRASH)
319  " FROM tasks WHERE id = report_task)"
320  " THEN"
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);"
325  " ELSE"
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);"
330  " END IF;"
331  " IF target_hosts IS NULL THEN"
332  " RETURN 0;"
333  " END IF;"
334  " maximum_hosts := max_hosts (target_hosts, target_exclude_hosts);"
335  " IF maximum_hosts = 0 THEN"
336  " RETURN 0;"
337  " END IF;"
338  " FOR report_host IN SELECT current_port, max_port"
339  " FROM report_hosts WHERE report = $1"
340  " LOOP"
341  " IF report_host.max_port = -1 THEN"
342  " progress := 0;"
343  " dead_hosts := dead_hosts + 1;"
344  " ELSEIF report_host.max_port IS NOT NULL"
345  " AND report_host.max_port != 0"
346  " THEN"
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"
351  " THEN"
352  " progress := 0;"
353  " ELSE"
354  " progress := 100;"
355  " END IF;"
356  " total := total + progress;"
357  " END LOOP;"
358  " IF (maximum_hosts - dead_hosts) > 0 THEN"
359  " total_progress := total / (maximum_hosts - dead_hosts);"
360  " ELSE"
361  " total_progress := 0;"
362  " END IF;"
363  " IF total_progress = 0 THEN"
364  " RETURN 1;"
365  " ELSIF total_progress = 100 THEN"
366  " RETURN 99;"
367  " END IF;"
368  " RETURN total_progress;"
369  " END;"
370  "$$ LANGUAGE plpgsql;");
371 
372  sql ("CREATE OR REPLACE FUNCTION order_inet (text)"
373  " RETURNS text AS $$"
374  " BEGIN"
375  " IF $1 ~ '^[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}$' THEN"
376  " RETURN chr (1)" /* Make IPs sort before hostnames. */
377  " || to_char (split_part ($1, '.', 1)::integer, 'fm000')"
378  " || '.'"
379  " || to_char (split_part ($1, '.', 2)::integer, 'fm000')"
380  " || '.'"
381  " || to_char (split_part ($1, '.', 3)::integer, 'fm000')"
382  " || '.'"
383  " || to_char (split_part ($1, '.', 4)::integer, 'fm000');"
384  " ELSE"
385  " RETURN $1;"
386  " END IF;"
387  " END;"
388  "$$ LANGUAGE plpgsql"
389  " IMMUTABLE;");
390 
391  sql ("CREATE OR REPLACE FUNCTION order_message_type (text)"
392  " RETURNS integer AS $$"
393  " BEGIN"
394  " IF $1 = 'Security Hole' THEN"
395  " RETURN 1;"
396  " ELSIF $1 = 'Security Warning' THEN"
397  " RETURN 2;"
398  " ELSIF $1 = 'Security Note' THEN"
399  " RETURN 3;"
400  " ELSIF $1 = 'Log Message' THEN"
401  " RETURN 4;"
402  " ELSIF $1 = 'Debug Message' THEN"
403  " RETURN 5;"
404  " ELSIF $1 = 'Error Message' THEN"
405  " RETURN 6;"
406  " ELSE"
407  " RETURN 7;"
408  " END IF;"
409  " END;"
410  "$$ LANGUAGE plpgsql"
411  " IMMUTABLE;");
412 
413  sql ("CREATE OR REPLACE FUNCTION order_port (text)"
414  " RETURNS integer AS $$"
415  " BEGIN"
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);"
420  " ELSE"
421  " RETURN 0;"
422  " END IF;"
423  " END;"
424  "$$ LANGUAGE plpgsql"
425  " IMMUTABLE;");
426 
427  sql ("CREATE OR REPLACE FUNCTION order_role (text)"
428  " RETURNS text AS $$"
429  " BEGIN"
430  " IF $1 = 'Admin' THEN"
431  " RETURN ' !';"
432  " ELSE"
433  " RETURN $1;"
434  " END IF;"
435  " END;"
436  "$$ LANGUAGE plpgsql"
437  " IMMUTABLE;");
438 
439  sql ("CREATE OR REPLACE FUNCTION order_threat (text)"
440  " RETURNS integer AS $$"
441  " BEGIN"
442  " IF $1 = 'High' THEN"
443  " RETURN 1;"
444  " ELSIF $1 = 'Medium' THEN"
445  " RETURN 2;"
446  " ELSIF $1 = 'Low' THEN"
447  " RETURN 3;"
448  " ELSIF $1 = 'Log' THEN"
449  " RETURN 4;"
450  " ELSIF $1 = 'Debug' THEN"
451  " RETURN 5;"
452  " ELSIF $1 = 'False Positive' THEN"
453  " RETURN 6;"
454  " ELSIF $1 = 'None' THEN"
455  " RETURN 7;"
456  " ELSE"
457  " RETURN 8;"
458  " END IF;"
459  " END;"
460  "$$ LANGUAGE plpgsql"
461  " IMMUTABLE;");
462 
463  sql ("CREATE OR REPLACE FUNCTION severity_to_type (double precision)"
464  " RETURNS text AS $$"
465  " BEGIN"
466  " IF $1 IS NULL THEN"
467  " RETURN NULL;"
468  " ELSIF $1 = " G_STRINGIFY (SEVERITY_LOG) " THEN"
469  " RETURN 'Log Message';"
470  " ELSIF $1 = " G_STRINGIFY (SEVERITY_FP) " THEN"
471  " RETURN 'False Positive';"
472  " ELSIF $1 = " G_STRINGIFY (SEVERITY_DEBUG) " THEN"
473  " RETURN 'Debug Message';"
474  " ELSIF $1 = " G_STRINGIFY (SEVERITY_ERROR) " THEN"
475  " RETURN 'Error Message';"
476  " ELSIF $1 > 0.0 AND $1 <= 10.0 THEN"
477  " RETURN 'Alarm';"
478  " ELSE"
479  " RAISE EXCEPTION 'Invalid severity score given: %', $1;"
480  " END IF;"
481  " END;"
482  "$$ LANGUAGE plpgsql"
483  " IMMUTABLE;");
484 
485  sql ("CREATE OR REPLACE FUNCTION iso_time (seconds integer)"
486  " RETURNS text AS $$"
487  " DECLARE"
488  " user_zone text;"
489  " user_offset interval;"
490  " BEGIN"
491  " user_zone :="
492  " coalesce ((SELECT tz_override FROM current_credentials),"
493  " (SELECT timezone FROM users"
494  " WHERE uuid = (SELECT uuid"
495  " FROM current_credentials)));"
496  " BEGIN"
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';"
501  " user_offset = 0;"
502  " END;"
503  " RETURN CASE"
504  " WHEN $1 = 0"
505  " THEN ''"
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',"
509  " 'FMYYYY-MM-DD')"
510  " || to_char (to_timestamp ($1) AT TIME ZONE 'UTC',"
511  " 'FMTHH24:MI:SSZ')"
512  " ELSE to_char (to_timestamp ($1) AT TIME ZONE user_zone,"
513  " 'FMYYYY-MM-DD')"
514  " || to_char (to_timestamp ($1) AT TIME ZONE user_zone,"
515  " 'FMTHH24:MI:SS')"
516  " || CASE WHEN (extract (epoch FROM user_offset) > 0)"
517  " THEN '+' ELSE '' END"
518  " || to_char (extract (hours FROM user_offset)::integer,"
519  " 'FM00')"
520  " || ':'"
521  " || to_char (abs (extract (minutes FROM user_offset)"
522  " ::integer),"
523  " 'FM00')"
524  " END;"
525  " END;"
526  "$$ LANGUAGE plpgsql;");
527 
528  sql ("CREATE OR REPLACE FUNCTION days_from_now (seconds integer)"
529  " RETURNS integer AS $$"
530  " DECLARE"
531  " diff interval;"
532  " BEGIN"
533  " diff := age ( to_timestamp( seconds ), now() );"
534  " RETURN CASE"
535  " WHEN seconds = 0"
536  " THEN -2"
537  " WHEN diff < interval '0 seconds'"
538  " THEN -1"
539  " ELSE date_part( 'day', diff )"
540  " END;"
541  " END;"
542  "$$ LANGUAGE plpgsql"
543  " IMMUTABLE;");
544 
545  sql ("CREATE OR REPLACE FUNCTION uniquify (type text, proposed_name text,"
546  " owner integer, suffix text)"
547  " RETURNS text AS $$"
548  " DECLARE"
549  " number integer := 1;"
550  " candidate text := '';"
551  " separator text := ' ';"
552  " unique_candidate boolean;"
553  " BEGIN"
554  " IF type = 'user' THEN separator := '_'; END IF;"
555  " candidate := proposed_name || suffix || separator || number::text;"
556  " LOOP"
557  " EXECUTE 'SELECT count (*) = 0 FROM ' || type || 's"
558  " WHERE name = $1"
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;"
565  " END LOOP;"
566  " RETURN candidate;"
567  " END;"
568  "$$ LANGUAGE plpgsql;");
569 
570  sql ("CREATE OR REPLACE FUNCTION create_index (schema_name text,"
571  " index_name text,"
572  " table_name text,"
573  " columns text)"
574  " RETURNS void AS $$"
575  " BEGIN"
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))"
580  " THEN"
581  " EXECUTE 'CREATE INDEX ' || index_name"
582  " || ' ON ' || table_name || ' (' || columns || ');';"
583  " END IF;"
584  " END;"
585  "$$ LANGUAGE plpgsql;");
586 
587  sql ("CREATE OR REPLACE FUNCTION create_index (index_name text,"
588  " table_name text,"
589  " columns text)"
590  " RETURNS void AS $$"
591  " BEGIN"
592  " PERFORM create_index ('public', index_name, table_name, columns);"
593  " END;"
594  "$$ LANGUAGE plpgsql;");
595 
596  sql ("CREATE OR REPLACE FUNCTION user_has_super_on_resource (arg_type text, arg_id integer)"
597  " RETURNS boolean AS $$"
598  /* Test whether a user has super permissions on a resource.
599  *
600  * This must match user_has_super_on_resource in manage_acl.c. */
601  " DECLARE"
602  " owns boolean;"
603  " BEGIN"
604  " EXECUTE"
605  " 'SELECT"
606  " EXISTS (SELECT * FROM permissions"
607  " WHERE name = ''Super''"
608  /* Super on everyone. */
609  " AND ((resource = 0)"
610  /* Super on other_user. */
611  " OR ((resource_type = ''user'')"
612  " AND (resource = (SELECT ' || $1 || 's.owner"
613  " FROM ' || $1 || 's"
614  " WHERE id = $2)))"
615  /* Super on other_user's role. */
616  " OR ((resource_type = ''role'')"
617  " AND (resource"
618  " IN (SELECT DISTINCT role"
619  " FROM role_users"
620  " WHERE \"user\""
621  " = (SELECT ' || $1 || 's.owner"
622  " FROM ' || $1 || 's"
623  " WHERE id = $2))))"
624  /* Super on other_user's group. */
625  " OR ((resource_type = ''group'')"
626  " AND (resource"
627  " IN (SELECT DISTINCT \"group\""
628  " FROM group_users"
629  " WHERE \"user\""
630  " = (SELECT ' || $1 || 's.owner"
631  " FROM ' || $1 || 's"
632  " WHERE id = $2)))))"
633  " AND subject_location = " G_STRINGIFY (LOCATION_TABLE)
634  " AND ((subject_type = ''user''"
635  " AND subject"
636  " = (SELECT id FROM users"
637  " WHERE users.uuid"
638  " = (SELECT uuid"
639  " FROM current_credentials)))"
640  " OR (subject_type = ''group''"
641  " AND subject"
642  " IN (SELECT DISTINCT \"group\""
643  " FROM group_users"
644  " WHERE"
645  " \"user\""
646  " = (SELECT id"
647  " FROM users"
648  " WHERE users.uuid"
649  " = (SELECT uuid"
650  " FROM current_credentials))))"
651  " OR (subject_type = ''role''"
652  " AND subject"
653  " IN (SELECT DISTINCT role"
654  " FROM role_users"
655  " WHERE"
656  " \"user\""
657  " = (SELECT id"
658  " FROM users"
659  " WHERE users.uuid"
660  " = (SELECT uuid"
661  " FROM current_credentials))))))'"
662  " USING arg_type, arg_id"
663  " INTO owns;"
664  " RETURN owns;"
665  " END;"
666  "$$ LANGUAGE plpgsql;");
667 
668  sql ("CREATE OR REPLACE FUNCTION user_owns (arg_type text, arg_id integer)"
669  " RETURNS boolean AS $$"
670  /* Test whether a user owns a resource.
671  *
672  * This must match user_owns in manage_acl.c. */
673  " DECLARE"
674  " owns boolean;"
675  " BEGIN"
676  " CASE"
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'"
683  " THEN RETURN true;"
684  " WHEN user_has_super_on_resource (arg_type, arg_id)"
685  " THEN RETURN true;"
686  " WHEN arg_type = 'result'"
687  " THEN CASE"
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)"
692  " OR (reports.owner"
693  " = (SELECT id FROM users"
694  " WHERE users.uuid"
695  " = (SELECT uuid"
696  " FROM current_credentials)))))"
697  " THEN RETURN true;"
698  " ELSE RETURN false;"
699  " END CASE;"
700  " WHEN arg_type = 'task'"
701  " THEN CASE"
702  " WHEN EXISTS (SELECT * FROM tasks"
703  " WHERE id = arg_id"
704  " AND hidden < 2"
705  " AND ((owner IS NULL)"
706  " OR (owner"
707  " = (SELECT id FROM users"
708  " WHERE users.uuid"
709  " = (SELECT uuid"
710  " FROM current_credentials)))))"
711  " THEN RETURN true;"
712  " ELSE RETURN false;"
713  " END CASE;"
714  " ELSE"
715  " EXECUTE"
716  " 'SELECT EXISTS (SELECT * FROM ' || $1 || 's"
717  " WHERE id = $2"
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"
723  " INTO owns;"
724  " RETURN owns;"
725  " END CASE;"
726  " END;"
727  "$$ LANGUAGE plpgsql;");
728 
729  /* Functions in SQL. */
730 
731  sql ("CREATE OR REPLACE FUNCTION t () RETURNS boolean AS $$"
732  " SELECT true;"
733  "$$ LANGUAGE SQL"
734  " IMMUTABLE;");
735 
736  sql ("CREATE OR REPLACE FUNCTION m_now () RETURNS integer AS $$"
737  " SELECT extract (epoch FROM now ())::integer;"
738  "$$ LANGUAGE SQL"
739  " STABLE;");
740 
741  sql ("CREATE OR REPLACE FUNCTION common_cve (text, text)"
742  " RETURNS boolean AS $$"
743  /* Check if two CVE lists contain a common CVE. */
744  " SELECT EXISTS (SELECT trim (unnest (string_to_array ($1, ',')))"
745  " INTERSECT"
746  " SELECT trim (unnest (string_to_array ($2, ','))));"
747  "$$ LANGUAGE SQL;");
748 
749  if (manage_scap_loaded ())
750  {
751  sql ("CREATE OR REPLACE FUNCTION cpe_title (text)"
752  " RETURNS text AS $$"
753  " SELECT title FROM scap.cpes WHERE uuid = $1;"
754  "$$ LANGUAGE SQL;");
755  }
756  else
757  {
758  sql ("CREATE OR REPLACE FUNCTION cpe_title (text)"
759  " RETURNS text AS $$"
760  " SELECT null::text;"
761  "$$ LANGUAGE SQL;");
762  }
763 
764  sql ("CREATE OR REPLACE FUNCTION hosts_contains (text, text)"
765  " RETURNS boolean AS $$"
766  /* Check if a host list contains a host. */
767  " SELECT trim ($2)"
768  " IN (SELECT trim (unnest (string_to_array ($1, ','))));"
769  "$$ LANGUAGE SQL"
770  " IMMUTABLE;");
771 
772  sql ("CREATE OR REPLACE FUNCTION make_uuid () RETURNS text AS $$"
773  " SELECT uuid_generate_v4 ()::text AS result;"
774  "$$ LANGUAGE SQL;");
775 
776  sql ("CREATE OR REPLACE FUNCTION tag (text, text) RETURNS text AS $$"
777  /* Extract a tag from an OTP tag list. */
778  " SELECT split_part (unnest, '=', 2)"
779  " FROM unnest (string_to_array ($1, '|'))"
780  " WHERE split_part (unnest, '=', 1) = $2;"
781  "$$ LANGUAGE SQL;");
782 
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')"
787  " ::integer;",
788  sql_database ()))
789  {
790  sql ("CREATE OR REPLACE FUNCTION report_active (integer)"
791  " RETURNS boolean AS $$"
792  /* Check whether a report is active. */
793  " SELECT CASE"
794  " WHEN (SELECT scan_run_status FROM reports"
795  " WHERE reports.id = $1)"
796  " IN (SELECT unnest (ARRAY [%i, %i, %i, %i, %i, %i,"
797  " %i]))"
798  " THEN true"
799  " ELSE false"
800  " END;"
801  "$$ LANGUAGE SQL;",
802  TASK_STATUS_REQUESTED,
803  TASK_STATUS_RUNNING,
804  TASK_STATUS_DELETE_REQUESTED,
805  TASK_STATUS_DELETE_ULTIMATE_REQUESTED,
806  TASK_STATUS_STOP_REQUESTED,
807  TASK_STATUS_STOP_REQUESTED_GIVEUP,
808  TASK_STATUS_STOPPED);
809 
810  sql ("CREATE OR REPLACE FUNCTION report_progress (integer)"
811  " RETURNS integer AS $$"
812  /* Calculate the progress of a report. */
813  " SELECT CASE"
814  " WHEN $1 = 0"
815  " THEN -1"
816  " WHEN (SELECT slave_task_uuid FROM reports WHERE id = $1)"
817  " != ''"
818  " THEN (SELECT slave_progress FROM reports WHERE id = $1)"
819  " WHEN report_active ($1)"
820  " THEN report_progress_active ($1)"
821  " ELSE -1"
822  " END;"
823  "$$ LANGUAGE SQL;");
824 
825  sql ("CREATE OR REPLACE FUNCTION dynamic_severity ()"
826  " RETURNS boolean AS $$"
827  /* Get Dynamic Severity user setting. */
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"
832  " WHERE users.uuid"
833  " = (SELECT uuid"
834  " FROM current_credentials))))"
835  " ORDER BY coalesce (owner, 0) DESC LIMIT 1;"
836  "$$ LANGUAGE SQL;");
837 
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)"
843  " FROM nvts"
844  " WHERE nvts.oid = $2)"
845  " ELSE $1"
846  " END),"
847  " $1);"
848  "$$ LANGUAGE SQL;");
849 
850 #define OVERRIDES_SQL(severity_sql) \
851  " coalesce" \
852  " ((SELECT overrides.new_severity" \
853  " FROM overrides" \
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" \
859  " = (SELECT 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," \
870  " results.host))" \
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" \
881  " LIMIT 1)," \
882  " " severity_sql ")"
883 
884  /* min_qod column was added in version 147 */
885  if (current_db_version >= 147)
886  sql ("CREATE OR REPLACE FUNCTION report_severity (report integer,"
887  " overrides integer,"
888  " min_qod integer)"
889  " RETURNS double precision AS $$"
890  /* Calculate the severity of a report. */
891  " WITH max_severity AS (SELECT max(severity) AS max"
892  " FROM report_counts"
893  // FIX should have user like report_counts_cache_exists? c version too?
894  " WHERE report = $1"
895  " AND override = $2"
896  " AND min_qod = $3"
897  " AND (end_time = 0 or end_time >= m_now ()))"
898  " SELECT CASE"
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"
903  /* Dynamic severity, overrides on. */
904  " THEN (SELECT max"
905  " (" OVERRIDES_SQL
906  ("current_severity (results.severity,"
907  " results.nvt)") ")"
908  " FROM results"
909  " WHERE results.report = $1"
910  " AND results.qod >= $3)"
911  " WHEN dynamic_severity ()"
912  /* Dynamic severity, overrides off. */
913  " THEN (SELECT max (CASE"
914  " WHEN results.type IS NULL"
915  " THEN 0::real"
916  " ELSE current_severity"
917  " (results.severity, results.nvt)"
918  " END)"
919  " FROM results"
920  " WHERE results.report = $1"
921  " AND results.qod >= $3)"
922  " WHEN $2::boolean"
923  /* Overrides on. */
924  " THEN (SELECT max (" OVERRIDES_SQL ("results.severity") ")"
925  " FROM results"
926  " WHERE results.report = $1"
927  " AND results.qod >= $3)"
928  /* Overrides off. */
929  " ELSE (SELECT max (CASE"
930  " WHEN results.type IS NULL"
931  " THEN 0::real"
932  " ELSE results.severity"
933  " END)"
934  " FROM results"
935  " WHERE results.report = $1"
936  " AND results.qod >= $3)"
937  " END;"
938  "$$ LANGUAGE SQL;");
939 
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;"
944  "$$ LANGUAGE SQL;");
945 
946  sql ("CREATE OR REPLACE FUNCTION report_result_host_count (report integer,"
947  " min_qod 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)"
954  "$$ LANGUAGE SQL;");
955 
956  sql ("CREATE OR REPLACE FUNCTION severity_class ()"
957  " RETURNS text AS $$"
958  /* Get the user's severity class setting. */
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;"
966  "$$ LANGUAGE SQL;");
967 
968  /* min_qod column was added in version 147 */
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 $$"
974  /* Calculate the severity of a report. */
975  " WITH severity_count AS (SELECT sum (count) AS total"
976  " FROM report_counts"
977  " WHERE report = $1"
978  " AND override = $2"
979  " AND min_qod = $3"
980  " AND (end_time = 0"
981  " or end_time >= m_now ())"
982  " AND (severity"
983  " BETWEEN level_min_severity"
984  " ($4, severity_class ())"
985  " AND level_max_severity"
986  " ($4, severity_class ())))"
987  " SELECT CASE"
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"
992  /* Dynamic severity, overrides on. */
993  " THEN (SELECT count (*)"
994  " FROM results"
995  " WHERE results.report = $1"
996  " AND results.qod >= $3"
997  " AND (" OVERRIDES_SQL
998  ("current_severity (results.severity,"
999  " results.nvt)")
1000  " BETWEEN level_min_severity"
1001  " ($4, severity_class ())"
1002  " AND level_max_severity"
1003  " ($4, severity_class ())))"
1004  " WHEN dynamic_severity ()"
1005  /* Dynamic severity, overrides off. */
1006  " THEN (SELECT count (*)"
1007  " FROM results"
1008  " WHERE results.report = $1"
1009  " AND results.qod >= $3"
1010  " AND ((CASE"
1011  " WHEN results.type IS NULL"
1012  " THEN 0::real"
1013  " ELSE current_severity (results.severity,"
1014  " results.nvt)"
1015  " END)"
1016  " BETWEEN level_min_severity ($4, severity_class ())"
1017  " AND level_max_severity"
1018  " ($4, severity_class ())))"
1019  " WHEN $2::boolean"
1020  /* Overrides on. */
1021  " THEN (SELECT count (*)"
1022  " FROM results"
1023  " WHERE results.report = $1"
1024  " AND results.qod >= $3"
1025  " AND (" OVERRIDES_SQL ("results.severity")
1026  " BETWEEN level_min_severity ($4, severity_class ())"
1027  " AND level_max_severity"
1028  " ($4, severity_class ())))"
1029  /* Overrides off. */
1030  " ELSE (SELECT count (*)"
1031  " FROM results"
1032  " WHERE results.report = $1"
1033  " AND results.qod >= $3"
1034  " AND ((CASE"
1035  " WHEN results.type IS NULL"
1036  " THEN 0::real"
1037  " ELSE results.severity"
1038  " END)"
1039  " BETWEEN level_min_severity ($4, severity_class ())"
1040  " AND level_max_severity"
1041  " ($4, severity_class ())))"
1042  " END;"
1043  "$$ LANGUAGE SQL;");
1044 
1045  sql ("CREATE OR REPLACE FUNCTION task_last_report (integer)"
1046  " RETURNS integer AS $$"
1047  /* Get the report from the most recently completed invocation of task. */
1048  " SELECT id FROM reports WHERE task = $1 AND scan_run_status = %u"
1049  " ORDER BY date DESC LIMIT 1;"
1050  "$$ LANGUAGE SQL;",
1051  TASK_STATUS_DONE);
1052 
1053  sql ("CREATE OR REPLACE FUNCTION task_second_last_report (integer)"
1054  " RETURNS integer AS $$"
1055  /* Get report from second most recently completed invocation of task. */
1056  " SELECT id FROM reports WHERE task = $1 AND scan_run_status = %u"
1057  " ORDER BY date DESC LIMIT 1 OFFSET 1;"
1058  "$$ LANGUAGE SQL;",
1059  TASK_STATUS_DONE);
1060 
1061  if (current_db_version >= 147)
1062  {
1063  sql ("CREATE OR REPLACE FUNCTION task_severity (integer, integer,"
1064  " integer)"
1065  " RETURNS double precision AS $$"
1066  /* Calculate the severity of a task. */
1067  " SELECT CASE"
1068  " WHEN (SELECT target IS NULL OR target = 0"
1069  " FROM tasks WHERE id = $1)"
1070  " THEN CAST (NULL AS double precision)"
1071  " ELSE"
1072  " (SELECT report_severity ((SELECT id FROM reports"
1073  " WHERE task = $1"
1074  " AND scan_run_status = %u"
1075  " ORDER BY date DESC"
1076  " LIMIT 1 OFFSET 0), $2, $3))"
1077  " END;"
1078  "$$ LANGUAGE SQL;",
1079  TASK_STATUS_DONE);
1080 
1081  sql ("CREATE OR REPLACE FUNCTION task_trend (integer, integer, integer)"
1082  " RETURNS text AS $$"
1083  /* Calculate the trend of a task. */
1084  " DECLARE"
1085  " last_report integer;"
1086  " second_last_report integer;"
1087  " severity_a double precision;"
1088  " severity_b double precision;"
1089  " high_a bigint;"
1090  " high_b bigint;"
1091  " medium_a bigint;"
1092  " medium_b bigint;"
1093  " low_a bigint;"
1094  " low_b bigint;"
1095  " threat_a integer;"
1096  " threat_b integer;"
1097  " BEGIN"
1098  " CASE"
1099  /* Ensure there are enough reports. */
1100  " WHEN (SELECT count(*) <= 1 FROM reports"
1101  " WHERE task = $1"
1102  " AND scan_run_status = %u)"
1103  " THEN RETURN ''::text;"
1104  /* Get trend only for authenticated users. */
1105  " WHEN NOT EXISTS (SELECT uuid FROM current_credentials)"
1106  " OR (SELECT uuid = '' FROM current_credentials)"
1107  " THEN RETURN ''::text;"
1108  /* Skip running and container tasks. */
1109  " WHEN (SELECT run_status = %u OR target = 0"
1110  " FROM tasks WHERE id = $1)"
1111  " THEN RETURN ''::text;"
1112  " ELSE"
1113  " END CASE;"
1114  /* Check if the severity score changed. */
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;"
1123  " END IF;"
1124  /* Calculate trend. */
1125  " high_a := report_severity_count (last_report, $2, $3,"
1126  " 'high');"
1127  " high_b := report_severity_count (second_last_report, $2, $3,"
1128  " 'high');"
1129  " medium_a := report_severity_count (last_report, $2, $3,"
1130  " 'medium');"
1131  " medium_b := report_severity_count (second_last_report, $2, $3,"
1132  " 'medium');"
1133  " low_a := report_severity_count (last_report, $2, $3,"
1134  " 'low');"
1135  " low_b := report_severity_count (second_last_report, $2, $3,"
1136  " 'low');"
1137  " IF high_a > 0 THEN"
1138  " threat_a := 4;"
1139  " ELSIF medium_a > 0 THEN"
1140  " threat_a := 3;"
1141  " ELSIF low_a > 0 THEN"
1142  " threat_a := 2;"
1143  " ELSE"
1144  " threat_a := 1;"
1145  " END IF;"
1146  " IF high_b > 0 THEN"
1147  " threat_b := 4;"
1148  " ELSIF medium_b > 0 THEN"
1149  " threat_b := 3;"
1150  " ELSIF low_b > 0 THEN"
1151  " threat_b := 2;"
1152  " ELSE"
1153  " threat_b := 1;"
1154  " END IF;"
1155  /* Check if the threat level changed. */
1156  " IF threat_a > threat_b THEN"
1157  " RETURN 'up'::text;"
1158  " ELSIF threat_b > threat_a THEN"
1159  " RETURN 'down'::text;"
1160  " END IF;"
1161  /* Check if the threat count changed. */
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;"
1167  " END IF;"
1168  " RETURN 'same'::text;"
1169  " END IF;"
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;"
1175  " END IF;"
1176  " RETURN 'same'::text;"
1177  " END IF;"
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;"
1183  " END IF;"
1184  " RETURN 'same'::text;"
1185  " END IF;"
1186  " RETURN 'same'::text;"
1187  " END;"
1188  "$$ LANGUAGE plpgsql;",
1189  TASK_STATUS_DONE,
1190  TASK_STATUS_RUNNING);
1191  }
1192  }
1193 
1194  sql ("CREATE OR REPLACE FUNCTION run_status_name (integer)"
1195  " RETURNS text AS $$"
1196  /* Get the name of a task run status. */
1197  " SELECT CASE"
1198  " WHEN $1 = %i"
1199  " OR $1 = %i"
1200  " THEN 'Delete Requested'"
1201  " WHEN $1 = %i OR $1 = %i"
1202  " THEN 'Ultimate Delete Requested'"
1203  " WHEN $1 = %i"
1204  " THEN 'Done'"
1205  " WHEN $1 = %i"
1206  " THEN 'New'"
1207  " WHEN $1 = %i"
1208  " THEN 'Requested'"
1209  " WHEN $1 = %i"
1210  " THEN 'Running'"
1211  " WHEN $1 = %i OR $1 = %i OR $1 = %i"
1212  " THEN 'Stop Requested'"
1213  " WHEN $1 = %i"
1214  " THEN 'Stopped'"
1215  " ELSE 'Internal Error'"
1216  " END;"
1217  "$$ LANGUAGE SQL"
1218  " IMMUTABLE;",
1219  TASK_STATUS_DELETE_REQUESTED,
1220  TASK_STATUS_DELETE_WAITING,
1221  TASK_STATUS_DELETE_ULTIMATE_REQUESTED,
1222  TASK_STATUS_DELETE_ULTIMATE_WAITING,
1223  TASK_STATUS_DONE,
1224  TASK_STATUS_NEW,
1225  TASK_STATUS_REQUESTED,
1226  TASK_STATUS_RUNNING,
1227  TASK_STATUS_STOP_REQUESTED_GIVEUP,
1228  TASK_STATUS_STOP_REQUESTED,
1229  TASK_STATUS_STOP_WAITING,
1230  TASK_STATUS_STOPPED);
1231 
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')"
1236  " ::integer;",
1237  sql_database ()))
1238  sql ("CREATE OR REPLACE FUNCTION user_can_everything (text)"
1239  " RETURNS boolean AS $$"
1240  /* Test whether a user may perform any operation.
1241  *
1242  * This must match user_can_everything in manage_acl.c. */
1243  " SELECT count(*) > 0 FROM permissions"
1244  " WHERE resource = 0"
1245  " AND ((subject_type = 'user'"
1246  " AND subject"
1247  " = (SELECT id FROM users"
1248  " WHERE users.uuid = $1))"
1249  " OR (subject_type = 'group'"
1250  " AND subject"
1251  " IN (SELECT DISTINCT \"group\""
1252  " FROM group_users"
1253  " WHERE \"user\" = (SELECT id"
1254  " FROM users"
1255  " WHERE users.uuid"
1256  " = $1)))"
1257  " OR (subject_type = 'role'"
1258  " AND subject"
1259  " IN (SELECT DISTINCT role"
1260  " FROM role_users"
1261  " WHERE \"user\" = (SELECT id"
1262  " FROM users"
1263  " WHERE users.uuid"
1264  " = $1))))"
1265  " AND name = 'Everything';"
1266  "$$ LANGUAGE SQL;");
1267 
1268  sql ("CREATE OR REPLACE FUNCTION group_concat_pair (text, text, text)"
1269  " RETURNS text AS $$"
1270  " SELECT CASE"
1271  " WHEN $1 IS NULL OR $1 = ''"
1272  " THEN $2"
1273  " ELSE $1 || $3 || $2"
1274  " END;"
1275  "$$ LANGUAGE SQL"
1276  " IMMUTABLE;");
1277 
1278  sql ("DROP AGGREGATE IF EXISTS group_concat (text, text);");
1279 
1280  sql ("CREATE AGGREGATE group_concat (text, text)"
1281  " (sfunc = group_concat_pair,"
1282  " stype = text,"
1283  " initcond = '');");
1284 
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')"
1289  " ::integer;",
1290  sql_database ()))
1291  {
1292  sql ("CREATE OR REPLACE FUNCTION severity_in_level (double precision,"
1293  " text)"
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"
1299  " WHERE users.uuid"
1300  " = (SELECT uuid"
1301  " FROM current_credentials))))"
1302  " ORDER BY coalesce (owner, 0) DESC LIMIT 1)"
1303  " WHEN 'classic'"
1304  " THEN (CASE lower ($2)"
1305  " WHEN 'high'"
1306  " THEN $1 > 5"
1307  " AND $1 <= 10"
1308  " WHEN 'medium'"
1309  " THEN $1 > 2"
1310  " AND $1 <= 5"
1311  " WHEN 'low'"
1312  " THEN $1 > 0"
1313  " AND $1 <= 2"
1314  " WHEN 'none'"
1315  " THEN $1 = 0"
1316  " WHEN 'log'"
1317  " THEN $1 = 0"
1318  " ELSE 0::boolean"
1319  " END)"
1320  " WHEN 'pci-dss'"
1321  " THEN (CASE lower ($2)"
1322  " WHEN 'high'"
1323  " THEN $1 >= 4.0"
1324  " WHEN 'none'"
1325  " THEN $1 >= 0.0 AND $1 < 4.0"
1326  " WHEN 'log'"
1327  " THEN $1 >= 0.0 AND $1 < 4.0"
1328  " ELSE 0::boolean"
1329  " END)"
1330  " ELSE " /* NIST/BSI */
1331  " (CASE lower ($2)"
1332  " WHEN 'high'"
1333  " THEN $1 >= 7"
1334  " AND $1 <= 10"
1335  " WHEN 'medium'"
1336  " THEN $1 >= 4"
1337  " AND $1 < 7"
1338  " WHEN 'low'"
1339  " THEN $1 > 0"
1340  " AND $1 < 4"
1341  " WHEN 'none'"
1342  " THEN $1 = 0"
1343  " WHEN 'log'"
1344  " THEN $1 = 0"
1345  " ELSE 0::boolean"
1346  " END)"
1347  " END;"
1348  "$$ LANGUAGE SQL;");
1349 
1350  sql ("CREATE OR REPLACE FUNCTION severity_to_level (text, integer)"
1351  " RETURNS text AS $$"
1352  " SELECT CASE"
1353  " WHEN $1::double precision = " G_STRINGIFY (SEVERITY_LOG)
1354  " THEN 'Log'"
1355  " WHEN $1::double precision = " G_STRINGIFY (SEVERITY_FP)
1356  " THEN 'False Positive'"
1357  " WHEN $1::double precision = " G_STRINGIFY (SEVERITY_DEBUG)
1358  " THEN 'Debug'"
1359  " WHEN $1::double precision = " G_STRINGIFY (SEVERITY_ERROR)
1360  " THEN 'Error'"
1361  " WHEN $1::double precision > 0.0"
1362  " AND $1::double precision <= 10.0"
1363  " THEN (SELECT CASE"
1364  " WHEN $2 = 1"
1365  " THEN 'Alarm'"
1366  " WHEN severity_in_level ($1::double precision,"
1367  " 'high')"
1368  " THEN 'High'"
1369  " WHEN severity_in_level ($1::double precision,"
1370  " 'medium')"
1371  " THEN 'Medium'"
1372  " WHEN severity_in_level ($1::double precision,"
1373  " 'low')"
1374  " THEN 'Low'"
1375  " ELSE 'Log'"
1376  " END)"
1377  " ELSE 'Internal Error'"
1378  " END;"
1379  "$$ LANGUAGE SQL"
1380  " IMMUTABLE;");
1381 
1382  sql ("CREATE OR REPLACE FUNCTION severity_to_level (double precision,"
1383  " integer)"
1384  " RETURNS text AS $$"
1385  " SELECT CASE"
1386  " WHEN $1 = " G_STRINGIFY (SEVERITY_LOG)
1387  " THEN 'Log'"
1388  " WHEN $1 = " G_STRINGIFY (SEVERITY_FP)
1389  " THEN 'False Positive'"
1390  " WHEN $1 = " G_STRINGIFY (SEVERITY_DEBUG)
1391  " THEN 'Debug'"
1392  " WHEN $1 = " G_STRINGIFY (SEVERITY_ERROR)
1393  " THEN 'Error'"
1394  " WHEN $1 > 0.0 AND $1 <= 10.0"
1395  " THEN (SELECT CASE"
1396  " WHEN $2 = 1"
1397  " THEN 'Alarm'"
1398  " WHEN severity_in_level ($1, 'high')"
1399  " THEN 'High'"
1400  " WHEN severity_in_level ($1, 'medium')"
1401  " THEN 'Medium'"
1402  " WHEN severity_in_level ($1, 'low')"
1403  " THEN 'Low'"
1404  " ELSE 'Log'"
1405  " END)"
1406  " ELSE 'Internal Error'"
1407  " END;"
1408  "$$ LANGUAGE SQL"
1409  " IMMUTABLE;");
1410 
1411  if (current_db_version >= 147)
1412  sql ("CREATE OR REPLACE FUNCTION task_threat_level (integer, integer,"
1413  " integer)"
1414  " RETURNS text AS $$"
1415  /* Calculate the threat level of a task. */
1416  " SELECT severity_to_level (task_severity ($1, $2, $3), 0);"
1417  "$$ LANGUAGE SQL"
1418  " IMMUTABLE;");
1419  }
1420 
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'))"
1429  " ::integer;",
1430  sql_database (), sql_database ()))
1431  {
1432  sql ("CREATE OR REPLACE FUNCTION credential_value (integer, integer, text)"
1433  " RETURNS text AS $$"
1434  " SELECT CASE"
1435  " WHEN $2 != 0"
1436  " THEN"
1437  " (SELECT value FROM credentials_trash_data"
1438  " WHERE credential = $1 AND type = $3)"
1439  " ELSE"
1440  " (SELECT value FROM credentials_data"
1441  " WHERE credential = $1 AND type = $3)"
1442  " END;"
1443  "$$ LANGUAGE SQL;");
1444  }
1445 
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'))"
1454  " ::integer;",
1455  sql_database (), sql_database ()))
1456  {
1457  sql ("CREATE OR REPLACE FUNCTION target_credential (integer, integer, text)"
1458  " RETURNS integer AS $$"
1459  " SELECT CASE"
1460  " WHEN $2 != 0"
1461  " THEN"
1462  " (SELECT credential FROM targets_trash_login_data"
1463  " WHERE target = $1 AND type = $3)"
1464  " ELSE"
1465  " (SELECT credential FROM targets_login_data"
1466  " WHERE target = $1 AND type = $3)"
1467  " END;"
1468  "$$ LANGUAGE SQL;");
1469 
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;");
1475 
1476  sql ("CREATE OR REPLACE FUNCTION target_login_port (integer, integer, text)"
1477  " RETURNS integer AS $$"
1478  " SELECT CASE"
1479  " WHEN $2 != 0"
1480  " THEN"
1481  " (SELECT port FROM targets_trash_login_data"
1482  " WHERE target = $1 AND type = $3)"
1483  " ELSE"
1484  " (SELECT port FROM targets_login_data"
1485  " WHERE target = $1 AND type = $3)"
1486  " END;"
1487  "$$ LANGUAGE SQL;");
1488  }
1489 
1490  sql ("CREATE OR REPLACE FUNCTION lower (integer)"
1491  " RETURNS integer AS $$"
1492  " SELECT $1;"
1493  "$$ LANGUAGE SQL"
1494  " IMMUTABLE;");
1495 
1496  return 0;
1497 }
1498 
1499 
1500 /* Creation. */
1501 
1505 void
1506 create_tables ()
1507 {
1508  gchar *owned_clause;
1509 
1510  sql ("DROP TABLE IF EXISTS current_credentials");
1511  sql ("CREATE TABLE IF NOT EXISTS current_credentials"
1512  " (id SERIAL PRIMARY KEY,"
1513  " uuid text UNIQUE NOT NULL,"
1514  " tz_override text);");
1515 
1516  sql ("CREATE TABLE IF NOT EXISTS meta"
1517  " (id SERIAL PRIMARY KEY,"
1518  " name text UNIQUE NOT NULL,"
1519  " value text);");
1520 
1521  sql ("CREATE TABLE IF NOT EXISTS users"
1522  " (id SERIAL PRIMARY KEY,"
1523  " uuid text UNIQUE NOT NULL,"
1524  " owner integer REFERENCES users (id) ON DELETE RESTRICT,"
1525  " name text NOT NULL,"
1526  " comment text,"
1527  " password text,"
1528  " timezone text,"
1529  " hosts text,"
1530  " hosts_allow integer,"
1531  " ifaces text,"
1532  " ifaces_allow integer,"
1533  " method text,"
1534  " creation_time integer,"
1535  " modification_time integer);");
1536 
1537  sql ("CREATE TABLE IF NOT EXISTS auth_cache"
1538  " (id SERIAL PRIMARY KEY,"
1539  " username text NOT NULL,"
1540  " hash text,"
1541  " method integer,"
1542  " creation_time integer);");
1543 
1544  sql ("CREATE TABLE IF NOT EXISTS agents"
1545  " (id SERIAL PRIMARY KEY,"
1546  " uuid text UNIQUE NOT NULL,"
1547  " owner integer REFERENCES users (id) ON DELETE RESTRICT,"
1548  " name text NOT NULL,"
1549  " comment text,"
1550  " installer bytea,"
1551  " installer_64 text,"
1552  " installer_filename text,"
1553  " installer_signature_64 text,"
1554  " installer_trust integer,"
1555  " installer_trust_time integer,"
1556  " howto_install text,"
1557  " howto_use text,"
1558  " creation_time integer,"
1559  " modification_time integer);");
1560 
1561  sql ("CREATE TABLE IF NOT EXISTS agents_trash"
1562  " (id SERIAL PRIMARY KEY,"
1563  " uuid text UNIQUE NOT NULL,"
1564  " owner integer REFERENCES users (id) ON DELETE RESTRICT,"
1565  " name text NOT NULL,"
1566  " comment text,"
1567  " installer bytea,"
1568  " installer_64 text,"
1569  " installer_filename text,"
1570  " installer_signature_64 text,"
1571  " installer_trust integer,"
1572  " installer_trust_time integer,"
1573  " howto_install text,"
1574  " howto_use text,"
1575  " creation_time integer,"
1576  " modification_time integer);");
1577 
1578  sql ("CREATE TABLE IF NOT EXISTS alerts"
1579  " (id SERIAL PRIMARY KEY,"
1580  " uuid text UNIQUE NOT NULL,"
1581  " owner integer REFERENCES users (id) ON DELETE RESTRICT,"
1582  " name text NOT NULL,"
1583  " comment text,"
1584  " event integer,"
1585  " condition integer,"
1586  " method integer,"
1587  " filter integer,"
1588  " creation_time integer,"
1589  " modification_time integer);");
1590 
1591  sql ("CREATE TABLE IF NOT EXISTS alerts_trash"
1592  " (id SERIAL PRIMARY KEY,"
1593  " uuid text UNIQUE NOT NULL,"
1594  " owner integer REFERENCES users (id) ON DELETE RESTRICT,"
1595  " name text NOT NULL,"
1596  " comment text,"
1597  " event integer,"
1598  " condition integer,"
1599  " method integer,"
1600  " filter integer,"
1601  " filter_location integer,"
1602  " creation_time integer,"
1603  " modification_time integer);");
1604 
1605  sql ("CREATE TABLE IF NOT EXISTS alert_condition_data"
1606  " (id SERIAL PRIMARY KEY,"
1607  " alert integer REFERENCES alerts (id) ON DELETE RESTRICT,"
1608  " name text,"
1609  " data text);");
1610 
1611  sql ("CREATE TABLE IF NOT EXISTS alert_condition_data_trash"
1612  " (id SERIAL PRIMARY KEY,"
1613  " alert integer REFERENCES alerts_trash (id) ON DELETE RESTRICT,"
1614  " name text,"
1615  " data text);");
1616 
1617  sql ("CREATE TABLE IF NOT EXISTS alert_event_data"
1618  " (id SERIAL PRIMARY KEY,"
1619  " alert integer REFERENCES alerts (id) ON DELETE RESTRICT,"
1620  " name text,"
1621  " data text);");
1622 
1623  sql ("CREATE TABLE IF NOT EXISTS alert_event_data_trash"
1624  " (id SERIAL PRIMARY KEY,"
1625  " alert integer REFERENCES alerts_trash (id) ON DELETE RESTRICT,"
1626  " name text,"
1627  " data text);");
1628 
1629  sql ("CREATE TABLE IF NOT EXISTS alert_method_data"
1630  " (id SERIAL PRIMARY KEY,"
1631  " alert integer REFERENCES alerts (id) ON DELETE RESTRICT,"
1632  " name text,"
1633  " data text);");
1634 
1635  sql ("CREATE TABLE IF NOT EXISTS alert_method_data_trash"
1636  " (id SERIAL PRIMARY KEY,"
1637  " alert integer REFERENCES alerts_trash (id) ON DELETE RESTRICT,"
1638  " name text,"
1639  " data text);");
1640 
1641  sql ("CREATE TABLE IF NOT EXISTS credentials"
1642  " (id SERIAL PRIMARY KEY,"
1643  " uuid text UNIQUE NOT NULL,"
1644  " owner integer REFERENCES users (id) ON DELETE RESTRICT,"
1645  " name text NOT NULL,"
1646  " comment text,"
1647  " creation_time integer,"
1648  " modification_time integer,"
1649  " type text,"
1650  " allow_insecure integer);");
1651 
1652  sql ("CREATE TABLE IF NOT EXISTS credentials_trash"
1653  " (id SERIAL PRIMARY KEY,"
1654  " uuid text UNIQUE NOT NULL,"
1655  " owner integer REFERENCES users (id) ON DELETE RESTRICT,"
1656  " name text NOT NULL,"
1657  " comment text,"
1658  " creation_time integer,"
1659  " modification_time integer,"
1660  " type text,"
1661  " allow_insecure integer);");
1662 
1663  sql ("CREATE TABLE IF NOT EXISTS credentials_data"
1664  " (id SERIAL PRIMARY KEY,"
1665  " credential INTEGER REFERENCES credentials (id) ON DELETE RESTRICT,"
1666  " type TEXT,"
1667  " value TEXT);");
1668 
1669  sql ("CREATE TABLE IF NOT EXISTS credentials_trash_data"
1670  " (id SERIAL PRIMARY KEY,"
1671  " credential INTEGER REFERENCES credentials_trash (id) ON DELETE RESTRICT,"
1672  " type TEXT,"
1673  " value TEXT);");
1674 
1675  sql ("CREATE TABLE IF NOT EXISTS filters"
1676  " (id SERIAL PRIMARY KEY,"
1677  " uuid text UNIQUE NOT NULL,"
1678  " owner integer REFERENCES users (id) ON DELETE RESTRICT,"
1679  " name text NOT NULL,"
1680  " comment text,"
1681  " type text,"
1682  " term text,"
1683  " creation_time integer,"
1684  " modification_time integer);");
1685 
1686  sql ("CREATE TABLE IF NOT EXISTS filters_trash"
1687  " (id SERIAL PRIMARY KEY,"
1688  " uuid text UNIQUE NOT NULL,"
1689  " owner integer REFERENCES users (id) ON DELETE RESTRICT,"
1690  " name text NOT NULL,"
1691  " comment text,"
1692  " type text,"
1693  " term text,"
1694  " creation_time integer,"
1695  " modification_time integer);");
1696 
1697  sql ("CREATE TABLE IF NOT EXISTS groups"
1698  " (id SERIAL PRIMARY KEY,"
1699  " uuid text UNIQUE NOT NULL,"
1700  " owner integer REFERENCES users (id) ON DELETE RESTRICT,"
1701  " name text NOT NULL,"
1702  " comment text,"
1703  " creation_time integer,"
1704  " modification_time integer);");
1705 
1706  sql ("CREATE TABLE IF NOT EXISTS groups_trash"
1707  " (id SERIAL PRIMARY KEY,"
1708  " uuid text UNIQUE NOT NULL,"
1709  " owner integer REFERENCES users (id) ON DELETE RESTRICT,"
1710  " name text NOT NULL,"
1711  " comment text,"
1712  " creation_time integer,"
1713  " modification_time integer);");
1714 
1715  sql ("CREATE TABLE IF NOT EXISTS group_users"
1716  " (id SERIAL PRIMARY KEY,"
1717  " \"group\" integer REFERENCES groups (id) ON DELETE RESTRICT,"
1718  " \"user\" integer REFERENCES users (id) ON DELETE RESTRICT);");
1719 
1720  sql ("CREATE TABLE IF NOT EXISTS group_users_trash"
1721  " (id SERIAL PRIMARY KEY,"
1722  " \"group\" integer REFERENCES groups_trash (id) ON DELETE RESTRICT,"
1723  " \"user\" integer REFERENCES users (id) ON DELETE RESTRICT);");
1724 
1725  sql ("CREATE TABLE IF NOT EXISTS hosts"
1726  " (id SERIAL PRIMARY KEY,"
1727  " uuid text UNIQUE NOT NULL,"
1728  " owner integer REFERENCES users (id) ON DELETE RESTRICT,"
1729  " name text NOT NULL,"
1730  " comment text,"
1731  " creation_time integer,"
1732  " modification_time integer);");
1733 
1734  sql ("CREATE TABLE IF NOT EXISTS host_identifiers"
1735  " (id SERIAL PRIMARY KEY,"
1736  " uuid text UNIQUE NOT NULL,"
1737  " host integer REFERENCES hosts (id) ON DELETE RESTRICT,"
1738  " owner integer REFERENCES users (id) ON DELETE RESTRICT,"
1739  " name text NOT NULL,"
1740  " comment text,"
1741  " value text NOT NULL,"
1742  " source_type text NOT NULL,"
1743  " source_id text NOT NULL,"
1744  " source_data text NOT NULL,"
1745  " creation_time integer,"
1746  " modification_time integer);");
1747 
1748  sql ("CREATE TABLE IF NOT EXISTS oss"
1749  " (id SERIAL PRIMARY KEY,"
1750  " uuid text UNIQUE NOT NULL,"
1751  " owner integer REFERENCES users (id) ON DELETE RESTRICT,"
1752  " name text NOT NULL,"
1753  " comment text,"
1754  " creation_time integer,"
1755  " modification_time integer);");
1756 
1757  sql ("CREATE TABLE IF NOT EXISTS host_oss"
1758  " (id SERIAL PRIMARY KEY,"
1759  " uuid text UNIQUE NOT NULL,"
1760  " host integer REFERENCES hosts (id) ON DELETE RESTRICT,"
1761  " owner integer REFERENCES users (id) ON DELETE RESTRICT,"
1762  " name text NOT NULL,"
1763  " comment text,"
1764  " os integer REFERENCES oss (id) ON DELETE RESTRICT,"
1765  " source_type text NOT NULL,"
1766  " source_id text NOT NULL,"
1767  " source_data text NOT NULL,"
1768  " creation_time integer,"
1769  " modification_time integer);");
1770 
1771  sql ("CREATE TABLE IF NOT EXISTS host_max_severities"
1772  " (id SERIAL PRIMARY KEY,"
1773  " host integer REFERENCES hosts (id) ON DELETE RESTRICT,"
1774  " severity real,"
1775  " source_type text NOT NULL,"
1776  " source_id text NOT NULL,"
1777  " creation_time integer);");
1778 
1779  sql ("CREATE TABLE IF NOT EXISTS host_details"
1780  " (id SERIAL PRIMARY KEY,"
1781  " host integer REFERENCES hosts (id) ON DELETE RESTRICT,"
1782  /* The report that the host detail came from. */
1783  " source_type text NOT NULL,"
1784  " source_id text NOT NULL,"
1785  /* The original source of the host detail, from the scanner. */
1786  " detail_source_type text,"
1787  " detail_source_name text,"
1788  " detail_source_description text,"
1789  " name text,"
1790  " value text);");
1791 
1792  sql ("CREATE TABLE IF NOT EXISTS roles"
1793  " (id SERIAL PRIMARY KEY,"
1794  " uuid text UNIQUE NOT NULL,"
1795  " owner integer REFERENCES users (id) ON DELETE RESTRICT,"
1796  " name text NOT NULL,"
1797  " comment text,"
1798  " creation_time integer,"
1799  " modification_time integer);");
1800 
1801  sql ("CREATE TABLE IF NOT EXISTS roles_trash"
1802  " (id SERIAL PRIMARY KEY,"
1803  " uuid text UNIQUE NOT NULL,"
1804  " owner integer REFERENCES users (id) ON DELETE RESTRICT,"
1805  " name text NOT NULL,"
1806  " comment text,"
1807  " creation_time integer,"
1808  " modification_time integer);");
1809 
1810  sql ("CREATE TABLE IF NOT EXISTS role_users"
1811  " (id SERIAL PRIMARY KEY,"
1812  " role integer REFERENCES roles (id) ON DELETE RESTRICT,"
1813  " \"user\" integer REFERENCES users (id) ON DELETE RESTRICT);");
1814 
1815  sql ("CREATE TABLE IF NOT EXISTS role_users_trash"
1816  " (id SERIAL PRIMARY KEY,"
1817  " role integer REFERENCES roles_trash (id) ON DELETE RESTRICT,"
1818  " \"user\" integer REFERENCES users (id) ON DELETE RESTRICT);");
1819 
1820  sql ("CREATE TABLE IF NOT EXISTS nvt_selectors"
1821  " (id SERIAL PRIMARY KEY,"
1822  " name text,"
1823  " exclude integer,"
1824  " type integer,"
1825  " family_or_nvt text,"
1826  " family text);");
1827 
1828  sql ("CREATE TABLE IF NOT EXISTS port_lists"
1829  " (id SERIAL PRIMARY KEY,"
1830  " uuid text UNIQUE NOT NULL,"
1831  " owner integer REFERENCES users (id) ON DELETE RESTRICT,"
1832  " name text NOT NULL,"
1833  " comment text,"
1834  " creation_time integer,"
1835  " modification_time integer);");
1836 
1837  sql ("CREATE TABLE IF NOT EXISTS port_lists_trash"
1838  " (id SERIAL PRIMARY KEY,"
1839  " uuid text UNIQUE NOT NULL,"
1840  " owner integer REFERENCES users (id) ON DELETE RESTRICT,"
1841  " name text NOT NULL,"
1842  " comment text,"
1843  " creation_time integer,"
1844  " modification_time integer);");
1845 
1846  sql ("CREATE TABLE IF NOT EXISTS port_ranges"
1847  " (id SERIAL PRIMARY KEY,"
1848  " uuid text UNIQUE NOT NULL,"
1849  " port_list integer REFERENCES port_lists (id) ON DELETE RESTRICT,"
1850  " type integer,"
1851  " start integer,"
1852  " \"end\" integer,"
1853  " comment text,"
1854  " exclude integer);");
1855 
1856  sql ("CREATE TABLE IF NOT EXISTS port_ranges_trash"
1857  " (id SERIAL PRIMARY KEY,"
1858  " uuid text UNIQUE NOT NULL,"
1859  " port_list integer REFERENCES port_lists_trash (id) ON DELETE RESTRICT,"
1860  " type integer,"
1861  " start integer,"
1862  " \"end\" integer,"
1863  " comment text,"
1864  " exclude integer);");
1865 
1866  sql ("CREATE TABLE IF NOT EXISTS port_names"
1867  " (id SERIAL PRIMARY KEY,"
1868  " number integer,"
1869  " protocol text,"
1870  " name text,"
1871  " UNIQUE (number, protocol));");
1872 
1873  sql ("CREATE TABLE IF NOT EXISTS targets"
1874  " (id SERIAL PRIMARY KEY,"
1875  " uuid text UNIQUE NOT NULL,"
1876  " owner integer REFERENCES users (id) ON DELETE RESTRICT,"
1877  " name text NOT NULL,"
1878  " hosts text,"
1879  " exclude_hosts text,"
1880  " reverse_lookup_only integer,"
1881  " reverse_lookup_unify integer,"
1882  " comment text,"
1883  " port_list integer REFERENCES port_lists (id) ON DELETE RESTRICT,"
1884  " alive_test integer,"
1885  " creation_time integer,"
1886  " modification_time integer);");
1887 
1888  sql ("CREATE TABLE IF NOT EXISTS targets_trash"
1889  " (id SERIAL PRIMARY KEY,"
1890  " uuid text UNIQUE NOT NULL,"
1891  " owner integer REFERENCES users (id) ON DELETE RESTRICT,"
1892  " name text NOT NULL,"
1893  " hosts text,"
1894  " exclude_hosts text,"
1895  " reverse_lookup_only integer,"
1896  " reverse_lookup_unify integer,"
1897  " comment text,"
1898  " port_list integer," // REFERENCES port_lists (id) ON DELETE RESTRICT,"
1899  " port_list_location integer,"
1900  " alive_test integer,"
1901  " creation_time integer,"
1902  " modification_time integer);");
1903 
1904  sql ("CREATE TABLE IF NOT EXISTS targets_login_data"
1905  " (id SERIAL PRIMARY KEY,"
1906  " target INTEGER REFERENCES targets (id) ON DELETE RESTRICT,"
1907  " type TEXT,"
1908  " credential INTEGER REFERENCES credentials (id) ON DELETE RESTRICT,"
1909  " port INTEGER);");
1910 
1911  sql ("CREATE TABLE IF NOT EXISTS targets_trash_login_data"
1912  " (id SERIAL PRIMARY KEY,"
1913  " target INTEGER REFERENCES targets_trash (id) ON DELETE RESTRICT,"
1914  " type TEXT,"
1915  " credential INTEGER,"//REFERENCES credentials (id) ON DELETE RESTRICT,"
1916  " port INTEGER,"
1917  " credential_location INTEGER);");
1918 
1919  sql ("CREATE TABLE IF NOT EXISTS scanners"
1920  " (id SERIAL PRIMARY KEY,"
1921  " uuid text UNIQUE NOT NULL,"
1922  " owner integer REFERENCES users (id) ON DELETE RESTRICT,"
1923  " name text,"
1924  " comment text,"
1925  " host text,"
1926  " port integer,"
1927  " type integer,"
1928  " ca_pub text,"
1929  " credential integer REFERENCES credentials (id) ON DELETE RESTRICT,"
1930  " creation_time integer,"
1931  " modification_time integer);");
1932 
1933  sql ("CREATE TABLE IF NOT EXISTS configs"
1934  " (id SERIAL PRIMARY KEY,"
1935  " uuid text UNIQUE NOT NULL,"
1936  " owner integer REFERENCES users (id) ON DELETE RESTRICT,"
1937  " name text NOT NULL,"
1938  " nvt_selector text," /* REFERENCES nvt_selectors (name) */
1939  " comment text,"
1940  " family_count integer,"
1941  " nvt_count integer,"
1942  " families_growing integer,"
1943  " nvts_growing integer,"
1944  " type integer,"
1945  " scanner integer REFERENCES scanners (id) ON DELETE RESTRICT,"
1946  " creation_time integer,"
1947  " modification_time integer);");
1948 
1949  sql ("CREATE TABLE IF NOT EXISTS configs_trash"
1950  " (id SERIAL PRIMARY KEY,"
1951  " uuid text UNIQUE NOT NULL,"
1952  " owner integer REFERENCES users (id) ON DELETE RESTRICT,"
1953  " name text NOT NULL,"
1954  " nvt_selector text," /* REFERENCES nvt_selectors (name) */
1955  " comment text,"
1956  " family_count integer,"
1957  " nvt_count integer,"
1958  " families_growing integer,"
1959  " nvts_growing integer,"
1960  " type integer,"
1961  " scanner integer REFERENCES scanners (id) ON DELETE RESTRICT,"
1962  " creation_time integer,"
1963  " modification_time integer);");
1964 
1965  sql ("CREATE TABLE IF NOT EXISTS config_preferences"
1966  " (id SERIAL PRIMARY KEY,"
1967  " config integer REFERENCES configs (id) ON DELETE RESTRICT,"
1968  " type text,"
1969  " name text,"
1970  " value text,"
1971  " default_value text,"
1972  " hr_name text);");
1973 
1974  sql ("CREATE TABLE IF NOT EXISTS config_preferences_trash"
1975  " (id SERIAL PRIMARY KEY,"
1976  " config integer REFERENCES configs_trash (id) ON DELETE RESTRICT,"
1977  " type text,"
1978  " name text,"
1979  " value text,"
1980  " default_value text,"
1981  " hr_name text);");
1982 
1983  sql ("CREATE TABLE IF NOT EXISTS schedules"
1984  " (id SERIAL PRIMARY KEY,"
1985  " uuid text UNIQUE NOT NULL,"
1986  " owner integer REFERENCES users (id) ON DELETE RESTRICT,"
1987  " name text NOT NULL,"
1988  " comment text,"
1989  " first_time integer,"
1990  " period integer,"
1991  " period_months integer,"
1992  " duration integer,"
1993  " timezone text,"
1994  " initial_offset integer,"
1995  " creation_time integer,"
1996  " modification_time integer);");
1997 
1998  sql ("CREATE TABLE IF NOT EXISTS schedules_trash"
1999  " (id SERIAL PRIMARY KEY,"
2000  " uuid text UNIQUE NOT NULL,"
2001  " owner integer REFERENCES users (id) ON DELETE RESTRICT,"
2002  " name text NOT NULL,"
2003  " comment text,"
2004  " first_time integer,"
2005  " period integer,"
2006  " period_months integer,"
2007  " duration integer,"
2008  " timezone text,"
2009  " initial_offset integer,"
2010  " creation_time integer,"
2011  " modification_time integer);");
2012 
2013  sql ("CREATE TABLE IF NOT EXISTS scanners_trash"
2014  " (id SERIAL PRIMARY KEY,"
2015  " uuid text UNIQUE NOT NULL,"
2016  " owner integer REFERENCES users (id) ON DELETE RESTRICT,"
2017  " name text,"
2018  " comment text,"
2019  " host text,"
2020  " port integer,"
2021  " type integer,"
2022  " ca_pub text,"
2023  " credential integer,"
2024  " credential_location integer,"
2025  " creation_time integer,"
2026  " modification_time integer);");
2027 
2028  sql ("CREATE TABLE IF NOT EXISTS tasks"
2029  " (id SERIAL PRIMARY KEY,"
2030  " uuid text UNIQUE NOT NULL,"
2031  " owner integer REFERENCES users (id) ON DELETE RESTRICT,"
2032  " name text,"
2033  " hidden integer,"
2034  " comment text,"
2035  " run_status integer,"
2036  " start_time integer,"
2037  " end_time integer,"
2038  " config integer," // REFERENCES configs (id) ON DELETE RESTRICT,"
2039  " target integer," // REFERENCES targets (id) ON DELETE RESTRICT,"
2040  " schedule integer," // REFERENCES schedules (id) ON DELETE RESTRICT,"
2041  " schedule_next_time integer,"
2042  " schedule_periods integer,"
2043  " scanner integer," // REFERENCES scanner (id) ON DELETE RESTRICT,"
2044  " config_location integer,"
2045  " target_location integer,"
2046  " schedule_location integer,"
2047  " scanner_location integer,"
2048  " upload_result_count integer,"
2049  " hosts_ordering text,"
2050  " alterable integer,"
2051  " creation_time integer,"
2052  " modification_time integer);");
2053 
2054  sql ("CREATE TABLE IF NOT EXISTS task_files"
2055  " (id SERIAL PRIMARY KEY,"
2056  " task integer REFERENCES tasks (id) ON DELETE RESTRICT,"
2057  " name text,"
2058  " content text);");
2059 
2060  sql ("CREATE TABLE IF NOT EXISTS task_alerts"
2061  " (id SERIAL PRIMARY KEY,"
2062  " task integer REFERENCES tasks (id) ON DELETE RESTRICT,"
2063  " alert integer," // REFERENCES alerts (id) ON DELETE RESTRICT,"
2064  " alert_location integer);");
2065 
2066  sql ("CREATE TABLE IF NOT EXISTS task_preferences"
2067  " (id SERIAL PRIMARY KEY,"
2068  " task integer REFERENCES tasks (id) ON DELETE RESTRICT,"
2069  " name text,"
2070  " value text);");
2071 
2072  sql ("CREATE TABLE IF NOT EXISTS reports"
2073  " (id SERIAL PRIMARY KEY,"
2074  " uuid text UNIQUE NOT NULL,"
2075  " owner integer REFERENCES users (id) ON DELETE RESTRICT,"
2076  " hidden integer,"
2077  " task integer REFERENCES tasks (id) ON DELETE RESTRICT,"
2078  " date integer,"
2079  " start_time integer,"
2080  " end_time integer,"
2081  " nbefile text,"
2082  " comment text,"
2083  " scan_run_status integer,"
2084  " slave_progress integer,"
2085  " slave_task_uuid text,"
2086  " slave_uuid text,"
2087  " slave_name text,"
2088  " slave_host text,"
2089  " slave_port integer,"
2090  " source_iface text,"
2091  " flags integer);");
2092 
2093  sql ("CREATE TABLE IF NOT EXISTS report_counts"
2094  " (id SERIAL PRIMARY KEY,"
2095  " report integer REFERENCES reports (id) ON DELETE RESTRICT,"
2096  " \"user\" integer REFERENCES users (id) ON DELETE RESTRICT,"
2097  " severity decimal,"
2098  " count integer,"
2099  " override integer,"
2100  " end_time integer,"
2101  " min_qod integer);");
2102 
2103  sql ("CREATE TABLE IF NOT EXISTS resources_predefined"
2104  " (id SERIAL PRIMARY KEY,"
2105  " resource_type text,"
2106  " resource integer);");
2107 
2108  sql ("CREATE TABLE IF NOT EXISTS results"
2109  " (id SERIAL PRIMARY KEY,"
2110  " uuid text UNIQUE NOT NULL,"
2111  " task integer REFERENCES tasks (id) ON DELETE RESTRICT,"
2112  " host text,"
2113  " port text,"
2114  " nvt text,"
2115  " type text,"
2116  " description text,"
2117  " report integer REFERENCES reports (id) ON DELETE RESTRICT,"
2118  " nvt_version text,"
2119  " severity real,"
2120  " qod integer,"
2121  " qod_type text,"
2122  " owner integer REFERENCES users (id) ON DELETE RESTRICT,"
2123  " date integer);");
2124 
2125  sql ("CREATE TABLE IF NOT EXISTS report_formats"
2126  " (id SERIAL PRIMARY KEY,"
2127  " uuid text UNIQUE NOT NULL,"
2128  " owner integer REFERENCES users (id) ON DELETE RESTRICT,"
2129  " name text NOT NULL,"
2130  " extension text,"
2131  " content_type text,"
2132  " summary text,"
2133  " description text,"
2134  " signature text,"
2135  " trust integer,"
2136  " trust_time integer,"
2137  " flags integer,"
2138  " creation_time integer,"
2139  " modification_time integer);");
2140 
2141  sql ("CREATE TABLE IF NOT EXISTS report_formats_trash"
2142  " (id SERIAL PRIMARY KEY,"
2143  " uuid text UNIQUE NOT NULL,"
2144  " owner integer REFERENCES users (id) ON DELETE RESTRICT,"
2145  " name text NOT NULL,"
2146  " extension text,"
2147  " content_type text,"
2148  " summary text,"
2149  " description text,"
2150  " signature text,"
2151  " trust integer,"
2152  " trust_time integer,"
2153  " flags integer,"
2154  " original_uuid text,"
2155  " creation_time integer,"
2156  " modification_time integer);");
2157 
2158  sql ("CREATE TABLE IF NOT EXISTS report_format_params"
2159  " (id SERIAL PRIMARY KEY,"
2160  " report_format integer REFERENCES report_formats (id) ON DELETE RESTRICT,"
2161  " name text,"
2162  " type integer,"
2163  " value text,"
2164  " type_min bigint,"
2165  " type_max bigint,"
2166  " type_regex text,"
2167  " fallback text);");
2168 
2169  sql ("CREATE TABLE IF NOT EXISTS report_format_params_trash"
2170  " (id SERIAL PRIMARY KEY,"
2171  " report_format integer REFERENCES report_formats_trash (id) ON DELETE RESTRICT,"
2172  " name text,"
2173  " type integer,"
2174  " value text,"
2175  " type_min bigint,"
2176  " type_max bigint,"
2177  " type_regex text,"
2178  " fallback text);");
2179 
2180  sql ("CREATE TABLE IF NOT EXISTS report_format_param_options"
2181  " (id SERIAL PRIMARY KEY,"
2182  " report_format_param integer REFERENCES report_format_params (id) ON DELETE RESTRICT,"
2183  " value text);");
2184 
2185  sql ("CREATE TABLE IF NOT EXISTS report_format_param_options_trash"
2186  " (id SERIAL PRIMARY KEY,"
2187  " report_format_param integer REFERENCES report_format_params_trash (id) ON DELETE RESTRICT,"
2188  " value text);");
2189 
2190  sql ("CREATE TABLE IF NOT EXISTS report_hosts"
2191  " (id SERIAL PRIMARY KEY,"
2192  " report integer REFERENCES reports (id) ON DELETE RESTRICT,"
2193  " host text,"
2194  " start_time integer,"
2195  " end_time integer,"
2196  " current_port integer,"
2197  " max_port integer);");
2198 
2199  sql ("CREATE TABLE IF NOT EXISTS report_host_details"
2200  " (id SERIAL PRIMARY KEY,"
2201  " report_host integer REFERENCES report_hosts (id) ON DELETE RESTRICT,"
2202  " source_type text,"
2203  " source_name text,"
2204  " source_description text,"
2205  " name text,"
2206  " value text);");
2207 
2208  sql ("CREATE TABLE IF NOT EXISTS nvt_preferences"
2209  " (id SERIAL PRIMARY KEY,"
2210  " name text UNIQUE NOT NULL,"
2211  " value text);");
2212 
2213  sql ("CREATE TABLE IF NOT EXISTS nvts"
2214  " (id SERIAL PRIMARY KEY,"
2215  " uuid text UNIQUE NOT NULL,"
2216  " oid text UNIQUE NOT NULL,"
2217  " version text,"
2218  " name text,"
2219  " comment text,"
2220  " copyright text,"
2221  " cve text,"
2222  " bid text,"
2223  " xref text,"
2224  " tag text,"
2225  " category text,"
2226  " family text,"
2227  " cvss_base text,"
2228  " creation_time integer,"
2229  " modification_time integer,"
2230  " solution_type text,"
2231  " qod integer,"
2232  " qod_type text);");
2233 
2234  sql ("CREATE TABLE IF NOT EXISTS nvt_cves"
2235  " (id SERIAL PRIMARY KEY,"
2236  " nvt integer REFERENCES nvts (id) ON DELETE RESTRICT,"
2237  " oid text,"
2238  " cve_name text);");
2239 
2240  sql ("CREATE TABLE IF NOT EXISTS notes"
2241  " (id SERIAL PRIMARY KEY,"
2242  " uuid text UNIQUE NOT NULL,"
2243  " owner integer REFERENCES users (id) ON DELETE RESTRICT,"
2244  " nvt text NOT NULL,"
2245  " creation_time integer,"
2246  " modification_time integer,"
2247  " text text,"
2248  " hosts text,"
2249  " port text,"
2250  " severity double precision,"
2251  " task integer," // REFERENCES tasks (id) ON DELETE RESTRICT,"
2252  " result integer," // REFERENCES results (id) ON DELETE RESTRICT,"
2253  " end_time integer);");
2254 
2255  sql ("CREATE TABLE IF NOT EXISTS notes_trash"
2256  " (id SERIAL PRIMARY KEY,"
2257  " uuid text UNIQUE NOT NULL,"
2258  " owner integer REFERENCES users (id) ON DELETE RESTRICT,"
2259  " nvt text NOT NULL,"
2260  " creation_time integer,"
2261  " modification_time integer,"
2262  " text text,"
2263  " hosts text,"
2264  " port text,"
2265  " severity double precision,"
2266  " task integer," // REFERENCES tasks (id) ON DELETE RESTRICT,"
2267  " result integer," // REFERENCES results (id) ON DELETE RESTRICT,"
2268  " end_time integer);");
2269 
2270  sql ("CREATE TABLE IF NOT EXISTS overrides"
2271  " (id SERIAL PRIMARY KEY,"
2272  " uuid text UNIQUE NOT NULL,"
2273  " owner integer REFERENCES users (id) ON DELETE RESTRICT,"
2274  " nvt text NOT NULL,"
2275  " creation_time integer,"
2276  " modification_time integer,"
2277  " text text,"
2278  " hosts text,"
2279  " new_severity double precision,"
2280  " port text,"
2281  " severity double precision,"
2282  " task integer," // REFERENCES tasks (id) ON DELETE RESTRICT,"
2283  " result integer," // REFERENCES results (id) ON DELETE RESTRICT,"
2284  " end_time integer);");
2285 
2286  sql ("CREATE TABLE IF NOT EXISTS overrides_trash"
2287  " (id SERIAL PRIMARY KEY,"
2288  " uuid text UNIQUE NOT NULL,"
2289  " owner integer REFERENCES users (id) ON DELETE RESTRICT,"
2290  " nvt text NOT NULL,"
2291  " creation_time integer,"
2292  " modification_time integer,"
2293  " text text,"
2294  " hosts text,"
2295  " new_severity double precision,"
2296  " port text,"
2297  " severity double precision,"
2298  " task integer," // REFERENCES tasks (id) ON DELETE RESTRICT,"
2299  " result integer," // REFERENCES results (id) ON DELETE RESTRICT,"
2300  " end_time integer);");
2301 
2302  sql ("CREATE TABLE IF NOT EXISTS permissions"
2303  " (id SERIAL PRIMARY KEY,"
2304  " uuid text UNIQUE NOT NULL,"
2305  " owner integer REFERENCES users (id) ON DELETE RESTRICT,"
2306  " name text NOT NULL,"
2307  " comment text,"
2308  " resource_type text,"
2309  " resource integer,"
2310  " resource_uuid text,"
2311  " resource_location integer,"
2312  " subject_type text,"
2313  " subject integer,"
2314  " subject_location integer,"
2315  " creation_time integer,"
2316  " modification_time integer);");
2317 
2318  sql ("CREATE TABLE IF NOT EXISTS permissions_trash"
2319  " (id SERIAL PRIMARY KEY,"
2320  " uuid text UNIQUE NOT NULL,"
2321  " owner integer REFERENCES users (id) ON DELETE RESTRICT,"
2322  " name text NOT NULL,"
2323  " comment text,"
2324  " resource_type text,"
2325  " resource integer,"
2326  " resource_uuid text,"
2327  " resource_location integer,"
2328  " subject_type text,"
2329  " subject integer,"
2330  " subject_location integer,"
2331  " creation_time integer,"
2332  " modification_time integer);");
2333 
2334  sql ("CREATE TABLE IF NOT EXISTS settings"
2335  " (id SERIAL PRIMARY KEY,"
2336  " uuid text NOT NULL," /* Note: not UNIQUE. */
2337  " owner integer REFERENCES users (id) ON DELETE RESTRICT,"
2338  " name text NOT NULL,"
2339  " comment text,"
2340  " value text);");
2341 
2342  sql ("CREATE TABLE IF NOT EXISTS tags"
2343  " (id SERIAL PRIMARY KEY,"
2344  " uuid text UNIQUE NOT NULL,"
2345  " owner integer REFERENCES users (id) ON DELETE RESTRICT,"
2346  " name text NOT NULL,"
2347  " comment text,"
2348  " resource_type text,"
2349  " resource integer,"
2350  " resource_uuid text,"
2351  " resource_location integer,"
2352  " active integer,"
2353  " value text,"
2354  " creation_time integer,"
2355  " modification_time integer);");
2356 
2357  sql ("CREATE TABLE IF NOT EXISTS tags_trash"
2358  " (id SERIAL PRIMARY KEY,"
2359  " uuid text UNIQUE NOT NULL,"
2360  " owner integer REFERENCES users (id) ON DELETE RESTRICT,"
2361  " name text NOT NULL,"
2362  " comment text,"
2363  " resource_type text,"
2364  " resource integer,"
2365  " resource_uuid text,"
2366  " resource_location integer,"
2367  " active integer,"
2368  " value text,"
2369  " creation_time integer,"
2370  " modification_time integer);");
2371 
2372  /* Create result views. */
2373 
2374  /* Create functions, so that current_severity is created for
2375  * result_new_severities. */
2376  manage_create_sql_functions ();
2377 
2378  owned_clause = acl_where_owned_for_get ("override", "users.id");
2379 
2380  sql ("CREATE OR REPLACE VIEW result_overrides AS"
2381  " SELECT users.id AS user,"
2382  " results.id as result,"
2383  " overrides.id AS override,"
2384  " overrides.severity AS ov_old_severity,"
2385  " overrides.new_severity AS ov_new_severity"
2386  " FROM users, results, overrides"
2387  " WHERE overrides.nvt = results.nvt"
2388  " AND (overrides.result = 0 OR overrides.result = results.id)"
2389  " AND %s"
2390  " AND ((overrides.end_time = 0)"
2391  " OR (overrides.end_time >= m_now ()))"
2392  " AND (overrides.task ="
2393  " (SELECT reports.task FROM reports"
2394  " WHERE results.report = reports.id)"
2395  " OR overrides.task = 0)"
2396  " AND (overrides.result = results.id"
2397  " OR overrides.result = 0)"
2398  " AND (overrides.hosts is NULL"
2399  " OR overrides.hosts = ''"
2400  " OR hosts_contains (overrides.hosts, results.host))"
2401  " AND (overrides.port is NULL"
2402  " OR overrides.port = ''"
2403  " OR overrides.port = results.port)"
2404  " ORDER BY overrides.result DESC, overrides.task DESC,"
2405  " overrides.port DESC, overrides.severity ASC,"
2406  " overrides.creation_time DESC",
2407  owned_clause);
2408 
2409  g_free (owned_clause);
2410 
2411  sql ("CREATE OR REPLACE VIEW result_new_severities AS"
2412  " SELECT results.id as result, users.id as user, dynamic, override,"
2413  " CASE WHEN dynamic != 0 THEN"
2414  " CASE WHEN override != 0 THEN"
2415  " coalesce ((SELECT ov_new_severity FROM result_overrides"
2416  " WHERE result = results.id"
2417  " AND result_overrides.user = users.id"
2418  " AND severity_matches_ov"
2419  " (current_severity (results.severity,"
2420  " results.nvt),"
2421  " ov_old_severity)"
2422  " LIMIT 1),"
2423  " current_severity (results.severity, results.nvt))"
2424  " ELSE"
2425  " current_severity (results.severity, results.nvt)"
2426  " END"
2427  " ELSE"
2428  " CASE WHEN override != 0 THEN"
2429  " coalesce ((SELECT ov_new_severity FROM result_overrides"
2430  " WHERE result = results.id"
2431  " AND result_overrides.user = users.id"
2432  " AND severity_matches_ov"
2433  " (results.severity,"
2434  " ov_old_severity)"
2435  " LIMIT 1),"
2436  " results.severity)"
2437  " ELSE"
2438  " results.severity"
2439  " END"
2440  " END AS new_severity"
2441  " FROM results, users,"
2442  " (SELECT 0 AS override UNION SELECT 1 AS override) AS override_opts,"
2443  " (SELECT 0 AS dynamic UNION SELECT 1 AS dynamic) AS dynamic_opts;");
2444 
2445  sql ("CREATE OR REPLACE VIEW results_autofp AS"
2446  " SELECT results.id as result, autofp_selection,"
2447  " (CASE autofp_selection"
2448  " WHEN 1 THEN"
2449  " (CASE WHEN"
2450  " (((SELECT family FROM nvts WHERE oid = results.nvt)"
2451  " IN (" LSC_FAMILY_LIST "))"
2452  " OR results.nvt = '0'" /* Open ports previously had 0 NVT. */
2453  " OR EXISTS"
2454  " (SELECT id FROM nvts"
2455  " WHERE oid = results.nvt"
2456  " AND"
2457  " (cve = 'NOCVE'"
2458  " OR cve NOT IN (SELECT cve FROM nvts"
2459  " WHERE oid"
2460  " IN (SELECT source_name"
2461  " FROM report_host_details"
2462  " WHERE report_host"
2463  " = (SELECT id"
2464  " FROM report_hosts"
2465  " WHERE report = %llu"
2466  " AND host"
2467  " = results.host)"
2468  " AND name = 'EXIT_CODE'"
2469  " AND value = 'EXIT_NOTVULN')"
2470  " AND family IN (" LSC_FAMILY_LIST ")))))"
2471  " THEN NULL"
2472  " WHEN severity = " G_STRINGIFY (SEVERITY_ERROR) " THEN NULL"
2473  " ELSE 1 END)"
2474  " WHEN 2 THEN"
2475  " (CASE WHEN"
2476  " (((SELECT family FROM nvts WHERE oid = results.nvt)"
2477  " IN (" LSC_FAMILY_LIST "))"
2478  " OR results.nvt = '0'" /* Open ports previously had 0 NVT.*/
2479  " OR EXISTS"
2480  " (SELECT id FROM nvts AS outer_nvts"
2481  " WHERE oid = results.nvt"
2482  " AND"
2483  " (cve = 'NOCVE'"
2484  " OR NOT EXISTS"
2485  " (SELECT cve FROM nvts"
2486  " WHERE oid IN (SELECT source_name"
2487  " FROM report_host_details"
2488  " WHERE report_host"
2489  " = (SELECT id"
2490  " FROM report_hosts"
2491  " WHERE report = results.report"
2492  " AND host = results.host)"
2493  " AND name = 'EXIT_CODE'"
2494  " AND value = 'EXIT_NOTVULN')"
2495  " AND family IN (" LSC_FAMILY_LIST ")"
2496  /* The CVE of the result NVT is outer_nvts.cve. The CVE of the
2497  * NVT that has registered the "closed" host detail is nvts.cve.
2498  * Either can be a list of CVEs. */
2499  " AND common_cve (nvts.cve, outer_nvts.cve)))))"
2500  " THEN NULL"
2501  " WHEN severity = " G_STRINGIFY (SEVERITY_ERROR) " THEN NULL"
2502  " ELSE 1 END)"
2503  " ELSE 0 END) AS autofp"
2504  " FROM results,"
2505  " (SELECT 0 AS autofp_selection"
2506  " UNION SELECT 1 AS autofp_selection"
2507  " UNION SELECT 2 AS autofp_selection) AS autofp_opts;");
2508 
2509  /* Create indexes. */
2510 
2511  sql ("SELECT create_index ('host_details_by_host',"
2512  " 'host_details', 'host');");
2513 
2514  sql ("SELECT create_index ('host_identifiers_by_host',"
2515  " 'host_identifiers', 'host');");
2516  sql ("SELECT create_index ('host_identifiers_by_value',"
2517  " 'host_identifiers', 'value');");
2518 
2519  sql ("SELECT create_index ('host_max_severities_by_host',"
2520  " 'host_max_severities', 'host');");
2521  sql ("SELECT create_index ('host_oss_by_host',"
2522  " 'host_oss', 'host');");
2523 
2524  sql ("SELECT create_index ('nvt_cves_by_oid', 'nvt_cves', 'oid');");
2525  sql ("SELECT create_index ('nvt_selectors_by_family_or_nvt',"
2526  " 'nvt_selectors',"
2527  " 'type, family_or_nvt');");
2528  sql ("SELECT create_index ('nvt_selectors_by_name',"
2529  " 'nvt_selectors',"
2530  " 'name');");
2531  sql ("SELECT create_index ('nvts_by_creation_time',"
2532  " 'nvts',"
2533  " 'creation_time');");
2534  sql ("SELECT create_index ('nvts_by_family', 'nvts', 'family');");
2535  sql ("SELECT create_index ('nvts_by_name', 'nvts', 'name');");
2536  sql ("SELECT create_index ('nvts_by_modification_time',"
2537  " 'nvts', 'modification_time');");
2538  sql ("SELECT create_index ('nvts_by_cvss_base',"
2539  " 'nvts', 'cvss_base');");
2540  sql ("SELECT create_index ('nvts_by_solution_type',"
2541  " 'nvts', 'solution_type');");
2542 
2543  sql ("SELECT create_index ('permissions_by_name',"
2544  " 'permissions', 'name');");
2545  sql ("SELECT create_index ('permissions_by_resource',"
2546  " 'permissions', 'resource');");
2547 
2548  sql ("SELECT create_index ('report_counts_by_report_and_override',"
2549  " 'report_counts', 'report, override');");
2550 
2551 
2552 #if 0
2553  /* TODO The value column can be bigger than 8191, the maximum size that
2554  * Postgres can handle. For example, this can happen for "ports".
2555  * Mostly value is short, like a CPE for the "App" detail, which is
2556  * what the index is for. */
2557  sql ("SELECT create_index"
2558  " ('report_host_details_by_report_host_and_name_and_value',"
2559  " 'report_host_details',"
2560  " 'report_host, name, value');");
2561 #else
2562  sql ("SELECT create_index"
2563  " ('report_host_details_by_report_host_and_name',"
2564  " 'report_host_details',"
2565  " 'report_host, name');");
2566 #endif
2567  sql ("SELECT create_index"
2568  " ('report_hosts_by_report_and_host',"
2569  " 'report_hosts',"
2570  " 'report, host');");
2571  sql ("SELECT create_index ('results_by_host_and_qod', 'results',"
2572  " 'host, qod');");
2573  sql ("SELECT create_index ('results_by_report', 'results', 'report');");
2574  sql ("SELECT create_index ('results_by_task', 'results', 'task');");
2575  sql ("SELECT create_index ('results_by_date', 'results', 'date');");
2576 }
2577 
2583 void
2584 check_db_sequences ()
2585 {
2586  iterator_t sequence_tables;
2587  init_iterator(&sequence_tables,
2588  "SELECT table_name, column_name,"
2589  " pg_get_serial_sequence (table_name, column_name)"
2590  " FROM information_schema.columns"
2591  " WHERE table_schema = 'public'"
2592  " AND pg_get_serial_sequence (table_name, column_name)"
2593  " IS NOT NULL;");
2594 
2595  while (next (&sequence_tables))
2596  {
2597  const char* table = iterator_string (&sequence_tables, 0);
2598  const char* column = iterator_string (&sequence_tables, 1);
2599  const char* sequence = iterator_string (&sequence_tables, 2);
2600  resource_t old_start, new_start;
2601 
2602  sql_int64 (&old_start,
2603  "SELECT last_value + 1 FROM %s;",
2604  sequence);
2605 
2606  sql_int64 (&new_start,
2607  "SELECT coalesce (max (%s), 0) + 1 FROM %s;",
2608  column, table);
2609 
2610  if (old_start < new_start)
2611  sql ("ALTER SEQUENCE %s RESTART WITH %llu;", sequence, new_start);
2612  }
2613 
2614  cleanup_iterator (&sequence_tables);
2615 }
2616 
2617 
2618 /* SecInfo. */
2619 
2623 void
2624 manage_attach_databases ()
2625 {
2626  if (manage_scap_loaded ())
2627  sql ("SELECT set_config ('search_path',"
2628  " current_setting ('search_path') || ',scap',"
2629  " false);");
2630 
2631  if (manage_cert_loaded ())
2632  sql ("SELECT set_config ('search_path',"
2633  " current_setting ('search_path') || ',cert',"
2634  " false);");
2635 }
2636 
2642 int
2643 manage_cert_loaded ()
2644 {
2645  return !!sql_int ("SELECT EXISTS (SELECT * FROM information_schema.tables"
2646  " WHERE table_catalog = '%s'"
2647  " AND table_schema = 'cert'"
2648  " AND table_name = 'dfn_cert_advs')"
2649  " ::integer;",
2650  sql_database ());
2651 }
2652 
2658 int
2659 manage_scap_loaded ()
2660 {
2661  return !!sql_int ("SELECT EXISTS (SELECT * FROM information_schema.tables"
2662  " WHERE table_catalog = '%s'"
2663  " AND table_schema = 'scap'"
2664  " AND table_name = 'cves')"
2665  " ::integer;",
2666  sql_database ());
2667 }
2668 
2669 
2670 /* Backup. */
2671 
2679 int
2680 manage_backup_db (const gchar *database)
2681 {
2682  g_warning ("%s: database backup not supported for Postgres", __FUNCTION__);
2683  return -1;
2684 }
2685 
2686 
2687 /* Migrator helper. */
2688 
2694 int
2695 manage_create_migrate_51_to_52_convert ()
2696 {
2697  return 0;
2698 }
LOCATION_TRASH
#define LOCATION_TRASH
Location of a constituent of a trashcan resource.
Definition: manage_sql.h:86
manage_db_empty
int manage_db_empty()
Check whether database is empty.
Definition: manage_pg.c:78
manage_scap_loaded
int manage_scap_loaded()
Check whether SCAP is available.
Definition: manage_pg.c:2659
sql_int
int sql_int(char *sql,...)
Get a particular cell from a SQL query, as an int.
Definition: sql.c:438
LSC_FAMILY_LIST
#define LSC_FAMILY_LIST
SQL list of LSC families.
Definition: manage.h:1065
iterator
A generic SQL iterator.
Definition: iterator.h:52
sql
void sql(char *sql,...)
Perform an SQL statement, retrying if database is busy or locked.
Definition: sql.c:199
manage_cert_loaded
int manage_cert_loaded()
Check whether CERT is available.
Definition: manage_pg.c:2643
manage_session_set_timezone
void manage_session_set_timezone(const char *)
Setup session timezone.
Definition: manage_pg.c:63
sql_int64
int sql_int64(long long int *ret, char *sql,...)
Get a particular cell from a SQL query, as an int64.
Definition: sql.c:501
acl_where_owned_for_get
gchar * acl_where_owned_for_get(const char *type, const char *user_sql)
Generate ownership part of WHERE, for getting a type of resource.
Definition: manage_acl.c:1378
manage_backup_db
int manage_backup_db(const gchar *)
Backup the database to a file.
Definition: manage_pg.c:2680
manage_create_sql_functions
int manage_create_sql_functions()
Create functions.
Definition: manage_pg.c:113
OVERRIDES_SQL
#define OVERRIDES_SQL(severity_sql)
manage_sql.h
manage_session_init
void manage_session_init(const char *uuid)
Setup session.
Definition: manage_pg.c:46
check_db_sequences
void check_db_sequences()
Ensure sequences for automatic ids are in a consistent state.
Definition: manage_pg.c:2584
manage_utils.h
SEVERITY_ERROR
#define SEVERITY_ERROR
Definition: manage_utils.h:38
manage_acl.h
SEVERITY_FP
#define SEVERITY_FP
Definition: manage_utils.h:34
create_tables
void create_tables()
Create all tables.
Definition: manage_pg.c:1506
SEVERITY_DEBUG
#define SEVERITY_DEBUG
Definition: manage_utils.h:36
cleanup_iterator
void cleanup_iterator(iterator_t *)
Cleanup an iterator.
Definition: sql.c:664
iterator_string
const char * iterator_string(iterator_t *iterator, int col)
Get a string column from an iterator.
Definition: sql.c:652
manage_create_migrate_51_to_52_convert
int manage_create_migrate_51_to_52_convert()
Dummy for SQLite3 compatibility.
Definition: manage_pg.c:2695
manage_db_version
int manage_db_version()
Return the database version of the actual database.
Definition: manage_sql.c:6262
sql_database
const char * sql_database()
Return name of current database.
Definition: sql_pg.c:226
sql_rename_column
void sql_rename_column(const char *old_table, const char *new_table, const char *old_name, const char *new_name)
Move data from a table to a new table, heeding column rename.
Definition: manage_pg.c:101
next
gboolean next(iterator_t *)
Increment an iterator.
Definition: sql.c:689
LOCATION_TABLE
#define LOCATION_TABLE
Location of a constituent of a trashcan resource.
Definition: manage_sql.h:81
init_iterator
void init_iterator(iterator_t *iterator, const char *sql,...)
Initialise an iterator.
Definition: sql.c:577
manage_attach_databases
void manage_attach_databases()
Attach external databases.
Definition: manage_pg.c:2624
SEVERITY_LOG
#define SEVERITY_LOG
Definition: manage_utils.h:32
resource_t
long long int resource_t
A resource, like a task or target.
Definition: iterator.h:42