task1.sql - master - arutiunian/subd

task1.sql
47 строк3 KB
arutiunian
second commit
26 мар 2025, 22:26
26 мар 2025, 22:26d2b6559
CREATE USER admin WITH PASSWORD 'admin';

CREATE DATABASE db1 with owner admin;

\c db1

ALTER DATABASE db1 SET anon.default_locale='ru_RU';
CREATE EXTENSION IF NOT EXISTS anon;
select anon.init();
select anon.start_dynamic_masking();

DROP TABLE public.clients CASCADE;
CREATE TABLE public.clients (
    firstname VARCHAR(50),
    lastname VARCHAR(50),
    middlename VARCHAR(50),
    gender CHAR(1),
    email VARCHAR(200),
    city VARCHAR(50),
    address VARCHAR(200),
    phone VARCHAR(20),
    balance INTEGER
);

INSERT INTO public.clients VALUES
('Иван', 'Иванов', 'Иванович', 'm', 'ivan@example.com', 'Москва', 'ул. Пушкина, д. 1', '+7(999)123-4567', 1000.00),
('Мария', 'Петрова', 'Сергеевна', 'f', 'maria@example.com', 'Санкт-Петербург', 'пр. Ленина, д. 10', '+7(999)765-4321', 2000.00),
('Алексей', 'Смирнов', 'Петрович', 'm', 'alex@example.com', 'Новосибирск', 'ул. Гагарина, д. 5', '+7(999)222-3333', 3000.00),
('Елена', 'Козлова', 'Андреевна', 'f', 'elena@example.com', 'Екатеринбург', 'ул. Мира, д. 15', '+7(999)444-5555', 4000.00),
('Дмитрий', 'Николаев', 'Александрович', 'm', 'dmitry@example.com', 'Казань', 'ул. Советская, д. 7', '+7(999)666-7777', 5000.00);

SECURITY LABEL FOR anon ON COLUMN public.clients.firstname IS 'MASKED WITH FUNCTION anon.fake_first_name()';
SECURITY LABEL FOR anon ON COLUMN public.clients.lastname IS 'MASKED WITH FUNCTION anon.fake_last_name()';
SECURITY LABEL FOR anon ON COLUMN public.clients.middlename IS 'MASKED WITH FUNCTION anon.fake_last_name()';
SECURITY LABEL FOR anon ON COLUMN public.clients.email IS 'MASKED WITH FUNCTION anon.partial_email(email)';
SECURITY LABEL FOR anon ON COLUMN public.clients.city IS 'MASKED WITH FUNCTION anon.fake_city()';
SECURITY LABEL FOR anon ON COLUMN public.clients.address IS 'MASKED WITH FUNCTION anon.fake_address()';
SECURITY LABEL FOR anon ON COLUMN public.clients.phone IS 'MASKED WITH FUNCTION anon.random_phone($$+7 XX$$)';
SECURITY LABEL FOR anon ON COLUMN public.clients.balance IS 'MASKED WITH FUNCTION anon.random_int_between(1000, 10000)';


select firstname as имя,middlename,lastname,CASE WHEN gender='m' THEN 'М' ELSE 'Ж' END,email,city,address,phone,balance from public.clients limit 5;
select firstname as имя,middlename,lastname,CASE WHEN gender='m' THEN 'М' ELSE 'Ж' END,email,city,address,phone,balance from mask.clients limit 5;
select attnum,relnamespace,attname,col_description,masking_value,masking_filter from anon.pg_masking_rules where relname='clients';
SELECT * from pg_seclabels;

\drds