How do I use Mitto to mask or encrypt sensitive data? For example, salary information.
How do I manage permissions to this data? For example, if I want Architect A to see the unmasked data but Architect B to only see the masked data.
Great questions! I’m sure there are an array of ways to approach this. I may approach this from a dba standpoint. Any permissions needed to restrict access to tables, schemas, databases, etc. should probably be controlled at the database level by setting up roles and privileges (PostgreSQL: Documentation: 9.0: Database Roles and Privileges)
The workflow would essentially be having Mitto admins create roles and privileges for the tables being output to the database. You can and probably should (as it would make it easier) run sql commands to setup the privileges right from a Mitto SQL job. The reason I suggest this is because the Mitto db user is the one creating tables and schemas so the Mitto user will have to be the one to set roles and privileges for pre-existing tables and schemas. Here is an example of a query that I can run in a Mitto sql job that will create a role called readonly and grant read only permissions to that role:
CREATE ROLE readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA _models to readonly;
GRANT CONNECT ON DATABASE analytics TO readonly;
GRANT USAGE ON SCHEMA _models TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA _models TO readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA _models TO readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA _models TO readonly;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA _models TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA _models GRANT SELECT ON TABLES TO readonly;
Further, if you are wanting to encrypt data. PSQL has a module called pgcrypto. I’m less familiar with this but here are the docs: PostgreSQL: Documentation: 14: F.26. pgcrypto
Just be aware of potential performance and maintenance considerations when using some type of encryption.
2 Likes