RLS impact on existing data

This page answers the most common question about the v2.0.0 upgrade:

Info

Does enabling RLS change row visibility for existing single-tenant data?

No. Existing single-tenant data remains visible to all existing users after the upgrade. The migration is designed so that the transition from v1.x to v2.0.0 is transparent for end users at the row-visibility level.

Three pieces work together to preserve visibility:

The v2.0.0 upgrade inserts a row into cypex.t_organization with organization_domain = 'default'. This is the implicit tenant for all v1.x data.

The same upgrade step populates cypex.t_module_organization and cypex.t_role_organization so that every existing module and every existing role is associated with the Default Organization.

Every RLS policy on a table with a direct, nullable organization_id column includes a clause that allows rows where organization_id IS NULL to be visible — this is the explicit “legacy compatibility” branch. Tables that scope visibility through a join instead of an organization_id column (t_organization, t_module, t_role_organization, and the object-model tables) don’t have this clause and don’t need it: their scoping is derived, not nullable.

Combined, these three pieces mean:

• New rows written through CYPEX carry an organization_id because the application sets it — no database trigger stamps it (see Behavior changes).
• Existing rows have organization_id IS NULL and are visible to every user mapped to the Default Organization (which is everyone after the upgrade).

Run these queries before and after the upgrade to confirm that the row counts are identical.

1
2
3
4

SELECT count(*) AS n_ui FROM cypex.t_ui;
SELECT count(*) AS n_file FROM cypex.t_file;
SELECT count(*) AS n_report FROM cypex.t_report;
SELECT count(*) AS n_user FROM cypex.t_user;

Record the numbers.

1
2
3
4

SELECT count(*) AS n_ui FROM cypex.t_ui;
SELECT count(*) AS n_file FROM cypex.t_file;
SELECT count(*) AS n_report FROM cypex.t_report;
SELECT count(*) AS n_user FROM cypex.t_user;

The numbers must match the pre-upgrade counts. If they do not, do not cut traffic — investigate immediately.

After the upgrade, every tenant-scoped table should have a nullable organization_id column with NULL for every pre-existing row:

1
2
3

SELECT count(*) AS n_ui_null_org
FROM cypex.t_ui
WHERE organization_id IS NULL;

For a fresh post-upgrade instance, this count should equal the total row count of t_ui from the pre-upgrade snapshot.

1
2
3
4

SELECT schemaname, tablename, rowsecurity
FROM pg_tables
WHERE schemaname IN ('cypex', 'cypex_log')
ORDER BY schemaname, tablename;

rowsecurity should be t for every tenant-scoped table. If any row shows f, the migration did not apply correctly for that table.

1
2
3
4

SELECT schemaname, tablename, policyname, cmd
FROM pg_policies
WHERE schemaname IN ('cypex', 'cypex_log')
ORDER BY schemaname, tablename, policyname;

You should see one or more policies per tenant-scoped table. Tables with a direct, nullable organization_id column should have a policy that allows organization_id IS NULL; tables that derive their scope through a join (t_organization, t_module, t_role_organization, the object-model tables) legitimately won’t — don’t flag those as a failure.

RLS policies read the JWT’s claims via request.jwt.claims (the GUC PostgREST sets from the caller’s token). That GUC is the only input the policies consult — there is no separate organization session variable. To confirm RLS is actually filtering, simulate the claims PostgREST would set for a request scoped to the Default Organization:

1
2
3
4

-- Organization IDs are bigint, not UUID. Look yours up first:
--   SELECT id FROM cypex.t_organization WHERE organization_domain = 'default';
SET request.jwt.claims = '{"org_id": 1, "organization_ids": [1], "isSuperAdmin": false, "isOrganizationAdmin": false}';
SELECT count(*) FROM cypex.t_ui;

The count should match the pre-upgrade total. Connect as a non-superuser role for this check — PostgreSQL superusers bypass RLS regardless of the claims set.

As your deployment writes new rows through the CYPEX backend:

• Rows written through CYPEX get organization_id set to the active organization by the application. Direct SQL writes must supply it.
• The organization_id IS NULL clause in the policies keeps legacy rows visible to users mapped to the Default Organization.
• Users mapped to a non-default organization see only rows with organization_id matching their organization.

If you want to fully migrate from the Default Organization to per-client organizations, follow the procedure in Detailed organization setup.

If your deployment had custom RLS policies on tenant-scoped tables, the v2.0.0 migration adds policies on top. The combined policy set is the OR of all policies, so visibility becomes “more permissive, not less.” You should still review the policies after the upgrade.

PostgreSQL superusers bypass RLS by default. The migration relies on the backend never connecting as a superuser for application traffic. If you have superuser-issued background jobs or maintenance scripts, they will see every row regardless of organization_id. Use a non-superuser role for application traffic, or ALTER TABLE ... FORCE ROW LEVEL SECURITY to force policies on the table owners.

A non-superuser psql session will see only rows matching the RLS policies. To inspect a non-default organization, set the claims GUC to that organization first:

1

SET LOCAL "request.jwt.claims" = '{"org_id": 2, "organization_ids": [2], "isSuperAdmin": false, "isOrganizationAdmin": false}';

Use SET LOCAL inside a transaction so the claims do not leak into the rest of the session.