Задание 3: Доступ к маскированным данным

Анонимные дампы

Обфусцированные данные могут быть экспортированы с использованием утилит pg_dump, pg_dumpall. Для этого на уровне каждой базы данных должна быть задана функция обфускации, к которой предъявляются следующие требования:

Функция должна быть объявлена на уровне базы данных с помощью SECURITY LABEL FOR anon ON DATABASE <database_name> IS 'EXPORTED WITH FUNCTION anon.mask_select';
Функция должна принимать единственный аргумент экспортируемого объекта - oid::regclass
Функция должна возвращать форматированный запрос, соответствующий выборке обфусцированных данных из объекта
Для автоматического использования функции при экспорте пользователю необходимо назначить флаг SECURITY LABEL FOR anon ON ROLE <role_name> IS 'MASKED DUMP';

Создание обфусцированного дампа

Под пользователем postgres создайте роль для формирования дампа:

postgres@postgres=# CREATE ROLE backup_user LOGIN;

CREATE ROLE

postgres@postgres=# \dg

                                      List of roles
┌─────────────┬────────────────────────────────────────────────────────────┬───────────┐
│  Role name  │                         Attributes                         │ Member of │
├─────────────┼────────────────────────────────────────────────────────────┼───────────┤
│ backup_user │                                                            │ {}        │
│ dbadmin     │                                                            │ {}        │
│ postgres    │ Superuser, Create role, Create DB, Replication, Bypass RLS │ {}        │
└─────────────┴────────────────────────────────────────────────────────────┴───────────┘

Наложите маску на пользователя:

postgres@postgres=# \c prod

You are now connected to database "prod" as user "postgres".

postgres@prod=# SECURITY LABEL FOR anon ON ROLE backup_user IS 'MASKED DUMP';

SECURITY LABEL

Отобразите роли с их масками:

postgres@prod=# SELECT rolname,hasmask FROM anon.pg_masked_roles;

┌────────────────────────────┬─────────┐
│          rolname           │ hasmask │
├────────────────────────────┼─────────┤
│ postgres                   │ f       │
│ pg_database_owner          │ f       │
│ pg_read_all_data           │ f       │
│ pg_write_all_data          │ f       │
│ pg_monitor                 │ f       │
│ pg_read_all_settings       │ f       │
│ pg_read_all_stats          │ f       │
│ pg_stat_scan_tables        │ f       │
│ pg_read_all_perfinsight    │ f       │
│ pg_read_server_files       │ f       │
│ pg_write_server_files      │ f       │
│ pg_execute_server_program  │ f       │
│ pg_signal_backend          │ f       │
│ pg_checkpoint              │ f       │
│ pg_create_plpgsql_language │ f       │
│ pg_read_all_tracing        │ f       │
│ pg_tracing                 │ f       │
│ dbadmin                    │ f       │
│ backup_user                │ t       │
└────────────────────────────┴─────────┘
(19 rows)

Отобразите все метки безопасности:

postgres@prod=# SELECT * FROM pg_seclabels;

┌────────┬──────────┬──────────┬─────────┬──────────────┬──────────────────┬──────────┬─────────────────────────────────────────────────────────────────┐
│ objoid │ classoid │ objsubid │ objtype │ objnamespace │     objname      │ provider │                              label                              │
├────────┼──────────┼──────────┼─────────┼──────────────┼──────────────────┼──────────┼─────────────────────────────────────────────────────────────────┤
│  24578 │     1259 │        8 │ column  │         2200 │ people.balance   │ anon     │ MASKED WITH VALUE NULL                                          │
│  24578 │     1259 │        6 │ column  │         2200 │ people.address   │ anon     │ MASKED WITH VALUE $$CONFIDENTIAL$$                              │
│  24578 │     1259 │        5 │ column  │         2200 │ people.city      │ anon     │ MASKED WITH FUNCTION anon.fake_city()                           │
│  24578 │     1259 │        7 │ column  │         2200 │ people.phone     │ anon     │ MASKED WITH FUNCTION anon.random_phone($$+7 XX$$)               │
│  24578 │     1259 │        4 │ column  │         2200 │ people.email     │ anon     │ MASKED WITH FUNCTION anon.partial_email(email)                  │
│  24578 │     1259 │        3 │ column  │         2200 │ people.lastname  │ anon     │ MASKED WITH FUNCTION anon.pseudo_last_name(lastname,$$wjz,do$$) │
│  24578 │     1259 │        2 │ column  │         2200 │ people.firstname │ anon     │ MASKED WITH FUNCTION anon.fake_first_name()                     │
│  16393 │     2615 │        0 │ schema  │        16393 │ anon             │ anon     │ TRUSTED                                                         │
│     11 │     2615 │        0 │ schema  │           11 │ pg_catalog       │ anon     │ TRUSTED                                                         │
│  32768 │     1260 │        0 │ role    │              │ backup_user      │ anon     │ MASKED DUMP                                                     │
└────────┴──────────┴──────────┴─────────┴──────────────┴──────────────────┴──────────┴─────────────────────────────────────────────────────────────────┘
(10 rows)

