(Optional) Encryption of Fields

[ChuckMoe]

Hello together :)

the European General Data Protection Regulation (GDPR) describe multiple ways on how to enforce privacy. One is the encryption of person relatable data in the database, in case that someone gets access to it.

As @frankie567 pointed out below, encrypting the email address, that is used for login, would add complexity to the whole system. I tried to find an answer if it is still necessary but most of the post I found either cover the protection of the password or email exchange.

Nevertheless, I would welcome the option to encrypt User Fields values in the database.

== Old ==
As fas as I can see the email address is not encrypted by default.
It would be possible to encrypt the data with sqlalchemy. I did not read through all of the fief code but as far as I can see, it only uses sqlalchemy code for working with the database (no custom sql). As far as I can tell, this should not change any other logic of fief, right?

Another option could be sqlalchemy_utils, which implements some colum types, including the EncryptType. With this you only need to set it as column type and specify parameters like the secret and it automatically encrypts/decrypts your data.
An example taken straight from the sqlalchemy_utils docs:

import sqlalchemy
from sqlalchemy_utils import EncryptedType
from sqlalchemy_utils.types.encrypted.encrypted_type import AesEngine

username = sqlalchemy.Column(
    EncryptedType(
        sqlalchemy.Unicode,
        secret_key,
        AesEngine,
        'pkcs5'))

[frankie567]

Hi @ChuckMoe 👋

Thank you for the suggestion! Actually, we already encrypt some fields in the database, in particular:

• External databases credentials:

  fief/backend/fief/models/workspace.py

  Lines 103 to 108 in 4505030

  	event.listen(Workspace.database_host, "set", encrypt_database_setting, retval=True)
  	event.listen(Workspace.database_port, "set", encrypt_database_setting, retval=True)
  	event.listen(Workspace.database_username, "set", encrypt_database_setting, retval=True)
  	event.listen(Workspace.database_password, "set", encrypt_database_setting, retval=True)
  	event.listen(Workspace.database_name, "set", encrypt_database_setting, retval=True)
  	event.listen(Workspace.database_ssl_mode, "set", encrypt_database_setting, retval=True)

• OAuth providers client ID and secret:

  fief/backend/fief/models/oauth_provider.py

  Lines 32 to 46 in 4505030

  	@hybrid_property
  	def client_id(self):
  	return decrypt(self._client_id, settings.encryption_key)
  	@client_id.setter # type: ignore
  	def client_id(self, value: str):
  	self._client_id = encrypt(value, settings.encryption_key)
  	@hybrid_property
  	def client_secret(self):
  	return decrypt(self._client_secret, settings.encryption_key)
  	@client_secret.setter # type: ignore
  	def client_secret(self, value: str):
  	self._client_secret = encrypt(value, settings.encryption_key)

• OAuth accounts access and refresh tokens:

  fief/backend/fief/models/oauth_account.py

  Lines 53 to 72 in 4505030

  	@hybrid_property
  	def access_token(self):
  	return decrypt(self._access_token, settings.encryption_key)
  	@access_token.setter # type: ignore
  	def access_token(self, value: str):
  	self._access_token = encrypt(value, settings.encryption_key)
  	@hybrid_property
  	def refresh_token(self) -> Optional[str]:
  	if self._refresh_token is not None:
  	return decrypt(self._refresh_token, settings.encryption_key)
  	return None
  	@refresh_token.setter # type: ignore
  	def refresh_token(self, value: Optional[str]):
  	if value is not None:
  	self._refresh_token = encrypt(value, settings.encryption_key)
  	else:
  	self._refresh_token = None

All of them use the Fernet encryption method:

fief/backend/fief/crypto/encryption.py

Lines 19 to 26 in 4505030

	def encrypt(value: str, key: bytes) -> str:
	f = fernet.Fernet(key)
	return f.encrypt(value.encode("utf-8")).decode("utf-8")
	def decrypt(value: str, key: bytes) -> str:
	f = fernet.Fernet(key)
	return f.decrypt(value.encode("utf-8")).decode("utf-8")

---

As for encrypting e-mails, well, I'm not really sure of the added benefits it would have in terms of security/privacy. The e-mail address is shown in lot of places, in the Admin Dashboard and the API.

Besides, it would make login much more difficult (if not impossible) since we would not be able to query the email in the database. With the Fernet method for example, if you encrypt the same value twice, you don't get the same output.

[ChuckMoe]

Hi @frankie567 ,
thank you for the fast reply again!

You are right, I didn't think of the problem with the email query.

I must apologize for my initial answer, it didn't state the root of my concern. The European privacy regulations state that person relatable data has to be secured in some way. One option is to encrypt the data in the database in case of breaches, where the attackers get a hold of the databases. This means I do not mind when the email is shown in the admin panel or the api, only that if someone is somehow able to access the database directly, all person relatable data is encrypted.

I am going to edit my initial question to make it for clear for everyone who is coming here.

[frankie567]

Thank you for the clarifications! I think indeed encrypting e-mails is not really worth the hassle.

Encrypting user fields values, however might be interesting indeed. We could imagine two different approaches:

• Encrypt all the values by default, but I believe it would have severe performance issues when it comes to retrieve the user data.
• Have an option on User Field to explicitly toggle encryption on that field.

What do you think?

[ChuckMoe]

I also thought about having an option on what field to encrypt. Not all data is person relatable and needs encryption. 👍

[frankie567]

By the way, thank you for making me aware of SQLAlchemy Utils EncryptedType: it allowed me to greatly improve the implementation! Much cleaner now: 2ca7772

[ChuckMoe]

If I get this right, you create one table for all different kinds of field values, right?

fief/backend/fief/models/user_field_value.py

Lines 20 to 41 in 2ca7772

	class UserFieldValue(UUIDModel, CreatedUpdatedAt, WorkspaceBase):
	__tablename__ = "user_field_values"
	__table_args__ = (UniqueConstraint("user_field_id", "user_id"),)
	value_string = Column(Text, nullable=True)
	value_integer = Column(Integer, nullable=True)
	value_boolean = Column(Boolean, nullable=True)
	value_date = Column(Date, nullable=True)
	value_datetime = Column(TIMESTAMPAware(timezone=True), nullable=True)
	value_json = Column(JSON, nullable=True)
	user_id: UUID4 = Column(
	GUID, ForeignKey(User.id, ondelete="CASCADE"), nullable=False
	)
	user: User = relationship("User", back_populates="user_field_values")
	user_field_id: UUID4 = Column(
	GUID, ForeignKey(UserField.id, ondelete="CASCADE"), nullable=False
	)
	user_field: UserField = relationship(
	"UserField", back_populates="user_field_values", lazy="selectin"
	)

Would it not be better to create a base class field_values, then create different classes that inherit from it so that all of the types like string, int, etc. have their own table? This would keep the tables much cleaner and you can add new types relatively easily. One could try it like this
https://github.com/ChuckMoe/fief/commit/87671692d393823fecabb542f71d289cadcce4f0

[frankie567]

That's right! What you propose would work, but I purposely didn't take that path:

• Having several tables is harder to maintain, we need to account for migrations, etc.
• When retrieving the user values, we would have N JOIN queries to make instead of 1; which would have quite a big performance impact.

It's not the most elegant way, but I think it's a good trade-off. The DB gives us the row, and we let Python read the right column.

What I thought about this encrypted value would be to add a dedicated column value_encrypted; and add the necessary Python code in the model to read it and convert it to the right format.

[ChuckMoe]

Makes sense 👍
The first time I saw it, it simply confused me a little why you took this approach, so thanks for the explanation!