Enable RLS

This page walks through the SQL primitives for enabling PostgreSQL Row-Level Security on a CYPEX application-schema table and attaching the baseline organization policy.

The v2.0.0 migration already enabled RLS on CYPEX core tables (t_object, t_object_field, t_ui, etc.). This page is about enabling RLS on client application-schema tables during the post-upgrade multi-tenant setup.

Prefer the automated client-add-organization-id SQL template for bulk migrations. Use the primitives below when you need a single-table or large-table (batched) path.

For every client application-schema table:

1
2
3
4
5
6

ALTER TABLE <schema>.<table>
  ADD COLUMN organization_id BIGINT
  REFERENCES cypex.t_organization(id) ON DELETE RESTRICT;

CREATE INDEX idx_<schema>_<table>_organization_id
  ON <schema>.<table> (organization_id);

Notes:

• The column type is BIGINT, matching cypex.t_organization.id — not UUID.

• Leave the column nullable only while you backfill. Set NOT NULL after every row has an organization (see Assign clients to organizations).

• After backfill, set a session-aware default so omitted inserts do not silently land in a hard-coded migration org:

  1 2	ALTER TABLE <schema>.<table> ALTER COLUMN organization_id SET DEFAULT cypex.current_organization_id();

• Prefer (organization_id, <natural_key>) composite indexes for hot list queries; a lone organization_id index is the minimum.

1

ALTER TABLE <schema>.<table> ENABLE ROW LEVEL SECURITY;

By default, RLS does not apply to the table owner. To force policies for the table owner (and any other privileged role), add:

1

ALTER TABLE <schema>.<table> FORCE ROW LEVEL SECURITY;

Most deployments should FORCE so that ad-hoc sessions as the table owner do not bypass the policies.

Session organization comes from JWT claims on the request.jwt.claims GUC, read by cypex.current_organization_id() and cypex.is_admin(). There is no separate organization session variable in the engine.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

CREATE POLICY <schema>_<table>_user_access
ON <schema>.<table>
FOR ALL
USING (
  organization_id = cypex.current_organization_id()
  OR cypex.is_admin()
)
WITH CHECK (
  organization_id = cypex.current_organization_id()
  OR cypex.is_admin()
);

Notes:

• Always define both USING and WITH CHECK. A USING-only policy can allow writes that the same policy would reject on read in edge cases; explicit WITH CHECK is the contract you want for tenant isolation.

• system-admin bypass is cypex.is_admin(), which reads the JWT claim isSuperAdmin — not merely SET ROLE cypex_admin.

• To also admit organization administrators across every org in their organization_ids claim (core catalog style), extend both clauses with:

  1 2 3 4

  OR ( cypex.is_organization_admin() AND organization_id = ANY (cypex.current_user_organization_ids()) )

• Do not keep an organization_id IS NULL visibility escape on client business tables after backfill; NULL rows are visible to every tenant under that pattern.

1
2
3
4

SELECT schemaname, tablename, policyname, cmd, qual, with_check
FROM pg_policies
WHERE schemaname = '<schema>'
  AND tablename = '<table>';

Confirm with_check is not null. A FOR ALL policy appears as one row with cmd = '*'.

1
2
3
4

SELECT relname, relrowsecurity, relforcerowsecurity
FROM pg_class
WHERE relname = '<table>'
  AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = '<schema>');

• relrowsecurity = t — RLS is enabled.
• relforcerowsecurity = t — RLS is enforced even for the table owner.

In a non-superuser psql session:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

-- Regular user scoped to organization 1
SET LOCAL "request.jwt.claims" = '{
  "org_id": "1",
  "organization_ids": [1],
  "role": "cypex_user",
  "isSuperAdmin": false,
  "isOrganizationAdmin": false
}';
SET ROLE cypex_user;
SELECT count(*) FROM <schema>.<table>;
RESET ROLE;
RESET "request.jwt.claims";

-- System admin (sees all orgs via cypex.is_admin())
SET LOCAL "request.jwt.claims" = '{
  "org_id": "0",
  "organization_ids": [],
  "role": "cypex_admin",
  "isSuperAdmin": true,
  "isOrganizationAdmin": false
}';
SET ROLE cypex_admin;
SELECT count(*) FROM <schema>.<table>;
RESET ROLE;
RESET "request.jwt.claims";

The regular-user count must be less than or equal to the admin count when multiple organizations have rows.

Move a row only from a privileged session (system-admin JWT, or a maintenance role with BYPASSRLS). Example as system admin:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

BEGIN;
SET LOCAL "request.jwt.claims" = '{
  "org_id": "0",
  "organization_ids": [],
  "role": "cypex_admin",
  "isSuperAdmin": true,
  "isOrganizationAdmin": false
}';
SET LOCAL ROLE cypex_admin;
UPDATE <schema>.<table>
   SET organization_id = 2
 WHERE id = <row-id>;
COMMIT;

A normal user session cannot change organization_id to another org: the WITH CHECK clause rejects the new row.

If you need to bypass RLS for a one-off maintenance task, use a privileged role (BYPASSRLS) or temporarily disable RLS:

1
2
3

ALTER TABLE <schema>.<table> DISABLE ROW LEVEL SECURITY;
-- ... do the work ...
ALTER TABLE <schema>.<table> ENABLE ROW LEVEL SECURITY;

Re-enabling RLS does not re-evaluate existing policies; they remain in place.

After enabling RLS on the application-schema tables, proceed to Assign clients to organizations if you still need to backfill or bulk-assign organizations, or to Verify and roll back to confirm the result.