Выдайте необходимые привилегии:

postgres@prod=# GRANT USAGE ON SCHEMA public TO backup_user;

GRANT

postgres@prod=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_user;

GRANT

postgres@prod=# GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO backup_user;

GRANT

postgres@prod=# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO backup_user;

ALTER DEFAULT PRIVILEGES

postgres@prod=# SECURITY LABEL FOR anon ON DATABASE prod IS 'EXPORTED WITH FUNCTION anon.mask_select';

SECURITY LABEL

Создайте немаскированный дамп:

[postgres@ServerName ~]$ cd $PGDATA

[postgres@ServerName data]$ pg_dump -U dbadmin  --no-security-labels --exclude-schema=anon,mask -f prod.dmp prod

[postgres@ServerName data]$ less prod.dmp

...
-- Name: people id; Type: DEFAULT; Schema: public; Owner: dbadmin
--

ALTER TABLE ONLY public.people ALTER COLUMN id SET DEFAULT nextval('public.people_id_seq'::regclass);

--
-- Data for Name: people; Type: TABLE DATA; Schema: public; Owner: dbadmin
--

COPY public.people (id, firstname, lastname, email, city, address, phone, balance) FROM stdin;
1       Jo      Patrick boyerkrystal@example.com        Popemouth       (188,"020 Martin Springs Suite 903, Cherylville, WA 25148")     +7 1697687341   3169.77
2       Margaret        Mccarthy        brandon51@example.com   Port Ashley     (576,"6762 Wolf Stravenue Apt. 081, West Thomasport, OH 01468") +7 4185665191   3418.57
3       Tony    Sims    anna37@example.net      East Michelle   (433,"88378 Donald Canyon, East Roberttown, VT 31812")  +7 7535498786   3753.55
4       Angel   Duran   millsjoseph@example.org East Victoriatown       (197,"399 Giles Circles Suite 059, New Nancy, DE 15985")        +7 9858672324   3985.87
5       Wayne   Ayers   mgates@example.org      Monicaville     (131,"8646 Gilbert Shores Suite 151, Port Jennifer, WV 21987")  +7 1264467555   4264.47
...

Создайте маскированный дамп:

[postgres@ServerName data]$ pg_dump -U backup_user --no-security-labels --exclude-schema=anon,mask -f prod_secure.dmp prod

[postgres@ServerName data]$ less prod_secure.dmp

...
ALTER SEQUENCE public.people_id_seq OWNED BY public.people.id;

--
-- Name: people id; Type: DEFAULT; Schema: public; Owner: dbadmin
--

ALTER TABLE ONLY public.people ALTER COLUMN id SET DEFAULT nextval('public.people_id_seq'::regclass);

--
-- Data for Name: people; Type: TABLE DATA; Schema: public; Owner: dbadmin
--

COPY public.people (id, firstname, lastname, email, city, address, phone, balance) FROM stdin;
1       Tracy   Todd    bo******@ex******.com   Davischester    CONFIDENTIAL    +7 XX979544063  \N
2       Candace Garza   br******@ex******.com   Daniellechester CONFIDENTIAL    +7 XX539895048  \N
3       Brittney        Cameron an******@ex******.net   Port Kevinland  CONFIDENTIAL    +7 XX693006761  \N
4       Johnny  Dunn    mi******@ex******.org   Michaelmouth    CONFIDENTIAL    +7 XX685935796  \N
5       Marie   Trevino mg******@ex******.org   Casemouth       CONFIDENTIAL    +7 XX512567511  \N
...

