Author: VividCortex

How NOT to Monitor Your Database

Do you have experience putting out backend database fires? What were some things you wished you had done differently? Proactive database monitoring is more cost efficient, manageable, and sanity-saving than reactive monitoring. We reviewed some of the most common mistakes – too many log messages, metric “melting pots,” retroactive changes, incomplete visibility, undefined KPIs – and put together an action plan on how to prevent them. From our experience, we’ve listed out the top 5 biggest (and preventable!) database monitoring pitfalls.

Log Levels
There never seem to be enough logging levels to capture the desired granularity and relevance of a log message accurately. Is it INFO, TRACE, or DEBUG? What if it’s DEBUG but it’s for a condition we should WARN about? Is there really a linear hierarchy here? If you’re like most people, you’ve seen at least once an extension of those types of standard logging levels on top of a widely available logging system in an attempt to add even more custom levels. There exists a good argument that there should really only be two types of log messages: those useful for writing and debugging the code, and those useful for operating it. Dave Cheney has a good blog post about this differentiation.
Mixed Status and Configuration Variables
Many systems don’t distinguish between status variables, which signal the system’s state, and configuration variables, which are inputs to the system’s operation. For example, in both MySQL and Redis, the commands to get system status will return mixtures of configuration variables and status metrics. Such a metrics “melting pot” is a very common problem that usually requires custom code or exception lists (blacklist/whitelist) to identify which variables are what. 
Breaking Backwards Compatibility

If you change the meaning or dimensions of a metric, ideally you should leave the old behavior unchanged and introduce a replacement alongside it. Failure to do this causes a lot of work for other systems. For example, in MySQL, the SHOW STATUS command was changed to include connection-specific counters by default, with the old system-wide global counters accessible via a different query syntax. This change was just a bad decision, and it caused an enormous amount of grief. Likewise, the meaning of MySQL’s “Questions” status variable was changed at one point, and the old behavior was available in a new variable called “Queries.” Essentially, they renamed a variable and then introduced a new, different variable with the same name as the old one. This change also caused a lot of confusion. Don’t do this.

Incomplete Visibility
Again, the easiest example of this is in MySQL, which has had a SHOW VARIABLES command for many years. Most, but not all, of the server’s command line options had identically named variables visible in the output of this command. But some were missing entirely, and others were present but under names that didn’t match.
Missing KPIs
The list of crucial metrics for finding and diagnosing performance issues isn’t that large. Metrics such as utilization, latency, queue length, and the like can be incredibly valuable, and can be computed from fundamental metrics, if those are available. For an example, see the Linux /proc/diskstats metrics, which include values that you can analyze with queueing theory, as illustrated on Baron’s personal blog. But you’d be surprised how many systems don’t have any way to inspect these key metrics, because people without much knowledge of good monitoring built the systems. For example, PostgreSQL has a standard performance counter for transactions, but not for statements, so if you want to know how many queries (statements) per second your server is handling, you have to resort to much more complex alternatives. This lack of a basic performance metric (throughput) is quite a serious oversight.
These are just some don’ts for developing and monitoring database applications. Interested in learning some of the do’s? Download the full eBook, Best Practices for Architecting Highly Monitorable Applications.

Monitor Critical Databases Confidently with the Sensitive Data Vault

Building extremely deep monitoring as a SaaS product has a drawback: we capture too much data for some customers’ compliance requirements. As a result, some companies have been unable to deploy us, or have had to redact data before sending it to our cloud platform. To address this, we built the Sensitive Data Vault, a highly secure, completely on-premises storage module for the most critically private data that must never leave the customer’s firewall.
 

What is it?
The VividCortex Sensitive Data Vault is a new component of the overall VividCortex solution that you deploy inside your firewall. It ensures that the data never leaves your servers and never enters the VividCortex cloud environment. It consists of:

a Go service that the VividCortex collector agent communicates with
a customer-maintained MySQL or PostgreSQL database that the Go application uses

Once installed, the application is entirely contained within your firewall. It has no communication with the open Internet:  there’s no backdoor, it’s not accessible outside your firewall, and VividCortex employees have no access to it or the underlying database. You can install, configure, and harden the Vault and the systems that run it, to meet your own compliance requirements.
Why is it important?
Companies who previously couldn’t use VividCortex due to security and compliance requirements now have an on-premises option that may meet their needs. For example, industries that are highly regulated, such as medical and ecommerce, are now able to monitor sensitive databases with confidence that they have full control over their data.

