Re: Encryption of Data Specific to a Tenant in PostgreSQL database | General Idea

Greetings,

* Jagmohan Kaintura (jagmohan@tecorelabs.com) wrote:
> Yup right now data is being accessed in this manner only.
> application access using tenant user only who have specific tenantId in
> that session and can see its own data only. It doesn't know about anyone
> else's data and neither can get/fetch.
>
> So isolation is 100% guaranteed right now.

Note that views aren't actually guaranteed to provide the isolation
you're looking for unless you mark them as being a security barrier,
see: https://www.postgresql.org/docs/current/rules-privileges.html

Alternatively, you could use RLS and CREATE POLICY:

https://www.postgresql.org/docs/current/ddl-rowsecurity.html

> But isolation is not enough from an operations perspective, so I need
> encryption too in some way or another way, whatever postgreSQL supports
> and  encryption key should differ for  a tenant .

You can have PG do encryption by using the pgcrypto extension, perhaps
with some custom GUC and views (which should really also be security
barrier..) to have it be transparent.  As mentioned elsewhere, you're
really better off doing it in the application though, so that the DB
server doesn't ever see the plaintext data.  You should really be
considering what the attack vector you're concerned about is though-
SQL injection?  Insider threat?  Improper media disposal?  Application
server compromise?  DB server compromise?  etc.

Thanks,

Stephen