OnRamp staff lockout recovery
Give one OnRamp staff user the two role assignments that restore every permission, restricted permissions included.
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_ADMINseat 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
| Environment | Platform tenant vendor id |
|---|---|
| stage | 0 |
| prod | 0 |
| gdpr-prod (eu) | 0 |
| dev | 1 |
The SQL presets the id to 0. Change the id only in dev.
Run it
- Connect to the environment's database with
psqlas the admin identity. - Edit the email on the first
\setline. Edit the id on the second line only in dev. - Paste the block below into
psql. - Check that the first result echoes the intended email and platform tenant id.
\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
The email does not match a user, or the user is deleted. Check the spelling and the user's state in the user table.
The email exists in more than one organization. Ask an engineer to choose the user. This runbook cannot choose between them.
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 database never ran flask reconcile-authz. Run it, then run this block again.