Sensitive data never leaves your firewall

You can purge the data VividCortex collects—the “right to be forgotten”

If you have stronger contractual and/or governmental compliance requirements for managing data—such as PCI, ePHI, PII, etc.—the Sensitive Data Vault lets you store  the sensitive parts of these systems’ performance data in your compliant location

How does it work?
In a typical VividCortex installation, the agent sends sensitive data such as SQL text securely to the VividCortex cloud-based APIs where it is encrypted and stored. When later viewing the UI, that data is fetched from the APIs, decrypted, and displayed.
When using the Sensitive Data Vault, the agents instead send the data to the Vault, running within the local/internal network. The Sensitive Data Vault stores that information and returns a special nonreversible token, which the agent sends to the cloud instead. When later viewing the UI, the user’s web browser uses the token to retrieve the original form of the sensitive data from the Vault, and display it.
As always with VividCortex, the Sensitive Data Vault was built with security in mind. Access to the Vault can only occur within the same firewall, does not communicate any sensitive data to VividCortex’s public APIs, is encrypted with SSL, requires the user to be authenticated to the VividCortex service, and requires special user permissions within VividCortex.
What about GDPR compliance?
The General Data Protection Regulation is a new comprehensive data protection law in the EU that strengthens the protection of personal data in light of rapid technological developments, increased globalization, and more complex international flows of personal data. This law is an important step forward in streamlining data protection requirements across the EU. The Sensitive Data Vault may be an important part of a customer’s overall GDPR compliance controls, because it allows customers to:

Manage their own data securely
Keep sensitive data locally so it is not in scope for Sub-processors
Delete data as needed to comply with GDPR

How do I get it?
For the vast majority of customers, our fully-managed cloud platform is the right solution, with security and controls that are well above what is needed. We would be glad to walk through the architecture and implementation in more detail to help you understand whether the Sensitive Data Vault is a solution for your needs. Contact a VividCortex representative to arrange that.
The Sensitive Data Vault is in early access, meaning that we consider it production-ready and secure, but we are offering it to selected customers who are willing to help us validate the solution and its value, as well as learning more about deployment scenarios, helping us develop comprehensive documentation, and understand the market needs better.

How SendGrid Ships Better Code Faster with VividCortex

VividCortex CEO Baron Schwartz and SendGrid DBA Silvia Botros teamed up to discuss how performance monitoring leads to better, faster code deployment. This 30-minute webinar covers: 

How developers can deploy code faster and more safely.
A close-up view of a health- and monitoring-focused work environment.

How database monitoring fits into a culture of DevOps and lean, agile development. 

“Now, with VividCortex, whenever we have an issue that’s impacting the mail processing throughput, we can very quickly go and answer, “What was running at that time? What was the most expensive query? What was taking up all the load?” Within an hour, we can typically figure out exactly which JIRA WAR to go to.” 

—Silvia Botros, Lead MySQL DBA

Take a look:

 

 

Announcement: p99 Percentile Metrics

I’m pleased to announce that VividCortex now offers 99th percentile metrics to help understand latency outliers in a query workload. These metrics provide visibility beyond the average latency, help identify the worst outliers, and improve focus on the customer experience. They are offered for all of the databases we currently support when using On-Host monitoring.
What You’ll See
Latency percentile metrics are one of our most popular feature requests, so we know this will make a lot of you very happy! We actually started collecting these metrics some time ago; you’ll have p99 latency metrics for the last couple of months if you look back at your historical metrics. These metrics are captured globally for an environment, per-query, per-database, per-user, per-verb, per-caller and per-custom-query-tag.
Why Did We Choose to Implement This? 
It is extremely useful for a lot of reasons. Averages can be misleading. Customers don’t just experience an application’s average behavior; they remember the worst experience they’ve had. P99 metrics show outlying behavior in the long-tail, while averages don’t. You can rank by highest-latency p99 in the Profiler which makes it very easy to focus on the queries with the worst outliers.
They’re most meaningful for high-frequency queries; where other monitoring systems have trouble providing any visibility at all into fast and frequent queries, we can also identify outlier performance. This is a huge blind spot for many people.
It is also a useful feature for proactive monitoring and notification. Since we are generating this value per-query you can set an alert on specific query performance. This could be a much more accurate way of alerting on unusual behaviour as compared to setting a threshold against average latency.
What Exactly Are We Collecting? 
There is a wide variety in what monitoring tools delivers as a “percentile” measurement. The most literal definition is to take a complete population of datasets, discard a certain percentage of them such as the top 1%, and then present the largest remaining value. What VividCortex is returning for p99 is a metric of percentiles. We don’t keep the full dataset from which a percentile can be calculated; our agents calculate the p99 at 1 second intervals with an approximation algorithm and store a time series metric of that value. This is similar to how StatsD generates their upper_99 metrics of percentiles.
When charting the metric over an arbitrary timeframes, the API averages the metrics for display. This is necessary whenever you request data at a time resolution that differs from the stored resolution. If you want to render a chart of a metric over a day at 600px wide, each pixel will represent 144 seconds of data. We also average the data when it is downsampled for long-term storage at a lower resolutions.
In Conclusion
It is interesting that averaging percentiles is improper, but still useful. If you store a p99 metric and then zoom out and view an averaged version over a long time range, it may be quite different from the actual 99th percentile. However the ways in which it is different don’t render it unusable for the desired purpose, i.e. understanding the worst experience most of your users are having with your application. Regardless of their exact values, percentile metrics tend to show outlying behavior and get bigger when outlying behavior gets badder. Super useful!
VividCortex does offer a free trial; the signup page can be found here: https://app.vividcortex.com/sign-up
 
 

