Skip to content

OnRamp staff lockout recovery ​

Give one OnRamp staff user the two role assignments that restore every permission, restricted permissions included.

Owner: PlatformWhen to use: No OnRamp staff user can open an OnRamp staff tool

Canonical sources. The ONRAMP_ADMIN seat license grants no access by itself. The "All OnRamp Admins" group and the builtin roles are defined in app/api/authz/groups.py and app/api/authz/builtin_access.py. The platform tenant id is platform_tenant_vendor_id in app/config/environments.yaml.

When to use this runbook ​

Use this runbook when no OnRamp staff user can open an OnRamp staff tool, for example after someone deletes the explicit assignments of the "All OnRamp Admins" group. Do not use it while another staff user can still create role assignments, because that user can restore access in the product.

What the SQL does ​

  • It gives the user two explicit assignments at organization scope in the platform tenant: the builtin "All Permissions" role and the builtin "OnRamp Admin" role.
  • "All Permissions" covers every non-restricted permission. "OnRamp Admin" covers the restricted platform.* permissions.
  • It stops with an error, and changes nothing, when the email matches no live user or several live users.
  • It stops with an error, and changes nothing, when the matched user is deactivated, is a customer user, does not hold the ONRAMP_ADMIN seat license, or does not belong to the platform tenant.
  • It skips an assignment that already exists, so it is safe to run twice.

Platform tenant id per environment ​

EnvironmentPlatform tenant vendor id
stage0
prod0
gdpr-prod (eu)0
dev1

The SQL presets the id to 0. Change the id only in dev.

Run it ​

  1. Connect to the environment's database with psql as the admin identity.
  2. Edit the email on the first \set line. Edit the id on the second line only in dev.
  3. Paste the block below into psql.
  4. Check that the first result echoes the intended email and platform tenant id.
sql
\set ON_ERROR_STOP on
\set staff_email 'name@onramp.us'
\set platform_tenant_vendor_id 0

BEGIN;

SELECT set_config('onramp_recovery.email', :'staff_email', true) AS target_email,
       set_config('onramp_recovery.platform_tenant_vendor_id', :'platform_tenant_vendor_id', true) AS platform_tenant_vendor_id;

DO $recovery$
DECLARE
  v_email text := current_setting('onramp_recovery.email');
  v_platform_tenant_vendor_id integer := current_setting('onramp_recovery.platform_tenant_vendor_id')::integer;
  v_match_count integer;
  v_user_uuid uuid;
  v_vendor_id integer;
  v_is_active boolean;
  v_is_customer_user boolean;
  v_seat_role_code text;
  v_role_name text;
  v_role_id integer;
BEGIN
  SELECT count(*) INTO v_match_count
  FROM "user" u
  WHERE lower(u.email_address) = lower(v_email) AND u.deleted_at IS NULL;

  IF v_match_count = 0 THEN
    RAISE EXCEPTION 'No live user has the email %.', v_email;
  ELSIF v_match_count > 1 THEN
    RAISE EXCEPTION 'More than one live user has the email %. Ask an engineer to choose the user.', v_email;
  END IF;

  SELECT u.user_uuid, u.vendor, u.is_active, u.is_customer_user, ur.role_code
  INTO v_user_uuid, v_vendor_id, v_is_active, v_is_customer_user, v_seat_role_code
  FROM "user" u
  JOIN user_role ur ON ur.id = u.user_role
  WHERE lower(u.email_address) = lower(v_email) AND u.deleted_at IS NULL;

  IF v_is_active IS NOT TRUE THEN
    RAISE EXCEPTION 'The user % is deactivated.', v_email;
  END IF;
  IF v_is_customer_user THEN
    RAISE EXCEPTION 'The user % is a customer user.', v_email;
  END IF;
  IF v_seat_role_code IS DISTINCT FROM 'ONRAMP_ADMIN' THEN
    RAISE EXCEPTION 'The seat license of the user % is %, not ONRAMP_ADMIN.', v_email, coalesce(v_seat_role_code, 'empty');
  END IF;
  IF v_vendor_id IS DISTINCT FROM v_platform_tenant_vendor_id THEN
    RAISE EXCEPTION 'The user % belongs to vendor %, and the platform tenant is vendor %.', v_email, v_vendor_id, v_platform_tenant_vendor_id;
  END IF;

  FOREACH v_role_name IN ARRAY ARRAY['All Permissions', 'OnRamp Admin'] LOOP
    SELECT r.id INTO v_role_id
    FROM or_roles r
    WHERE r.name = v_role_name AND r.vendor_id IS NULL AND r.deleted_at IS NULL;

    IF v_role_id IS NULL THEN
      RAISE EXCEPTION 'The builtin role % does not exist. Run flask reconcile-authz first.', v_role_name;
    END IF;

    INSERT INTO or_role_assignments (vendor_id, principal_type, principal_uuid, role_id, implicit, created_at)
    SELECT v_vendor_id, 'USER', v_user_uuid, v_role_id, false, now()
    WHERE NOT EXISTS (
      SELECT 1 FROM or_role_assignments a
      WHERE a.vendor_id = v_vendor_id
        AND a.principal_type = 'USER'
        AND a.principal_uuid = v_user_uuid
        AND a.role_id = v_role_id
        AND a.scope_workspace_uuid IS NULL
        AND a.scope_resource_type IS NULL
        AND a.scope_resource_id IS NULL
        AND NOT a.implicit
        AND a.deleted_at IS NULL
    );
  END LOOP;
END
$recovery$;

COMMIT;

SELECT u.email_address, r.name AS role_name, a.vendor_id, a.implicit, a.created_at
FROM or_role_assignments a
JOIN "user" u ON u.user_uuid = a.principal_uuid
JOIN or_roles r ON r.id = a.role_id
WHERE a.principal_type = 'USER'
  AND lower(u.email_address) = lower(:'staff_email')
  AND u.deleted_at IS NULL
  AND r.vendor_id IS NULL
  AND r.name IN ('All Permissions', 'OnRamp Admin')
  AND a.scope_workspace_uuid IS NULL
  AND a.scope_resource_type IS NULL
  AND a.scope_resource_id IS NULL
  AND NOT a.implicit
  AND a.deleted_at IS NULL
ORDER BY r.name;

Verify ​

The final SELECT returns exactly two rows for the user: one for "All Permissions" and one for "OnRamp Admin". Both rows show implicit as f. Ask the user to sign out, sign in, and open an OnRamp staff tool. The server reads role assignments on every request, so no restart is needed.

Troubleshooting ​

No live user has the email

The email does not match a user, or the user is deleted. Check the spelling and the user's state in the user table.

More than one live user has the email

The email exists in more than one organization. Ask an engineer to choose the user. This runbook cannot choose between them.

The seat license is not ONRAMP_ADMIN

The user holds another seat license. The ONRAMP_ADMIN seat is the only seat license that reaches the restricted platform.* permissions. Change the seat license first.

The builtin role does not exist

The database never ran flask reconcile-authz. Run it, then run this block again.

Internal documentation — gated behind Cloudflare Access.