Представления обфускации данных
Представления, обеспечиваемые функциональностью «обфускация данных»:
| Объект | Аргументы, атрибуты | Возвращаемое значение | Описание |
|---|---|---|---|
anon.pg_identifiers | - | attrelid::oid, attnum::integer, relname::name, attname::name, format_type::text, col_description::text, indirect_identifier::boolean, priority::integer | Список автоматически созданных правил маскировки, при использовании функции anon.detect() |
anon.pg_masked_roles | - | rolname::name, rolsuper::boolean, rolinherit::boolean, rolcreaterole::boolean, rolcreatedb::boolean, rolcanlogin::boolean, rolreplication::boolean, rolconnlimit::integer, rolpassword::text, rolvaliduntil::timestamp with time zone, rolbypassrls::boolean, rolconfig::text[], oid::oid, grace_period::interval, grace_period_source::text, grace_time_left::interval, rolprevpassword::text, hasmask::boolean | Список ролей экземпляра, дополненный признаком маскирования роли |
anon.pg_masking_rules | - | attrelid::oid, attnum::integer, relnamespace::regnamespace, relname::name, attname::name, format_type::text, col_description::text, masking_function::text, masking_value::text, priority::integer, masking_filter::text, trusted_schema::boolean | Список правил маскировки |
anon.pg_masks | - | attrelid::oid, attnum::integer, relnamespace::regnamespace, relname::name, attname::name, format_type::text, col_description::text, masking_function::text, masking_value::text, priority::integer, masking_filter::text, trusted_schema::boolean | Список правил маскировки (deprecated) |
anon.pg_identifiers
DDL
View "anon.pg_identifiers"
Column | Type | Collation | Nullable | Default | Storage | Description
---------------------+---------+-----------+----------+---------+----------+-------------
attrelid | oid | | | | plain |
attnum | integer | | | | plain |
relname | name | | | | plain |
attname | name | | | | plain |
format_type | text | | | | extended |
col_description | text | C | | | extended |
indirect_identifier | boolean | | | | plain |
priority | integer | | | | plain |
View definition:
WITH const AS (
SELECT '%(quasi|indirect) identifier%'::text AS pattern_indirect_identifier
)
SELECT sl.objoid AS attrelid,
sl.objsubid AS attnum,
c.relname,
a.attname,
format_type(a.atttypid, a.atttypmod) AS format_type,
sl.label AS col_description,
lower(sl.label) ~ similar_to_escape(k.pattern_indirect_identifier, '#'::text) AS indirect_identifier,
100 AS priority
FROM const k,
pg_seclabel sl
JOIN pg_class c ON sl.classoid = c.tableoid AND sl.objoid = c.oid
JOIN pg_attribute a ON a.attrelid = c.oid AND sl.objsubid = a.attnum
WHERE a.attnum > 0 AND NOT a.attisdropped AND lower(sl.label) ~ similar_to_escape(k.pattern_indirect_identifier, '#'::text) AND sl.provider = current_setting('anon.k_anonymity_provider'::text);
anon.pg_masked_roles
DDL
View "anon.pg_masked_roles"
Column | Type | Collation | Nullable | Default | Storage | Description
---------------------+--------------------------+-----------+----------+---------+----------+-------------
rolname | name | | | | plain |
rolsuper | boolean | | | | plain |
rolinherit | boolean | | | | plain |
rolcreaterole | boolean | | | | plain |
rolcreatedb | boolean | | | | plain |
rolcanlogin | boolean | | | | plain |
rolreplication | boolean | | | | plain |
rolconnlimit | integer | | | | plain |
rolpassword | text | | | | extended |
rolvaliduntil | timestamp with time zone | | | | plain |
rolbypassrls | boolean | | | | plain |
rolconfig | text[] | C | | | extended |
oid | oid | | | | plain |
grace_period | interval | | | | plain |
grace_period_source | text | | | | extended |
grace_time_left | interval | | | | plain |
rolprevpassword | text | | | | extended |
hasmask | boolean | | | | plain |
View definition:
SELECT r.rolname,
r.rolsuper,
r.rolinherit,
r.rolcreaterole,
r.rolcreatedb,
r.rolcanlogin,
r.rolreplication,
r.rolconnlimit,
r.rolpassword,
r.rolvaliduntil,
r.rolbypassrls,
r.rolconfig,
r.oid,
r.grace_period,
r.grace_period_source,
r.grace_time_left,
r.rolprevpassword,
anon.hasmask(r.oid::regrole) AS hasmask
FROM pg_roles r;
anon.pg_masking_rules
DDL
View "anon.pg_masking_rules"
Column | Type | Collation | Nullable | Default | Storage | Description
------------------+--------------+-----------+----------+---------+----------+-------------
attrelid | oid | | | | plain |
attnum | integer | | | | plain |
relnamespace | regnamespace | | | | plain |
relname | name | | | | plain |
attname | name | | | | plain |
format_type | text | | | | extended |
col_description | text | C | | | extended |
masking_function | text | C | | | extended |
masking_value | text | C | | | extended |
priority | integer | | | | plain |
masking_filter | text | C | | | extended |
trusted_schema | boolean | | | | plain |
View definition:
WITH const AS (
SELECT '%MASKED +WITH +FUNCTION +#"%#(%#)#"%'::text AS pattern_mask_column_function,
'MASKED +WITH +VALUE +#"%#" ?'::text AS pattern_mask_column_value
), rules_from_default AS (
SELECT c.oid AS attrelid,
a.attnum,
c.relnamespace::regnamespace AS relnamespace,
c.relname,
a.attname,
format_type(a.atttypid, a.atttypmod) AS format_type,
NULL::text AS col_description,
NULL::text AS masking_function,
anon.masking_value_for_column(c.oid, a.attnum::integer, 'anon'::text) AS masking_value,
0 AS priority
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_attribute a ON a.attrelid = c.oid
LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum
WHERE a.attnum > 0 AND (n.nspname <> ALL (ARRAY['information_schema'::name, 'pg_catalog'::name, 'pg_toast'::name, 'anon'::name])) AND NOT a.attisdropped AND current_setting('anon.privacy_by_default'::text)::boolean
), rules_from_seclabels AS (
SELECT sl.objoid AS attrelid,
sl.objsubid AS attnum,
c.relnamespace::regnamespace AS relnamespace,
c.relname,
a.attname,
format_type(a.atttypid, a.atttypmod) AS format_type,
sl.label AS col_description,
TRIM(BOTH FROM SUBSTRING(sl.label SIMILAR k.pattern_mask_column_function ESCAPE '#'::text)) AS masking_function,
TRIM(BOTH FROM SUBSTRING(sl.label SIMILAR k.pattern_mask_column_value ESCAPE '#'::text)) AS masking_value,
100 AS priority
FROM const k,
pg_seclabel sl
JOIN pg_class c ON sl.classoid = c.tableoid AND sl.objoid = c.oid
JOIN pg_attribute a ON a.attrelid = c.oid AND sl.objsubid = a.attnum
WHERE a.attnum > 0 AND NOT a.attisdropped AND (sl.label ~ similar_to_escape(k.pattern_mask_column_function, '#'::text) OR sl.label ~ similar_to_escape(k.pattern_mask_column_value, '#'::text)) AND sl.provider = 'anon'::text
), rules_from_all AS (
SELECT rules_from_default.attrelid,
rules_from_default.attnum,
rules_from_default.relnamespace,
rules_from_default.relname,
rules_from_default.attname,
rules_from_default.format_type,
rules_from_default.col_description,
rules_from_default.masking_function,
rules_from_default.masking_value,
rules_from_default.priority
FROM rules_from_default
UNION
SELECT rules_from_seclabels.attrelid,
rules_from_seclabels.attnum,
rules_from_seclabels.relnamespace,
rules_from_seclabels.relname,
rules_from_seclabels.attname,
rules_from_seclabels.format_type,
rules_from_seclabels.col_description,
rules_from_seclabels.masking_function,
rules_from_seclabels.masking_value,
rules_from_seclabels.priority
FROM rules_from_seclabels
)
SELECT DISTINCT ON (rules_from_all.attrelid, rules_from_all.attnum) rules_from_all.attrelid,
rules_from_all.attnum,
rules_from_all.relnamespace,
rules_from_all.relname,
rules_from_all.attname,
rules_from_all.format_type,
rules_from_all.col_description,
rules_from_all.masking_function,
rules_from_all.masking_value,
rules_from_all.priority,
COALESCE(rules_from_all.masking_function, rules_from_all.masking_value) AS masking_filter,
( SELECT count(sl.label) > 0 AND bool_and(sl.label = 'TRUSTED'::text)
FROM pg_seclabel sl,
anon.get_function_schema(rules_from_all.masking_function) f(schema)
WHERE f.schema <> ''::text AND sl.objoid = f.schema::regnamespace::oid) AS trusted_schema
FROM rules_from_all
ORDER BY rules_from_all.attrelid, rules_from_all.attnum, rules_from_all.priority DESC;
anon.pg_masks
DDL
View "anon.pg_masks"
Column | Type | Collation | Nullable | Default | Storage | Description
------------------+--------------+-----------+----------+---------+----------+-------------
attrelid | oid | | | | plain |
attnum | integer | | | | plain |
relnamespace | regnamespace | | | | plain |
relname | name | | | | plain |
attname | name | | | | plain |
format_type | text | | | | extended |
col_description | text | C | | | extended |
masking_function | text | C | | | extended |
masking_value | text | C | | | extended |
priority | integer | | | | plain |
masking_filter | text | C | | | extended |
trusted_schema | boolean | | | | plain |
View definition:
SELECT pg_masking_rules.attrelid,
pg_masking_rules.attnum,
pg_masking_rules.relnamespace,
pg_masking_rules.relname,
pg_masking_rules.attname,
pg_masking_rules.format_type,
pg_masking_rules.col_description,
pg_masking_rules.masking_function,
pg_masking_rules.masking_value,
pg_masking_rules.priority,
pg_masking_rules.masking_filter,
pg_masking_rules.trusted_schema
FROM anon.pg_masking_rules;