User passwords getting reset after every SnowDDL Run

[bhargavpatil-dignifi]

Recently, we added user types to user.yaml file.

Since then there is an alter statement that is unsetting the passwords.
We are not able to understand from the documentation as to what is causing these password resets.

SnowDDL CLI Command deploying SnowDDL:
snowddl --env-prefix dev --placeholder-values="{\"env\":\"dev\", \"aws_account_id\":\"***\"}" --apply-unsafe apply

Sample type=person user:

bhargavpatil:
  login_name: bhargav.patil
  display_name: "Bhargav Patil"
  first_name: Bhargav
  last_name: Patil
  disabled: False
  email: bhargav.patil@test.com
  type: "PERSON"
  business_roles:
    - engineer
  default_namespace: test
  default_warehouse: test_wh

[littleK0i]

I did some tests. Resolver seems to work fine with type=PERSON.

Password is being unset because it is not listed in parameters. SnowDDL thinks user is not supposed to have a password and unsets it.

Ways to fix this:

1. Specify password in config. You may use fernet encryption to avoid plain-text passwords: https://docs.snowddl.com/guides/other-guides/encrypt-user-passwords
2. Use SSO and auth human users via 3rd party provider. No password is required, and it is much more secure.

[bhargavpatil-dignifi]

we have not provided passwords from the very beginning & earlier passwords were not reset on deployments.
Once user was created by snowddl, we used to set a temp password & force them to change it on first login.

the only change we have deployed recently is:

1. We added these user types for all users as person, service, legacy service due to recent snowflake changes.
2. upgraded from 0.23->0.33

ill explore fernet & how it can be implemented but this is causing issues to prod rn.

what can be done to keep the earlier flow, we let user reset on first login after manual reset & this way only he has the password?

[bhargavpatil-dignifi]

we have CI/CD implemented in our infra & below is the CLI command executed during deployment:
snowddl --env-prefix dev --placeholder-values="{\"env\":\"dev\", \"aws_account_id\":\"***\"}" --apply-unsafe apply

Would the --apply-unsafe force to refresh the user password as per the config file since it broadens the scope to alter & drop commands?
or would it be just --refresh-user-passwords that can replace the password with the once mentioned in the config?

[littleK0i]

Only --refresh-user-passwords will trigger it.

Here is the specific place in code responsible for this: https://github.com/littleK0i/SnowDDL/blob/master/snowddl/resolver/user.py#L207-L237

[littleK0i]

Actually, I would suggest to have a separate Snowflake DEV account and test anything before applying to PROD account.

You may also consider running plan action instead of apply when you have any doubts. With plan everything will be "suggested", but nothing will be applied.

[bhargavpatil-dignifi]

Yes. I'll try implementing that on our dev env.

Secondly, snowddl is creating two users on a single snowflake account. One for dev db & other for prod db.
Going forward lets say we go with SSO/SAML authentication for our account (which will be through one single email), How can we handle this situation to maintain two different users on one snowflake account using one single email through Sso/saml authentication?

[littleK0i]

It depends on SSO provider.

For examle, GSuite (Google) should allow you to create email aliases. E.g. bhargav.patil@test.com and bhargav.patil+dev@test.com will be delivered into the same mail box, but are different email addresses. It might work for SSO as well.

If you're talking about a use case when you have ACCOUNTADMIN user and SSO user, these two can be different users. ACCOUNTADMIN should be created manually, it may have password + MFA, empty login_name. SSO user should be created via SnowDDL, no password, login name equals to SSO provider identifier.