593 lines
16 KiB
PL/PgSQL
593 lines
16 KiB
PL/PgSQL
-- Migration : ajout des 7 tables manquantes en local
|
||
-- (chassis, hypervisors, qualys_missing_servers, qualys_vuln_snapshot,
|
||
-- qualys_vuln_snapshot_run, secops_duty, server_databases)
|
||
-- DDL aligné sur prod CT 116 (PG 15.16) — idempotent
|
||
|
||
-- 1. Extension requise (citext pour hostname case-insensitive)
|
||
CREATE EXTENSION IF NOT EXISTS citext;
|
||
|
||
-- 2. Fonction trigger pour qualys_missing_servers
|
||
CREATE OR REPLACE FUNCTION public.qms_set_updated_at()
|
||
RETURNS trigger LANGUAGE plpgsql AS $function$
|
||
BEGIN NEW.updated_at = now(); RETURN NEW; END;
|
||
$function$;
|
||
|
||
-- 3. Tables (idempotent)
|
||
--
|
||
|
||
--
|
||
-- Name: chassis; Type: TABLE; Schema: public; Owner: -
|
||
--
|
||
|
||
CREATE TABLE IF NOT EXISTS public.chassis (
|
||
id integer NOT NULL,
|
||
hostname public.citext NOT NULL,
|
||
fqdn character varying(255),
|
||
site character varying(100),
|
||
domain_ltd character varying(50),
|
||
description text,
|
||
responsable_nom text,
|
||
moved_from_server_id integer,
|
||
created_at timestamp with time zone DEFAULT now()
|
||
);
|
||
|
||
|
||
--
|
||
-- Name: chassis_id_seq; Type: SEQUENCE; Schema: public; Owner: -
|
||
--
|
||
|
||
CREATE SEQUENCE IF NOT EXISTS public.chassis_id_seq
|
||
AS integer
|
||
START WITH 1
|
||
INCREMENT BY 1
|
||
NO MINVALUE
|
||
NO MAXVALUE
|
||
CACHE 1;
|
||
|
||
|
||
--
|
||
-- Name: chassis_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
|
||
--
|
||
|
||
ALTER SEQUENCE public.chassis_id_seq OWNED BY public.chassis.id;
|
||
|
||
|
||
--
|
||
-- Name: hypervisors; Type: TABLE; Schema: public; Owner: -
|
||
--
|
||
|
||
CREATE TABLE IF NOT EXISTS public.hypervisors (
|
||
id integer NOT NULL,
|
||
hostname public.citext NOT NULL,
|
||
fqdn character varying(255),
|
||
cluster_name character varying(100),
|
||
underlying_server character varying(100),
|
||
site character varying(100),
|
||
domain_ltd character varying(50),
|
||
description text,
|
||
responsable_nom text,
|
||
moved_from_server_id integer,
|
||
created_at timestamp with time zone DEFAULT now(),
|
||
kind character varying(20) DEFAULT 'hypervisor'::character varying
|
||
);
|
||
|
||
|
||
--
|
||
-- Name: hypervisors_id_seq; Type: SEQUENCE; Schema: public; Owner: -
|
||
--
|
||
|
||
CREATE SEQUENCE IF NOT EXISTS public.hypervisors_id_seq
|
||
AS integer
|
||
START WITH 1
|
||
INCREMENT BY 1
|
||
NO MINVALUE
|
||
NO MAXVALUE
|
||
CACHE 1;
|
||
|
||
|
||
--
|
||
-- Name: hypervisors_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
|
||
--
|
||
|
||
ALTER SEQUENCE public.hypervisors_id_seq OWNED BY public.hypervisors.id;
|
||
|
||
|
||
--
|
||
-- Name: qualys_missing_servers; Type: TABLE; Schema: public; Owner: -
|
||
--
|
||
|
||
CREATE TABLE IF NOT EXISTS public.qualys_missing_servers (
|
||
id integer NOT NULL,
|
||
hostname character varying(255) NOT NULL,
|
||
hostname_norm character varying(255) GENERATED ALWAYS AS (lower((hostname)::text)) STORED,
|
||
environnement character varying(50),
|
||
sources_present character varying(100),
|
||
in_cyberark boolean DEFAULT false,
|
||
in_sentinel boolean DEFAULT false,
|
||
in_itop boolean DEFAULT false,
|
||
server_id integer,
|
||
reason_category character varying(30),
|
||
reason_detail text,
|
||
status character varying(20) DEFAULT 'a_traiter'::character varying,
|
||
priority smallint DEFAULT 3,
|
||
notes text,
|
||
source_file character varying(100),
|
||
last_seen_at timestamp with time zone DEFAULT now(),
|
||
created_at timestamp with time zone DEFAULT now(),
|
||
updated_at timestamp with time zone DEFAULT now(),
|
||
CONSTRAINT qms_reason_check CHECK (((reason_category IS NULL) OR ((reason_category)::text = ANY (ARRAY[('appliance'::character varying)::text, ('ot_scada'::character varying)::text, ('virtualisation'::character varying)::text, ('embedded'::character varying)::text, ('oubli'::character varying)::text, ('decom'::character varying)::text, ('inconnu'::character varying)::text, ('other'::character varying)::text])))),
|
||
CONSTRAINT qms_status_check CHECK (((status)::text = ANY (ARRAY[('a_traiter'::character varying)::text, ('a_enroler'::character varying)::text, ('exempt'::character varying)::text, ('enrole'::character varying)::text, ('decom'::character varying)::text])))
|
||
);
|
||
|
||
|
||
--
|
||
-- Name: TABLE qualys_missing_servers; Type: COMMENT; Schema: public; Owner: -
|
||
--
|
||
|
||
COMMENT ON TABLE public.qualys_missing_servers IS 'Serveurs détectés ailleurs (CA/S1/iTop) mais absents de Qualys + raison';
|
||
|
||
|
||
--
|
||
-- Name: COLUMN qualys_missing_servers.reason_category; Type: COMMENT; Schema: public; Owner: -
|
||
--
|
||
|
||
COMMENT ON COLUMN public.qualys_missing_servers.reason_category IS 'appliance, ot_scada, virtualisation (ESXi), embedded, oubli (à enrôler), decom, inconnu, other';
|
||
|
||
|
||
--
|
||
-- Name: COLUMN qualys_missing_servers.status; Type: COMMENT; Schema: public; Owner: -
|
||
--
|
||
|
||
COMMENT ON COLUMN public.qualys_missing_servers.status IS 'a_traiter, a_enroler, exempt (légitimement hors Qualys), enrole (fait), decom';
|
||
|
||
|
||
--
|
||
-- Name: COLUMN qualys_missing_servers.priority; Type: COMMENT; Schema: public; Owner: -
|
||
--
|
||
|
||
COMMENT ON COLUMN public.qualys_missing_servers.priority IS '1 urgent → 5 faible (auto-calc selon sources)';
|
||
|
||
|
||
--
|
||
-- Name: qualys_missing_servers_id_seq; Type: SEQUENCE; Schema: public; Owner: -
|
||
--
|
||
|
||
CREATE SEQUENCE IF NOT EXISTS public.qualys_missing_servers_id_seq
|
||
AS integer
|
||
START WITH 1
|
||
INCREMENT BY 1
|
||
NO MINVALUE
|
||
NO MAXVALUE
|
||
CACHE 1;
|
||
|
||
|
||
--
|
||
-- Name: qualys_missing_servers_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
|
||
--
|
||
|
||
ALTER SEQUENCE public.qualys_missing_servers_id_seq OWNED BY public.qualys_missing_servers.id;
|
||
|
||
|
||
--
|
||
-- Name: qualys_vuln_snapshot; Type: TABLE; Schema: public; Owner: -
|
||
--
|
||
|
||
CREATE TABLE IF NOT EXISTS public.qualys_vuln_snapshot (
|
||
id integer NOT NULL,
|
||
run_id integer NOT NULL,
|
||
dimension character varying(20) NOT NULL,
|
||
dimension_value character varying(100),
|
||
dimension_value2 character varying(100),
|
||
total integer DEFAULT 0 NOT NULL,
|
||
critical integer DEFAULT 0 NOT NULL,
|
||
high integer DEFAULT 0 NOT NULL,
|
||
medium integer DEFAULT 0 NOT NULL,
|
||
sain integer DEFAULT 0 NOT NULL,
|
||
non_scanne integer DEFAULT 0 NOT NULL
|
||
);
|
||
|
||
|
||
--
|
||
-- Name: qualys_vuln_snapshot_id_seq; Type: SEQUENCE; Schema: public; Owner: -
|
||
--
|
||
|
||
CREATE SEQUENCE IF NOT EXISTS public.qualys_vuln_snapshot_id_seq
|
||
AS integer
|
||
START WITH 1
|
||
INCREMENT BY 1
|
||
NO MINVALUE
|
||
NO MAXVALUE
|
||
CACHE 1;
|
||
|
||
|
||
--
|
||
-- Name: qualys_vuln_snapshot_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
|
||
--
|
||
|
||
ALTER SEQUENCE public.qualys_vuln_snapshot_id_seq OWNED BY public.qualys_vuln_snapshot.id;
|
||
|
||
|
||
--
|
||
-- Name: qualys_vuln_snapshot_run; Type: TABLE; Schema: public; Owner: -
|
||
--
|
||
|
||
CREATE TABLE IF NOT EXISTS public.qualys_vuln_snapshot_run (
|
||
id integer NOT NULL,
|
||
run_at timestamp with time zone DEFAULT now() NOT NULL,
|
||
status character varying(20) DEFAULT 'pending'::character varying NOT NULL,
|
||
asset_count integer DEFAULT 0 NOT NULL,
|
||
duration_sec integer DEFAULT 0 NOT NULL,
|
||
msg text,
|
||
triggered_by character varying(50)
|
||
);
|
||
|
||
|
||
--
|
||
-- Name: qualys_vuln_snapshot_run_id_seq; Type: SEQUENCE; Schema: public; Owner: -
|
||
--
|
||
|
||
CREATE SEQUENCE IF NOT EXISTS public.qualys_vuln_snapshot_run_id_seq
|
||
AS integer
|
||
START WITH 1
|
||
INCREMENT BY 1
|
||
NO MINVALUE
|
||
NO MAXVALUE
|
||
CACHE 1;
|
||
|
||
|
||
--
|
||
-- Name: qualys_vuln_snapshot_run_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
|
||
--
|
||
|
||
ALTER SEQUENCE public.qualys_vuln_snapshot_run_id_seq OWNED BY public.qualys_vuln_snapshot_run.id;
|
||
|
||
|
||
--
|
||
-- Name: secops_duty; Type: TABLE; Schema: public; Owner: -
|
||
--
|
||
|
||
CREATE TABLE IF NOT EXISTS public.secops_duty (
|
||
id integer NOT NULL,
|
||
year smallint NOT NULL,
|
||
week_number smallint NOT NULL,
|
||
week_code character varying(5) NOT NULL,
|
||
week_start date,
|
||
week_end date,
|
||
absences text,
|
||
tdg_s1 character varying(50),
|
||
tdg_symantec character varying(50),
|
||
tdg_m365 character varying(50),
|
||
tdg_commvault character varying(50),
|
||
tdg_meteo character varying(50),
|
||
tdg_dmz character varying(50),
|
||
tdg_safenet character varying(50),
|
||
tdg_quarantaine character varying(50),
|
||
tdg_securisation character varying(50),
|
||
tdg_incident_majeur character varying(50),
|
||
tdg_incident_critique character varying(50),
|
||
emails_dest character varying(100),
|
||
created_at timestamp with time zone DEFAULT now()
|
||
);
|
||
|
||
|
||
--
|
||
-- Name: TABLE secops_duty; Type: COMMENT; Schema: public; Owner: -
|
||
--
|
||
|
||
COMMENT ON TABLE public.secops_duty IS 'Tour de garde SecOps hebdomadaire (source: Tour de garde secops_2026.xlsx)';
|
||
|
||
|
||
--
|
||
-- Name: secops_duty_id_seq; Type: SEQUENCE; Schema: public; Owner: -
|
||
--
|
||
|
||
CREATE SEQUENCE IF NOT EXISTS public.secops_duty_id_seq
|
||
AS integer
|
||
START WITH 1
|
||
INCREMENT BY 1
|
||
NO MINVALUE
|
||
NO MAXVALUE
|
||
CACHE 1;
|
||
|
||
|
||
--
|
||
-- Name: secops_duty_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
|
||
--
|
||
|
||
ALTER SEQUENCE public.secops_duty_id_seq OWNED BY public.secops_duty.id;
|
||
|
||
|
||
--
|
||
-- Name: server_databases; Type: TABLE; Schema: public; Owner: -
|
||
--
|
||
|
||
CREATE TABLE IF NOT EXISTS public.server_databases (
|
||
id integer NOT NULL,
|
||
server_id integer,
|
||
hostname public.citext,
|
||
instance_name character varying(100),
|
||
db_type character varying(50),
|
||
db_version character varying(100),
|
||
db_edition character varying(50),
|
||
cluster_name character varying(100),
|
||
environnement character varying(50),
|
||
etat character varying(30),
|
||
description text,
|
||
created_at timestamp with time zone DEFAULT now()
|
||
);
|
||
|
||
|
||
--
|
||
-- Name: server_databases_id_seq; Type: SEQUENCE; Schema: public; Owner: -
|
||
--
|
||
|
||
CREATE SEQUENCE IF NOT EXISTS public.server_databases_id_seq
|
||
AS integer
|
||
START WITH 1
|
||
INCREMENT BY 1
|
||
NO MINVALUE
|
||
NO MAXVALUE
|
||
CACHE 1;
|
||
|
||
|
||
--
|
||
-- Name: server_databases_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
|
||
--
|
||
|
||
ALTER SEQUENCE public.server_databases_id_seq OWNED BY public.server_databases.id;
|
||
|
||
|
||
--
|
||
-- Name: chassis id; Type: DEFAULT; Schema: public; Owner: -
|
||
--
|
||
|
||
ALTER TABLE ONLY public.chassis ALTER COLUMN id SET DEFAULT nextval('public.chassis_id_seq'::regclass);
|
||
|
||
|
||
--
|
||
-- Name: hypervisors id; Type: DEFAULT; Schema: public; Owner: -
|
||
--
|
||
|
||
ALTER TABLE ONLY public.hypervisors ALTER COLUMN id SET DEFAULT nextval('public.hypervisors_id_seq'::regclass);
|
||
|
||
|
||
--
|
||
-- Name: qualys_missing_servers id; Type: DEFAULT; Schema: public; Owner: -
|
||
--
|
||
|
||
ALTER TABLE ONLY public.qualys_missing_servers ALTER COLUMN id SET DEFAULT nextval('public.qualys_missing_servers_id_seq'::regclass);
|
||
|
||
|
||
--
|
||
-- Name: qualys_vuln_snapshot id; Type: DEFAULT; Schema: public; Owner: -
|
||
--
|
||
|
||
ALTER TABLE ONLY public.qualys_vuln_snapshot ALTER COLUMN id SET DEFAULT nextval('public.qualys_vuln_snapshot_id_seq'::regclass);
|
||
|
||
|
||
--
|
||
-- Name: qualys_vuln_snapshot_run id; Type: DEFAULT; Schema: public; Owner: -
|
||
--
|
||
|
||
ALTER TABLE ONLY public.qualys_vuln_snapshot_run ALTER COLUMN id SET DEFAULT nextval('public.qualys_vuln_snapshot_run_id_seq'::regclass);
|
||
|
||
|
||
--
|
||
-- Name: secops_duty id; Type: DEFAULT; Schema: public; Owner: -
|
||
--
|
||
|
||
ALTER TABLE ONLY public.secops_duty ALTER COLUMN id SET DEFAULT nextval('public.secops_duty_id_seq'::regclass);
|
||
|
||
|
||
--
|
||
-- Name: server_databases id; Type: DEFAULT; Schema: public; Owner: -
|
||
--
|
||
|
||
ALTER TABLE ONLY public.server_databases ALTER COLUMN id SET DEFAULT nextval('public.server_databases_id_seq'::regclass);
|
||
|
||
|
||
--
|
||
-- Name: chassis chassis_hostname_key; Type: CONSTRAINT; Schema: public; Owner: -
|
||
--
|
||
|
||
ALTER TABLE ONLY public.chassis
|
||
ADD CONSTRAINT chassis_hostname_key UNIQUE (hostname);
|
||
|
||
|
||
--
|
||
-- Name: chassis chassis_pkey; Type: CONSTRAINT; Schema: public; Owner: -
|
||
--
|
||
|
||
ALTER TABLE ONLY public.chassis
|
||
ADD CONSTRAINT chassis_pkey PRIMARY KEY (id);
|
||
|
||
|
||
--
|
||
-- Name: hypervisors hypervisors_hostname_key; Type: CONSTRAINT; Schema: public; Owner: -
|
||
--
|
||
|
||
ALTER TABLE ONLY public.hypervisors
|
||
ADD CONSTRAINT hypervisors_hostname_key UNIQUE (hostname);
|
||
|
||
|
||
--
|
||
-- Name: hypervisors hypervisors_pkey; Type: CONSTRAINT; Schema: public; Owner: -
|
||
--
|
||
|
||
ALTER TABLE ONLY public.hypervisors
|
||
ADD CONSTRAINT hypervisors_pkey PRIMARY KEY (id);
|
||
|
||
|
||
--
|
||
-- Name: qualys_missing_servers qualys_missing_servers_pkey; Type: CONSTRAINT; Schema: public; Owner: -
|
||
--
|
||
|
||
ALTER TABLE ONLY public.qualys_missing_servers
|
||
ADD CONSTRAINT qualys_missing_servers_pkey PRIMARY KEY (id);
|
||
|
||
|
||
--
|
||
-- Name: qualys_vuln_snapshot qualys_vuln_snapshot_pkey; Type: CONSTRAINT; Schema: public; Owner: -
|
||
--
|
||
|
||
ALTER TABLE ONLY public.qualys_vuln_snapshot
|
||
ADD CONSTRAINT qualys_vuln_snapshot_pkey PRIMARY KEY (id);
|
||
|
||
|
||
--
|
||
-- Name: qualys_vuln_snapshot_run qualys_vuln_snapshot_run_pkey; Type: CONSTRAINT; Schema: public; Owner: -
|
||
--
|
||
|
||
ALTER TABLE ONLY public.qualys_vuln_snapshot_run
|
||
ADD CONSTRAINT qualys_vuln_snapshot_run_pkey PRIMARY KEY (id);
|
||
|
||
|
||
--
|
||
-- Name: secops_duty secops_duty_pkey; Type: CONSTRAINT; Schema: public; Owner: -
|
||
--
|
||
|
||
ALTER TABLE ONLY public.secops_duty
|
||
ADD CONSTRAINT secops_duty_pkey PRIMARY KEY (id);
|
||
|
||
|
||
--
|
||
-- Name: server_databases server_databases_hostname_instance_name_key; Type: CONSTRAINT; Schema: public; Owner: -
|
||
--
|
||
|
||
ALTER TABLE ONLY public.server_databases
|
||
ADD CONSTRAINT server_databases_hostname_instance_name_key UNIQUE (hostname, instance_name);
|
||
|
||
|
||
--
|
||
-- Name: server_databases server_databases_pkey; Type: CONSTRAINT; Schema: public; Owner: -
|
||
--
|
||
|
||
ALTER TABLE ONLY public.server_databases
|
||
ADD CONSTRAINT server_databases_pkey PRIMARY KEY (id);
|
||
|
||
|
||
--
|
||
-- Name: idx_srv_db_server; Type: INDEX; Schema: public; Owner: -
|
||
--
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_srv_db_server ON public.server_databases USING btree (server_id);
|
||
|
||
|
||
--
|
||
-- Name: idx_srv_db_type; Type: INDEX; Schema: public; Owner: -
|
||
--
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_srv_db_type ON public.server_databases USING btree (db_type);
|
||
|
||
|
||
--
|
||
-- Name: idx_vuln_run_at; Type: INDEX; Schema: public; Owner: -
|
||
--
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_vuln_run_at ON public.qualys_vuln_snapshot_run USING btree (run_at DESC);
|
||
|
||
|
||
--
|
||
-- Name: idx_vuln_snap_dim_val; Type: INDEX; Schema: public; Owner: -
|
||
--
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_vuln_snap_dim_val ON public.qualys_vuln_snapshot USING btree (dimension, dimension_value);
|
||
|
||
|
||
--
|
||
-- Name: idx_vuln_snap_run_dim; Type: INDEX; Schema: public; Owner: -
|
||
--
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_vuln_snap_run_dim ON public.qualys_vuln_snapshot USING btree (run_id, dimension);
|
||
|
||
|
||
--
|
||
-- Name: qms_hostname_norm_uniq; Type: INDEX; Schema: public; Owner: -
|
||
--
|
||
|
||
CREATE UNIQUE INDEX IF NOT EXISTS qms_hostname_norm_uniq ON public.qualys_missing_servers USING btree (hostname_norm);
|
||
|
||
|
||
--
|
||
-- Name: qms_reason_idx; Type: INDEX; Schema: public; Owner: -
|
||
--
|
||
|
||
CREATE INDEX IF NOT EXISTS qms_reason_idx ON public.qualys_missing_servers USING btree (reason_category);
|
||
|
||
|
||
--
|
||
-- Name: qms_server_id_idx; Type: INDEX; Schema: public; Owner: -
|
||
--
|
||
|
||
CREATE INDEX IF NOT EXISTS qms_server_id_idx ON public.qualys_missing_servers USING btree (server_id);
|
||
|
||
|
||
--
|
||
-- Name: qms_status_idx; Type: INDEX; Schema: public; Owner: -
|
||
--
|
||
|
||
CREATE INDEX IF NOT EXISTS qms_status_idx ON public.qualys_missing_servers USING btree (status);
|
||
|
||
|
||
--
|
||
-- Name: secops_duty_week_idx; Type: INDEX; Schema: public; Owner: -
|
||
--
|
||
|
||
CREATE INDEX IF NOT EXISTS secops_duty_week_idx ON public.secops_duty USING btree (year, week_number);
|
||
|
||
|
||
--
|
||
-- Name: secops_duty_year_week_uniq; Type: INDEX; Schema: public; Owner: -
|
||
--
|
||
|
||
CREATE UNIQUE INDEX IF NOT EXISTS secops_duty_year_week_uniq ON public.secops_duty USING btree (year, week_number);
|
||
|
||
|
||
--
|
||
-- Name: qualys_missing_servers qms_updated_at_trg; Type: TRIGGER; Schema: public; Owner: -
|
||
--
|
||
|
||
CREATE TRIGGER qms_updated_at_trg BEFORE UPDATE ON public.qualys_missing_servers FOR EACH ROW EXECUTE FUNCTION public.qms_set_updated_at();
|
||
|
||
|
||
--
|
||
-- Name: qualys_missing_servers qualys_missing_servers_server_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
||
--
|
||
|
||
ALTER TABLE ONLY public.qualys_missing_servers
|
||
ADD CONSTRAINT qualys_missing_servers_server_id_fkey FOREIGN KEY (server_id) REFERENCES public.servers(id) ON DELETE SET NULL;
|
||
|
||
|
||
--
|
||
-- Name: qualys_vuln_snapshot qualys_vuln_snapshot_run_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
||
--
|
||
|
||
ALTER TABLE ONLY public.qualys_vuln_snapshot
|
||
ADD CONSTRAINT qualys_vuln_snapshot_run_id_fkey FOREIGN KEY (run_id) REFERENCES public.qualys_vuln_snapshot_run(id) ON DELETE CASCADE;
|
||
|
||
|
||
--
|
||
-- Name: server_databases server_databases_server_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
||
--
|
||
|
||
ALTER TABLE ONLY public.server_databases
|
||
ADD CONSTRAINT server_databases_server_id_fkey FOREIGN KEY (server_id) REFERENCES public.servers(id) ON DELETE CASCADE;
|
||
|
||
|
||
--
|
||
|
||
-- Privilèges pour le user applicatif sur les nouvelles tables + futures
|
||
GRANT USAGE ON SCHEMA public TO patchcenter;
|
||
GRANT SELECT, INSERT, UPDATE, DELETE ON
|
||
chassis, hypervisors, qualys_missing_servers,
|
||
qualys_vuln_snapshot, qualys_vuln_snapshot_run,
|
||
secops_duty, server_databases
|
||
TO patchcenter;
|
||
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO patchcenter;
|
||
|
||
-- Default privileges : toute future table créée par postgres aura ces droits automatiquement
|
||
ALTER DEFAULT PRIVILEGES IN SCHEMA public
|
||
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO patchcenter;
|
||
ALTER DEFAULT PRIVILEGES IN SCHEMA public
|
||
GRANT USAGE, SELECT ON SEQUENCES TO patchcenter;
|