Enforcing Database Change Policies

Many Harness Customers use Harness to orchestrate changes to their databases. A common need is centralized policy enforcement that restricts what SQL is permitted in database migrations. To enforce policies on this, customers leverage the Harness rego-based policy engine. This approach not only ensures consistency and compliance but also enhances the overall reliability and security of database operations.

The Role of Database Migrations in Modern Development

Database migrations involve the systematic application of changes to a database schema. They are often needed to support new application features. If an application needs to accept a new column of data, it needs to add a column to the database to store the new data. This requires companies to evolve the database structure in tandem with the application. These changes can range from adding new tables and columns to modifying existing ones or even deleting obsolete structures. Given the critical nature of these operations, it is imperative to manage them with precision and care.

The Need for Centrally Defined Policies

In industries with stringent regulatory requirements, such as finance and healthcare, compliance is non-negotiable. Centrally defined policies enable organizations to enforce compliance with industry standards. For instance, policies can be set to prevent the execution of potentially harmful SQL commands, such as `DROP TABLE`, without proper authorization. These policies ensure compliance with data protection standards that are mandatory in many industries.

Even in an unregulated industry, policies around database changes are important. Database changes, if not managed properly, can lead to significant downtime and data loss. Centrally defined policies act as a safeguard, ensuring that only vetted and approved changes are applied. This reduces the likelihood of errors and minimizes the impact of any issues that do arise.

Additional Benefits of Centrally Defined Policies

In many organizations, database administrators (DBAs) and developers operate in silos, leading to communication gaps and inefficiencies. Centrally defined policies foster collaboration by providing a common framework for both teams to work within. Developers can make changes with the confidence that they adhere to DBA-approved standards, while DBAs can focus on more strategic tasks rather than micromanaging every change.

Automation is a cornerstone of DevOps, and centrally defined policies enable automated governance of database changes. By integrating policy enforcement into the CI/CD pipeline, organizations can automatically validate SQL changes against predefined rules, ensuring compliance without manual intervention. This empowers development teams to ship software faster and increases self sufficiency–all while ensuring compliance.

Real-World Policy Example

Here is a rego implementation of one of the most common policies from our customer base – preventing the execution of commands that drop data in production environments

package db_sql


 rules := [
           {
               "types": ["mssql","oracle","postgres","mysql"],
               "environments": ["prod"],
               "regex": [
                   "drop\\s+table",
                   "drop\\s+column",
                   "drop\\s+database",
                   "drop\\s+schema"
               ]
           },{
               "types": ["oracle"],
               "environments": ["prod"],
               "regex": ["drop\\s+catalog"]
           }
   ]


deny[msg] {
   some i, j,k,l
   rule := rules[i];
   db_instance = input.db_instances[j]
   db_instance.db_type = rule.types[_];
   db_instance.tags.env = rule.environments[_];
   regex.match(concat("",[".*",rule.regex[k],".*"]),lower(input.sqlStatements[l]));
   msg:="dropping data is not permitted"
 }

This policy is implemented in rego, and can be enforced by open policy agent. It checks the SQL environment, and is only applied in production. It also has a series of regular expressions it matches the SQL against. These regular expressions detect operations that would drop data from the database. If any of these expressions match the SQL, then the policy rejects the change.

Conclusion

Centrally defined policies for database migrations are a vital component of modern database management. By ensuring consistency, compliance, and security, these policies enable organizations to manage database migrations more effectively and confidently. 

Join Us on This Journey

Harness has been discussing the challenges unique to stateful applications with our customers for several years. Since we started building Database DevOps we’ve worked with over 30 customers to define the ideal solution. These customers have helped design our UIs, provided valuable feedback throughout our prototype and alpha phases, and regularly use Database DevOps to deploy their schema migrations. 

As we enter public beta, we invite you to join us as a design partner to shape the future of Database DevOps. Your insights and feedback are invaluable as we refine and expand the capabilities of this module. Together, we can make database changes as seamless and reliable as application code deployments. To join the beta, sign up here for a demo, and we’ll get you started. To learn more, view our product page, check out the documentation on the developer hub, watch brand new demo videos on our YouTube playlist, or come to our session at KubeCon this November.