incuna / django-pgcrypto-fields

Transparent field level encryption for Django using the pgcrypto postgresql extension.

django-pgcrypto-fields

django-pgcrypto-fields is a Django extension which relies upon pgcrypto to encrypt and decrypt data for fields.

Requirements

• postgres with pgcrypto
• Supports Django 2.2.x, 3.0.x, 3.1.x and 3.2.x
• Compatible with Python 3 only

Last version of this library that supports Django 1.8.x, 1.9.x, 1.10.x was django-pgcrypto-fields 2.2.0.

Last version of this library that supports Django 2.0.x and 2.1.x was was django-pgcrypto-fields 2.5.2.

Installation

Install package

pip install django-pgcrypto-fields

Django settings

Our library support different crypto keys for multiple databases by defining the keys in your DATABASES settings.

In settings.py:

import os
BASEDIR = os.path.dirname(os.path.dirname(__file__))
PUBLIC_PGP_KEY_PATH = os.path.abspath(os.path.join(BASEDIR, 'public.key'))
PRIVATE_PGP_KEY_PATH = os.path.abspath(os.path.join(BASEDIR, 'private.key'))

# Used by PGPPublicKeyField used by default if not specified by the db
PUBLIC_PGP_KEY = open(PUBLIC_PGP_KEY_PATH).read()
PRIVATE_PGP_KEY = open(PRIVATE_PGP_KEY_PATH).read()

# Used by TextHMACField and PGPSymmetricKeyField if not specified by the db
PGCRYPTO_KEY='ultrasecret'

DIFF_PUBLIC_PGP_KEY_PATH = os.path.abspath(
    os.path.join(BASEDIR, 'tests/keys/public_diff.key')
)
DIFF_PRIVATE_PGP_KEY_PATH = os.path.abspath(
    os.path.join(BASEDIR, 'tests/keys/private_diff.key')
)

# And add 'pgcrypto' to `INSTALLED_APPS` to create the extension for
# pgcrypto (in a migration).
INSTALLED_APPS = (
    'pgcrypto',
    # Other installed apps
)

DATABASES = {
    # This db will use the default keys above
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'pgcryto_fields',
        'USER': 'pgcryto_fields',
        'PASSWORD': 'xxxx',
        'HOST': 'psql.test.com',
        'PORT': 5432,
        'OPTIONS': {
            'sslmode': 'require',
        }
    },
    'diff_keys': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'pgcryto_fields_diff',
        'USER': 'pgcryto_fields_diff',
        'PASSWORD': 'xxxx',
        'HOST': 'psqldiff.test.com',
        'PORT': 5432,
        'OPTIONS': {
            'sslmode': 'require',
        },
        'PGCRYPTO_KEY': 'djangorocks',
        'PUBLIC_PGP_KEY': open(DIFF_PUBLIC_PGP_KEY_PATH, 'r').read(),
        'PRIVATE_PGP_KEY': open(DIFF_PRIVATE_PGP_KEY_PATH, 'r').read(),
    },
}

Generate GPG keys if using Public Key Encryption

The public key is going to encrypt the message and the private key will be needed to decrypt the content. The following commands have been taken from the pgcrypto documentation (see Generating PGP Keys with GnuPG).

Generating a public and a private key (The preferred key type is "DSA and Elgamal".):

$ gpg --gen-key
$ gpg --list-secret-keys

/home/bob/.gnupg/secring.gpg
---------------------------
sec   2048R/21 2014-10-23
uid                  Test Key <[email protected]>
ssb   2048R/42 2014-10-23


$ gpg -a --export 42 > public.key
$ gpg -a --export-secret-keys 21 > private.key

Limitations

This library currently does not support Public Key Encryption private keys that are password protected yet. See Issue #89 to help implement it.

Upgrading to 2.4.0 from previous versions

The 2.4.0 version of this library received a large rewrite in order to support auto-decryption when getting encrypted field data as well as the ability to filter on encrypted fields without using the old PGPCrypto aggregate functions available in previous versions.

The following items in this library have been removed and therefore references in your application to these items need to be removed as well:

• managers.PGPManager
• admin.PGPAdmin
• aggregates.*

Fields

django-pgcrypto-fields has 3 kinds of fields:

• Hash based fields
• Public Key (PGP) fields
• Symmetric fields

Hash Based Fields

Supported hash based fields are:

• TextDigestField
• TextHMACField

TextDigestField is hashed in the database using the digest pgcrypto function using the sha512 algorithm.

TextHMACField is hashed in the database using the hmac pgcrypto function using a key and the sha512 algorithm. This is similar to the digest version however the hash can only be recalculated knowing the key. This prevents someone from altering the data and also changing the hash to match.

Public Key Encryption Fields

Supported PGP public key fields are:

• CharPGPPublicKeyField
• EmailPGPPublicKeyField
• TextPGPPublicKeyField
• DatePGPPublicKeyField
• DateTimePGPPublicKeyField
• TimePGPPublicKeyField
• IntegerPGPPublicKeyField
• BigIntegerPGPPublicKeyField
• DecimalPGPPublicKeyField
• FloatPGPPublicKeyField
• BooleanPGPPublicKeyField

Public key encryption creates a token generated with a public key to encrypt the data and a private key to decrypt it.

Public and private keys can be set in settings with PUBLIC_PGP_KEY and PRIVATE_PGP_KEY.

Symmetric Key Encryption Fields

Supported PGP symmetric key fields are:

