Hi there,
I have successfully enabled encryption on postgres.
I have in postgres.conf:
pg_tde.enforce_encryption = on
default_table_access_method = ‘tde_heap’
Everything works well. Everything is encrypted by default.
However I have to use an application which creates indexes using
“USE BTREE”. This causes postgres to throw an error
“error: pg_tde.enforce_encryption is ON, only the tde_heap access method is allowed.”, which I guess makes sense.
I have no control over how the application works and so the only
option to get it working is to comment out pg_tde.enforce_encryption = on.
As the tables are created without any special handlers, the set default_table_access_method which uses tde_heap works fine.
However, does this mean that the indexes are not encrypted and can potentially leak data? Anyway around this, or does pg_tde somehow know to index them anyway?
I am not quite sure of the interaction between table data, pg_tde and indexes,
could it be it doesn’t matter if indexes are not encrypted as the data they index is?
I would be very grateful for some clarity on this,
Thanks in advance!
2 Likes
Hello @jonesofarc! I forwarded your question to our dev team and they’ll investigate this. Thank you!
1 Like
Hello @jonesofarc, thank you for the report! Could you please share more information about it, for example exact reproduction steps? We weren’t able to reproduce it, CREATE INDEX with USING btree seems to work, so there’s maybe something more specific to this.
Hi there,
It was using tde with infiscial , which uses the node pgboss module.
Its schemas create queues . Drilling down it was the creation of the the indexes.
There was an ALTER owner command and a using BTREE command. I removed them both. I considered the using BTREE as the likely issue as that was directly USING another handler, ALTER didn’t seem a likely candidate, but could it be that?
I remember if I got the SQL and removed the ALTER and the BTREE lines , the indexes were created, if I did not, pg_tde threw the error. A bit vague I know, does this help?
1 Like
Hello!
Unfortunately this doesn’t help much. We have extensive test coverage for this feature, if there is an issue with it, it will be something very specific - without details this is impossible to reproduce. I tried to run additional tests with alter/create index commands, but so far I wasn’t able to reproduce it.
Hi there,
Sorry for the delay in replying, I have since stopped using infisicsal. However I am trying to import my gitlab database , and the issue appears again. It seems to be when alter table is run on something that is not a basic table. for instance:
CREATE SEQUENCE public.merge_requests_approval_rules_approver_groups_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE public.merge_requests_approval_rules_approver_groups_id_seq OWNER TO gitlab;
This SQL always triggers the issue. This is with enforced encryption on.
Hello!
Thank you, this is indeed a bug, I created the jira ticket Jira about it.
Hi there,
Nice! Looking forward to the resolution.