Best practice to avoid error in data exposing / support for per user encryption?

#1

Hi,

We are considering to use Metabase to expose analytics to our customers/organizations.
Of course we want to avoid that by accident, company 1 can see data of company 2.
I have troubles finding out what the best way is to mitigate that risk with Metabase.

Has anyone successfully implemented this without just depending on Application logic?

We are still open in which data source to use, but would for example be great if per user encryption possibilities in PostgresQL (https://www.postgresql.org/docs/current/pgcrypto.html) or something similar can be leveraged?

Any help would be greatly appreciated. Thanks!

Regards,
Rick

#2

Hi @rickgoud
It depends on how your data is organized, but it is at row level, then Sandboxing in Enterprise Edition would do what you’re looking for:
https://www.metabase.com/learn/organization/organization/data-sandboxing-row-permissions.html

#3

Hi Flamber,

Thanks! On a row level should not be a problem I think. Is there a way to programmatically assign groups and access for specific tables for different groups? Ideally in configs or so?

And is this the only route you see, other other ways to accomplish the same, like with native database encryption possibilities that would also ensure that database maintainers would not be able to see the data.

Thanks!

Regards,
Rick

#4

@rickgoud Yes, with permissions: https://www.metabase.com/learn/organization/organization/data-permissions.html
You can do anything programmatically via the API as you can in the interface:
https://www.metabase.com/learn/developing-applications/advanced-metabase/metabase-api.html
The best way to learn the API, is to just use Metabase while having your browser developer Network-tab open and looking at the request, and what data is being send/received.

#5

Thanks!

Then for now the final question; is Postgress native row encryption supported? Because is not ideal to be able to rely on application logic… Any suggestions?

#6

@rickgoud I don’t think I understand how the encryption would work. Is there any BI analysis tool that would work with your data, then please reference that functionality.

#7

Hi,

This article explains how native crypto in Postgresl would work: https://marcopeg.com/2019/per-user-encryption-in-postgres, basically coming down to:

-- Insert Encrypted
INSERT INTO users (name, data)
VALUES ('marco', PGP_SYM_ENCRYPT('marco stuff', 'key')::text);

-- Update Encrypted
UPDATE users
SET data = PGP_SYM_ENCRYPT('some other private stuff', 'key')::text
WHERE name = 'marco';

-- Read and decrypt
SELECT
	name,
	PGP_SYM_DECRYPT(data::bytea, 'key') as data
FROM users WHERE name = 'marco';
#8

@rickgoud You can write SQL in Metabase, so you could select such data, but until something like this issue is implemented, then you wouldn’t be able to use many other parts of Metabase:
https://github.com/metabase/metabase/issues/14054 - upvote by clicking :+1: on the first post

It sounds like you are looking for TDE, which should be introduced in Postgres 14 later this year. And that would not require any changes on Metabase.

But I still don’t understand - I don’t see how encryption and multi-tenant (row level access) are related.

TLDR; Probably no.

#9

Thanks, I’ll look into TDE.
With encryption, only with the right key someone can read the data. So if the database in per user row level encrypted with a user or company key, that means that if the user is authenticated by a service that can also issue a copy of the key to the application that serves metabase, that application can then provide the key the the query, ensuring that application layer errors are avoided and database administrators can access the data. Hope that clarifies my goal? :slight_smile:

#10

@rickgoud It sounds like you would benefit from splitting your customers into different database - then Metabase’s “application layer error” would just be the credentials to that customers database.
I don’t know your data or setup, so hard to say what the “perfect” solution would be, but I don’t see a way that Metabase will be able to handle your scenario any time soon (if ever).