Create RLS policies
Build RLS policies for all organization isolated tables. Ensure the table has RLS enabled.
The definitions should be auto-construct from the definitions within `db/docs`.
The sharding key will dictate the specific isolation conditions. This one below is for tables using `organization_id` as the sharding key.
```sql
ALTER TABLE namespaces ENABLE ROW LEVEL SECURITY;
CREATE POLICY namespaces_isolation_policy ON namespaces
USING (
-- Allow access if no organization ID is set OR if it matches
current_organization_id() IS NULL OR
organization_id = current_organization_id()
)
WITH CHECK (
-- For writes, organization_id must match the current organization
organization_id = current_organization_id()
);
```
The set of policies queries per sharding key will be along these lines. They will need to be extended to use the `current_organization_id()` function, etc. They are a guide only.
```sql
-- For organization_id sharded tables
CREATE POLICY org_isolation ON namespaces
USING (organization_id = current_setting('current_tenant_id')::uuid);
-- For namespace_id sharded tables
CREATE POLICY namespace_uploads_isolation ON namespace_uploads
USING (namespace_id IN (
SELECT id FROM namespaces
WHERE organization_id = current_setting('current_tenant_id')::uuid
));
-- For project_id sharded tables
CREATE POLICY security_scans_isolation ON security_scans
USING (project_id IN (
SELECT id FROM projects
WHERE organization_id = current_setting('current_tenant_id')::uuid
));
```
We need an effective way to manage these policies. There will be hundreds of them. The management needs to be automated.
epic