Убедитесь, что в директории data находятся два дампа – маскированный и немаскированный:

[postgres@ServerName data]$ ls -la | grep prod

-rw-r--r--.  1 postgres postgres 48235 Sep 16 14:59 prod.dmp
-rw-r--r--.  1 postgres postgres 32616 Sep 16 15:07 prod_secure.dmp

Восстановите базу из маскированного дампа:

[postgres@ServerName data]$ psql

Pager is used for long output.
Border style is 2.
Line style is unicode.
psql (15.5)
Type "help" for help.

postgres@postgres=# \l

                                                 List of databases
┌───────────┬──────────┬──────────┬─────────────┬─────────────┬────────────┬─────────────────┬───────────────────────┐
│   Name    │  Owner   │ Encoding │   Collate   │    Ctype    │ ICU Locale │ Locale Provider │   Access privileges   │
├───────────┼──────────┼──────────┼─────────────┼─────────────┼────────────┼─────────────────┼───────────────────────┤
│ postgres  │ postgres │ UTF8     │ en_US.UTF-8 │ en_US.UTF-8 │            │ libc            │                       │
│ prod      │ dbadmin  │ UTF8     │ en_US.UTF-8 │ en_US.UTF-8 │            │ libc            │                       │
│ template0 │ postgres │ UTF8     │ en_US.UTF-8 │ en_US.UTF-8 │            │ libc            │ =c/postgres          ↵│
│           │          │          │             │             │            │                 │ postgres=CTc/postgres │
│ template1 │ postgres │ UTF8     │ en_US.UTF-8 │ en_US.UTF-8 │            │ libc            │ =c/postgres          ↵│
│           │          │          │             │             │            │                 │ postgres=CTc/postgres │
└───────────┴──────────┴──────────┴─────────────┴─────────────┴────────────┴─────────────────┴───────────────────────┘
(4 rows)

Создайте новую базу для восстановления дампа:

postgres@postgres=# CREATE DATABASE test WITH OWNER dbadmin;

CREATE DATABASE

postgres@postgres=# \q

[postgres@ServerName data]$ psql test < prod_secure.dmp

[postgres@ServerName data]$ psql -U dbadmin test

Pager is used for long output.
Border style is 2.
Line style is unicode.
psql (15.5)
Type "help" for help.

Убедитесь, что в базе test существует только одна таблица public.people:

dbadmin@test=# \dt

          List of relations
┌────────┬────────┬───────┬─────────┐
│ Schema │  Name  │ Type  │  Owner  │
├────────┼────────┼───────┼─────────┤
│ public │ people │ table │ dbadmin │
└────────┴────────┴───────┴─────────┘
(1 row)

dbadmin@test=# TABLE public.people LIMIT 10;

┌────┬───────────┬────────────┬───────────────────────┬──────────────────┬──────────────┬────────────────┬─────────┐
│ id │ firstname │  lastname  │         email         │       city       │   address    │     phone      │ balance │
├────┼───────────┼────────────┼───────────────────────┼──────────────────┼──────────────┼────────────────┼─────────┤
│  1 │ Tracy     │ Todd       │ bo******@ex******.com │ Davischester     │ CONFIDENTIAL │ +7 XX979544063 │         │
│  2 │ Candace   │ Garza      │ br******@ex******.com │ Daniellechester  │ CONFIDENTIAL │ +7 XX539895048 │         │
│  3 │ Brittney  │ Cameron    │ an******@ex******.net │ Port Kevinland   │ CONFIDENTIAL │ +7 XX693006761 │         │
│  4 │ Johnny    │ Dunn       │ mi******@ex******.org │ Michaelmouth     │ CONFIDENTIAL │ +7 XX685935796 │         │
│  5 │ Marie     │ Trevino    │ mg******@ex******.org │ Casemouth        │ CONFIDENTIAL │ +7 XX512567511 │         │
│  6 │ Douglas   │ Villarreal │ ma******@ex******.net │ North Davidmouth │ CONFIDENTIAL │ +7 XX683494078 │         │
│  7 │ Lance     │ Briggs     │ sh******@ex******.com │ Colemanville     │ CONFIDENTIAL │ +7 XX451495581 │         │
│  8 │ Sheena    │ Ingram     │ br******@ex******.net │ Justinburgh      │ CONFIDENTIAL │ +7 XX154889103 │         │
│  9 │ Clarence  │ Clark      │ ml******@ex******.net │ Justinshire      │ CONFIDENTIAL │ +7 XX385557464 │         │
│ 10 │ Melinda   │ Munoz      │ it******@ex******.net │ Lynchshire       │ CONFIDENTIAL │ +7 XX260131784 │         │
└────┴───────────┴────────────┴───────────────────────┴──────────────────┴──────────────┴────────────────┴─────────┘
(10 rows)

