In the fast-paced world of DevOps, safely accessing production databases is a crucial competency that balances operational efficiency with stringent security measures. This comprehensive guide explores DevOps database access best practices, ensuring your engineering teams can swiftly address issues without compromising on security or system integrity. We'll cover the importance of giving engineers access, the place of migration tools, analytics, and best practices around maintenance and operational tasks. We'll also look at the role of the Four-Eyes Principle in this post.
Why is it important to give engineers access to production databases?
We already have a post on this topic, Should Engineers Have Production Access? but let's summarize the key points here.
- Engineers need production access to ensure they can swiftly resolve issues, such as system downtime, bug investigations, or unautomated processes, directly impacting system performance and reliability.
- Granting access fosters a sense of ownership and responsibility for the system, aligning with the DevOps philosophy of "You build it, you run it," which encourages engineers to be intimately involved with their products throughout the whole lifecycle.
- However, this access comes with risks, as even the most skilled engineers can make critical mistakes, highlighting the importance of implementing robust security measures and tools that enable safe production access while maintaining system integrity and ensuring sensitive data remains secure.
We will touch on how exactly to implement these security measures in the following sections.
Using Migration Tools for Safe Database Changes
Migration tools are a critical component of the modern DevOps environment, playing a key role in safe production database access. They allow engineers to make changes to the database schema and data without having to manually write SQL. This is important because it allows for a more controlled and repeatable process. It also allows for better collaboration between engineers and the database team.
Different languages and frameworks have different migration tools. For example, Ruby on Rails uses ActiveRecord Migrations, while Django uses Django Migrations. If you use Flask you can use Alembic. These tools allow you to write code that describes the changes you want to make to the database, and then apply those changes to the database. This is a very useful approach for anything that can be planned and tested in advance. E.g. schema changes, adding new columns, migrating data into a new format, etc.
Running migrations on production should be automated. In fact the same workflow should be true for all environments. E.g. if you write a migration it should automatically run on your dev, staging and production environment or any other ones you might have in between. You can e.g. use kubernetes jobs for this purpose, if your application runs on kubernetes.
A huge benefit of migrations is that they are written as code and managed within your normal code base. This means any changes to the database are versioned and can be reviewed and tested just like any other code change. This guarantess that whatever restrictions you have on your code are also true for your DB access via migrations, and heavily reduces the risk of mistakes, or even malicious changes on this path.
Analytical Tasks
Analytical tasks, crucial for data-driven decisions, form another challenge for database security in DevOps. They allow developers and Product Managers as well as business analysts to analyze the products data, but simply giving out the root password is ofcourse not an option.
In a mature setup you will usually find a separate interface for these kind of tasks. This is often a data warehouse or a data lake. This is important because it allows you to separate the analytical workloads from the operational workloads. For smaller startups a simple read replica of the production database might be sufficient. But as soon as you have a significant amount of analytical queries you should consider a separate setup.
The data on this interface can be anonymized or aggregated to protect the privacy of your users. This is important because it allows you to give data access to a wider audience without risking the privacy of your users. This is also important for compliance reasons, as you might have to comply with regulations like GDPR. The fact that this is a separate "read-only" database also makes it a lot safer to give access to a bigger part of your organization.
Maintenance and Operational Tasks
This is the crutch of most setups today and the main reason why developers need to access production databases. If you have a bug in your system, you need to be able to look at the data to understand what went wrong. Same for a performance issue, or if you need to run a report that isn't automated or part of your analytics setup yet. Most developer support tasks need access to the production database systems in one form or another.
So you need to run an ad-hoc query, what now? Most companies we've seen solve this by handing out secrets on a case by case basis to give temporary access. This approach fundamentally goes against the purpose of setting up a devops culture in the first place, which should make Operations and Engineers grow closer together. And It also doesn't solve the problem of the SRE/Devops team being able to do anything they want on the production database. This is a huge security and also a compliance risk.
To solve this issue you need an access control management workflow. Ofcourse as this is the Kviklet blog I highly suggest you try Kviklet out. It's free and Open Source The Four-Eyes Principle is a great way to solve this issue. It means that every action on the production database has to be approved by a second person. This is a great way to prevent mistakes and also malicious actions protecting any sensitive information in your databases. It also alleviates the need for the SRE/Devops team to be on call 24/7, as the developers can now do the work themselves and approve each others requests.
If you have money to drop onto the problem you might also want to consider a tool like Apono, StrongDM or Teleport, which try to solve secure access in general and not just DB access, but also come with a hefty price tag.
Best Practices for Production Database Workflows
- Automate as much as possible: Automation is key to ensuring safe production database access. This includes automating migrations, backups, and monitoring. Automation reduces the risk of human error and ensures that best practices are followed consistently.
- Use the Four-Eyes Principle: As mentioned above, the Four-Eyes Principle is a great way to ensure that all actions on the production database are approved by a second person. This is a great way to prevent mistakes and also malicious actions.
- Reduce access to be time bound: If you have to give out access to the production database, make sure it's time bound. This means that the access is only valid for a certain amount of time. This reduces the risk of someone forgetting to revoke access. So ideally you have a way to give JIT(Just in Time) access to the database in one form or another.
- Don't use shared accounts: This is a basic security principle. Don't use shared accounts. Every person should have their own account and their own access rights. This makes it easier to track who did what and also reduces the risk of someone doing something they shouldn't.
- Keep your development workflows in mind: Whatever we suggest here is only useful if it doesn't get in the way of your developers. If you make it too hard to access the production database, your company will suffer from slowed productivity. This is why we suggest using a tool like Kviklet, which makes it easy to access the production database, but also ensures that best practices are followed.
Conclusion
In conclusion, giving developers access to production databases is a critical part of the job for many devops teams. It's also a topic that can be fraught with risk and giving developers access sounds scary. However, with the right tools and processes in place, it can be done safely and securely. We've covered the importance of giving developers access, the place of migration tools, analytics, and best practices around maintenance and operational tasks. We've also looked at the role of the Four-Eyes Principle in this post. We hope you found this post helpful and that it has given you some ideas for how to improve your own production database workflows.
Ready to enhance your DevOps database access practices? Try Kviklet today for safe production database access and join the community driving the future of database security in DevOps. Star us on GitHub or better yet, give us your feedback and an Issue!