Обфускация данных#

# Дополнительные ключи командной строки для pg_dump
pg_dump --help
 --no-extension=PATTERN                 do not dump specified extension
 
# Дополнительные ключи командной строки для pg_dump
pg_dumpall --help
  --no-extension=PATTERN                 do not dump specified extension

ALTER DATABASE <database_name> SET session_preload_libraries='anon';
ALTER DATABASE <database_name> SET anon.default_locale='<locale>'; -- en_US,fr_FR,ru_RU
\c <database_name>
CREATE EXTENSION anon;
SELECT * FROM anon.init();
SECURITY LABEL FOR anon ON DATABASE <database_name> IS 'EXPORTED WITH FUNCTION anon.mask_select';

SECURITY LABEL FOR anon ON ROLE <sensitive_user> IS 'MASKED';

SECURITY LABEL FOR anon ON ROLE <sensitive_user> IS 'MASKED DUMP';

SECURITY LABEL FOR anon ON SCHEMA <sensitive_schema> IS 'TRUSTED';
SECURITY LABEL FOR anon ON COLUMN <sensitive_schema>.<sensitive_rel>.<sensitive_attr> IS 'MASKED WITH [VALUE $$<const_value>$$ | MASKED WITH FUNCTION <function_name>([<function_args>])];

pg_dump -Fc -U<sensitive_user> --no-security-labels --exclude-schema=anon,mask ---f <database_name>.pgdmp <database_name

SELECT anon.start_dynamic_masking();

SELECT anon.<function_name>(); -- где <function_name> - статическая функция

#!/usr/bin/python3
 
import sys
import csv
import argparse
import random
import faker
import importlib
 
 
def address():
    return [[oid, f.unique.address().replace('\n', ', ')]
            for oid in range(lines)]
 
 
def city():
    return [[oid, f.unique.city()] for oid in range(lines)]
 
 
def company():
    return [[oid, f.unique.company()] for oid in range(lines)]
 
 
# The dataset is too small, we're extracting the values directly
def country():
    values = []
    for loc in locales:
        m = importlib.import_module('faker.providers.address.'+loc)
        values += list(set(m.Provider.countries))
    random.shuffle(values)
    return [[oid, values[oid]]
            for oid in range(min(len(values), lines))]
 
 
def email():
    return [[oid, f.unique.email()] for oid in range(lines)]
 
 
# The dataset is too small, we're extracting the values directly
def first_name():
    values = []
    for loc in locales:
        m = importlib.import_module('faker.providers.person.'+loc)
        values += list("m"+fn for fn in m.Provider.first_names_male)
        values += list("f"+fn for fn in m.Provider.first_names_female)
    random.shuffle(values)
    return [[oid, values[oid][:1], values[oid][1:]]
            for oid in range(min(len(values), lines))]
 
# The dataset is too small, we're extracting the values directly
def middle_name():
    values = []
    for loc in locales:
        if loc == 'ru_RU':
            m = importlib.import_module('faker.providers.person.ru_RU')
            values += list("m"+fn for fn in m.Provider.middle_names_male)
            values += list("f"+fn for fn in m.Provider.middle_names_female)
    random.shuffle(values)
    return [[oid,  values[oid][:1], values[oid][1:]]
            for oid in range(min(len(values), lines))]
 
def iban():
    return [[oid, f.unique.iban()] for oid in range(lines)]
 
 
# The dataset is too small, we're extracting the values directly
def last_name():
    values = []
    for loc in locales:
        m = importlib.import_module('faker.providers.person.'+loc)
        if hasattr(m.Provider,'last_names_male'):
            values += list("m"+fn for fn in m.Provider.last_names_male)
            values += list("f"+fn for fn in m.Provider.last_names_female)
        else:
            values += list("m"+fn for fn in m.Provider.last_names)
            values += list("f"+fn for fn in m.Provider.last_names)           
    random.shuffle(values)
    return [[oid, values[oid][:1], values[oid][1:]]
            for oid in range(min(len(values), lines))]
 
 
def lorem_ipsum():
    return [[oid, f.unique.paragraph(nb_sentences=8)] for oid in range(lines)]
 
 
def postcode():
    return [[oid, f.unique.postcode()] for oid in range(lines)]
 
 
def siret():
    # override the locales this data is only relevant in France
    french_faker = faker.Faker('fr_FR')
    return [[oid, french_faker.unique.siret()] for oid in range(lines)]
 
 
