New created table is not encrypted

[gurucubano]

What I've done wrong or missed:

I followed exactly https://github.com/Percona-Lab/pg_tde?tab=readme-ov-file

psql -Usisis sisis
psql (16.2)
Type "help" for help.

sisis=# ALTER SYSTEM SET shared_preload_libraries = 'pg_tde';

(PostgreSQL restart)

psql -Usisis sisis
psql (16.2)
Type "help" for help.

sisis=# show shared_preload_libraries ;
 shared_preload_libraries
--------------------------
 pg_tde

sisis=# CREATE EXTENSION pg_tde;

sisis=# SELECT pg_tde_add_key_provider_file('file','/tmp/pgkeyring');

sisis=# SELECT pg_tde_set_master_key('my-master-key','file');

od -c /tmp/pgkeyring
0000000   m   y   -   m   a   s   t   e   r   -   k   e   y   _   1  \0
0000020  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0
*
0000400 352   A 356 200 300 255 177 226   H   ^ 024 036 036   N 274  \n
0000420   P 343 200 001  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0
0000440 020  \0  \0  \0
0000444

Created a new table 'd02ben_enc':

psql -Usisis sisis < d02ben_enc.table_create.sql
CREATE TABLE

sisis=# INSERT INTO d02ben_enc VALUES ('4711');
INSERT 0 1
sisis=# SELECT d02bnr FROM d02ben_enc;
 d02bnr
--------
 4711
(1 row)

sisis=# SELECT pgtde_is_encrypted('d02ben_enc');
 pgtde_is_encrypted
--------------------
 f
(1 row)

[dAdAbird]

Hi @gurucubano
You have to specify USING pg_tde during the table creation for it to be encrypted. Otherwise it will be created as the unseal Postgres table.

For example:

CREATE TABLE albums (
    album_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    artist_id INTEGER,
    title TEXT NOT NULL,
    released DATE NOT NULL
) USING pg_tde;

It is quite unclear from the docs and the Readme. We'll update it.

[gurucubano]

El día jueves, marzo 28, 2024 a las 09:27:15a. m. -0700, Andrew Pogrebnoi escribió:

Hi @gurucubano You have to specify `USING pg_tde` during the table creation for it to be encrypted. Otherwise it will be created as the unseal Postgres table. For example: ```sql CREATE TABLE albums ( album_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, artist_id INTEGER, title TEXT NOT NULL, released DATE NOT NULL ) USING pg_tde; ```

Thanks, I will test it tomorrow (today I'm off).

It is quite unclear from the docs and the Readme. We'll update it.

I will try to help on this, pointing out what's unclear for me. Short questions: 1) How the encryption is done? GnuPG? 2) Is there a way to read / export the rows in encrypted form? matthias

…

-- Reply to this email directly or view it on GitHub: #155 (comment) You are receiving this because you were mentioned. Message ID: ***@***.***>

-- Matthias Apitz, ✉ ***@***.***, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub