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
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@xxxxxxxxxxx> wrote:
Greetings,
* Jagmohan Kaintura (jagmohan@xxxxxxxxxxxxxx) 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.
