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

Hi Stephen,

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

By using Security Barrier we had a huge impact on performance , it was not
considering proper indexes and was doing some filtration with respect to
that User on top of other filtration. So we didn't choose to add a security
barrier with each view we created.

Similar issue we had with ROW level security enabling, tables were always
going for Sequential Scan, when policies were imposed on rows.

From an implementation perspective, I thought I asked questions from bottom
to top.

From a Development perspective we are moving our already multi-tenant
system (achieved at database level with views) to SaaS implementation . In
SaaS we have tried to achieve isolation to bit extend , but now we wanted
to have encryption for multiple tenants .

So as over from all these discussions best would be to achieve encryption
at application level only.

But not sure how we can Limit access of DBA's across tenants. We dont want
DBA's of one customer accessing or viewing data to another customer. Or
Overall DBA's shouldn't be able to access sensitive data from database.

We are mostly looking over Insider Thread... Application Server
Compromise..DB server Compromise.

On Fri, Feb 12, 2021 at 1:29 AM Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> Greetings,
>
> * Jagmohan Kaintura (jagmohan(at)tecorelabs(dot)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
>

--
*Best Regards,*
Jagmohan
Senior Consultant, TecoreLabs.