I'm looking for a good database to meet the following requirement:
- Runs remotely (on Linux), single point of truth for multiple user
- API for multiple languages (Java, C++, Python are a must) on different platforms (Linux, Windows) (Any SQL database fits here)
- Can handle concurrent access (both read & write) from a few dozen users
- The records I store are a mix of highly structured information and free text
- Has to support versioning of records and should help creating nice diffs between versions
- Also has to support actual deletion of data (not just replacing it with a newer version)
- Supports different owners for records with some sort of ACL mechanism
An open source database would be preferred, but is not a hard requirement. I looked at Elasticsearch but role management and access security is not really convienient there. Postgres seems nice too, but I'm unsure how to use it to handle versioned data. So maybe you guys can share some experiences and insights to help me make a decision.
Edit: Let me clarify what I mean with versioning: The basic principle is that of a VCS like git. Each object (a file in git, a data type with some structured fields and an unstructured field in my case) is tracked. If I modify my object and store it into the database, I see two options (which keep the history). I do it like git and just store the diff, or I create a new record with the same ID, but a version counter is incremented. For the first one, it is trivial to query the diff to present it to the user. But the second one is probably more how most databases actually do it, and is easier to implement, both for the database as well for my client application.
Also, additional information about role management: The individual records I store are created by some user. Since multiple users do work with the database, multiple records of the same type can (and will) have different creators. Imagine a use case where only the owner/creator of a record is allowed to modify or delete it. This fine grained control should be available on record level (field level access security is not required).