Encrypting user provided Postgre creds & Encrypting dynamically created SQL tables

Questions / Help
, ,
1

Hello everyone,

I’m creating an app which, has a part very similar to Airtable or Notion’s databases.

The app allows users to create SQL tables through a GUI. These tables are either stored in our DB or in the user’s DB if they provide the connection details.

When the user creates a table in our DB, I first create a user and schema in Postgre and I create the table in the schema. I want users to be able to directly connect to the DB to fetch their tables (this could allow them to use their tables in Power BI, for example), and with schemas I can grant them access to their tables and block them from accessing others.

I have two problems:

  1. Right now I’m storing the connection details of the user’s DB in plain text and I’d like to know your opinion on what’s the best way to encrypt the conn details.

As a note, the DB connection details belong to a group of users, so all users in the group must be able to decrypt the connection details. (So a group has many users and has many connection details)

  1. Ideally I’d like to encrypt our DB in a way that only the users can access their data, essentially blocking ourselves from accessing the user’s data. I perform ordering, filtering, etc on these tables so the encryption should still allow these operations.

I’m sure there’s a way to do #1 but not so sure about #2.

Everything is done with Ecto.

Any ideas on how to approach it?

Thank you in advanced!

2

Take a look at Cloak.Ecto — cloak_ecto v1.3.0?

3 Likes
3

Hey @dimitarvp, thanks for the quick reply. Yes, Cloak.Ecto looks like the perfect solution for my first problem. It does’t support User specific encryption keys out of the box, but it says in the docs that it can be implemented :+1:

Any ideas on how I could encrypt the table? With Cloak I think I can’t do it, because for filtering they recommend adding a mirrored hash field, but that’s only good for direct matching, if I want to do something like where string contains x or ordering by a date for example, I won’t be able to do it.