I am developing a software that handles privacy sensitive data in a local network environment, using Mac computers, to which only a select group of people may have access.
I need to understand how I can provide secure and personalized access to the (same) data by each authorized person.
The data is stored in a SQL database (currently Sqlite but eventually probably Postgresql) and shall be encrypted in the database file. The DB engine shall be able to decrypt the data so that it can search it effortlessly, i.e. I do not want to encrypt the data on the client end already.
With that, I believe that when opening the database, a decryption key needs to be provided since the database cannot manage that on its own. Which means that the computers accessing the server have to know the key. From what I've learned so far, this is done like this:
Each authorized user has a password - that password is used to create a hash code. The hash code is then used to encrypt and decrypt the secret data, in this case the key for the database. This means that for each user account I create, I have to encrypt the same DB key with the user's password.
Furthermore, to prevent people from giving away their passwords too easily, a physical key shall be used to authenticate each user. Some keycard system, probably.
I do not know any keycard systems nor have I ever looked into any authentication systems (e.g, I suspect Kerberos is something related to that, but I have never used it, so there's my level of "expertise" in this area).
My questions are:
How does authentication work with a keycard? What is the output I get from the card reader so that I can then authenticate a user with it? I do need to end up with a hash code to decrypt that user's encrypted data in the end, after all.
What kind of keycards are there for Macs that I can use for custom user authentication in my scenario? I guess I need something that comes with a set of programmable cards? At worst, I imagine I can use any USB based card system as long as its USB level API is publically documented (I've managed to write USB drivers for the Mac in the past). But I don't even know what to look for. What are the systems called I should look for? I've ran into RSA for instance, but I can't even tell if that's suitable for my purpose from their marketing-focused website.
I just learned about RFID tags - they're practically cards with a fixed UUID on them. That's not going to work as they can be easily scanned and replicated, I think. So I rather need something that's actually running a little CPU inside, to perform some secret challenge-response process. As someone pointed out, that'd be "smart cards", I assume now.
Update 1
I had some additional thoughts on using RFID cards. While they can be copied easily, I could combine them with having the user enter a personal password, then use the RFID's unique ID with the password to create a hash code that I then use to decode the encryption key for the database. An attacker would therefore need two things - the card and the user's password, which is good enough a proctection in my case. Does that technique make sense?