Capabilities vs Data Scope

The CYPEX permission model is two-dimensional. A request succeeds only when both dimensions allow it:

1. Capabilities — what the user is allowed to do. Defined by PostgreSQL role grants on queries, functions, and tables.
2. Data Scope — which rows the user is allowed to act on. Defined by Organization membership and enforced by Row-Level Security policies that read the request JWT.

The two are deliberately independent. Granting a capability and assigning an organization are separate operations, and neither implies the other.

The admin-panel labels follow this split:

Schema Access is new in v2.0.0 — it is the third control that decides which schemas an organization may use at all. The sidebar entry for organizations is Organizations, under Access Control.

Warning

Read this before designing a tenant model.

The RLS policies shipped in v2.0.0 protect CYPEX’s own catalog — the cypex, cypex_log, and sso_gateway schemas. They isolate applications, queries, entities, workflows, files, reports, notifications, export and import jobs, translations, connectors, SSO records, and the log tables.

They do not apply to your own business tables. CYPEX does not install policies on the schemas you model in. If two organizations must not see each other’s invoices, you write the policy on your invoice table yourself.

CYPEX gives you the plumbing to do that: cypex.current_organization_id() returns the active organization from the JWT, and cypex.current_user_organization_ids() returns every organization the role can reach. A policy on your own table follows the same shape as the ones CYPEX installs on its catalog:

1
2
3
4
5
6
7
8
9

ALTER TABLE sales.t_invoice ADD COLUMN organization_id bigint
    REFERENCES cypex.t_organization (id);

ALTER TABLE sales.t_invoice ENABLE ROW LEVEL SECURITY;

CREATE POLICY invoice_org_access ON sales.t_invoice
    FOR ALL
    TO PUBLIC
    USING (organization_id = cypex.current_organization_id());

Assign the policy to PUBLIC. CYPEX reads through views, and a view is evaluated with the privileges of its owner, so a policy naming a specific role is checked against the view owner rather than the requesting user. See Terminology for the full explanation.

Think of a request as the conjunction of two predicates:

row_returned(request, row) =
      capabilities_allow(request)
    ∧ data_scope_matches(request, row)

A role with broad capabilities but no organization mapping sees no organization-scoped rows. A role with an organization mapping but read-only capabilities sees every row in its organization and can change none of them. Both layers must agree before PostgreSQL returns a row.

CYPEX installs three group roles. All are NOLOGIN: people and the authenticator connection pool log in as other roles that are granted membership in these groups, never as the groups themselves.

organization_admin is granted to other roles. Its members inherit cypex_admin’s write privileges but are confined to their mapped organizations by cypex.is_organization_admin(), which reads the isOrganizationAdmin JWT claim. Organization administrators do not get the global cypex.is_admin() clause; that requires cypex_admin membership without organization_admin.

Info

No role bypasses RLS.

Administrative reach is not a PostgreSQL BYPASSRLS attribute — no CYPEX role carries one. It is written into the policies themselves. Every catalog policy has the form:

1 2 3 4 5 6 7

USING ( organization_id IS NULL OR cypex.is_admin() OR organization_id = cypex.current_organization_id() OR (cypex.is_organization_admin() AND organization_id = ANY(cypex.current_user_organization_ids())) )

Administrative access is therefore evaluated by the PostgreSQL engine on every query, like any other access, and is visible in pg_policy for anyone auditing the deployment.

Note the first clause above. Rows where organization_id IS NULL are visible to every role, regardless of organization mapping. This is deliberate: it keeps system-wide catalog rows and any data not yet assigned to an organization readable after an upgrade, rather than making the installation appear empty.

The consequence is that a NULL organization is not a private organization. If a row must be confined to one tenant, it needs an organization_id.

Nothing in the database enforces that. There is no trigger stamping organization_id onto new rows — the helper cypex.validate_root_table_organization() exists in the schema but is not bound to any table. Rows written through CYPEX are assigned by the application layer; rows inserted directly from psql, a migration, or an ETL job keep whatever organization_id you give them, and a NULL will be visible to every organization.

v2.0.0 narrowed cypex_user substantially, but it is not read-only:

The audit trail is the case worth understanding. cypex.t_history is revoked from cypex_user entirely, so an ordinary user can neither read nor alter it. Their changes are still recorded, because cypex.changelog_func() is SECURITY DEFINER and runs with the privileges of its owner. Users generate audit evidence they cannot see or tamper with.

The scenarios below assume two organizations, Org A and Org B, and describe access to CYPEX catalog objects — applications, reports, queries, workflows. Access to your own business tables depends on the policies you write.

Warning

If a user appears to have lost access after the upgrade, check cypex.t_role_organization before you check their role. Organization mapping is attached to the role, not to the user record, and a role with no mapping resolves to an empty data scope no matter how many grants it holds.

Most catalog policies are declared FOR ALL with a USING clause and no explicit WITH CHECK. PostgreSQL then applies the USING expression to new and updated rows as well, so a role cannot write a row it would not be permitted to read. cypex.t_role_organization is the exception: it carries an explicit WITH CHECK so that organization administrators cannot grant themselves reach into an organization they are not mapped to.

Creating a role through the API sets a roleType. The three values are a CYPEX-level classification, not PostgreSQL role names, and they map onto the role layer as follows:

Info

The name roleType means two different things in the API.

On role creation and organization-mapping requests it is the classification above. On the role list endpoint it is a filter with the values system and user-defined, which separates CYPEX’s own roles from ones you created and says nothing about administrative reach. The two are unrelated.

Info

Business rule on organization mappings.

• organization_member and organization_administrator roles must be created with at least one entry in organization mappings. The request is rejected otherwise.
• system_administrator roles must have no entries. They are global rather than tenant-scoped, and a request that supplies a mapping is rejected with “System Administrator roles cannot have organization mappings”.

This rule is enforced in CYPEX, not by a database constraint. pg_policy will not stop you from inserting a mapping row directly into cypex.t_role_organization, so make role changes through the admin panel.

• Glossary — one-line Capability and Data Scope definitions.
• What is an Organization? — the tenancy contract and the JWT-to-RLS plumbing.
• Roles and permissions — the full role table and verification queries.
• User management — how the CYPEX user concept meets the capability layer.
• Administration panel — where capability and data-scope changes are made.