generator_methods = [
  'address', 'city', 'company', 'country', 'email', 'first_name', 'iban',
  'last_name', 'middle_name', 'lorem_ipsum', 'postcode', 'siret'
]
 
 
# Input
parser = argparse.ArgumentParser()
parser.add_argument(
    '--table',
    help='Type of data ({})'.format(generator_methods),
    choices=generator_methods,
    required=True
)
parser.add_argument(
    '--locales',
    help='Localization of the fake data (comma separated list)',
    default='en'
)
parser.add_argument(
    '--lines',
    help='Number of rows to add to the table',
    type=int,
    default=1000
)
parser.add_argument(
    '--seed',
    help='Initializes the random generator'
)
args = parser.parse_args()
 
locales = [loc.strip() for loc in args.locales.split(' ')]
lines = args.lines
# Generator
f = faker.Faker(locales)
if args.seed:
    random.seed(args.seed)
    faker.Faker.seed(args.seed)
 
for row in locals().get(args.table)():
    csv.writer(sys.stdout, delimiter='\t').writerow(row)

bash$ generator.py --help
usage: generator.py [-h] --table
                    {address,city,company,country,email,first_name,iban,last_name,middle_name,lorem_ipsum,postcode,siret}
                    [--locales LOCALES] [--lines LINES] [--seed SEED]
 
optional arguments:
  -h, --help            show this help message and exit
  --table {address,city,company,country,email,first_name,iban,last_name,middle_name,lorem_ipsum,postcode,siret}
                        Type of data (['address', 'city', 'company',
                        'country', 'email', 'first_name', 'iban', 'last_name',
                        'middle_name', 'lorem_ipsum', 'postcode', 'siret'])
  --locales LOCALES     Localization of the fake data (comma separated list)
  --lines LINES         Number of rows to add to the table
  --seed SEED           Initializes the random generator

bash$
cd $PGHOME/share/extension/anon/ru_RU/fake
for file in *.csv; do echo $file; generator.py --locales=ru_RU --lines=3000 --table $(basename $file .csv) 1>$file; done

-- Параметр :nr_fake_records - количество генерируемых элементов
-- ИНН
-- !! Генерируется только ИНН юр. лица. При необходимости генерировать ИНН физ. лица используйте функцию anon.random_inn12()
  INSERT INTO anon.inn(val) SELECT anon.random_inn10() FROM generate_series(1,:nr_fake_records) id;
-- СНИЛС
  INSERT INTO anon.snils(val) SELECT anon.random_snils() FROM generate_series(1,:nr_fake_records) id;
-- ОГРН
-- !! Генерируется только ОГРН юр. лица. При необходимости генерировать ОГРН физ. лица используйте функцию anon.random_ogrnip()
  INSERT INTO anon.ogrn(val) SELECT anon.random_ogrn() FROM generate_series(1,:nr_fake_records) id;
-- ФИО
  WITH fname AS (SELECT gender,COALESCE(val,'') first_name FROM anon.first_name),
    mname AS (SELECT gender,COALESCE(val,'') middle_name FROM anon.middle_name),
    lname AS (SELECT gender,COALESCE(val,'') last_name FROM anon.last_name)
  INSERT INTO anon.personal_name(gender,first_name,middle_name,last_name)
  SELECT fname.gender,first_name,middle_name,last_name
  FROM fname
      NATURAL JOIN mname
      NATURAL JOIN lname
  ORDER BY random() LIMIT :nr_fake_records;

-- Исходная таблицы
\d+ public.people01
                                                             Table "public.people01"
┌───────────┬───────────────┬───────────┬──────────┬──────────────────────────────────────┬──────────┬─────────────┬──────────────┬─────────────┐
│  Column   │     Type      │ Collation │ Nullable │               Default                │ Storage  │ Compression │ Stats target │ Description │
├───────────┼───────────────┼───────────┼──────────┼──────────────────────────────────────┼──────────┼─────────────┼──────────────┼─────────────┤
│ id        │ integer       │           │ not null │ nextval('people01_id_seq'::regclass) │ plain    │             │              │             │
│ firstname │ text          │           │          │                                      │ extended │             │              │             │
│ lastname  │ text          │           │          │                                      │ extended │             │              │             │
│ phone     │ text          │           │          │                                      │ extended │             │              │             │
│ balance   │ numeric(17,2) │           │          │                                      │ main     │             │              │             │
└───────────┴───────────────┴───────────┴──────────┴──────────────────────────────────────┴──────────┴─────────────┴──────────────┴─────────────┘
Indexes:
    "people01_pkey" PRIMARY KEY, btree (id)
