Data-at-rest
There may be a terminology problem here. Data-at-rest encryption usually means
- Storage-encryption
- Not peer-to-peer nor any other form of data-at-use encryption.
On the forms of encryption suggested, I would advise staying away from those RDBMS-specific solutions as they're less tested than the other options which PostgreSQL suggests
Storage encryption can be performed at the file system level or the block level. Linux file system encryption options include eCryptfs and EncFS, while FreeBSD uses PEFS. Block level or full disk encryption options include dm-crypt + LUKS on Linux and GEOM modules geli and gbde on FreeBSD. Many other operating systems support this functionality, including Windows.
This mechanism prevents unencrypted data from being read from the drives if the drives or the entire computer is stolen. This does not protect against attacks while the file system is mounted, because when mounted, the operating system provides an unencrypted view of the data. However, to mount the file system, you need some way for the encryption key to be passed to the operating system, and sometimes the key is stored somewhere on the host that mounts the disk.
Essentially, different operating systems and file systems abstraction layers provide a better better-tested method of handling data-at-rest encryption.
Yes, that means you have a key. Yes, that means if the key is compromised the data can be read. But if your database is compromised and not the key, the data is secure. And, that's why it's data-at-rest.
So you normally store the key owned by root. Have root mount the secured location, and let the postgres
user access that. Obviously PostgreSQL needs access to the data and has to know how to decrypt it.
Now, if other users are on the machine they can't access the data unless they're they postgres
user. Moreover, they can't access the key. And if they do manage to compromise the data or even steal the physical encrypted back up they can't access it without the key.
You can restrict user per database. I will explain here using sql authentication and you can do the same if you have AD set up.
I have 3 database and 'taiobmdjamshed' is the server admin and have access to all the databases.
I create security1 login with db_owner privilege to security1 database and a user at master database.
CREATE LOGIN security1
WITH PASSWORD = 'S<<MM12345!@@!'
GO
Create a user in master database.
CREATE USER security1
FOR LOGIN security1
WITH DEFAULT_SCHEMA = dbo
GO
Make a user in security1 database and make it a member of db_owner role.
CREATE USER security1
FOR LOGIN security1
WITH DEFAULT_SCHEMA = dbo
GO
-- Add user to the database owner role
EXEC sp_addrolemember N'db_owner', N'security1'
GO
If connecting to the server using security1 login and try to access security2 database this is what will happen because security1 login does not have a corresponding user or privilege in security2 database.
This link explains more in details.
Best Answer
You don't add an additional provider, the provider is simply a reference to a DLL on the SQL Server that provides the interface to your EKM provider.
You add a credential to interact with the key vault (using the FOR CRYPTOGRAPHIC PROVIDER option) and associate that credential with a login. If you have additional key vaults, you add additional credentials and additional logins.
(Assuming you mean TDE) Yes. You create a database encryption key, scoped to the DB in question, protected by a certificate or server asymmetric key per database. You already get a different encryption key per DB and you can even protect the DEK using a different cert or asymmetric key per database.
Scenario: I want to protect 2 databases with TDE using individual Asymmetric Keys provided by different Azure Key Vaults.
Steps: