Snowflake
Published in

Snowflake

How to automatically rotate Snowflake user password through AWS Lambda

Everyone hates it — password rotation. That message which you get every XX days saying that your password is too old and needs to be changed. But suddenly it does not look as bad idea at all when you are behind the wall and maintaining data warehouse or data app full of sensitive customer data. Password rotation could be just another security layer if you are not using Federated authentication and SSO.

In today’s blog post we will have a look on how to automate whole process of password rotation outside of Snowflake account by using AWS Lambda. Rotated password is stored in AWS Secrets Manager to make it available (in secure way) to third party tool which is using this programatic user to access Snowflake. We will have a look on how to integrate all parts together, what Snowflake privileges we need and how it works end to end.

Suppose you have a tool which needs to access Snowflake to read the data and you are planning to use user & password authentication. To increase security you want to rotate password on daily basis. I had such use case for our Great Expectations development environment. Every developer has its own Python virtual environment on common AWS EC2 server. One of the requirement was automate whole process us much as possible to make the developers life easy — no one wants to update the password everyday manually. Another requirements were:

  • do not store password locally in any GE configuration files
  • developers should not know the password — GE is using one central programatic user to read the data from Snowflake
Solution Architecture

Main part of the solution is AWS Lambda function. Lambda communicates with Snowflake via Snowflake Python Connector and updates user password in Snowflake account. There is used special user for communication with Snowflake. This user has assigned only USERADMIN role. Lambda also updates the password in AWS Secrets Manager to make it available to third party tools & apps. Last but not least, Lambda function also send a notification to our team Slack channel to let us know that password rotation has been successfully done.

Great Expectations supports integration with AWS Secrets Manager and password could be read directly from that secure storage. It does not need to be stored in any local configuration file. It is retrieved on the fly directly from AWS Secrets Manager. Thanks to that also developers working with Great Expectations don’t need to know the credentials to access Snowflake. It works seamlessly. Basically this can be used for any upstream/downstream application which supports AWS Secrets Manager or any similar cloud service (Azure Key Vault, etc.) .

What if you want to have whole solution manageable by Snowflake and not by Lambda? Would it be possible? I think so! I haven’t tried to implement this but if you want, you can give it a try. It would be also nice exercise for Snowflake External Functions.

I think it could work in the way that you will have a stored procedure in Snowflake which will be scheduled via Tasks. This procedure would do the password rotation and call snowflake external function to update the password in AWS Secrets Manager through linked Lambda function. Or stored procedure would just call external function which would still do all the work.

This is just an easy and quick tip how password rotation in Snowflake could be automated and having solution with regular password renewal. Password is kept in cloud secure storage and available to other services without any need to distribute it every day to all users and systems. Usage of secure password storage also makes possible to integrate third party tools and apps without any need for keeping passwords locally in configuration files.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Tomáš Sobotík

Tomáš Sobotík

281 Followers

Lead data engineer @Tietoevry. Currently obsessed by cloud technologies and solutions in relation to data & analytics. ☁️ ❄️