search

cossacklabs / acra-engineering-demo

Acra-based example projects: check how easy it is to deploy data security in typical web infrastructures.
https://www.cossacklabs.com/acra/
Apache License 2.0
28 stars 16 forks source link

Error during insert - Example 4. Transparent encryption, Zones, Python app, MySQL #59

Closed amri closed 1 year ago

amri commented 1 year ago

I'm trying to follow these steps https://github.com/cossacklabs/acra-engineering-demo#example-4-transparent-encryption-zones-python-app-mysql-deprecated-will-be-removed but I encounter an issue on Insert data using updated config.

Error i receiedd when running: root@ubuntu-s-1vcpu-2gb-intel-sgp1-01:~/acra-engineering-demo# docker exec -it python-mysql-python-1 python3 extended_example_with_zone.py --host=acra-server --port=9393 --data=data.json

data: [{'token_i32': 1234, 'data_i32': '1234', 'token_i64': 645664, 'data_i64': '645664', 'token_str': '078-05-1111', 'token_bytes': 'byt13es', 'token_email': 'john_wed@cl.com', 'data_str': 'John Wed, Senior Relationshop Manager', 'masking': '$112000', 'searchable': 'john_wed@cl.com'}, {'token_i32': 1234, 'data_i32': '1234', 'token_i64': 645664, 'data_i64': '645664', 'token_str': '078-05-1112', 'token_bytes': 'byt13es2', 'token_email': 'april_cassini@cl.com', 'data_str': 'April Cassini, Marketing Manager', 'masking': '$168000', 'searchable': 'april_cassini@cl.com'}, {'token_i32': 1234, 'data_i32': '1234', 'token_i64': 645664, 'data_i64': '645664', 'token_str': '078-05-1117', 'token_bytes': 'byt13es3', 'token_email': 'george_clooney@cl.com', 'data_str': 'George Clooney, Famous Actor', 'masking': '$780000', 'searchable': 'george_clooney@cl.com'}]
Traceback (most recent call last):
  File "/usr/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1965, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 921, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/lib/python3.9/site-packages/pymysql/cursors.py", line 153, in execute
    result = self._query(query)
  File "/usr/lib/python3.9/site-packages/pymysql/cursors.py", line 322, in _query
    conn.query(q)
  File "/usr/lib/python3.9/site-packages/pymysql/connections.py", line 558, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/lib/python3.9/site-packages/pymysql/connections.py", line 822, in _read_query_result
    result.read()
  File "/usr/lib/python3.9/site-packages/pymysql/connections.py", line 1200, in read
    first_packet = self.connection._read_packet()
  File "/usr/lib/python3.9/site-packages/pymysql/connections.py", line 739, in _read_packet
    packet_header = self._read_bytes(4)
  File "/usr/lib/python3.9/site-packages/pymysql/connections.py", line 795, in _read_bytes
    raise err.OperationalError(
pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query')

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

Traceback (most recent call last):
  File "/app/extended_example_with_zone.py", line 146, in <module>
    write_data(args.data, connection)
  File "/app/extended_example_with_zone.py", line 104, in write_data
    connection.execute(
  File "/usr/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1412, in execute
    return meth(
  File "/usr/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 483, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/usr/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1635, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1844, in _execute_context
    return self._exec_single_context(
  File "/usr/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1984, in _exec_single_context
    self._handle_dbapi_exception(
  File "/usr/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2339, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/usr/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1965, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 921, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/lib/python3.9/site-packages/pymysql/cursors.py", line 153, in execute
    result = self._query(query)
  File "/usr/lib/python3.9/site-packages/pymysql/cursors.py", line 322, in _query
    conn.query(q)
  File "/usr/lib/python3.9/site-packages/pymysql/connections.py", line 558, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/lib/python3.9/site-packages/pymysql/connections.py", line 822, in _read_query_result
    result.read()
  File "/usr/lib/python3.9/site-packages/pymysql/connections.py", line 1200, in read
    first_packet = self.connection._read_packet()
  File "/usr/lib/python3.9/site-packages/pymysql/connections.py", line 739, in _read_packet
    packet_header = self._read_bytes(4)
  File "/usr/lib/python3.9/site-packages/pymysql/connections.py", line 795, in _read_bytes
    raise err.OperationalError(
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2013, 'Lost connection to MySQL server during query')
[SQL: INSERT INTO test (data_str, masking, token_i32, data_i32, token_i64, data_i64, token_str, token_bytes, token_email) VALUES (%(data_str)s, %(masking)s, %(token_i32)s, %(data_i32)s, %(token_i64)s, %(data_i64)s, %(token_str)s, %(token_bytes)s, %(token_email)s)]
[parameters: {'data_str': b'John Wed, Senior Relationshop Manager', 'masking': b'$112000', 'token_i32': 1234, 'data_i32': b'1234', 'token_i64': 645664, 'data_i64': b'645664', 'token_str': '078-05-1111', 'token_bytes': b'byt13es', 'token_email': 'john_wed@cl.com'}]
(Background on this error at: https://sqlalche.me/e/20/e3q8)
amri commented 1 year ago

After checking logs against the acra-server container, I got following lines after I ran the same command:

time="2023-07-07T03:06:59Z" level=info msg="Got new connection to AcraServer: 172.25.0.3:49108" connection_string="tcp://0.0.0.0:9393" from_descriptor=false
time="2023-07-07T03:06:59Z" level=info msg="Handle client's connection" client_id="" session_id=3
time="2023-07-07T03:06:59Z" level=info msg="Got new connection to AcraServer: 172.25.0.3:49124" connection_string="tcp://0.0.0.0:9393" from_descriptor=false
time="2023-07-07T03:06:59Z" level=info msg="Handle client's connection" client_id="" session_id=4
time="2023-07-07T03:06:59Z" level=error msg="Can't encrypt expression" code=903 error="can't read private key for matched zone_id to encrypt with AcraBlock: open /data/acra-server/DDDDDDDDPfBoWiixeMTUuEOk_zone_sym: no such file or directory"
time="2023-07-07T03:06:59Z" level=error msg="Key found error" client_id="" code=1100 error="can't read private key for matched zone_id to encrypt with AcraBlock: open /data/acra-server/DDDDDDDDPfBoWiixeMTUuEOk_zone_sym: no such file or directory" interrupt_side=Client-AcraServer session_id=3
time="2023-07-07T03:06:59Z" level=info msg="Closing client's connection" client_id="" interrupt_side=Client-AcraServer session_id=3
time="2023-07-07T03:06:59Z" level=info msg="Finished processing client's connection" client_id="" interrupt_side=Client-AcraServer session_id=3
time="2023-07-07T03:06:59Z" level=info msg="Closing client's connection" client_id="" interrupt_side=Client-AcraServer session_id=4
time="2023-07-07T03:06:59Z" level=info msg="Finished processing client's connection" client_id="" interrupt_side=Client-AcraServer session_id=4
amri commented 1 year ago

I think I misunderstood 2.2 Set ZoneID in encryptor config, I get it now.. Trying

amri commented 1 year ago

It's working.

root@ubuntu-s-1vcpu-2gb-intel-sgp1-01:~/acra-engineering-demo# docker exec -it python-mysql-python-1 python3 extended_example_with_zone.py --host=acra-server --port=9393 --data=data.json
data: [{'token_i32': 1234, 'data_i32': '1234', 'token_i64': 645664, 'data_i64': '645664', 'token_str': '078-05-1111', 'token_bytes': 'byt13es', 'token_email': 'john_wed@cl.com', 'data_str': 'John Wed, Senior Relationshop Manager', 'masking': '$112000', 'searchable': 'john_wed@cl.com'}, {'token_i32': 1234, 'data_i32': '1234', 'token_i64': 645664, 'data_i64': '645664', 'token_str': '078-05-1112', 'token_bytes': 'byt13es2', 'token_email': 'april_cassini@cl.com', 'data_str': 'April Cassini, Marketing Manager', 'masking': '$168000', 'searchable': 'april_cassini@cl.com'}, {'token_i32': 1234, 'data_i32': '1234', 'token_i64': 645664, 'data_i64': '645664', 'token_str': '078-05-1117', 'token_bytes': 'byt13es3', 'token_email': 'george_clooney@cl.com', 'data_str': 'George Clooney, Famous Actor', 'masking': '$780000', 'searchable': 'george_clooney@cl.com'}]

Closing this.

vixentael commented 1 year ago

Good job!

amrih-pg commented 1 year ago

@vixentael however when i checked the phymyadmin for this database's table it shows no results:

image

I noticed that the example uses this file to perform insert & select. On this file, I don't see any reference to acrawriter that perform the encryption into an acrastruct. Am I missing something ?

The docker command that executes the insert (as per the tutorial: https://github.com/cossacklabs/acra-engineering-demo/#24-insert-data-using-updated-config): docker exec -it python-mysql_python_1 \ python3 extended_example_without_zone.py --host=acra-server --port=9393 --data=data.json

Could you assist me on this ?