Snowflake Key-Pair Authentication and Programmatic Access Tokens

1.Setting up key-pair authentication

Key-pair authentication replaces a password with an RSA key pair of at least 2048 bits. The user keeps the private key, and Snowflake stores the public key on the user object. The private key can be encrypted, which is generally the safer option. Encrypt it with AES-256-CBC. Triple DES is not recommended. The passphrase only protects the key file on the user's side and is never sent to Snowflake.

Generate an encrypted PKCS#8 private keybash

openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 aes-256-cbc -inform PEM -out rsa_key.p8

Next, derive the public key with openssl rsa -pubout. Assigning it to a user requires either OWNERSHIP of the user or the MODIFY PROGRAMMATIC AUTHENTICATION METHODS privilege on it, which makes it possible to give a service user's key management to a custom role. Snowflake recommends a named key pair, which supports role restriction and expiration. The older SET RSA_PUBLIC_KEY approach supports neither. In either case, leave out the BEGIN/END delimiters.

Register a named key pair for a usersql

ALTER USER example_user ADD KEY PAIR my_key
  PUBLIC_KEY = 'MIIBIjANBgkqh...';

To check the setup, compare the RSA_PUBLIC_KEY_FP fingerprint from DESC USER with a SHA-256 digest of your local public key. If they match, the key is configured correctly. SHOW USER KEY PAIRS lists a user's named key pairs.

Checkpoint 1 of 4· Check yourself

A custom role must assign public keys to the service user my_service_user without owning it. What should you grant?

Sources1

2.Rotating keys without downtime

Snowflake supports two ways to rotate keys. With named key pairs (recommended): generate a new key pair and run ALTER USER ... ROTATE KEY PAIR my_key PUBLIC_KEY = '...'. The old key keeps working during a grace period. EXPIRE_ROTATED_KEY_PAIR_AFTER_HOURS sets the length of that period, and 0 expires the old key immediately. Once the grace period ends, Snowflake rejects the old key.

With the two user properties: a user can hold up to two public keys, RSA_PUBLIC_KEY and RSA_PUBLIC_KEY_2. Put the new key in whichever slot is empty, switch clients to the new private key, and then remove the old key. Snowflake matches whichever private key a client presents to the corresponding public key, so the two keys can overlap during the changeover.

Load the new public key into the unused slotsql

ALTER USER example_user SET RSA_PUBLIC_KEY_2='JERUEHtcve...';

Checkpoint 2 of 4· Put it in order

Put the RSA_PUBLIC_KEY_2 rotation steps in order

1.Update client code to connect with the new private key
2.Set the new public key on RSA_PUBLIC_KEY_2 (the unused slot)
3.Generate a new private and public key set
4.Run ALTER USER example_user UNSET RSA_PUBLIC_KEY to remove the old key

Sources1

3.Programmatic access tokens: where they work and what they require

A programmatic access token (PAT) can authenticate to the REST APIs, the SQL API, Snowpark Container Services endpoints and SCIM. It can also take the place of a password in drivers, in tools such as Tableau and Power BI, and in the Snowflake CLI. Both PERSON and SERVICE users can have PATs. Because a PAT is a long-lived secret, Snowflake by default only allows PATs for users who are subject to a network policy. How that rule applies depends on the user type:

Default network-policy requirement for PATs by user TYPE

User TYPE	Generate a PAT without a network policy?	Authenticate with a PAT without a network policy?
SERVICE / LEGACY_SERVICE	No	No
SERVICE_AGENT	Yes	Yes
PERSON	Yes	No

You can relax this rule with the NETWORK_POLICY_EVALUATION setting in an authentication policy's PAT_POLICY. ENFORCED_REQUIRED is the default. ENFORCED_NOT_REQUIRED removes the requirement but still enforces any network policy the user has. NOT_ENFORCED removes the requirement and also ignores any network policy the user has during authentication.

Remove the network-policy requirement and stop enforcing it for PATssql

ALTER AUTHENTICATION POLICY my_authentication_policy
  SET PAT_POLICY = (
    NETWORK_POLICY_EVALUATION = NOT_ENFORCED
  );

Authentication policies also limit which methods a user can use. If the policy's AUTHENTICATION_METHODS list does not include PROGRAMMATIC_ACCESS_TOKEN, the user can neither generate nor use PATs.

Checkpoint 3 of 4· Fill the gap

Users under my_auth_policy cannot create PATs. Which value must you add to the list?

ALTER AUTHENTICATION POLICY my_auth_policy
  SET AUTHENTICATION_METHODS = ('OAUTH', 'PASSWORD', ' ? ');