• CharPGPSymmetricKeyField
• EmailPGPSymmetricKeyField
• TextPGPSymmetricKeyField
• DatePGPSymmetricKeyField
• DateTimePGPSymmetricKeyField
• TimePGPSymmetricKeyField
• IntegerPGPSymmetricKeyField
• BigIntegerPGPSymmetricKeyField
• DecimalPGPSymmetricKeyField
• FloatPGPSymmetricKeyField
• BooleanPGPSymmetricKeyField

Encrypt and decrypt the data with settings.PGCRYPTO_KEY which acts like a password.

Django Model Field Equivalents

Django Field	Public Key Field	Symmetric Key Field
CharField	CharPGPPublicKeyField	CharPGPSymmetricKeyField
EmailField	EmailPGPPublicKeyField	EmailPGPSymmetricKeyField
TextField	TextPGPPublicKeyField	TextPGPSymmetricKeyField
DateField	DatePGPPublicKeyField	DatePGPSymmetricKeyField
DateTimeField	DateTimePGPPublicKeyField	DateTimePGPSymmetricKeyField
TimeField	TimePGPPublicKeyField	TimePGPSymmetricKeyField
IntegerField	IntegerPGPPublicKeyField	IntegerPGPSymmetricKeyField
BigIntegerField	BigIntegerPGPPublicKeyField	BigIntegerPGPSymmetricKeyField
DecimalField	DecimalPGPPublicKeyField	DecimalPGPSymmetricKeyField
FloatField	FloatPGPPublicKeyField	FloatPGPSymmetricKeyField
BooleanField	BooleanPGPPublicKeyField	BooleanPGPSymmetricKeyField

Other Django model fields are not currently supported. Pull requests are welcomed.

Usage

Model Definition

from django.db import models

from pgcrypto import fields

class MyModel(models.Model):
    digest_field = fields.TextDigestField()
    digest_with_original_field = fields.TextDigestField(original='pgp_sym_field')
    hmac_field = fields.TextHMACField()
    hmac_with_original_field = fields.TextHMACField(original='pgp_sym_field')

    email_pgp_pub_field = fields.EmailPGPPublicKeyField()
    integer_pgp_pub_field = fields.IntegerPGPPublicKeyField()
    pgp_pub_field = fields.TextPGPPublicKeyField()
    date_pgp_pub_field = fields.DatePGPPublicKeyField()
    datetime_pgp_pub_field = fields.DateTimePGPPublicKeyField()
    time_pgp_pub_field = fields.TimePGPPublicKeyField()
    decimal_pgp_pub_field = fields.DecimalPGPPublicKeyField()
    float_pgp_pub_field = fields.FloatPGPPublicKeyField()
    boolean_pgp_pub_field = fields.BooleanPGPPublicKeyField()
    
    email_pgp_sym_field = fields.EmailPGPSymmetricKeyField()
    integer_pgp_sym_field = fields.IntegerPGPSymmetricKeyField()
    pgp_sym_field = fields.TextPGPSymmetricKeyField()
    date_pgp_sym_field = fields.DatePGPSymmetricKeyField()
    datetime_pgp_sym_field = fields.DateTimePGPSymmetricKeyField()
    time_pgp_sym_field = fields.TimePGPSymmetricKeyField()
    decimal_pgp_sym_field = fields.DecimalPGPSymmetricKeyField()
    float_pgp_sym_field = fields.FloatPGPSymmetricKeyField()
    boolean_pgp_sym_field = fields.BooleanPGPSymmetricKeyField()

Encrypting

Data is automatically encrypted when inserted into the database.

Example:

>>> MyModel.objects.create(value='Value to be encrypted...')

Hash fields can have hashes auto updated if you use the original attribute. This attribute allows you to indicate another field name to base the hash value on.

from django.db import models

from pgcrypto import fields

class User(models.Model):
    first_name = fields.TextPGPSymmetricKeyField(max_length=20, verbose_name='First Name')
    first_name_hashed = fields.TextHMACField(original='first_name') 

In the above example, if you specify the optional original attribute it would take the unencrypted value from the first_name model field as the input value to create the hash. If you did not specify an original attribute, the field would work as it does now and would remain backwards compatible.

PGP fields

When accessing the field name attribute on a model instance we are getting the decrypted value.

Example:

>>> # When using a PGP public key based encryption
>>> my_model = MyModel.objects.get()
>>> my_model.value
'Value decrypted'

Filtering encrypted values is now handled automatically as of 2.4.0. And aggregate methods are not longer supported and have been removed from the library.

Also, auto-decryption is support for select_related() models.

from django.db import models

from pgcrypto import fields


class EncryptedFKModel(models.Model):
    fk_pgp_sym_field = fields.TextPGPSymmetricKeyField(blank=True, null=True)


class EncryptedModel(models.Model):
    pgp_sym_field = fields.TextPGPSymmetricKeyField(blank=True, null=True)
    fk_model = models.ForeignKey(
        EncryptedFKModel, blank=True, null=True, on_delete=models.CASCADE
    )

Example:

>>> import EncryptedModel
>>> my_model = EncryptedModel.objects.get().select_releated('fk_model')
>>> my_model.pgp_sym_field
'Value decrypted'
>>> my_model.fk_model.fk_pgp_sym_field
'Value decrypted'

Hash fields

To filter hash based values we need to compare hashes. This is achieved by using a __hash_of lookup.

Example:

>>> my_model = MyModel.objects.filter(digest_field__hash_of='value')
[<MyModel: MyModel object>]
>>> my_model = MyModel.objects.filter(hmac_field__hash_of='value')
[<MyModel: MyModel object>]

Limitations

Unique Indexes

It is usually not possible to index a bytea column in the database as 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.

