[Timebox] Datasource / permissions per workspace

[Weiko]

Context

We currently create a datasource per workspace. As of today, this has no effect (actually it's even degrading the DB because we never close those datasource connections) but we were planning to use them to isolate the access to each workspace schema and improve security. Turns out since we introduced pg_graphql we now do raw queries and kind of ignore the typeorm datasource scope.

Implementation

As stated above, the current state is bad because we create a lot of datasources hence a lot of pools to the DB which seems to affect the production. We need to find a way to keep that isolation, maybe through RLS? Security is on application level by the simple fact that we set the search_path manually before each query, making sure it's hitting the right schema. However, we should not depend on application level security only.

[charlesBochet]

[magrinj]

After some research, it seems that it can works with ROLE but only for classic sql queries, when using graphql.resolve from pg_graphql roles seems to be ignored.

How to test it:

1. Create a base user, this one should be the one we're connecting with, and shouldn't have access to any workspace schemas.

CREATE USER twenty_base WITH PASSWORD 'twenty_base';
GRANT USAGE ON SCHEMA core TO twenty_base;
GRANT USAGE ON SCHEMA metadata TO twenty_base;
GRANT USAGE ON SCHEMA public TO twenty_base;
GRANT USAGE ON SCHEMA graphql TO twenty_base;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA core TO twenty_base;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA metadata TO twenty_base;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO twenty_base;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA graphql TO twenty_base;

2. When we create a new workspace:
   • We need to create a new ROLE for this workspace, and add him the right privileges:

CREATE ROLE role_workspace_1wgvd1injqtife6y4rvfbu3h5;
GRANT USAGE ON SCHEMA graphql TO role_workspace_1wgvd1injqtife6y4rvfbu3h5;
GRANT USAGE ON SCHEMA workspace_1wgvd1injqtife6y4rvfbu3h5 TO role_workspace_1wgvd1injqtife6y4rvfbu3h5;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA workspace_1wgvd1injqtife6y4rvfbu3h5 TO role_workspace_1wgvd1injqtife6y4rvfbu3h5;

• Also our base role need to be able to set this role:

GRANT role_workspace_1wgvd1injqtife6y4rvfbu3h5 TO twenty_base;

3. To query anything inside a workspace we need to set the role before and reset it after:

SET search_path TO workspace_1wgvd1injqtife6y4rvfbu3h5;
SET ROLE role_workspace_1wgvd1injqtife6y4rvfbu3h5;
SELECT * FROM companies;
RESET ROLE;