-- Odoo 14 – Anonimizzazione DB (PostgreSQL) – compatibile e safe
-- Esegui su una COPIA del database.
CREATE EXTENSION IF NOT EXISTS pgcrypto;
-- =========================
-- Funzioni helper
-- =========================
-- Email fittizia deterministica
CREATE OR REPLACE FUNCTION _anon_email(p_id bigint, p_prefix text DEFAULT 'u')
RETURNS text LANGUAGE sql IMMUTABLE AS $$
SELECT p_prefix || substring(encode(digest(p_id::text,'sha256'),'hex') FROM 1 FOR 10) || '@example.invalid'
$$;
-- Telefono fittizio deterministico
CREATE OR REPLACE FUNCTION _anon_phone(p_id bigint)
RETURNS text LANGUAGE sql IMMUTABLE AS $$
SELECT '+39 3' || substring(encode(digest(p_id::text,'sha256'),'hex') FROM 1 FOR 1)
|| ' ' || substring(encode(digest(p_id::text,'sha256'),'hex') FROM 2 FOR 8)
$$;
-- Nome sintetico deterministico
CREATE OR REPLACE FUNCTION _anon_name(p_id bigint, p_prefix text)
RETURNS text LANGUAGE sql IMMUTABLE AS $$
SELECT p_prefix || ' ' || upper(substring(encode(digest(p_id::text,'sha256'),'hex') FROM 1 FOR 8))
$$;
-- VAT fittizia deterministica
CREATE OR REPLACE FUNCTION _anon_vat(p_id bigint)
RETURNS text LANGUAGE sql IMMUTABLE AS $$
WITH h AS (SELECT encode(digest(p_id::text,'sha256'),'hex') AS hx)
SELECT 'IT' || substring(regexp_replace(hx,'\D','0','g') FROM 1 FOR 11) FROM h
$$;
-- Indirizzo sintetico deterministico
CREATE OR REPLACE FUNCTION _anon_address(p_id bigint)
RETURNS TABLE (street text, street2 text, zip text, city text, state_id int, country_id int)
LANGUAGE plpgsql IMMUTABLE AS $$
DECLARE h text;
BEGIN
h := encode(digest(p_id::text,'sha256'),'hex');
street := 'Via ' || upper(substring(h,1,8)) || ', ' || ((p_id % 200)+1)::text;
street2 := NULL;
zip := lpad(((p_id % 90000) + 10000)::text, 5, '0');
city := 'Citta' || substring(h,9,4);
state_id := NULL;
country_id := NULL;
RETURN;
END
$$;
-- Prefisso robusto per partner: usa is_company se presente, altrimenti 'Partner'
CREATE OR REPLACE FUNCTION _partner_prefix(p_id bigint)
RETURNS text LANGUAGE plpgsql STABLE AS $$
DECLARE has_is_company boolean; is_comp boolean := false;
BEGIN
SELECT EXISTS (
SELECT 1 FROM information_schema.columns
WHERE table_schema='public' AND table_name='res_partner' AND column_name='is_company'
) INTO has_is_company;
IF has_is_company THEN
EXECUTE 'SELECT COALESCE(is_company,false) FROM res_partner WHERE id=$1'
INTO is_comp USING p_id;
END IF;
RETURN CASE WHEN is_comp THEN 'Company' ELSE 'Partner' END;
END
$$;
-- =========================
-- res.partner
-- =========================
UPDATE res_partner p
SET name = _anon_name(p.id, _partner_prefix(p.id)),
email = _anon_email(p.id, 'p'),
phone = CASE WHEN p.phone IS NOT NULL THEN _anon_phone(p.id) ELSE NULL END,
mobile = CASE WHEN p.mobile IS NOT NULL THEN _anon_phone(p.id+999999) ELSE NULL END,
vat = CASE WHEN p.vat IS NOT NULL THEN _anon_vat(p.id) ELSE NULL END,
website = NULL,
function = CASE WHEN p.function IS NOT NULL THEN 'Role ' || substring(encode(digest(p.id::text,'sha256'),'hex') FROM 1 FOR 6) ELSE NULL END,
comment = NULL,
street = (SELECT street FROM _anon_address(p.id)),
street2 = (SELECT street2 FROM _anon_address(p.id)),
zip = (SELECT zip FROM _anon_address(p.id)),
city = (SELECT city FROM _anon_address(p.id)),
state_id = (SELECT state_id FROM _anon_address(p.id)),
country_id = (SELECT country_id FROM _anon_address(p.id))
WHERE p.id != 1; -- opzionale: preserva il partner "Your Company" se è 1
-- =========================
-- res.company (compatibile)
-- =========================
-- La maggior parte dei campi contatto è related al partner: l'anonimizzazione sopra basta.
UPDATE res_company c
SET name = _anon_name(c.id,'Company');
-- Azzeramento difensivo di colonne opzionali, solo se presenti
DO $$
DECLARE col text;
BEGIN
FOREACH col IN ARRAY ARRAY['email','phone','website','street','street2','zip','city','company_registry','rml_footer'] LOOP
IF EXISTS (
SELECT 1 FROM information_schema.columns
WHERE table_schema='public' AND table_name='res_company' AND column_name=col
) THEN
EXECUTE format('UPDATE res_company SET %I=NULL', col);
END IF;
END LOOP;
END
$$;
-- =========================
-- res.users (compatibile)
-- =========================
WITH keep AS (
SELECT id FROM res_users WHERE login IN ('admin','__system__') OR id IN (2)
)
UPDATE res_users u
SET login='user.' || substring(encode(digest(u.id::text,'sha256'),'hex') FROM 1 FOR 10),
signature=NULL
WHERE u.id NOT IN (SELECT id FROM keep);
-- Azzeramento difensivo di colonne opzionali se esistono
DO $$
DECLARE col text;
BEGIN
FOREACH col IN ARRAY ARRAY['email','phone','mobile'] LOOP
IF EXISTS (
SELECT 1 FROM information_schema.columns
WHERE table_schema='public' AND table_name='res_users' AND column_name=col
) THEN
EXECUTE format(
'UPDATE res_users SET %I=NULL WHERE id NOT IN (SELECT id FROM res_users WHERE login IN (''admin'',''__system__'') OR id IN (2))',
col
);
END IF;
END LOOP;
END
$$;
-- =========================
-- Mail server off
-- =========================
UPDATE ir_mail_server SET active=FALSE, smtp_pass=NULL;
-- =========================
-- CRM lead/opportunity
-- =========================
UPDATE crm_lead l
SET contact_name = CASE WHEN l.contact_name IS NOT NULL THEN _anon_name(l.id,'Contact') ELSE NULL END,
email_from = CASE WHEN l.email_from IS NOT NULL THEN _anon_email(l.id,'l') ELSE NULL END,
phone = CASE WHEN l.phone IS NOT NULL THEN _anon_phone(l.id) ELSE NULL END,
mobile = CASE WHEN l.mobile IS NOT NULL THEN _anon_phone(l.id+777777) ELSE NULL END,
street = (SELECT street FROM _anon_address(l.id)),
street2 = (SELECT street2 FROM _anon_address(l.id)),
city = (SELECT city FROM _anon_address(l.id)),
zip = (SELECT zip FROM _anon_address(l.id)),
description = NULL;
-- =========================
-- HR (se presente)
-- =========================
UPDATE hr_employee e
SET name = _anon_name(e.id,'Employee'),
work_email = _anon_email(e.id,'e'),
mobile_phone = CASE WHEN e.mobile_phone IS NOT NULL THEN _anon_phone(e.id) ELSE NULL END,
work_phone = NULL,
identification_id = NULL,
address_home_id = address_home_id
WHERE EXISTS (SELECT 1 FROM pg_class WHERE relname='hr_employee');
-- =========================
-- Vendite/Acquisti/Contabilità (testi liberi)
-- =========================
UPDATE sale_order SET client_order_ref=NULL, note=NULL;
UPDATE purchase_order SET notes=NULL;
UPDATE account_move SET narration=NULL;
UPDATE account_move_line aml
SET name = CASE WHEN aml.display_type IS NULL
THEN 'Line ' || substring(encode(digest(aml.id::text,'sha256'),'hex') FROM 1 FOR 6)
ELSE aml.name END;
-- =========================
-- Messaggistica
-- =========================
UPDATE mail_message
SET body=NULL, email_from=NULL, message_id=NULL, reply_to=NULL;
-- mail_mail – compatibile con schemi senza alcune colonne
DO $$
DECLARE col text;
BEGIN
FOREACH col IN ARRAY ARRAY['email_to','email_cc','email_bcc','reply_to','message_id','email_from','headers','subject','body_html'] LOOP
IF EXISTS (
SELECT 1
FROM information_schema.columns
WHERE table_schema='public' AND table_name='mail_mail' AND column_name=col
) THEN
EXECUTE format('UPDATE mail_mail SET %I = NULL', col);
END IF;
END LOOP;
IF EXISTS (
SELECT 1 FROM information_schema.columns
WHERE table_schema='public' AND table_name='mail_mail' AND column_name='state'
) THEN
EXECUTE 'UPDATE mail_mail SET state = ''cancel''';
END IF;
END
$$;
-- =========================
-- Allegati (compatibile con varianti di schema)
-- =========================
DO $$
DECLARE
has_res_model boolean;
has_type boolean;
where_expr text := '1=1';
col text;
BEGIN
SELECT EXISTS (
SELECT 1 FROM information_schema.columns
WHERE table_schema='public' AND table_name='ir_attachment' AND column_name='res_model'
) INTO has_res_model;
SELECT EXISTS (
SELECT 1 FROM information_schema.columns
WHERE table_schema='public' AND table_name='ir_attachment' AND column_name='type'
) INTO has_type;
IF has_res_model THEN
where_expr := where_expr || ' AND res_model IN (' ||
quote_literal('res.partner') || ',' ||
quote_literal('res.company') || ',' ||
quote_literal('crm.lead') || ',' ||
quote_literal('sale.order') || ',' ||
quote_literal('purchase.order') || ',' ||
quote_literal('account.move') || ',' ||
quote_literal('mail.message') || ',' ||
quote_literal('mail.mail') || ',' ||
quote_literal('hr.employee') || ')';
END IF;
IF has_type THEN
where_expr := where_expr || ' AND type = ' || quote_literal('binary');
END IF;
FOREACH col IN ARRAY ARRAY['datas','db_datas','store_fname','checksum','index_content','url'] LOOP
IF EXISTS (
SELECT 1 FROM information_schema.columns
WHERE table_schema='public' AND table_name='ir_attachment' AND column_name=col
) THEN
EXECUTE format('UPDATE ir_attachment SET %I = NULL WHERE %s', col, where_expr);
END IF;
END LOOP;
IF EXISTS (
SELECT 1 FROM information_schema.columns
WHERE table_schema='public' AND table_name='ir_attachment' AND column_name='file_size'
) THEN
EXECUTE format('UPDATE ir_attachment SET file_size = 0 WHERE %s', where_expr);
END IF;
END
$$;
-- =========================
-- Marketing/Tracking
-- =========================
UPDATE utm_source SET name='SRC-' || substring(encode(digest(id::text,'sha256'),'hex') FROM 1 FOR 6);
UPDATE utm_medium SET name='MED-' || substring(encode(digest(id::text,'sha256'),'hex') FROM 1 FOR 6);
UPDATE utm_campaign SET name='CMP-' || substring(encode(digest(id::text,'sha256'),'hex') FROM 1 FOR 6);
-- =========================
-- Parametri/config esterni – safe (no NULL su value)
-- =========================
DO $$
BEGIN
UPDATE ir_config_parameter
SET value = CASE
WHEN key IN ('web.base.url', 'web.base.url.freeze')
THEN 'http://example.invalid'
WHEN key = 'mail.catchall.domain'
THEN 'example.invalid'
WHEN key IN ('mail.catchall.alias', 'mail.bounce.alias', 'fetchmail.user')
THEN 'disabled'
WHEN key = 'fetchmail.server'
THEN 'localhost'
WHEN key = 'fetchmail.port'
THEN '0'
WHEN key = 'fetchmail.password'
THEN '' -- vuoto per rispettare NOT NULL
WHEN key = 'database.uuid'
THEN substring(encode(digest(current_database() || '-' || now()::text, 'sha256'),'hex') FROM 1 FOR 32)
ELSE value
END
WHERE key IN (
'web.base.url.freeze','web.base.url','database.uuid',
'mail.catchall.domain','mail.catchall.alias','mail.bounce.alias',
'fetchmail.server','fetchmail.port','fetchmail.user','fetchmail.password'
);
END
$$;
Mi piace:
Mi piace Caricamento...