You can use the hash field ability to 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 unencrypted value that is in the name field when the model is saved or updated. A unique constraint exists on the name_digest so no two digests are allowed. Note well that bulk updates do NOT cause hashes to be updated.

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'
            )
       ]

.distinct('encrypted_field_name')

Due to a missing feature in the Django ORM, using distinct() on an encrypted field does not work for Django 2.0.x and lower.

The normal distinct works on Django 2.1.x and higher:

items = EncryptedFKModel.objects.filter(
    pgp_sym_field__startswith='P'
).only(
    'id', 'pgp_sym_field', 'fk_model__fk_pgp_sym_field'
).distinct(
    'pgp_sym_field'
)

Workaround for Django 2.0.x and lower:

from django.db import models

items = EncryptedFKModel.objects.filter(
    pgp_sym_field__startswith='P'
).annotate(
    _distinct=models.F('pgp_sym_field')
).only(
    'id', 'pgp_sym_field', 'fk_model__fk_pgp_sym_field'
).distinct(
    '_distinct'
)

This works because the annotated field is auto-decrypted by Django as a F field and that field is used in the distinct().

Migrating existing fields into PGCrypto Fields

Migrating existing fields into PGCrypto Fields is not performed by this library. You will need to migrate the data in a forwards migration or other means. The only migration that is supported except to create/activate the pgcrypto extension in Postgres.

Migrating data is complicated as there might be a few things to consider such as:

• the shape of the data
• validations/constrains done on the table/model/form and anywhere else

The library has no way of doing all these guesses or to make all these decisions.

If you need to migrate data from unencrypted fields to encrypted fields, three ways to solve it:

1. When there's no data in the db it should be possible to start from scratch by recreating the db
2. When there's no data in the table it should be possible to recreate the table
3. When there's data or if the project is shared it should be possible to do it in a non destructive way

Option 1: No data is in the db

1. Drop the database
2. Squash the migrations
3. Recreate the db

Option 2: No data in the table

1. Create a migration to drop the table
2. Create a new migration for the table with the encrypted field
3. Optionally squash the migration

Option 3: Migrating in a non-destructive way

The goal here is to be able to use to legacy field if something goes wrong.

Part 1:

1. Create new field
2. When data is saved write both to legacy and new field
3. Create a data migration to cast data from legacy field to new field
4. check existing data from legacy and new field are the same if possible

Part 2:

1. Rename the fields and drop legacy fields
2. Update the code to use only the new field

Common Errors

psycopg2.errors.UndefinedFunction: function pgp_sym_encrypt(numeric, unknown) does not exist

This commonly means you do not have the pgcrypto extension installed in Postgres. Run the migration available in this library or install it manually in pgsql console.

Security Limitations

Taken direction from the PostgreSQL documentation:

https://www.postgresql.org/docs/9.6/static/pgcrypto.html#AEN187024

All pgcrypto functions run inside the database server. That means that all the data and passwords move between pgcrypto and client applications in clear text. Thus you must:

1. Connect locally or use SSL connections.
2. Trust both system and database administrator.

If you cannot, then better do crypto inside client application.

The implementation does not resist side-channel attacks. For example, the time required for a pgcrypto decryption function to complete varies among ciphertexts of a given size.

django-pgcrypto-fields's Issues

Add support for JSONField

We have some data in JSON-field (django.contrib.postgres.fields.JSONField), but this package does not support it. Could it be possible to add this functionality?

Update README

Update README to reflect latest changes.

Custom manager for Decrypt annotate

Add a custom manager to include the Decrypt annotate in get_queryset.

Add support for additional fields

Add support for additional fields to have more parity with the built-in fields in Django:

TODO:

• FloatPGPPublicKeyField
  • Tests
  • Documentation
• FloatPGPSymmetricKeyField
  • Tests
  • Documentation
• TimePGPPublicKeyField
  • Lookups
  • Tests
  • Documentation
• TimePGPSymmetricKeyField
  • Lookups
  • Tests
  • Documentation
• DecimalPGPPublicKeyField
  • Tests
  • Documentation
• DecimalPGPSymmetricKeyField
  • Tests
  • Documentation

ExternalRoutineInvocationException: Need password for secret key

I made all the configurations described in the README to use PGPPublicKeyField.
I created my public and private key, however when I make a query this error is thrown, ExternalRoutineInvocationException: Need password for secret key

Am I missing something?

cast_type of DateTimePGPSymmetricKeyField does not allow for dumpdata exports exporting timezone aware datetimes.

Extremely easy to override with a custom class when using Postgres as your database

class CustomDateTimePGPSymmetricKeyField(fields.DateTimePGPSymmetricKeyField):
    """
    Overriding cast_type so `dumpdata` command exports timezone aware datetimes.
    """
    cast_type = 'TIMESTAMPTZ'

but maybe this could be a setting? Or it could look at current USE_TZ setting?

Support multiple databases connections with different crypto keys

Add support for multiple databases that have different crypto keys. Just move PUBLIC_PGP_KEY, PRIVATE_PGP_KEY, and PGCRYPTO_KEY into the DATABASES configuration and grab that information from the connection instead of reading from the settings:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'PUBLIC_PGP_KEY': open(PUBLIC_PGP_KEY_PATH, 'r').read(),
        'PRIVATE_PGP_KEY': open(PRIVATE_PGP_KEY_PATH, 'r').read(),
        'PGCRYPTO_KEY': 'super_secret_key',
        ...
    },
}