Access method: heap
-- SECURITY LABELS таблицы
select * from anon.pg_masking_rules where relname='people01';
┌──────────┬────────┬──────────────┬──────────┬──────────┬─────────────┬────────────────────┬──────────────────┬───────────────┬──────────┬────────────────┬────────────────┐
│ attrelid │ attnum │ relnamespace │ relname  │ attname  │ format_type │  col_description   │ masking_function │ masking_value │ priority │ masking_filter │ trusted_schema │
├──────────┼────────┼──────────────┼──────────┼──────────┼─────────────┼────────────────────┼──────────────────┼───────────────┼──────────┼────────────────┼────────────────┤
│    51909 │      3 │ public       │ people01 │ lastname │ text        │ MASKED WITH VALUE …│ NULL             │ NULL          │      100 │ NULL           │ f              │
│          │        │              │          │          │             │…NULL               │                  │               │          │                │                │
└──────────┴────────┴──────────────┴──────────┴──────────┴─────────────┴────────────────────┴──────────────────┴───────────────┴──────────┴────────────────┴────────────────┘
 -- Результат работы функции
select * from anon.mask_select('public.people01'::regclass);
┌────────────────────────────────────────────────────────────────────────────────────────┐
│                                      mask_select                                       │
├────────────────────────────────────────────────────────────────────────────────────────┤
│ SELECT id,firstname,CAST(NULL AS text) AS lastname,phone,balance FROM public.people01  │
└────────────────────────────────────────────────────────────────────────────────────────┘

-- !! Здесь и далее - используем:
--    БД anon_feat
--    Пользователя для обфускации anon_user
--    Пользователя для логического дампа anon_admin
/*
-- database
DROP DATABASE IF EXISTS anon_feat;
CREATE DATABASE anon_feat;
ALTER DATABASE anon_feat SET session_preload_libraries='anon';
ALTER DATABASE anon_feat SET anon.default_locale='ru_RU';
ALTER DATABASE anon_feat SET anon.sourceschema='public';
ALTER DATABASE anon_feat SET anon.maskschema='mask';
ALTER DATABASE anon_feat SET anon.transparent_dynamic_masking='true';
 \c anon_feat
CREATE EXTENSION IF NOT EXISTS anon;
SELECT * FROM anon.init();
SET search_path=mask,public;
 
-- users
DROP USER IF EXISTS anon_user;
CREATE USER anon_user;
SECURITY LABEL FOR anon ON ROLE anon_user IS 'MASKED';
ALTER USER anon_user SET search_path='mask','public';
DROP USER IF EXISTS anon_dump;
CREATE USER anon_dump SUPERUSER;
SECURITY LABEL FOR anon ON ROLE anon_dump IS 'MASKED DUMP';
SECURITY LABEL FOR anon ON DATABASE anon_feat IS 'EXPORTED WITH FUNCTION anon.mask_select';
-- create masked schema
CREATE SCHEMA IF NOT EXISTS mask;
GRANT USAGE ON SCHEMA mask TO anon_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA mask GRANT ALL ON TABLES TO anon_user;
SELECT anon.start_dynamic_masking();
*/
 
 
 
-- создаем схему
CREATE SCHEMA IF NOT EXISTS my_anon;
-- помечаем схему, как TRUSTED. Только функции из схемы с атрибутом TRUSTED могут быть использованы для обфускации
SECURITY LABEL FOR anon ON SCHEMA my_anon IS 'TRUSTED';
-- создаем функцию обфускации
CREATE OR REPLACE FUNCTION my_anon.fake_noise
(
-- массив символов для генерации
elem TEXT DEFAULT 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyzАБВГДЕЁЖЗИЙКЛМНОПРСТУФХЦЧШЩЬЫЪЭЮЯабвгдеёжзиклмнопрстуфхцчшщьыъэюя0123456789_<>',
-- длина генерируемого массива
max_len integer DEFAULT 10
)
RETURNS text
AS $$
  SELECT array_to_string(
    array(
        select substr(elem,
                      ((random()*(length(elem)-1)+1)::integer)
                      ,1)
        from generate_series(1,(max_len))
    ),''
  );
$$
  LANGUAGE SQL
  VOLATILE            -- обязательно. Используем random() - каждое обращение к функции должно генерировать новый результат
  PARALLEL RESTRICTED
  SECURITY INVOKER
  SET search_path=''
