Support for SQL schemas
Machado117 opened this issue · 4 comments
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?
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.
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?
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.
Ok, thank you for the quick reply!