DecryptedCol

    def as_sql(self, compiler, connection):
        sql, params = super(DecryptedCol, self).as_sql(compiler, connection)
        decrypt_sql = self.decrypt_sql_template.format(
            key=self.target._get_encryption_key(connection),
            dbtype=self.target._get_base_db_type(connection),
            sql=sql
        )
        return decrypt_sql, params

This is easily made backwards compatible:

   def _get_encryption_key(self, connection):
        if 'PGCRYPTO_KEY' in connection.settings_dict:
            key = connection.settings_dict['PGCRYPTO_KEY']
        else:
            key = getattr(settings, 'PGCRYPTO_KEY', settings.SECRET_KEY)
        # Escape any percent symbols in the key, to avoid them being
        # interpreted as extra substitution placeholders later on.
        key = key.replace('%', '%%')
        return key

Add support for BigIntegerField

Add support for BigIntegerField:

https://docs.djangoproject.com/en/2.2/ref/models/fields/#bigintegerfield

Unique constraints

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.

Update requirements to allow them to be picked up by requires.io

At the moment the requirements are always up-to-date as they are not picked up by requires.io.

From http://requires.io

Requirements Files

Requires.io looks for the following requirements files in the project:

   setup.py,
   all files matching req*.txt or req*.pip,
   all files matching requirements/*.txt or requirements/*.pip,
   buildout.cfg and versions.cfg,
   tox.ini.

Add __range lookup for Date / DateTime fields

Currently, the date / datetime fields do not support the __range lookup.

TODO:

• Implement enhancement
• Write tests
• Update README
• Update CHANGELOG

DateTimePGPSymmetricKeyField does not save with timezone info, is this a defect?

imagine some field:

expires_at = fields.DateTimePGPSymmetricKeyField(null=True)

set equal to:

from datetime import timedelta
from django.utils.timezone import now
...
expires_at = now() + timedelta(seconds=token['expires_in'])

I noticed there was an issue when I got the error:

can't compare offset-naive and offset-aware datetimes

from this call on my model:

@property
def is_expired(self):
    return self.expires_at < now()

Inspecting the unencrypted datetime next to a never encrypted datetime -- you can see the never encrypted datetime still has timezone information:

I can remedy this by refacroting my is_expired method to:

import pytz
...
@property
def is_expired(self):
    return self.expires_at.replace(tzinfo=pytz.UTC) < now()

but I was curious if this was a defect.

Thanks for your time! :)

Migrate error after add new field

My model (migrate okay in the first time)

class Dkm(models.Model):
    name = fields.TextPGPSymmetricKeyField()
    value = fields.IntegerPGPSymmetricKeyField(default=0)

I update model and migrate again:

class Dkm(models.Model):
    name = fields.TextPGPSymmetricKeyField()
    value = fields.IntegerPGPSymmetricKeyField(default=0)
    value3 = fields.IntegerPGPSymmetricKeyField(default=0)

Error occur:

Traceback (most recent call last):
  File "manage.py", line 21, in <module>
    main()
  File "manage.py", line 17, in main
    execute_from_command_line(sys.argv)
  File "C:\Users\vu.tran\Desktop\kona-server\env\lib\site-packages\django\core\management\__init__.py", line 381, in execute_from_command_line
    utility.execute()
  File "C:\Users\vu.tran\Desktop\kona-server\env\lib\site-packages\django\core\management\__init__.py", line 375, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "C:\Users\vu.tran\Desktop\kona-server\env\lib\site-packages\django\core\management\base.py", line 323, in run_from_argv
    self.execute(*args, **cmd_options)
  File "C:\Users\vu.tran\Desktop\kona-server\env\lib\site-packages\django\core\management\base.py", line 364, in execute
    cursor.execute(sql, params)
  File "C:\Users\vu.tran\Desktop\kona-server\env\lib\site-packages\django\db\backends\utils.py", line 99, in execute
    return super().execute(sql, params)
  File "C:\Users\vu.tran\Desktop\kona-server\env\lib\site-packages\django\db\backends\utils.py", line 67, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "C:\Users\vu.tran\Desktop\kona-server\env\lib\site-packages\django\db\backends\utils.py", line 76, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "C:\Users\vu.tran\Desktop\kona-server\env\lib\site-packages\django\db\backends\utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "C:\Users\vu.tran\Desktop\kona-server\env\lib\site-packages\django\db\utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "C:\Users\vu.tran\Desktop\kona-server\env\lib\site-packages\django\db\backends\utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: column "value3" is of type bytea but default expression is of type integer
HINT:  You will need to rewrite or cast the expression.

The error will not occur if set null=True
Have you got any idea?

Support CharField for Django parity

Add support CharField for Django party. This will also support enforcing max_length of pre-encrypted form fields and also supports the right form field (instead of wrongly TextareaInput).

Side effect add support for max_length for EmailField (which is optional and set to 254 by default) and supports the right form field (instead of wrongly TextareaInput).

Release 2.1.1

I checked in a PR for Django 2.0 support. I upgraded the requirements and travis to get the build to run successfully again. I have tagged this as 2.1.1.

Please release what is in master to PyPi as release 2.1.1 at your earliest convenience or add my PyPi user peterfarrell-atdsaa to be able to push releases to the cheese shop.

@kevinetienne @maxpeterson @meshy @cyanc @Ian-Foote

Question - How to use a different hashing algorithm?

The default pgcrypto extension is PGP (public key/privacy key) output looks like:

\xc30d0407030286e239c36fe48d126dd24401086aecb7805d0e52fc77c32bc341f566153ee9e291d11f8972a6fd63a44282b5a7df09fad63d0bbd1eb1374980a9e10e13d2987a1289e193a749f496aec0195e1eb5e3

Not sure if this is the most secure by default. If improved algorithms are found in the future, how would you configure them?

If not using PGP and instead a one way hash, why not use other options like bcrypt are argon2, pbkdf2, scrypt, etc. instead of SHA.

Perhaps an improvement to django-pgcrypto-fields would be to configure what types of algorithms you would want to use? Or is there already a way to configure this with postgres or something?

Add Support For Django 3

Add Support For Django 3

Add support to hash fields to optionally base hash value off another field value

We wanted to run a new feature by you all to see if it's worth for us to do a PR.

We want to add support to the hash fields that optionally bases the value for the hash off the value of another field. This would be useful to pre-compute a hash based on the value from another model field and not have to worry about updating the hash when the other field value changes. Essentially, it ensures the hash is always based on the value of a sibling field.

Example:

class User(models.Models):
    first_name = fields.TextPGPSymmetricKeyField(max_length=20, verbose_name='First Name')
    first_name_hashed = fields. TextHMACField(original='first_name') 

In the above example, if you specify the optional original attribute -- it would take the unencrypted value from the first_name model field as the input value to create the hash. If you didn't specify an original attribute, the field would work as it does now and would remain backwards compatible.

Let us know if you would be willing to accept a PR for this feature.

Dynamic PGCRYPTO_KEY

Is there any option to use a key in a dynamic manner? For example, if there's a need to encrypt/decrypt data with different keys depending on some factors, is this possible using this package?

Django 1.10 deprecation notices

Before going into production with this, wanted to make sure there will be an upgrade path to Django 1.10 (which is already released). Accessing an encrypted field value in Django 1.9 throws several warnings.

.../virtualenvs/portal/lib/python3.5/site-packages/pgcrypto/aggregates.py:66: RemovedInDjango110Warning: The aggregates property is deprecated. Use annotations instead.
  query.aggregates[alias] = aggregate

09/07/2016 17:02:42 WARNING py.warnings:221 .../virtualenvs/portal/lib/python3.5/site-packages/pgcrypto/aggregates.py:66: RemovedInDjango110Warning: The aggregates property is deprecated. Use annotations instead.
  query.aggregates[alias] = aggregate

.../virtualenvs/portal/lib/python3.5/site-packages/django/db/models/aggregates.py:64: RemovedInDjango110Warning: The aggregates property is deprecated. Use annotations instead.
  sql_aggregate = query.aggregates.pop(placeholder_alias)

09/07/2016 17:02:42 WARNING py.warnings:221 .../virtualenvs/portal/lib/python3.5/site-packages/django/db/models/aggregates.py:64: RemovedInDjango110Warning: The aggregates property is deprecated. Use annotations instead.
  sql_aggregate = query.aggregates.pop(placeholder_alias)

Django 1.11 (2.0) compatibility

Hello everyone.
Is this project compatible to Django 1.11 and the upcoming Django 2.0?

Also: Where are the keys necessary for decryption stored? What kind of access would an attacker need to decrypt encrypted data in the database?

TypeError: %i format: a number is required, not str

I've used django-pgcrypto-fields for about a week now without a problem and then I created a migration to add an encrypted field:

# Generated by Django 2.2.19 on 2021-03-13 13:45

from django.db import migrations

def copy_field(apps, schema_editor):
    GenericEvent = apps.get_model('generic_event', 'GenericEvent')
    for ep in GenericEvent.objects.all():
        ep.pp_receiver_encrypted = ep.pp_receiver
        ep.save()


class Migration(migrations.Migration):

    dependencies = [
        ('generic_event', '0015_auto_20210313_0745'),
    ]

    operations = [
        migrations.RunPython(copy_field)
    ]

I've done a similar one with hashed fields before and it worked, but this one fails with:

Running migrations:
  Applying generic_event.0016_encrypt_payment_fields...Traceback (most recent call last):
  File "manage.py", line 24, in <module>
    execute_from_command_line(sys.argv)
  File "/var/www/envs/myenv/lib/pyston3.8/site-packages/django/core/management/__init__.py", line 381, in execute_from_command_line
    utility.execute()
  File "/var/www/envs/myenv/lib/pyston3.8/site-packages/django/core/management/__init__.py", line 375, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/var/www/envs/myenv/lib/pyston3.8/site-packages/django/core/management/base.py", line 323, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/var/www/envs/myenv/lib/pyston3.8/site-packages/django/core/management/base.py", line 364, in execute
    output = self.handle(*args, **options)
  File "/var/www/envs/myenv/lib/pyston3.8/site-packages/django/core/management/base.py", line 83, in wrapped
    res = handle_func(*args, **kwargs)
  File "/var/www/envs/myenv/lib/pyston3.8/site-packages/django/core/management/commands/migrate.py", line 232, in handle
    post_migrate_state = executor.migrate(
  File "/var/www/envs/myenv/lib/pyston3.8/site-packages/django/db/migrations/executor.py", line 117, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/var/www/envs/myenv/lib/pyston3.8/site-packages/django/db/migrations/executor.py", line 147, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/var/www/envs/myenv/lib/pyston3.8/site-packages/django/db/migrations/executor.py", line 245, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/var/www/envs/myenv/lib/pyston3.8/site-packages/django/db/migrations/migration.py", line 124, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/var/www/envs/myenv/lib/pyston3.8/site-packages/django/db/migrations/operations/special.py", line 190, in database_forwards
    self.code(from_state.apps, schema_editor)
  File "/var/www/project/projectproject/generic_event/migrations/0016_encrypt_payment_fields.py", line 7, in copy_field
    for ep in GenericEvent.objects.all():
  File "/var/www/envs/myenv/lib/pyston3.8/site-packages/django/db/models/query.py", line 274, in __iter__
    self._fetch_all()
  File "/var/www/envs/myenv/lib/pyston3.8/site-packages/cacheops/query.py", line 303, in _fetch_all
    return self._no_monkey._fetch_all(self)
  File "/var/www/envs/myenv/lib/pyston3.8/site-packages/django/db/models/query.py", line 1242, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/var/www/envs/myenv/lib/pyston3.8/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 "/var/www/envs/myenv/lib/pyston3.8/site-packages/django/db/models/sql/compiler.py", line 1129, in execute_sql
    sql, params = self.as_sql()
  File "/var/www/envs/myenv/lib/pyston3.8/site-packages/django/db/models/sql/compiler.py", line 474, in as_sql
    extra_select, order_by, group_by = self.pre_sql_setup()
  File "/var/www/envs/myenv/lib/pyston3.8/site-packages/django/db/models/sql/compiler.py", line 54, in pre_sql_setup
    self.setup_query()
  File "/var/www/envs/myenv/lib/pyston3.8/site-packages/django/db/models/sql/compiler.py", line 45, in setup_query
    self.select, self.klass_info, self.annotation_col_map = self.get_select()
  File "/var/www/envs/myenv/lib/pyston3.8/site-packages/django/db/models/sql/compiler.py", line 254, in get_select
    sql, params = self.compile(col, select_format=True)
  File "/var/www/envs/myenv/lib/pyston3.8/site-packages/django/db/models/sql/compiler.py", line 405, in compile
    sql, params = node.as_sql(self, self.connection)
  File "/var/www/envs/myenv/lib/pyston3.8/site-packages/pgcrypto/mixins.py", line 33, in as_sql
    sql = self.target.get_decrypt_sql(connection) % (sql, self.target.get_cast_sql())
TypeError: %i format: a number is required, not str

Local variables from Sentry:

__class__ | <class 'pgcrypto.mixins.DecryptedCol'>
compiler | <django.db.models.sql.compiler.SQLCompiler object at 0x7fbc3a838a30>
connection | <django.db.backends.postgresql.base.DatabaseWrapper object at 0x7fbc3a827550>
params | []
self | DecryptedCol(organizations_organization, organizations.Organization.pp_receiver_encrypted)
sql | '"organizations_organization"."pp_receiver_encrypted"'

Model field looks like this:
pp_receiver_encrypted = EmailPGPSymmetricKeyField(_('This is account (email)'), blank=True, null=True)

I have no idea how to debug or fix it. It worked fine on my local env. I'm using PostgreSQL 11.11 (through pgbouncer) on Ubuntu 20 server.
I've set PGCRYPTO_KEY in settings. Any help will be greatly appreciated!

Add support for password protected public key encryption private keys

The setting PGCRYPTO_KEY is used in PGPSymmetricKeyFieldMixin (

django-pgcrypto-fields/pgcrypto/mixins.py

Line 143 in 429b9a8

class PGPSymmetricKeyFieldMixin(PGPMixin):

)

but not PGPPublicKeyFieldMixin

django-pgcrypto-fields/pgcrypto/mixins.py

Line 128 in 429b9a8

class PGPPublicKeyFieldMixin(PGPMixin):

I was unable to use a public/private key with a passphrase with a TextPGPPublicKeyField. I regenerated a public/private key without a passphrase it worked fine.

Does PGPPublicKeyFieldMixin need to be updated to support PGCRYPTO_KEY?

Thanks

Support of type BOOLEAN field

Is there any plan on supporting boolean fields within Django for this package? I appreciate we can use Integer fields as an alternative approach, but before we venture to make change, thought it was worth a ticket.

Python 2.7 compatibility?

It appears this is Python 3? Is this supposed to be compatible with old Python 2.7 code? I'm getting the:

TypeError: super() takes at least 1 argument (0 given)

... after installing and running the package. (Django 1.9.8)

" filter() " is not working.

I was testing to use the 'filter()'.

While I was working on a simple example, I could not get any values ​​like this:

What did I do wrong? Or is there a manual error?

Please Help me...

youngsoo

• django==1.10
• python==3.4.5

>>> MyModel.objects.filter().first().pgp_pub_field
'hello, world!\r\n안녕하세요.'
>>> from pgcrypto.aggregates import PGPPublicKeyAggregate
>>> my_models = MyModel.objects.annotate(PGPPublicKeyAggregate('pgp_pub_field'))
>>> my_models
<QuerySet [<MyModel: #1>, <MyModel: #2>]>
>>> my_models.filter(pgp_pub_field='h')
<QuerySet []>
>>> my_models.filter(pgp_pub_field__decrypted='h')
<QuerySet []>
>>> my_models.filter(pgp_pub_field__decrypted='haa')
<QuerySet []>
>>> my_models.filter(pgp_pub_field__decrypted='1111')
<QuerySet []>

Use app config to define settings

With a specific app config it would be easier to override where to find the PGP keys and/or the secret keys.

Custom methods to `get_or_create`, `update_or_create`

At the moment using django get_or_create or update_or_create doesn't work as some values might be encrypted/hashed. We can have a mixin which would override those methods from django.

Explain the difference between kinds of fields

Currently, the readme says that

django-pgcrypto-fields has 3 kinds of fields:

• hash based fields
• PGP fields
• Symmetric fields"

It would be nice if there was a quick explanation of what these things are, how they differ, and when one might want to use them.

Fields are not being decrypted neither with TextDigestField or TextHMACField

Field was properly encrypted when I create instance, however no decryption is done in any of selects:

from pgcrypto import fields

class Test(models.Model):
    key = fields.TextDigestField()

t = Test(key='asdf')
t.save()

INSERT INTO "app_test" ("key")
VALUES (Digest('*****', 'sha512'), true) RETURNING "app_test"."id";

And for any selects I get raw data and selects contain no decryption functions:

for each in Test.objects.all():
    print(each, each.key)
# >>>> object (1) \xd0698d.....

SQL:

SELECT "app_test"."id",
       "app_test"."key"
FROM "app_test";

All I added to settings is:

PGCRYPTO_KEY = '*****'
# and
'pgcrypto', # To INSTALLED_APPS

Versions:
Python 3.6.4
Django 2.1.4 (Also tried Django 2.1.0)
django-pgcrypto-fields==2.5.1

[Need help] when using update with F() getting pgp_sym_encrypt(numeric, unknown) does not exist

I am using django-pgcrypto-fields symmetric encryption in DB.
I have following update query

MyTable.objects.filter(some_code=some_code).update(
                    some_value=(some_price * F('some_units')),
                    updated_on=datetime.now()
                )

I am getting the following issue psycopg2.errors.UndefinedFunction: function pgp_sym_encrypt(numeric, unknown) does not exist

I am not sure if this is the issue with the library or something with my code.

How to retrieve raw encrypted value?

I would like to show the encrypted values for Django admin users that are not super users, and only show the decrypted values to super users, but I'm not sure how to go about this. Do you have any guidance?

I was thinking I could extend admin.ModelAdmin class somehow or maybe create custom admin Forms and handle it that way, but I'm not sure how to access the encrypted value at this point when the model fields are already decrypted.

django.db.utils.ProgrammingError: function pgp_pub_decrypt(character varying, bytea) does not exist

aws rds postgresql is not exist function pgp_pub_decrypt(character varying, bytea)

Add mixin for get_by_natural_key

The method get_by_natural_key can check if the User.USERNAME_FIELDS is a hash_of the value provided for login.

Example:

class MyMixin:
    def get_by_natural_key(self, username):
        """Get user by comparing hashed email.

        `get_by_natural_key` is used to `authenticate` a user, see:
        https://github.com/django/django/blob/c5780adeecfbd85a80b5aa7130dd86e78b23e497/django/contrib/auth/backends.py#L16
        """
        return self.get(username_keyed__hash_of=username)

Add caching

When accessing the field value attribute on the model we are doing a database query. We need a way to cache the value. see #4

Merge .coveragerc into setup.cfg

See for example django-user-management on how to include coverage in setup.cfg.

Update outdated requirements.txt

Pass options to pgp_sym_encrypt

pgp_sym_encrypt function supports certain options. For example by default it uses AES128 algorithm to encrypt but you can customize pgp_sym_encrypt(data, psw, 'compress-algo=1, cipher-algo=aes256'). Is there a way I can provide the cipher-algo option with value AES256 when I create CharPGPSymmetricKeyField or maybe in the settings file? If there is not then could there please be an enhancement that can be made for this?

Manual Encrypt - Question

I am trying to encrypt the previous data using SQL but for some reason it does not decrypt as it suppose to

Add gitignore related to the project

Related to #41 (comment)

function digest(unknown, unknown) does not exist

It seems to be having a problem with the function digest and the correct hashing algorithm. Is there some dependency missing that would cause this, or misconfiguration?

I am trying to create a TextDigestField

Remove support for Django 1.11

Django 1.11 has reached the end of extended support on April 1, 2020.

https://www.djangoproject.com/weblog/2020/apr/01/bugfix-releases/

Adding default value to IntegerField in subsequent migrations throws an error

The issue is described in this SO topic. Looks like it has existed for over a year. I ran into it today, and seting null=True doesn't actually help. Any ideas if it has ever been resolved?

EmailPGPPublicKeyField does not use the correct mixin

As defined in https://github.com/incuna/django-pgcrypto-fields/blob/master/pgcrypto/fields.py#L41 EmailPGPPublicKeyField uses the PGPSymmetricKeyFieldMixin mixin instead of the PGPPublicKeyFieldMixin one.

Remove support for python3.5

Python 3.5 end-of-life is next month I suggest to drop the support. It would allow to update twine (see pyup.io build status #260) to the last version too as the library deprecated support for it last year.

Error when migrating existing fields into pgcrypto fields

Hey,

I am upgrading my Django application from 2.2.9 to Django 3.x and ran into errors on migrations that are converting normal fields (like CharField) into pgcrypto fields (CharPGPSymmetricKeyField in this case).

I'm not sure if this is a bug in Django, psycopg2 or django-pgcrypto-fields. I'm hoping you could assist me.
These migrations will work when running Django 2.2.9 but when I update Django package, they won't work any more.

I've managed to reproduce this in a fresh Django app.

I'm using Ubuntu 19.10, running Python 3.8.5 in a Docker container with the following packages:
django==3.0 (or any 3.x)
django-pgcrypto-fields==2.5.1
psycopg2==2.8.5

PostgreSQL is 11.1 and pgcrypto extension is installed.

Steps to reproduce:

1. Create a model with normal fields:

class CryptoTest(models.Model):
    name = fields.CharField(blank=True, null=True, max_length=255)

2. Create migration: python manage.py makemigrations

3. Modify model:

class CryptoTest(models.Model):
    name = fields.CharPGPSymmetricKeyField(blank=True, null=True, max_length=255)

4. Create migration: python manage.py makemigrations
5. Run migrations: python manage.py migrate

Traceback

Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.DatatypeMismatch: column "name" cannot be cast automatically to type bytea
HINT:  You might need to specify "USING name::bytea".


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

Traceback (most recent call last):
  File "./manage.py", line 21, in <module>
    main()
  File "./manage.py", line 17, in main
    execute_from_command_line(sys.argv)
  File "/usr/local/lib/python3.8/site-packages/django/core/management/__init__.py", line 401, in execute_from_command_line
    utility.execute()
  File "/usr/local/lib/python3.8/site-packages/django/core/management/__init__.py", line 395, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/local/lib/python3.8/site-packages/django/core/management/commands/test.py", line 23, in run_from_argv
    super().run_from_argv(argv)
  File "/usr/local/lib/python3.8/site-packages/django/core/management/base.py", line 328, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/usr/local/lib/python3.8/site-packages/django/core/management/base.py", line 369, in execute
    output = self.handle(*args, **options)
  File "/usr/local/lib/python3.8/site-packages/django/core/management/commands/test.py", line 53, in handle
    failures = test_runner.run_tests(test_labels)
  File "/usr/local/lib/python3.8/site-packages/django/test/runner.py", line 684, in run_tests
    old_config = self.setup_databases(aliases=databases)
  File "/usr/local/lib/python3.8/site-packages/django/test/runner.py", line 604, in setup_databases
    return _setup_databases(
  File "/usr/local/lib/python3.8/site-packages/django/test/utils.py", line 169, in setup_databases
    connection.creation.create_test_db(
  File "/usr/local/lib/python3.8/site-packages/django/db/backends/base/creation.py", line 67, in create_test_db
    call_command(
  File "/usr/local/lib/python3.8/site-packages/django/core/management/__init__.py", line 168, in call_command
    return command.execute(*args, **defaults)
  File "/usr/local/lib/python3.8/site-packages/django/core/management/base.py", line 369, in execute
    output = self.handle(*args, **options)
  File "/usr/local/lib/python3.8/site-packages/django/core/management/base.py", line 83, in wrapped
    res = handle_func(*args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/django/core/management/commands/migrate.py", line 231, in handle
    post_migrate_state = executor.migrate(
  File "/usr/local/lib/python3.8/site-packages/django/db/migrations/executor.py", line 117, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/usr/local/lib/python3.8/site-packages/django/db/migrations/executor.py", line 147, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/usr/local/lib/python3.8/site-packages/django/db/migrations/executor.py", line 245, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/usr/local/lib/python3.8/site-packages/django/db/migrations/migration.py", line 124, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/usr/local/lib/python3.8/site-packages/django/db/migrations/operations/fields.py", line 249, in database_forwards
    schema_editor.alter_field(from_model, from_field, to_field)
  File "/usr/local/lib/python3.8/site-packages/django/db/backends/base/schema.py", line 564, in alter_field
    self._alter_field(model, old_field, new_field, old_type, new_type,
  File "/usr/local/lib/python3.8/site-packages/django/db/backends/postgresql/schema.py", line 147, in _alter_field
    super()._alter_field(
  File "/usr/local/lib/python3.8/site-packages/django/db/backends/base/schema.py", line 710, in _alter_field
    self.execute(
  File "/usr/local/lib/python3.8/site-packages/django/db/backends/base/schema.py", line 142, in execute
    cursor.execute(sql, params)
  File "/usr/local/lib/python3.8/site-packages/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/usr/local/lib/python3.8/site-packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/usr/local/lib/python3.8/site-packages/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.8/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/usr/local/lib/python3.8/site-packages/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: column "name" cannot be cast automatically to type bytea
HINT:  You might need to specify "USING name::bytea".

If I was to create my model using pgcrypto fields from the start, it works - but the model in my application was converted later on in the development and now these migrations are in the production aswell.

If you feel like I haven't given enough details, feel free to ask and I will try to explain more.

Thanks in advance for any help.

Drop support for Django 1.8, 1.9, and 1.10

According to the Django project, Django 1.8, 1.9, and 1.10 ended extended support have all passed as of April 1, 2018 and are no longer supported.

https://www.djangoproject.com/download/

The last version of this library to support 1.8, 1.9, and 1.10 is the v2.2.0 release.

Distinct().order_by('field') results in ProgrammingError

Platform: macOS Catalina
Python: 3.6.9
Django: 2.2.11

Given a model with encrypted fields, doing;

EncryptedModel.objects.distinct().order_by('encrypted_field')

Results in

django.db.utils.ProgrammingError: syntax error at or near "Version"
LINE 59: Version: GnuPG v1

The syntax error refers to some (seemingly random) part of the armored key in the SQL.

Interestingly, this does not occur when you distinct on the encrypted_field:

EncryptedModel.objects.distinct('encrypted_field').order_by('encrypted_field')

Works fine. So does

EncryptedModel.objects.distinct().

I've created a PR #203 with a breaking test, but I don't know where to start fixing this.

Implement DatePGPPublicKeyField and DateTimePGPPublicKeyField

Implement DatePGPPublicKeyField and DateTimePGPPublicKeyField to match parity with symmetric key fields.

Todo:

• Add DatePGPPublicKeyField and DateTimePGPPublicKeyField
• Write tests
• Update README documentation
• Update CHANGELOG

Single quote in PGCRYPTO_KEY string causes syntax error

syntax error at or near "j1" LINE 1: ...SET value = pgp_sym_encrypt('hogehoge', 'hogehoge'j1') WHERE...

It seems that proper escaping is needed when constructing SQL statements.