;
-- проверка работоспособности
SELECT id, my_anon.fake_noise(max_len => (32*random())::int4+10)
FROM generate_series(1,10) id;
-- пример вывода:
-- ┌────┬──────────────────────────────────────────┐
-- │ id │                fake_noise                │
-- ├────┼──────────────────────────────────────────┤
-- │  1 │ БEpЯДvю13иUв4V5ш7ршC775qgе               │
-- │  2 │ sJФ8Ж6<FEHэзO                            │
-- │  3 │ Ш7ЙЛ1BJЖ1ЗХОxзnчЯJQD5ВегVСТс2cyy         │
-- │  4 │ лЪЭkVЖРЕРFBХДUК8ZНAВmмпзQkj9пoьWгSбa     │
-- │  5 │ kУ_zЛ3iЗяуВхSЦoрзfОj                     │
-- │  6 │ tЙГvУбl4XНп2Х9гПShJФCН6хIТ6вUyAП         │
-- │  7 │ ыAнtaЪ1lояtм3инжшaщ9ЁIRыЭPVcаууvяО1эдZДБ │
-- │  8 │ ОЪg95ЯoАъХз1YЁЮЮщ7рUxWTZkj               │
-- │  9 │ ЭQQlZPШщхЬRucЭэВMJeПЛoЬUXaSzуVdKu        │
-- │ 10 │ SъGKaqaвKUsоюьщюъжSоУщhzjёмшz1схгщясcА   │
-- └────┴──────────────────────────────────────────┘
-- (10 rows)
-- Создадим тестовые данные для проверки
DROP TABLE IF EXISTS public.people;
CREATE TABLE public.people(
id serial NOT NULL PRIMARY key,
firstname TEXT,
lastname TEXT,
phone TEXT,
balance numeric(17,2)
);
WITH fname AS (SELECT gender,COALESCE(val,'') first_name FROM anon.first_name),
  lname AS (SELECT gender,COALESCE(val,'') last_name FROM anon.last_name)
INSERT INTO public.people(firstname,lastname,phone,balance)
SELECT
  first_name
  ,last_name
  ,format('+7 %1$s',substring(ceil(random()*1e12)::TEXT,1,10))
  ,(random()*1e5-5e4)::numeric(17,2)
FROM fname
  NATURAL JOIN lname
ORDER BY random() LIMIT 1e5;
-- назначим вновь созданную функцию для обфускации
SECURITY LABEL FOR anon ON COLUMN public.people.balance IS NULL;
SECURITY LABEL FOR anon ON COLUMN public.people.lastname IS NULL;
SECURITY LABEL FOR anon ON COLUMN public.people.lastname IS 'MASKED WITH FUNCTION my_anon.fake_noise()';
-- проверим работоспособность
SET ROLE anon_user;
SELECT * FROM mask.people LIMIT 10;
-- Пример вывода:
-- ┌───────┬───────────┬────────────┬───────────────┬───────────┐
-- │  id   │ firstname │  lastname  │     phone     │  balance  │
-- ├───────┼───────────┼────────────┼───────────────┼───────────┤
-- │ 15688 │ Алла      │ ЛееъyЩehUщ │ +7 1586788872 │ -34703.93 │
-- │ 84143 │ Валерьян  │ ДТноеRлАU6 │ +7 8393111953 │  35820.20 │
-- │ 14596 │ Эрнст     │ НRрВмбЕsW_ │ +7 1476835007 │ -34000.72 │
-- │ 81187 │ Маргарита │ 5Фж7ЖтV4ЩV │ +7 8096235724 │  33986.22 │
-- │ 89740 │ Порфирий  │ пХZw>цУщН> │ +7 8952409074 │  42892.93 │
-- │ 17042 │ Никифор   │ ХZювГGХ4TУ │ +7 1718701798 │ -31806.35 │
-- │ 26149 │ Прохор    │ сAd_ВЛМЗwY │ +7 2612167258 │ -22276.37 │
-- │ 20473 │ Марфа     │ 4_PкG_СNkУ │ +7 2053565418 │ -26932.25 │
-- │ 89709 │ Полина    │ чHXnнсрmЬn │ +7 8949979071 │  41891.58 │
-- │ 90649 │ Ладислав  │ wЮIбnиCцD6 │ +7 9042466198 │  39378.97 │
-- └───────┴───────────┴────────────┴───────────────┴───────────┘
-- (10 rows)
RESET ROLE;
 