See You on the Road at 2018’s Shows and Events!

Calling all developers, DBAs, engineers, SREs, and database aficionados: we’re hitting the road and hope to see you along the way. It’s always a highlight of our year when we get to meet up with our users and friends face-to-face, and we’ll be attending events across the country (and abroad!) for the rest of 2018. If you see our booth, come say hello. 
Image Credit
Below is a list of the places you’ll be able to find us over the next nine months, plus descriptions of how we’ll be involved at each event (and, in some cases, special ticket discount codes).

Event
Start
End
Location

DevOps Days Charlotte
2/22/2018
2/23/2018
Charlotte, NC

Strata Conference
3/5/2018
3/8/2018
San Jose, CA

DevOps Days Baltimore
3/21/2018
3/22/2018
Baltimore, MD

SRECon
3/27/2018
3/29/2018
Santa Clara, CA

PostgresConf US 2018
4/16/2018
4/20/2018
Jersey City, NJ

Percona Live Santa Clara
4/23/2018
4/25/2018
Santa Clara, CA

PHP[tek]
5/31/2018
6/1/2018
Atlanta, GA

Velocity San Jose
6/11/2018
6/14/2018
San Jose, CA

MongoDB World
6/26/2018
6/27/2018
New York, NY

AWS re:Invent
11/26/2018
11/30/2018
Las Vegas, NV

The Lineup
DevOps Days Charlotte — We’re kicking off our 2018 tradeshow schedule by heading to DevOpsDays Charlotte. VividCortex engineer Preetam Jinka will be speaking on February 23rd at 3:00pm at the event. Check out his session here. DevOpsDays Charlotte will bring 200+ development, infrastructure, operations, information security, management and leadership professionals together to discuss the culture, processes and tools to enable better organizations and innovative products.
Strata Conf — Every year thousands of top data scientists, analysts, engineers, and executives converge at Strata Data Conference—the largest gathering of its kind. Baron Schwartz will be presenting, “Why nobody cares about your anomaly detection” during the event.  
DevOps Days Baltimore —  Join us for another DevOpsDays, this time in Baltimore! Baltimore will bring hundreds of development, infrastructure, operations, information security, management and leadership professionals together to discuss the latest tools and process improvements.
SRECon —  SREcon18 Americas is a gathering of engineers who care deeply about engineering resilience, reliability, and performance into complex distributed systems, and the scalability of products, services, and infrastructure within their organizations. Baron Schwartz will be presenting, be sure to check out his session here. 
PostgresConf US — We will start of spring at Postgres Conf US in Jersey City, NJ. Baron Schwartz will be delivering a keynote on April 20th at 1:50pm. Check out the session here.  Stop by our booth to see Matt Culmone and Jon Stumpf for a free hat and live product demo. 
Percona Live Santa Clara — We’re sponsoring Percona Live Santa Clara again this year and once again Baron Schwartz will be headlining!  Percona Live is where our community comes together to build the latest database technologies, and it’s where opensource and commercial worlds meet.  Much more exciting news and details to come…stay tuned! Use the discount code VividCortex20 at registration to save 20% off the ticket price
PHP [TEK] — Considered the premier PHP conference and annual homecoming for the PHP Community. Be sure to catch Baron’s sessions here.
Velocity SJ — We’re heading back to the Bay Area on June 20th for Velocity. It’s where practitioners fearlessly share their stories so that we can learn from their successes, failures, and new ideas. We’ll have some fun giveaways at our booth too, so stop by to grab some swag, see a product demo, or just to say hello!
MongoDB World — Join us in New York — in the heart of midtown— for MongoDB World. This is our second year sponsoring the event, and we’re pumped to be part of the MongoDB crowd and its Giant Ideas.
AWS re:Invent — We’ll wrap up our  2018 tradeshow schedule at AWS re:Invent on November 27 through December 1. More details to come! 

