Hello. I've created an encryption wrapper that marshals things in and out
of encryption, except that for the TypeDecorator the input and output type
are different. I'm having trouble wrapping float and Enum types while still
keeping them queryable.
Can someone take a look at the attached reproducer and let me know what I
can do to fix it up/work around the issue?
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/c44677c2-343e-49de-8bfe-0b5cfab70b41%40googlegroups.com.
import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base
import sqlalchemy.dialects.postgresql as postgresql
import enum
import tempfile
import contextlib
import pathlib
import subprocess
import getpass
PASSPHRASE_USER = "PASSPHRASE_USER"
class Encrypted(sqlalchemy.types.TypeDecorator):
impl = postgresql.BYTEA
def __init__(self, inner, passphrase):
super().__init__()
self.inner = inner
self.passphrase = passphrase
def bind_expression(self, bindvalue):
bindvalue = sqlalchemy.cast(bindvalue, sqlalchemy.String)
return sqlalchemy.func.pgp_sym_encrypt(bindvalue, self.passphrase)
def column_expression(self, col):
value = sqlalchemy.func.pgp_sym_decrypt(col, self.passphrase)
value = sqlalchemy.cast(value, self.inner)
return value
Base = declarative_base()
class Type(enum.Enum):
unverified = 0
normal = 1
superuser = 2
class User(Base):
__tablename__ = "users"
id = sqlalchemy.Column(sqlalchemy.types.BigInteger, primary_key=True)
user_type = sqlalchemy.Column(
Encrypted(sqlalchemy.types.Enum(Type), PASSPHRASE_USER)
)
reputation = sqlalchemy.Column(
Encrypted(sqlalchemy.types.Float, PASSPHRASE_USER)
)
@contextlib.contextmanager
def postgresql_instance():
with tempfile.TemporaryDirectory() as tempdir:
tmpdir = pathlib.Path(tempdir)
datadir = tmpdir / "data_dir"
pwfile = tmpdir / "passwd"
datadir.mkdir()
with open(pwfile, 'w') as f:
f.write("random_pw")
subprocess.run(
[
"initdb",
"-D",
str(datadir),
"-A",
"password",
"--pwfile",
str(pwfile),
],
check=True,
)
subprocess.run(
["pg_ctl", "start", "-D", str(datadir), "-l", str(tmpdir / "logfile")],
check=True,
)
yield
subprocess.run(["pg_ctl", "stop", "-D", str(datadir)], check=True)
@contextlib.contextmanager
def postgresql_engine():
with postgresql_instance():
from sqlalchemy import create_engine
from sqlalchemy_utils import create_database
connect_string = f"postgresql+psycopg2://{getpass.getuser()}:random_pw@localhost:5432/streamserver"
engine = create_engine(connect_string, echo=True)
create_database(engine.url)
Base.metadata.create_all(engine)
with engine.connect() as con:
con.execute("CREATE EXTENSION pgcrypto;")
yield engine
@contextlib.contextmanager
def postgresql_session():
from sqlalchemy.orm import sessionmaker
with postgresql_engine() as engine:
Session = sessionmaker(bind=engine)
session = Session()
yield session
session.close()
if __name__ == "__main__":
with postgresql_session() as session:
user1 = User(
user_type=Type.unverified,
reputation=0.0
)
session.add(user1)
session.commit()
assert session.query(User).count() == 1
users = session.query(User).all()
for user in users:
assert user == user1
