impexp
Import Export
Principle
Rules can be imported and exported in jsonb format via the functions :
anon.export_current_database_rules(provider text DEFAULT 'anon')anon.export_roles_rules(provider text DEFAULT 'anon')anon.import_database_rules(database_rules jsonb, provider text DEFAULT 'anon')anon.import_roles_rules(role_rules jsonb, provider text DEFAULT 'anon'
Since roles are instance wide objects they must be managed separately.
Compatibility of the import/export JSON schema between releases
The JSON schema used for imports / exports can change between versions and may not be backward compatible. All the changes to the format are detailed in the CHANGELOG.
It is recommended to re-export the rules after an upgrade.
The migration path for export files is:
- Import the rules in anon version N;
- Upgrade anon from version N to N+1;
- Export the rules using the new schema.
Export
We will create the following objects in the database anon to present the feature.
CREATE EXTENSION IF NOT EXISTS anon CASCADE;
ALTER DATABASE anon SET anon.masking_policies = 'impexp';
ALTER DATABASE anon SET session_preload_libraries=anon;
\c - -- reconnect
CREATE ROLE anna LOGIN;
CREATE SCHEMA test;
CREATE TABLE test.t1(c int, t text);
CREATE TABLE test.t2(c int, t text);
CREATE TABLE test.t3(c int, t text);
CREATE VIEW test.v AS SELECT * FROM test.t3;
SECURITY LABEL FOR impexp ON ROLE anna
IS 'MASKED';
SECURITY LABEL FOR impexp ON SCHEMA test
IS 'TRUSTED';
SECURITY LABEL FOR impexp ON TABLE test.t1
IS 'MASKED WHEN c > 10';
SECURITY LABEL FOR impexp ON COLUMN test.t1.t
IS 'MASKED WITH FUNCTION pg_catalog.md5(t)';
SECURITY LABEL FOR impexp ON COLUMN test.t2.t
IS 'MASKED WITH VALUE NULL';
SECURITY LABEL FOR impexp ON VIEW test.v
IS 'MASKED WHEN c > 10';
SECURITY LABEL FOR impexp ON COLUMN test.v.t
IS 'MASKED WITH FUNCTION pg_catalog.md5(t)';
SECURITY LABEL FOR impexp ON FUNCTION pg_catalog.md5(text)
IS 'TRUSTED';Exporting all role rules for the impexp masking policy (details about the
format are described in the Import section of this page):
SELECT jsonb_pretty(x) FROM anon.export_roles_rules('impexp') AS F(x); jsonb_pretty
-----------------------------
{ +
"anna": "MASKED"+
}
(1 row)Export the current database’s roles for the impexp provider:
SELECT jsonb_pretty(x) FROM anon.export_current_database_rules('impexp') AS F(x); jsonb_pretty
-----------------------------------------------------------------------
{ +
"schemas": { +
"test": { +
"mask": "TRUSTED", +
"views": { +
"v": { +
"mask": "MASKED WHEN c > 10", +
"columns": { +
"t": "MASKED WITH FUNCTION pg_catalog.md5(t)"+
} +
} +
}, +
"tables": { +
"t1": { +
"mask": "MASKED WHEN c > 10", +
"columns": { +
"t": "MASKED WITH FUNCTION pg_catalog.md5(t)"+
} +
}, +
"t2": { +
"columns": { +
"t": "MASKED WITH VALUE NULL" +
} +
} +
} +
}, +
"pg_catalog": { +
"functions": { +
"md5": [ +
{ +
"mask": "TRUSTED", +
"parameters": [ +
"text" +
] +
} +
], +
} +
} +
} +
}
(1 row)If no masking policy is provided, anon is the default. In this case all the
masking policies will be exported, including those added by the extension
(functions in the pg_catalog or anon schema).
Import
The import function take a json string and creates the security labels. All objects must exist beforehand with the correct type.
A masking rule is a string.
The role rules are imported with anon.import_roles_rules(rule, provider).
where provider is the masking policy and defaults to anon and rule is a
json string composed of a hashmap where:
- each key is a role name;
- each object is a masking rule.
All identifiers (object names and types) must preserve exact case because they are quoted during security label import to prevent SQL injection.
SELECT * FROM anon.import_roles_rules(
$$
{
"anna": "MASKED"
}
$$, 'impexp'
);The command outputs the SECURITY LABEL statements that where executed.
import_roles_rules
--------------------
(1 row)For a database the json follows this schema:
mask: an optional key, it contains the masking ruleschemas:mask: an optional key, it contains the masking rulefunctions: an optional key, it contains a hasmap where:- each key is a valid function name, function with duplicate names should include the parameter list;
- each object is a set (a collection that doesn’t allow duplicates):
parameters: an array with all the parameter types in order of apparition;mask: the masking rule;
tables|views|foreign tables: optional keys key, each can contain a hasmap where:- each key is a valid relation name of the specified kind;
- each object contains:
mask: an optional key, it contains the masking rule;columns: a hashmap of columns where:- each key is a valid column name;
- each object is a masking rule.
The security labels are created in the current database and the provided masking
policy (or anon otherwise) with the anon.import_database_rules(rules,
provider) function.
SELECT * FROM anon.import_database_rules(
$$
{
"schemas": {
"test": {
"mask": "TRUSTED",
"tables": {
"t1": {
"mask": "MASKED WHEN c > 10",
"columns": {
"t": "MASKED WITH FUNCTION pg_catalog.md5(t)"
}
},
"t2": {
"columns": {
"t": "MASKED WITH VALUE NULL"
}
}
},
"views": {
"v": {
"mask": "MASKED WHEN c > 10",
"columns": {
"t": "MASKED WITH FUNCTION pg_catalog.md5(t)"
}
}
}
},
"pg_catalog": {
"functions": {
"md5": [{
"parameters": ["text"],
"mask": "TRUSTED"
}]
}
}
}
}
$$, 'impexp'
);The rules are now visible in the pg_masking_rules view:
SELECT *
FROM anon.user_rules
WHERE full_objname LIKE '%t1'; provider | objtype | full_objname | label
----------+---------+--------------+----------------------
impexp | table | test.t1 | MASKED WHEN c > 10
(1 row)