incuna / django-pgcrypto-fields

Transparent field level encryption for Django using the pgcrypto postgresql extension.
BSD 2-Clause "Simplified" License
231 stars 51 forks source link

Unique constraints #315

Closed lucifurtun closed 3 years ago

lucifurtun commented 3 years ago

Is it possible to have unique constraints at the database level? For example, Meta.unique_together (on model) doesn't seem to have any effect.

peterfarrell commented 3 years ago

No, it's not possible to index a bytea column in the database. Usually, the value in the index exceeds the the pgsql's maximum length allowed for an index (8192 bytes)

One solution is to create a digest message of the value that you want unique and apply the unique constraint to the digest.

The code below is untested. The library will auto-create digest on the value of another field in the same model using the original argument. In the example below, a digest is created for whatever value that is saved() in the name field. A unique constraint exists on the name_digest so no two digests are allowed. This library creates sha512 digests.

from django.db import models
from pgcrypto import fields

class Product(models.Model):
    name_digest = fields.TextDigestField(original='name')
    name = fields.TextPGPSymmetricKeyField()

    class Meta:
        constraints = [
            models.UniqueConstraint(
                fields=['name_digest', ],
                name='name_digest_unique'
            )
       ]

Ref: https://stackoverflow.com/questions/27997514/postgresql-primary-key-constraint-on-encrypted-data-using-pgcrypto-extension

lucifurtun commented 3 years ago

@peterfarrell , make sense. Thank you very much!

peterfarrell commented 3 years ago

Added documentation about unique indexes:

https://github.com/incuna/django-pgcrypto-fields/commit/3becc414436133a85970fdbd80c3fdeec20a64cf