Database security is a huge and important topic. In this short video I'll give you a flavour of how CrateDB addresses security. We'll look at controlling access to CrateDB and encryption of data in transit and at rest, as well as other authentication, authorisation and audit trail topics.
Let's begin by understanding a little bit about role-based access control. We don't always want everyone to have the same level of access to the data stored in the database. A common way of addressing this is via Role-Based Access Control or RBAC. This is fully supported in CrateDB.
Here I'm creating a role that we'll call taxi_reader. Let's first deny that role access to all tables in the default doc schema. Then grant it query access to the 2 taxi tables named taxis and taxi_rides. Now we have a role. We can assign it to one or more users.
Let's create a user named taxi_user and give it a password. By default, this user has no privileges. Giving the taxi_reader role to our new user grants it the privileges associated with that role. So what can taxi_user access? Let's find out.
I'm now connected to CrateDB as the taxi_user. First, let's try a query against the taxis table. As you'd expect, this user is allowed to execute this query.
What about a query that uses the 311 call data? This results in an error as the taxi_user doesn't have the correct privileges to access that table. Remember that we gave the taxi_user query rights over the taxi tables.
This query attempts to modify the taxis table by adding a new row. Is this user allowed to perform this operation? No, this insert fails as our taxi user lacks the privileges to perform operations that modify data. It's essentially a read only user with access to the two taxi tables.
CrateDB supports data encryption in the following ways. For data in transit, both HTTP and PostgreSQL Wire Protocol endpoints can be encrypted with configuration options for the Postgres endpoint allowing host based authentication and SSL enforcement. Communications between nodes forming a CrateDB cluster can also be encrypted. For data at rest we recommend the use of encrypted disk storage and this is of course the default in our CrateDB managed cloud service.
Now let's revisit authentication and authorisation, beginning with authentication. We've seen how users can be authenticated by username and password. Authentication by client certificate is also possible. Passwords are stored securely and host based authentication can be enabled to provide an additional layer of trust. This also applies to communications between nodes in a CrateDB cluster.
Let's move on to authorisation. As demonstrated earlier, CrateDB uses Role-Based Access Control, or RBAC for fine grained permissions management of all aspects of working with data and administering the database itself. How does CrateDB allow you to keep track of actions performed by your database and its users? Queries performed on a CrateDB cluster are logged in internal tables. These can be exported to log files for future analysis or integration. Monitoring information can be collected and reported using JMX Java Management Extensions. This information can then be forwarded on to other monitoring tools, for example, Prometheus.
CrateDB maintains the following security certifications for its managed cloud platform. The ISO IEC 27001 certification signifies the attainment of the highest standards in operational and information security and SoC two compliance ensures that the client data is stored and processed in a secure manner. This video provided a high level overview of the various ways that CrateDB addresses security. Of course, this is a huge topic and it's something you should plan for and consider carefully when implementing any database product. I recommend you read the relevant sections of the online documentation and the CrateDB Architecture Guide to learn more about different aspects of security and how to take advantage of them. Furthermore, don't forget you can always find us online at https://community.cratedb.com our online forums where we're ready to help answer your questions.