---
name: add-remove-permifrost-user
description: Add or remove human users in permifrost.yml for Snowflake access control. Use when onboarding new team members, offboarding users, changing role assignments, or modifying database access in Permifrost.
---

# Permifrost Add/Remove User

Add or remove human users in [permifrost.yml](permifrost.yml) for Snowflake. Covers databases, user role, userrole_role_owner, and user entry. Use when the user asks to add a Snowflake user like ilja, create a new dev user, or remove a user from permifrost.

Add or remove human users in permifrost.yml following the existing pattern (e.g. ilja, elena, brittany). Each human user needs four object layers; removal is the reverse order.

## Quick reference

| Layer | What to add/remove | Section in permifrost.yml |
|-------|--------------------|---------------------------|
| **Databases** | `dev_<name>_raw`, `dev_<name>_analytics` | `databases:` |
| **Role** | `userrole_<name>` | `roles:` (under "User roles") |
| **Role owner** | `userrole_<name>` in list | `userrole_role_owner.member_of` |
| **User** | `<name>` | `users:` (under "Human users") |

Canonical examples: `ilja`, `elena`, `brittany` in permifrost.yml.

---

## Username rules (must be valid unquoted Snowflake identifiers)

**Before adding a user, validate the name.** Usernames in `permifrost.yml` must be valid **unquoted** Snowflake identifiers:

- allowed characters: letters, digits, underscore `_`, and dollar `$`
- must start with a letter or underscore
- **no dots, spaces, hyphens, or other special characters**

**Why:** tundri's inspector runs `DESCRIBE USER {user}` **without quoting** the identifier. Snowflake reads a dot as a namespace separator, so a `first.last` username fails at inspection — and it only surfaces late, during the CI dry run (`tundri run --dry`):

```
File ".../tundri/inspector.py", line 85, in inspect_users
    cursor.execute(f"DESCRIBE USER {user}")
snowflake.connector.errors.ProgrammingError: 002043 (02000): SQL compilation error:
Object does not exist, or operation cannot be performed.
```

So use dot-free names (e.g. `t_rex`, not `t.rex`; `bianca_frost`, not `bianca.frost`).

**Detect invalid names in permifrost.yml** — flags any user key that is not a valid unquoted identifier:

```bash
awk '/^users:/{f=1;next} /^[^[:space:]]/{f=0} f' permifrost.yml \
  | grep -oE '^[[:space:]]*-[[:space:]]*[^:]+:' \
  | sed -E 's/^[[:space:]]*-[[:space:]]*//; s/:$//' \
  | grep -vE '^[A-Za-z_][A-Za-z0-9_$]*$' \
  && echo ">>> invalid username(s) above — rename before running tundri" \
  || echo ">>> all usernames valid"
```

**Also check the actual Snowflake account.** Tundri inspects the users that exist in the account (the `name` column), not just what is in the yaml. Run `SHOW USERS;` and inspect the `name` column for any dotted/invalid names, even for users you are not editing.

**Remedy for an existing dotted user.** Rename the Snowflake user (run as a role that owns the user, e.g. `securityadmin`), then use the dot-free name in `permifrost.yml`:

```sql
ALTER USER "FIRST.LAST" RENAME TO FIRST_LAST;
```

`RENAME TO` changes only the user's `name` (the identifier tundri uses); it does **not** change `login_name`. End users keep logging in with their existing (possibly dotted) login, so there is no user-facing disruption.

---

## Adding a user

Use this checklist and apply edits in order.

**Checklist:**