Выполните запрос для получения меток безопасности в этой базе:

dbadmin@test=# SELECT * from pg_seclabels;

┌────────┬──────────┬──────────┬──────────┬──────────────┬─────────────┬──────────┬─────────────────────────────────────────┐
│ objoid │ classoid │ objsubid │ objtype  │ objnamespace │   objname   │ provider │                  label                  │
├────────┼──────────┼──────────┼──────────┼──────────────┼─────────────┼──────────┼─────────────────────────────────────────┤
│  16387 │     1262 │        0 │ database │              │ prod        │ anon     │ EXPORTED WITH FUNCTION anon.mask_select │
│  32768 │     1260 │        0 │ role     │              │ backup_user │ anon     │ MASKED DUMP                             │
└────────┴──────────┴──────────┴──────────┴──────────────┴─────────────┴──────────┴─────────────────────────────────────────┘
(2 rows)

Выгрузка логического дампа БД с обфусцированными данными

Настройте базу данных для использования функциональности обфускации.
Создайте пользователя и назначьте метку маскирования для выполнения выгрузки данных:
```
postgres@prod=# CREATE USER anon_dump SUPERUSER;
```
```
postgres@prod=# SECURITY LABEL FOR anon ON ROLE anon_dump IS 'MASKED DUMP';
```

Создайте таблицу и сгенерируйте в нее данные из таблиц подстановок:

postgres@prod=# DROP TABLE IF EXISTS public.people CASCADE;

postgres@prod=# \c - anon_dump

You are now connected to database "prod" as user "anon_dump".

anon_dump@prod=# CREATE TABLE public.people(
   id serial NOT NULL PRIMARY key,
   firstname TEXT,
   lastname TEXT,
   phone TEXT,
   balance numeric(17,2)
);

anon_dump@prod=# 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() * 50000)::numeric(17,2)
FROM fname
   NATURAL JOIN lname
ORDER BY random() LIMIT 1e5;

Пример вывода первых 10 записей:

anon_dump@prod=# SELECT * FROM people ORDER BY id LIMIT 10;

┌────┬───────────┬──────────┬─────────────┬─────────┐
│ id │ firstname │ lastname │    phone    │ balance │
├────┼───────────┼──────────┼─────────────┼─────────┤
│  1 │ Barry     │ Spears   │ +7 6508992  │    0.33 │
│  2 │ Kyle      │ Downs    │ +7 12702722 │    0.64 │
│  3 │ Morgan    │ Orr      │ +7 13715972 │    0.69 │
│  4 │ Roberta   │ Lloyd    │ +7 14709168 │    0.74 │
│  5 │ Dwayne    │ Sanchez  │ +7 16512598 │    0.83 │
│  6 │ Jackson   │ Romero   │ +7 27236265 │    1.36 │
│  7 │ Sara      │ Kennedy  │ +7 28601804 │    1.43 │
│  8 │ Marco     │ Weiss    │ +7 30734520 │    1.54 │
│  9 │ Wendy     │ Sutton   │ +7 31748493 │    1.59 │
│ 10 │ Sonia     │ Archer   │ +7 38600894 │    1.93 │
└────┴───────────┴──────────┴─────────────┴─────────┘
(10 rows)

