search

cossacklabs / acra

Database security suite. Database proxy with field-level encryption, search through encrypted data, SQL injections prevention, intrusion detection, honeypots. Supports client-side and proxy-side ("transparent") encryption. SQL, NoSQL.
https://www.cossacklabs.com/acra/
Apache License 2.0
1.33k stars 128 forks source link

Support for SQL schemas #562

Closed Machado117 closed 1 year ago

Machado117 commented 2 years ago

Hello,

I've been trying to setup acra server with an app that has tables in schemas other that the default one. When setting up encryption on a field in encryptor_config, I'm referencing a table using the schema name ex. table: myschema.mytable, however this doesn't seem to work as the field never gets encrypted.

If instead I setup encryption on a table from the public schema, using just the table name, encryption works as expected.

Is encryption on tables on schemas other that the default one not supported?

Lagovas commented 2 years ago

Encryptor's config doesn't support schemas for now. So now, you can declare common encryption settings which will be applied to all schemas. AcraEnterprise supports clientID per schema defined at runtime as SQL variable.

Machado117 commented 1 year ago

Thank you for helping! Indeed it works if I don't use the schema in the name.

Unfortunately I'm still having trouble with tables with all caps names. When I wrap the table name in double quotes on encryptor_config, it seems that acra parses the query but does not insert the double quotes on the table name. This is what I found on the postgres log:

ERROR:  relation "mytable" does not exist at character 13
STATEMENT:  insert into MYTABLE values (E'\\x2525259c00000000000000f0222222228c00000000000000003c25004c00000101400c000000100000002000000097a70f3cb8f40c590b0abb4f7f4e8bbaa64e88dbfdbaf47cb028b9d360a46c7686448f5abb19e70bf01e125d6e56fd28f78c6671d7c6a7c855056656000101400c00000010000000060000008cab9e20be6d1e3318a32173fe1588a4ab1df39d927c0d08c718c09998d640fb5055', 1)

The same happens if I don't wrap the table name with double quotes.

I also tried to wrap the table name in single quotes in addition to double quotes (ex. '"MYTABLE"'), but this way acra doesn't parse the query to encrypt the value.

LOG:  execute <unnamed>: insert into "MYTABLE" values ('John', 1)

However with lower case tables I can encrypt all caps columns without wrapping the column name in double quotes in encryptor_config

How can I enable encryption on allcaps tables?

Lagovas commented 1 year ago

It's not Acra problem. Please, read about table/column identifiers in documentation of database which you use. https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS this is for postgresql. Without quotes postgres processes identifiers as lowercased. Double-quoted turns on case-sensitivity. Single quotes it is string literal, not a table name. Recently we added case-sensitive support for identifiers in #532. It will be available in the next release or you can build binaries from the source code by yourself.

Machado117 commented 1 year ago

Ok, thank you for the quick reply!