- [ ] 0. Confirm `<name>` is a valid unquoted Snowflake identifier (no dots/special chars — see [Username rules](#username-rules-must-be-valid-unquoted-snowflake-identifiers))
- [ ] 1. Add two databases under `databases:`
- [ ] 2. Add `userrole_<name>` to `userrole_role_owner.member_of`
- [ ] 3. Add role `userrole_<name>` under `roles:` (User roles)
- [ ] 4. Add user `<name>` under `users:` (Human users)

### 1. Databases

Under `databases:` (with other dev databases), add:

```yaml
  - dev_<name>_raw:
      shared: no
  - dev_<name>_analytics:
      shared: no
```

### 2. userrole_role_owner

Under `userrole_role_owner:` → `member_of:`, add one line:

```yaml
        - userrole_<name>
```

Keep alphabetical or consistent with existing list (e.g. after other userrole_ entries).

### 3. Role userrole_<name>

Under `roles:` in the "User roles" section (after the comment *When adding a role for a new user: add the role to member_of of userrole_role_owner*), add:

```yaml
  - userrole_<name>:
      member_of:
        - developer
        - analyst
      owns:
        databases:
          - dev_<name>_raw
          - dev_<name>_analytics
        schemas:
          - dev_<name>_raw.*
          - dev_<name>_analytics.*
        tables:
          - dev_<name>_raw.*.*
          - dev_<name>_analytics.*.*
```

If the user also needs loader/transformer (like elena), add those to `member_of`:

```yaml
      member_of:
        - developer
        - loader
        - analyst
        - transformer
```

### 4. User entry

Under `users:` after "# Human users" and the admin/elena block, add:

```yaml
  - <name>:
      can_login: yes
      member_of:
        - userrole_<name>
      meta:
        default_role: userrole_<name>
        default_warehouse: develop
        password: 1passwordvault
        must_change_password: true
        type: person
```

Optional: if the user will use key-based auth, generate a key pair using the [snowflake-generate-keypair](../../snowflake-generate-keypair/) skill and add the public key:

```yaml
        rsa_public_key: |
          -----BEGIN PUBLIC KEY-----
          MIIBIjANBgkqh...
          -----END PUBLIC KEY-----
```

---

## Removing a user

Remove in this order to keep the file consistent and avoid dangling references.

**Order of removal:**

1. **User** – Remove the `<name>:` block under `users:` (the whole `- <name>: ...` entry including meta and optional rsa_public_key).
2. **Role** – Remove the `userrole_<name>:` block under `roles:` (the whole `- userrole_<name>: ...` including member_of and owns).
3. **userrole_role_owner** – Under `userrole_role_owner.member_of`, remove the line `- userrole_<name>`.
4. **Databases** – Under `databases:`, remove the two entries:
   - `dev_<name>_raw:`
   - `dev_<name>_analytics:`

Do not remove system or other users' databases/roles. Only remove objects that reference `<name>` or `userrole_<name>`.

---

## Examples

### Adding user `jdoe`

- **databases:** add `dev_jdoe_raw` and `dev_jdoe_analytics` with `shared: no`.
- **userrole_role_owner.member_of:** add `- userrole_jdoe`.
- **roles:** add role `userrole_jdoe` with member_of developer/analyst and owns dev_jdoe_raw, dev_jdoe_analytics (schemas/tables as in template).
- **users:** add user `jdoe` with member_of `userrole_jdoe`, default_role/default_warehouse develop, meta as in template.

### Removing user `jdoe`

1. Delete the `- jdoe:` user block from `users:`.
2. Delete the `- userrole_jdoe:` role block from `roles:`.
3. Remove `- userrole_jdoe` from `userrole_role_owner.member_of`.
4. Remove `dev_jdoe_raw` and `dev_jdoe_analytics` from `databases:`.

---

## Validation

- [ ] All usernames are valid unquoted Snowflake identifiers (no dots/special chars — see [Username rules](#username-rules-must-be-valid-unquoted-snowflake-identifiers))
- [ ] New user: all 4 layers added (databases, userrole_role_owner, role, user)
- [ ] Removed user: all 4 layers removed in reverse order
- [ ] No dangling references (role references existing databases, user references existing role)
- [ ] `permifrost.yml` passes dry run: `uv run tundri run --filepath permifrost.yml --dry`

## Applying Changes

After editing `permifrost.yml`, the changes need to be applied to Snowflake:

- **Locally:** Run `uv run tundri run --filepath permifrost.yml --dry` to validate, then `uv run tundri run --filepath permifrost.yml` to apply. See [setup-tundri-local-dev](../setup-tundri-local-dev/).
- **Via CI/CD:** Commit the changes, open a PR (triggers a dry run), and merge to `main` (triggers the production run). See [setup-tundri-cicd](../setup-tundri-cicd/).