Roles and permissions

This page describes the v2.0.0 role model and how roles, JWTs, and RLS policies work together to enforce multi-tenant isolation.

All three roles are created as NOLOGIN group roles. Humans (and the authenticator pool) log in as other roles that are granted membership in these groups — they never LOGIN as cypex_admin, cypex_user, or organization_admin themselves.

• Created as CREATE ROLE cypex_admin NOLOGIN CREATEROLE.
• Retains INSERT, UPDATE, DELETE on every tenant-scoped table.
• Is admitted by the policies through the cypex.is_admin() helper, which reads the JWT and returns true when isSuperAdmin = true (membership in cypex_admin without also being a member of organization_admin). The role does not carry the PostgreSQL BYPASSRLS attribute — its reach is written into the policy predicates.
• Used by the backend for admin-only endpoints and by the migration scripts.

• Created as CREATE ROLE cypex_user NOLOGIN.
• After v2.0.0, this role is read-only on most tenant-scoped tables.
• Sees rows where organization_id IS NULL (legacy data) or organization_id matches one of the user’s mapped organizations.
• The exceptions listed below retain full CRUD for cypex_user.

• NOLOGIN. Intended to be granted to existing roles that should have organization-admin scope.
• Created as CREATE ROLE organization_admin NOLOGIN;, then granted membership in cypex_admin via GRANT cypex_admin TO organization_admin WITH ADMIN OPTION; — so members inherit cypex_admin’s broad write privileges. Note the WITH ADMIN OPTION: anyone who can run GRANT/REVOKE as a member of organization_admin can also grant cypex_admin membership to other roles directly in SQL. This is a DBA/direct-SQL-access concern, not something reachable through the JWT claims or the application API.
• Membership in organization_admin is what makes CYPEX put isOrganizationAdmin: true in the token; cypex.is_organization_admin() reads that claim rather than inspecting role membership. A direct database session with no claims set gets false.
• Scope is bounded by t_role_organization: a role mapped to two organizations can act as an org admin on those two organizations only.

All other tenant-scoped tables are SELECT-only for cypex_user.

The v2.0.0 backend issues JWTs that carry the following claims in addition to the v1.x claims:

External services that verify CYPEX JWTs must accept these claims. Tokens issued by v1.x do not have them and will be rejected after the upgrade.

1
2
3
4
5
6

SELECT r.rolname, m.rolname AS member_of
FROM pg_roles r
JOIN pg_auth_members am ON am.member = r.oid
JOIN pg_roles m ON m.oid = am.roleid
WHERE m.rolname IN ('cypex_admin', 'cypex_user', 'organization_admin')
ORDER BY r.rolname, m.rolname;

Every existing role should appear at least once as member_of. Roles that should retain write access must appear under cypex_admin.

cypex.t_role_organization stores the role as a name (role_name TEXT), not an OID — join on rolname, not oid:

1
2
3
4

SELECT ro.role_name, o.organization_domain
FROM cypex.t_role_organization ro
JOIN cypex.t_organization o ON o.id = ro.organization_id
ORDER BY ro.role_name, o.organization_domain;

After the upgrade, every role should be mapped to the Default Organization.

1
2
3
4
5

SELECT m.module_name, m.schema_name, o.organization_domain
FROM cypex.t_module_organization mo
JOIN cypex.t_module m ON m.id = mo.module_id
JOIN cypex.t_organization o ON o.id = mo.organization_id
ORDER BY m.module_name, o.organization_domain;

After the upgrade, every module should be mapped to the Default Organization.

1
2
3
4

SELECT created_at, user_id, action, entity_type, entity_name, organization_id
FROM cypex_log.t_permission_audit_log
ORDER BY created_at DESC
LIMIT 50;

There is no allowed column — the table records what changed (action, entity_type, before_state/after_state), not a pass/fail verdict on an access check. Use it to investigate “who changed what, and when” questions; for row-level access denials, inspect the RLS policies and the JWT claims directly instead.

To grant a role organization-admin privileges for a specific organization:

1
2
3
4
5
6
7

GRANT organization_admin TO <role_name>;
INSERT INTO cypex.t_role_organization (role_name, organization_id)
VALUES (
  '<role_name>',
  (SELECT id FROM cypex.t_organization WHERE organization_domain = '<domain>')
)
ON CONFLICT (role_name, organization_id) DO NOTHING;

To revoke:

1
2
3
4
5
6

DELETE FROM cypex.t_role_organization
WHERE role_name = '<role_name>'
  AND organization_id = (
    SELECT id FROM cypex.t_organization WHERE organization_domain = '<domain>'
  );
REVOKE organization_admin FROM <role_name>;