Django filtering through encrypted fields doesn't work

user_15329233 0
Viewed 569

I am using django-pgcrypto package to work with encripted data. In documentation says that is possible to filter on encrypted fields as you would normal fields via exact, gt, gte, lt, and lte lookups this package for encryption. Model:

class Profiles(BaseModel):
    first_name = EncryptedCharField(max_length=255, null=True)
    last_name = EncryptedCharField(max_length=255, null=True)
    postal_code = models.CharField(max_length=32, null=True)
    skype = EncryptedCharField(max_length=255, null=True)
    email = EncryptedCharField(max_length=255, null=True)
...

Example of query:

email = Profiles.objects.filter(email__exact='example@email.com')

Error:

Traceback (most recent call last):
  File "/home/user/Projects/rab/rentautobus-site/uService3/env/lib/python3.7/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.UndefinedFunction: function dearmor(text) does not exist
LINE 1: ...irmed" FROM "profiles" WHERE convert_from(decrypt(dearmor("p...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/user/Projects/rab/rentautobus-site/uService3/env/lib/python3.7/site-packages/django/core/handlers/exception.py", line 34, in inner
    response = get_response(request)
  File "/home/user/Projects/rab/rentautobus-site/uService3/env/lib/python3.7/site-packages/django/core/handlers/base.py", line 115, in _get_response
    response = self.process_exception_by_middleware(e, request)
  File "/home/user/Projects/rab/rentautobus-site/uService3/env/lib/python3.7/site-packages/django/core/handlers/base.py", line 113, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "/home/user/Projects/rab/rentautobus-site/uService2/traveler/views.py", line 206, in conversations
    print(email)
  File "/home/user/Projects/rab/rentautobus-site/uService3/env/lib/python3.7/site-packages/django/db/models/query.py", line 250, in __repr__
    data = list(self[:REPR_OUTPUT_SIZE + 1])
  File "/home/user/Projects/rab/rentautobus-site/uService3/env/lib/python3.7/site-packages/django/db/models/query.py", line 274, in __iter__
    self._fetch_all()
  File "/home/user/Projects/rab/rentautobus-site/uService3/env/lib/python3.7/site-packages/django/db/models/query.py", line 1242, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/home/user/Projects/rab/rentautobus-site/uService3/env/lib/python3.7/site-packages/django/db/models/query.py", line 55, in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
  File "/home/user/Projects/rab/rentautobus-site/uService3/env/lib/python3.7/site-packages/django/db/models/sql/compiler.py", line 1097, in execute_sql
    cursor.execute(sql, params)
  File "/home/user/Projects/rab/rentautobus-site/uService3/env/lib/python3.7/site-packages/django/db/backends/utils.py", line 99, in execute
    return super().execute(sql, params)
  File "/home/user/Projects/rab/rentautobus-site/uService3/env/lib/python3.7/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/user/Projects/rab/rentautobus-site/uService3/env/lib/python3.7/site-packages/django/db/backends/utils.py", line 76, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/user/Projects/rab/rentautobus-site/uService3/env/lib/python3.7/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/home/user/Projects/rab/rentautobus-site/uService3/env/lib/python3.7/site-packages/django/db/utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/user/Projects/rab/rentautobus-site/uService3/env/lib/python3.7/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: function dearmor(text) does not exist
LINE 1: ...irmed" FROM "profiles" WHERE convert_from(decrypt(dearmor("p...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
python-3.x encryption django
1 Answers

If you read the documentation, it says here:

You must also make sure the pgcrypto extension is installed in your database. Django makes this easy with a CryptoExtension migration.

Your error log shows that you have yet to do this, and Django's documentation covers how to create a PostgreSQL extension in your database using a migration file CryptoExtension migration here.

The operation on your migration file looks like:

from django.contrib.postgres.operations import CryptoExtension

class Migration(migrations.Migration):
    ...

    operations = [
        CryptoExtension(),
        ...
    ]

Note that you have to set up the crypto extension in PostgreSQL before the first CreateModel or AddField operation that involves EncryptedCharField; So run this migration with the CryptoExtension operation before your you introduce EncryptedCharField.

If you rather not do it with a migration file, you can install the pgcrypto extension on your database directly. Assuming you're on PostgreSQL 9.1+, the [answer] here shows you how to install that extension on linux systems that use apt (or apt-get). Copying from Dustin's answer (linked to above),

sudo apt-get install postgresql-contrib

Once installation is completed, run this in your psql shell:

postgres@ztrustee:~$ psql test
psql (9.1.3)
Type "help" for help.
test=# CREATE EXTENSION pgcrypto;
CREATE EXTENSION

Once that is done, run your Django migrations again.

user_75155300
Related