Automatically Decrypt a column in Postgres on syncing with Database

1

Hi!

Is there a way were I could automatically decrypt encrypted columns to allow a user to run a SQL Question over that column?

As of now we are manually adding the Decryption Keys on every query that will use data inside the encrypted column. This has been great to some extent but, would we would like to know if we could automatically do decryption of the column while the syncing with database is happening

Thanks!

2

Hello
I have the same question. Any update?

Quick question: can you use the decrypted data on already existing dashboards? Or do you need to rebuild the questions at each query?

Thank you

3

How are you encrypting the data and how do you decrypt it?

4

Hello

We encrypt the data when it is written in mySQL table (data is very sensitive).
The idea is to have Metabase decrypt the data and use in to show graphs using the decrypted data.
At this point, I am thinking of writing SQL queries for all my database with the key being in the query itself.
I then need to ask the user to run the query at each connection to refresh the data.

Is there a way to automate that?
Any idea on a better way to proceed?

5

I take it then you are using the AES_ENCRYPT /AES_DECRYPT functions? You could use a model or database view to decrypt the data columns and allow Metabase full access.

But, the only way to hide a question definition (and thus the keys) in the OSS version is to deny access to the underlying table in Metabase permissions, but this also causes the output of questions to become read-only and not filterable. The paid versions of Metabase have more granular permissions.

Before getting too far, I would sit down and document what threat scenarios you are trying to address by encrypting the database data. If you’re going to decrypt the data in a BI tool in a way that doesn’t require the user to provide the key then there’s no point in encrypting the data in the database in the first place. If you’re worried about someone stealing the database files then there are data-at-rest features in InnoDB that are more secure and easier to manage than per-column user-space encryption.

You may be better served setting up a transform process to anonymize the data and then connect Metabase to the anonymized data. Then only the ETL process needs the keys, which is far easier to secure.

6

Thank you for your message: indeed, having the right risk matrix helped a lot! Finally we decided to change our hosting strategy