-- на основе функции обфускации создаем функцию псевдонимизации и таблицу подстановок для нее
-- таблица
CREATE TABLE my_anon.noise (
 oid serial,
 val text
);
INSERT INTO my_anon.noise (val) SELECT my_anon.fake_noise() FROM generate_series(1,2500) id;
-- функция
CREATE OR REPLACE FUNCTION my_anon.pseudo_noise(
  seed anyelement,              -- значение для подстановки. Одинаковое значение генерирует одинаковый hash.
  salt text DEFAULT NULL::text  -- salt для хеширования (при необходимости)
)
 RETURNS text
AS $$
  SELECT COALESCE(val,anon.notice_if_not_init())
  FROM my_anon.noise
  WHERE oid = anon.projection_to_oid( -- функция anon.projection_to_oid генерирует остаток от деления вычисляемого хеша на количество элементов подстановок. Из таблицы подстановок выбираем элемент c oid, соответствующего остатку
    seed,
    COALESCE(salt, pg_catalog.current_setting('anon.salt')),
    (SELECT last_value FROM my_anon.noise_oid_seq)
  );
$$
 LANGUAGE sql
 STABLE
 PARALLEL SAFE
 SECURITY DEFINER
 SET search_path TO ''
;
-- назначим вновь созданную функцию для обфускации
SECURITY LABEL FOR anon ON COLUMN public.people.balance IS NULL;
SECURITY LABEL FOR anon ON COLUMN public.people.lastname IS NULL;
SECURITY LABEL FOR anon ON COLUMN public.people.lastname IS 'MASKED WITH FUNCTION my_anon.pseudo_noise(lastname)';
-- проверим работоспособность
SET ROLE anon_user;
SELECT * FROM mask.people LIMIT 10;
SELECT * FROM mask.people LIMIT 10;
RESET ROLE;
-- Пример вывода:
-- ┌───────┬───────────┬────────────┬───────────────┬───────────┐
-- │  id   │ firstname │  lastname  │     phone     │  balance  │
-- ├───────┼───────────┼────────────┼───────────────┼───────────┤
-- │ 15688 │ Алла      │ пqсЦмжPTАE │ +7 1586788872 │ -34703.93 │
-- │ 84143 │ Валерьян  │ лГvИxJ4ИцЕ │ +7 8393111953 │  35820.20 │
-- │ 14596 │ Эрнст     │ ВeysM<gvRЁ │ +7 1476835007 │ -34000.72 │
-- │ 81187 │ Маргарита │ IkР_y7ЪvQZ │ +7 8096235724 │  33986.22 │
-- │ 89740 │ Порфирий  │ жяQrъDpn6к │ +7 8952409074 │  42892.93 │
-- │ 17042 │ Никифор   │ tтcВМeТ5мО │ +7 1718701798 │ -31806.35 │
-- │ 26149 │ Прохор    │ lii5ыцcY1Р │ +7 2612167258 │ -22276.37 │
-- │ 20473 │ Марфа     │ кkCбXээж5_ │ +7 2053565418 │ -26932.25 │
-- │ 89709 │ Полина    │ yjьЮЩЁ<Dgи │ +7 8949979071 │  41891.58 │
-- │ 90649 │ Ладислав  │ 50jY3ВвЛЬХ │ +7 9042466198 │  39378.97 │
-- └───────┴───────────┴────────────┴───────────────┴───────────┘
-- (10 rows)
-- Вывод второго SELECT аналогичен первому. Псевдономизация работает
 
 
-- Пример комбинированного использоания функций
SECURITY LABEL FOR anon ON COLUMN public.people.balance IS NULL;
SECURITY LABEL FOR anon ON COLUMN public.people.lastname IS NULL;
-- если balance >0, то сгененировать рандом в диапазоне [0,50000], иначе null
SECURITY LABEL FOR anon ON COLUMN public.people.balance IS 'MASKED WITH FUNCTION anon.ternary(balance > 0,anon.random_in_numrange($$[0,50000]$$),null)';

SELECT anon.stop_dynamic_masking();
SELECT anon.remove_masks_for_all_columns();
SELECT anon.remove_masks_for_all_roles();
SELECT anon.unload();
DROP EXTENSION anon;
ALTER DATABASE <database name> RESET session_preload_libraries; -- В случае, если session_preload_libraries для БД содержит только расширение anon. В ином случае - убрать расширение из списка предзагружаемых библиотек.