Примените правило динамического маскирования метода подмены константой:

anon_dump@prod=# ALTER DATABASE prod SET anon.transparent_dynamic_masking='true';

anon_dump@prod=# SELECT pg_reload_conf();

anon_dump@prod=# SELECT anon.start_dynamic_masking();

NOTICE:  schema "mask" already exists, skipping
┌───────────────────────┐
│ start_dynamic_masking │
├───────────────────────┤
│ t                     │
└───────────────────────┘
(1 row)

anon_dump@prod=# SECURITY LABEL FOR anon ON COLUMN public.people.lastname IS 'MASKED WITH VALUE $$*PVT*$$';

Выполните выгрузку. Пример запроса:

[postgres@ServerName ~]$ pg_dump -U anon_dump --no-security-labels --exclude-schema=anon,mask -f anon_dump_1.sql prod

Для проверки выполните создание базы данных и загрузку выполненного дампа:

[postgres@ServerName ~]$ createdb anon_feat_load_dump

[postgres@ServerName ~]$ psql --table=people -f anon_dump_1.sql anon_feat_load_dump

Вывод из таблицы базы данных anon_feat_load_dump:

[postgres@ServerName ~]$ psql -U anon_dump -d anon_feat_load_dump

anon_dump@anon_feat_load_dump=# SELECT * FROM people ORDER BY id LIMIT 10;

┌────┬───────────┬──────────┬─────────────┬─────────┐
│ id │ firstname │ lastname │    phone    │ balance │
├────┼───────────┼──────────┼─────────────┼─────────┤
│  1 │ Barry     │ *PVT*    │ +7 6508992  │    0.33 │
│  2 │ Kyle      │ *PVT*    │ +7 12702722 │    0.64 │
│  3 │ Morgan    │ *PVT*    │ +7 13715972 │    0.69 │
│  4 │ Roberta   │ *PVT*    │ +7 14709168 │    0.74 │
│  5 │ Dwayne    │ *PVT*    │ +7 16512598 │    0.83 │
│  6 │ Jackson   │ *PVT*    │ +7 27236265 │    1.36 │
│  7 │ Sara      │ *PVT*    │ +7 28601804 │    1.43 │
│  8 │ Marco     │ *PVT*    │ +7 30734520 │    1.54 │
│  9 │ Wendy     │ *PVT*    │ +7 31748493 │    1.59 │
│ 10 │ Sonia     │ *PVT*    │ +7 38600894 │    1.93 │
└────┴───────────┴──────────┴─────────────┴─────────┘
(10 rows)

Как можно заметить по выводу, данные из столбца lastname подверглись подмене и имеют не исходный вид.

Управление

Изучите все маски, наложенные на таблицу можно следующим запросом:

anon_dump@anon_feat_load_dump=# \c prod postgres

postgres@prod=# SELECT attnum,relnamespace,attname,col_description,masking_value,masking_filter FROM anon.pg_masking_rules WHERE relname='people';

┌────────┬──────────────┬──────────┬─────────────────────────────┬───────────────┬────────────────┐
│ attnum │ relnamespace │ attname  │       col_description       │ masking_value │ masking_filter │
├────────┼──────────────┼──────────┼─────────────────────────────┼───────────────┼────────────────┤
│      3 │ public       │ lastname │ MASKED WITH VALUE $$*PVT*$$ │ $$*PVT*$$     │ $$*PVT*$$      │
└────────┴──────────────┴──────────┴─────────────────────────────┴───────────────┴────────────────┘
(1 row)

Для получения меток безопасности можно обратиться к таблице pg_seclabels:

postgres@prod=# SELECT * FROM pg_seclabels;