2017 Year in Review at VividCortex

It’s easy to observe (pun intended) in the rear-view mirror. Hindsight bias aside, 2017 was a big year for VividCortex and our customers! We shipped lots of features and made tons of progress on the business. Here’s a brief overview of some of our proudest moments from 2017.
Enhanced PostgreSQL support
We love PostgreSQL, and we released lots of enhancements for it! Here’s some of the stuff we did for our PostgreSQL customers:

Supported the great work the Postgres community did on version 10 and all its new features
Made VividCortex awesome for our customers who use CitusDB
Added support to monitor PgBouncer, which nearly everyone who’s using Postgres in a mission-critical environment is using by default
Added SQL query analysis to provide that extra level of database-specific insight, so you get help really understanding what your SQL does
Added support for collecting and analyzing lots more data. You can now rank, sort, slice-and-dice so many things: Queries, Databases and Verbs by Shared Blocks Hit, Shared Blocks Read, Shared Blocks Dirtied, Shared Blocks Written, Local Blocks Hit, Local Blocks Read, Local Blocks Dirtied, Local Blocks Written, Temp Blocks Read, Temp Blocks Written, Block Read Time and Block Write Time. And that’s not all, you can rank and profile Verbs and Users too!

Released Many MongoDB Improvements
We spent a ton of time expanding our support for our MongoDB customers. A few of the many things we improved:

Index analysis, a key pain point for MongoDB, which relies heavily on indexes for performance
Automatic discovery of the full MongoDB scale-out configuration, including support for discovering and monitoring mongod, mongos, and config servers
Auto-discovery of replication sets and clusters
A Node Group view to visualize the cluster hierarchy
Deep profiling capabilities for missing indexes, locking performance, data size, index size, and looking for blocking and most frequent queries in db.currentOp() — plus a bunch more!
Cursor operations, EXPLAIN, new expert dashboards, and more
In-app help documentation tooltips to help you understand all those MongoDB metrics and what they mean

 
Released Extended Support for Amazon RDS
We added support for enhanced metrics in Amazon RDS, so now you get CloudWatch, host metrics, and tons more detail. This is super helpful when trying to debug black-box behaviors on RDS!
Released Expert Insights
In October we released Expert Insights for PostgreSQL, MongoDB, and MySQL. These features automatically and continually monitor your databases with dozens of rules and checks. They essentially continually test your database’s state and behavior to provide best practice recommendations about configuration, security, and query construction. Now you don’t have to manually review everything, because VividCortex never sleeps!

 
Improved Charts and Dashboards
We released a bunch of updates to the Charts section in 2017. KPI dashboards for MongoDB and PostgreSQL are new chart categories with key performance indicator (KPI) metrics preselected by our brainiacs. They show you the most important performance and health information at a glance. Built by experts, so you don’t have to do all that work yourself:

 
The new “System Resources by Hosts” category of charts plots several hosts on each chart so you can easily spot outliers. In the following screenshot, you can immediately see one of the hosts has much higher CPU utilization than the others:

There’s more. We redesigned Charts and Dashboards for a better experience with hundreds or thousands of hosts: new summarized charts scale to infinity and beyond, and you can make your own custom dashboards. Customize away!
New Funding, SOC-2 Compliance, And More!
In August we closed our $8.5M Series A-1 led by Osage Venture Partners with the participation of Bull City Venture Partners, New Enterprise Associates (NEA), and Battery Ventures. We’re thrilled to have recruited such a great group of investors to help us on our way! We’re using the funding to hire and we’re doubling down on our investments in product improvements.
In December we completed SOC 2 Type I certification. This was a massive effort involving almost everyone in the company. We value our customers and we work hard to keep everyone safe!
The most rewarding and important things we did in 2017 were for our customers. There’s too much to list in detail, but we invite you to read their stories. Here’s a few: Shopify, SendGrid, and DraftKings. If you just want the highlights, here’s a quick video that covers a lot of what our customers say about us.
2017 was a productive year for us. In 2018 we’re looking forward to more of the same: shipping more features for our users, keeping up with all of the database news, and seeing everyone at conferences! Here’s to a great 2018 from all of us at VividCortex. See you there — and if you haven’t experienced VividCortex yet, give it a try!

Explode Charts To Drill In With VividCortex

VividCortex is designed with two key scalability requirements in mind: it must perform well in large environments with many hosts, and the UI must help users understand and inspect those environments easily. The universal time selector, the host filter, and other features let you start with a top-level summary, grasp what’s happening in your entire environment no matter how many hosts, and then drill into hosts, time ranges, queries, and metrics of interest. We’ve enhanced our charting and graphing capabilities to make this same zoom-in-drill-down inspectability easier, too.
When you load a charts dashboard, you’ll see charts that contain one line per metric. For example, if I view my MongoDB dashboard and use the top-navigation filter to quickly limit the view to “checkpoints,” I’ll see the following single chart.

Notice how the subtitle says “Average of 5 hosts.” Each line on this chart is an average of 5 hosts’s metrics. VividCortex makes it simple to explode this chart into individual ones so you can see what’s happening with each host separately:

When you do this, VividCortex goes from one chart with averages of each metric, to one chart per host with metrics broken out separately. It opens up a new browser tab for this so you don’t lose your place. We designed this so you can explode a chart, inspect, and continue working without needing to navigate back and forth.
This workflow enables scalable charting and graphing, using either custom or prebuilt dashboards for MySQL, MongoDB, PostgreSQL and Redis, with simple drill-down. We hope you like it! Submit any questions or ideas for enhancements with the in-app chat, and we’ll keep making things better for you.

Custom Dashboards in VividCortex

VividCortex now offers custom dashboards, which are collections of charts you can curate as you wish. Custom dashboards are shared across your team, so they’re a great way to ensure everyone has access to the metrics that you use to monitor and inspect system status and health. And you can give them meaningful names, which is one of only two hard things in computer science, so your dashboards can be full of win.
In the “Choose Dashboards” navigation menu item, there’s a new option to “Add Custom Dashboard.” Type in a name, and then you’ll have an empty custom dashboard. You can also clone an existing dashboard (either custom or prebuilt) to get started.

When you’re viewing a custom dashboard, it’s easy to add a chart to it. Just start typing and you’ll get autocomplete suggestions based on chart and metric names. There’s turnkey charts for MySQL, PostgreSQL, MongoDB, Redis, Cassandra, operating system metrics, and much more.

You can rename custom dashboards, too, in case naming got the best of you the first time around. “Pumpkin Spice Dashboard” probably wasn’t the best name to pick. Hover over the name and you can edit it.

And we’ve simplified the charts, metrics, and dashboards navigation so you can find it all in one convenient place, under Charts on the lefthand navigation menu. Once you’re there you can toggle between dashboards and metrics at the top.

 

How We Encrypt Data In MySQL With Go

A SaaS product needs to use security measures you might not ordinarily use in an on-premises solution. In particular, it’s important that all sensitive data be secured. Encryption plays an important role in information security. At VividCortex, we encrypt data in-flight and at-rest, so your sensitive data is never exposed.
We use Go and MySQL extensively at VividCortex and thought other Go programmers might be interested to see how we’ve integrated encryption into our services layer (APIs). (And if you’d like to learn more about programming with Go in general, please take a look at our free ebook The Ultimate Guide to Building Database-Driven Apps with Go.)
Image Source
Encryption Techniques
At a high level, you can think of two kinds of data encryption inside of MySQL or any similar data store. I’ll oversimplify for purposes of illustration. You can:

Store the data in MySQL as normal, but encrypt the container that holds MySQL. Usually this means storing MySQL’s data on an encrypted disk volume. The protection? Broadly speaking, if someone gains access to a backup disk, they can’t see your data.
Encrypt the data before sending it to MySQL. In this case the security boundary is pushed out further: even if someone gets access to the server, and can run SQL commands, they can’t see your data.