Sources2

4.PAT expiry, role restriction and rotation

By default a PAT expires after 15 days, and the longest expiry you can set is 365 days. An ACCOUNTADMIN can change these limits with DEFAULT_EXPIRY_IN_DAYS and MAX_EXPIRY_IN_DAYS in a PAT_POLICY, then apply the policy to the account or to specific users. Lowering the maximum also affects tokens that already exist: any token whose expiry is later than the new maximum stops authenticating.

Cap PAT lifetime at 100 dayssql

CREATE AUTHENTICATION POLICY my_authentication_policy
  PAT_POLICY=(
    MAX_EXPIRY_IN_DAYS=100
  );

Role restriction: a PAT for a SERVICE, SERVICE_AGENT or LEGACY_SERVICE user must name a role by default, and sessions opened with that token use that role. Setting REQUIRE_ROLE_RESTRICTION_FOR_SERVICE_USERS = FALSE removes the requirement for tokens generated with ALTER USER ... ADD PROGRAMMATIC ACCESS TOKEN. In Snowsight, the role is still required. If you later set it back to TRUE, service-user tokens created without a role stop working. PERSON (and NULL) users don't need a role restriction unless REQUIRE_ROLE_RESTRICTION_FOR_PERSON_USERS = TRUE. DESC USER shows HAS_PAT for users who have tokens.

Rotation: Snowflake's guidance is that long-lived credentials such as PATs need a strong storage and rotation strategy. The sources for this lesson don't document a PAT rotation command, so the rotation procedure isn't covered here. Expiry policy is the control these sources do document.

Checkpoint 4 of 4· Check yourself

Your policy allowed 90-day PATs. You lower MAX_EXPIRY_IN_DAYS to 30. What happens to a PAT issued last week with a 90-day expiry?

Sources2 3

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1.ROTATE KEY PAIR invalidates the previous public key immediately, so every client has to switch at the same moment.Why is that wrong?
The old key keeps working for a grace period, 24 hours by default. EXPIRE_ROTATED_KEY_PAIR_AFTER_HOURS changes the length, and 0 expires it immediately.
Covered in Rotating keys without downtime
2.A PERSON user who is not subject to a network policy cannot generate a PAT at all.Why is that wrong?
A PERSON user can generate the token. Using it to authenticate is what requires a network policy, unless the PAT_POLICY changes that.
Covered in Programmatic access tokens: where they work and what they require

Sources

Every claim above is drawn from one of these pages, quoted as it was written on the date shown.

1.
https://docs.snowflake.com/en/user-guide/key-pair-authOfficial docs
“This authentication method requires, as a minimum, a 2048-bit RSA key pair.”
↩︎ Setting up key-pair authentication
“which supports role restriction, named key management, and key expiration”
↩︎ Setting up key-pair authentication
“The passphrase is only used for protecting the private key and will never be sent to Snowflake.”
↩︎ Setting up key-pair authentication
“To change the grace period, set EXPIRE_ROTATED_KEY_PAIR_AFTER_HOURS (0 to expire the prior key immediately).”
↩︎ Rotating keys without downtime
“Snowflake verifies the correct active public key for authentication based on the private key submitted with your connection information.”
↩︎ Rotating keys without downtime
“By default, the prior key remains valid for 24 hours.”
↩︎ Exam trap 1
“MODIFY PROGRAMMATIC AUTHENTICATION METHODS privilege on the user.”
↩︎ Checkpoint
“By default, the prior key remains valid for 24 hours.”
↩︎ Prediction
“Remove the old public key from the user profile using an ALTER USER command.”
↩︎ Checkpoint
2.
https://docs.snowflake.com/en/user-guide/programmatic-access-tokensOfficial docs
“you can only generate or use a token if the user is subject to a network policy.”
↩︎ Programmatic access tokens: where they work and what they require
“By default, a programmatic access token expires after 15 days.”
↩︎ PAT expiry, role restriction and rotation
“invalidates any programmatic access tokens for service users that were generated without the role restriction”
↩︎ PAT expiry, role restriction and rotation
“but the user must be subject to a network policy to authenticate with this token”
↩︎ Exam trap 2
“attempts to authenticate with those tokens will fail”
↩︎ Checkpoint
3.
https://docs.snowflake.com/en/user-guide/security-authentication-overviewOfficial docs
“Security risks associated with long-lived credentials must be mitigated with other security measures like a robust storage and rotation strategy.”
↩︎ PAT expiry, role restriction and rotation