┌────────┬──────────┬──────────┬──────────┬──────────────┬────────────────────────┬──────────┬─────────────────────────────────────────┐
│ objoid │ classoid │ objsubid │ objtype  │ objnamespace │        objname         │ provider │                  label                  │
├────────┼──────────┼──────────┼──────────┼──────────────┼────────────────────────┼──────────┼─────────────────────────────────────────┤
│  17792 │     1259 │        3 │ column   │         2200 │ public.people.lastname │ anon     │ MASKED WITH VALUE $$*PVT*$$             │
│  16818 │     2615 │        0 │ schema   │        16818 │ anon                   │ anon     │ TRUSTED                                 │
│     11 │     2615 │        0 │ schema   │           11 │ pg_catalog             │ anon     │ TRUSTED                                 │
│  16455 │     1262 │        0 │ database │              │ prod                   │ anon     │ EXPORTED WITH FUNCTION anon.mask_select │
│  17718 │     1260 │        0 │ role     │              │ backup_user            │ anon     │ MASKED DUMP                             │
│  17735 │     1260 │        0 │ role     │              │ anon_dump              │ anon     │ MASKED DUMP                             │
└────────┴──────────┴──────────┴──────────┴──────────────┴────────────────────────┴──────────┴─────────────────────────────────────────┘
(6 rows)

Запрос меток безопасности в тестовой базе данных:

postgres@prod=# \c test dbadmin

dbadmin@test=# SELECT * FROM pg_seclabels;

┌────────┬──────────┬──────────┬──────────┬──────────────┬─────────────┬──────────┬─────────────────────────────────────────┐
│ objoid │ classoid │ objsubid │ objtype  │ objnamespace │   objname   │ provider │                  label                  │
├────────┼──────────┼──────────┼──────────┼──────────────┼─────────────┼──────────┼─────────────────────────────────────────┤
│  16455 │     1262 │        0 │ database │              │ prod        │ anon     │ EXPORTED WITH FUNCTION anon.mask_select │
│  17718 │     1260 │        0 │ role     │              │ backup_user │ anon     │ MASKED DUMP                             │
│  17735 │     1260 │        0 │ role     │              │ anon_dump   │ anon     │ MASKED DUMP                             │
└────────┴──────────┴──────────┴──────────┴──────────────┴─────────────┴──────────┴─────────────────────────────────────────┘
(3 rows)

Выполните запрос получения ролей с их масками:

dbadmin@test=# \c prod postgres

postgres@prod=# SELECT rolname,hasmask FROM anon.pg_masked_roles;

┌────────────────────────────┬─────────┐
│          rolname           │ hasmask │
├────────────────────────────┼─────────┤
│ pg_database_owner          │ f       │
│ pg_read_all_data           │ f       │
│ pg_write_all_data          │ f       │
│ pg_monitor                 │ f       │
│ pg_read_all_settings       │ f       │
│ pg_read_all_stats          │ f       │
│ pg_stat_scan_tables        │ f       │
│ pg_read_all_perfinsight    │ f       │
│ pg_read_server_files       │ f       │
│ pg_write_server_files      │ f       │
│ pg_execute_server_program  │ f       │
│ pg_signal_backend          │ f       │
│ pg_checkpoint              │ f       │
│ pg_create_plpgsql_language │ f       │
│ pg_read_all_tracing        │ f       │
│ pg_tracing                 │ f       │
│ postgres                   │ f       │
│ dbadmin                    │ f       │
│ backup_user                │ t       │
│ anon_dump                  │ t       │
└────────────────────────────┴─────────┘
(20 rows)

Для отмены масок имеются операторы, например для столбца lastname:

postgres@prod=# SECURITY LABEL FOR anon ON COLUMN public.people.lastname IS NULL;

Подведем итоги

Вопрос 1

Вопрос 1: Какие требования предъявляются к функции обфускации, используемой при экспорте?

Должна быть объявлена на уровне базы данных

Должна принимать аргумент типа `oid::regclass`

Должна возвращать тип `boolean`

Должна возвращать формированный SQL-запрос выборки данных

Вопрос 2

Вопрос 2: Что показывает запрос:

SELECT * FROM pg_seclabels;

Все метки безопасности в текущей базе данных

Только маски столбцов

Только роли с маскированием

Метки для таблиц, ролей, схем и БД

Вопрос 3

Вопрос 3: Какая метка безопасности назначается роли для формирования маскированного дампа?

`MASKED EXPORT``MASKED DUMP``DUMP MASKED``ANON DUMP`

Анонимные дампы​

Создание обфусцированного дампа​

Выгрузка логического дампа БД с обфусцированными данными​

Управление​

Подведем итоги​