Each of these has advantages and disadvantages. These include ease of use, programmer overhead, ability to inspect (e.g. recovering from backups), searchability and indexability, and so on. There are a lot of things to consider here. Just a few:

Will data be exposed if backups are unencrypted? (Our backups are encrypted, by the way.)
Are sensitive values possibly in cleartext in query logs?
Will sensitive values be visible in status commands like SHOW FULL PROCESSLIST?

At VividCortex we err on the side of safety and security, rather than favoring convenience. There’s a fairly simple question that does a pretty good job of illustrating our goal: if someone succeeds in a SQL injection attack against our databases, will they see any sensitive data in cleartext? The answer needs to be “no.” This is a higher standard than on-disk encryption. It means that someone has to get access to the keys for the particular data they’re trying to decrypt, in order to see anything other than garbage. And those keys are not present or accessible on the database servers in any form, not even in-memory.
Making It Convenient
Convenience is important. If it’s too hard to do encryption, there’s an increased risk that it won’t be done. Fortunately, Go’s elegant interfaces for the database/sql package make the burden transparent to the programmer!
We learned how to do this from Jason Moiron’s excellent blog post on the Valuer and Scanner interfaces. Please read that if you haven’t yet.
To implement transparent encryption and decryption, we created a custom data type that implements the Valuer and Scanner interfaces. The implementation is straightforward and quite similar to Jason’s example of compressing and decompressing, except that we used encryption libraries instead.
Now our code is incredibly simple to use with encrypted values. All we do is define a variable of our custom type. For example, instead of
var password string
err = rows.Scan(&password)

We simply use
var password EncryptedValue
err = rows.Scan(&password)

It’s similarly simple to insert values encrypted into the database. Magic! This is why I often say that Go’s design, although it seems minimalistic at first, is actually very advanced and powerful.

“Go feels under-engineered because it only solves real problems.” Exactly. http://t.co/18LhLT0ALB #golang — VividCortex (@VividCortex)
September 18, 2014

Nuts And Bolts
The code is small. The exact details of all the code are not all that important for this blog post; much of it is about things that are out of scope here. The gist of it, though, is that we store values as byte arrays:

The first byte is an indicator of the version of our encryption algorithm used, so there’s a clear migration path for changes.
The next four bytes indicate which key we used to encrypt this value, so we have 4 billion possible keys.
The rest is the encrypted payload.

We can even change this in the future. For example, we can switch on the first byte’s value, if we want, to determine whether the key ID is in the next 4 bytes, or if it’s something more, such as the next 8 bytes. So we can easily expand the number of keys we can indicate. We can also, if we ever hit version 255, use that to indicate that the version number continues in the next byte. This is a standard trick used, among other places, by the MySQL wire protocol.
The result is that we have a simple and future-proof way to encrypt values.
Alternative Approaches
In addition to the approaches we’ve mentioned, there are several others. There are commercial projects designed to help ease the encryption and decryption techniques you might otherwise wrap around MySQL and perhaps fumble in some ways. There are encryption functions inside of MySQL—but educate yourself about those before using them. There are others, too, but you should be able to find all you need with a search.
Conclusions
By using Go’s built-in interfaces, we created a solution for transparently encrypting values in our database so that it’s never in the database in cleartext, either on-disk or in-memory. The code is easy for programmers to use, which improves our security posture automatically. All sensitive data gets encrypted in-flight and at-rest, and an attacker would have to have extensive access to our systems (an SQL injection wouldn’t suffice) to be able to decrypt the data.
We highly recommend that you use the standard Go interfaces for the power they give you. And please, ask your SaaS providers, including us, hard questions about security and how it’s implemented. Every service needs to be secure to make the Internet a safer place.
If you liked this and want to learn more about Go, you might also like our webinar about developing database-driven apps with Go and MySQL. Click below to watch a recording.

Post Updated 7/31/2017

How We Encrypt Data In MySQL With Go

A SaaS product needs to use security measures you might not ordinarily use in an on-premises solution. In particular, it’s important that all sensitive data be secured. Encryption plays an important role in information security. At VividCortex, we encrypt data in-flight and at-rest, so your sensitive data is never exposed.
We use Go and MySQL extensively at VividCortex and thought other Go programmers might be interested to see how we’ve integrated encryption into our services layer (APIs). (And if you’d like to learn more about programming with Go in general, please take a look at our free ebook The Ultimate Guide to Building Database-Driven Apps with Go.)
Image Source
Encryption Techniques
At a high level, you can think of two kinds of data encryption inside of MySQL or any similar data store. I’ll oversimplify for purposes of illustration. You can:

Store the data in MySQL as normal, but encrypt the container that holds MySQL. Usually this means storing MySQL’s data on an encrypted disk volume. The protection? Broadly speaking, if someone gains access to a backup disk, they can’t see your data.
Encrypt the data before sending it to MySQL. In this case the security boundary is pushed out further: even if someone gets access to the server, and can run SQL commands, they can’t see your data.

Each of these has advantages and disadvantages. These include ease of use, programmer overhead, ability to inspect (e.g. recovering from backups), searchability and indexability, and so on. There are a lot of things to consider here. Just a few:

Will data be exposed if backups are unencrypted? (Our backups are encrypted, by the way.)
Are sensitive values possibly in cleartext in query logs?
Will sensitive values be visible in status commands like SHOW FULL PROCESSLIST?

At VividCortex we err on the side of safety and security, rather than favoring convenience. There’s a fairly simple question that does a pretty good job of illustrating our goal: if someone succeeds in a SQL injection attack against our databases, will they see any sensitive data in cleartext? The answer needs to be “no.” This is a higher standard than on-disk encryption. It means that someone has to get access to the keys for the particular data they’re trying to decrypt, in order to see anything other than garbage. And those keys are not present or accessible on the database servers in any form, not even in-memory.
Making It Convenient
Convenience is important. If it’s too hard to do encryption, there’s an increased risk that it won’t be done. Fortunately, Go’s elegant interfaces for the database/sql package make the burden transparent to the programmer!
We learned how to do this from Jason Moiron’s excellent blog post on the Valuer and Scanner interfaces. Please read that if you haven’t yet.
To implement transparent encryption and decryption, we created a custom data type that implements the Valuer and Scanner interfaces. The implementation is straightforward and quite similar to Jason’s example of compressing and decompressing, except that we used encryption libraries instead.
Now our code is incredibly simple to use with encrypted values. All we do is define a variable of our custom type. For example, instead of
var password string
err = rows.Scan(&password)

We simply use
var password EncryptedValue
err = rows.Scan(&password)

It’s similarly simple to insert values encrypted into the database. Magic! This is why I often say that Go’s design, although it seems minimalistic at first, is actually very advanced and powerful.

“Go feels under-engineered because it only solves real problems.” Exactly. http://t.co/18LhLT0ALB #golang — VividCortex (@VividCortex)
September 18, 2014

Nuts And Bolts
The code is small. The exact details of all the code are not all that important for this blog post; much of it is about things that are out of scope here. The gist of it, though, is that we store values as byte arrays:

The first byte is an indicator of the version of our encryption algorithm used, so there’s a clear migration path for changes.
The next four bytes indicate which key we used to encrypt this value, so we have 4 billion possible keys.
The rest is the encrypted payload.

We can even change this in the future. For example, we can switch on the first byte’s value, if we want, to determine whether the key ID is in the next 4 bytes, or if it’s something more, such as the next 8 bytes. So we can easily expand the number of keys we can indicate. We can also, if we ever hit version 255, use that to indicate that the version number continues in the next byte. This is a standard trick used, among other places, by the MySQL wire protocol.
The result is that we have a simple and future-proof way to encrypt values.
Alternative Approaches
In addition to the approaches we’ve mentioned, there are several others. There are commercial projects designed to help ease the encryption and decryption techniques you might otherwise wrap around MySQL and perhaps fumble in some ways. There are encryption functions inside of MySQL—but educate yourself about those before using them. There are others, too, but you should be able to find all you need with a search.
Conclusions
By using Go’s built-in interfaces, we created a solution for transparently encrypting values in our database so that it’s never in the database in cleartext, either on-disk or in-memory. The code is easy for programmers to use, which improves our security posture automatically. All sensitive data gets encrypted in-flight and at-rest, and an attacker would have to have extensive access to our systems (an SQL injection wouldn’t suffice) to be able to decrypt the data.
We highly recommend that you use the standard Go interfaces for the power they give you. And please, ask your SaaS providers, including us, hard questions about security and how it’s implemented. Every service needs to be secure to make the Internet a safer place.
If you liked this and want to learn more about Go, you might also like our webinar about developing database-driven apps with Go and MySQL. Click below to watch a recording.

Post Updated 7/31/2017

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569