Skip to content

Impact of TCE on administration tasks

There are several factors to take into consideration:

  • columns encrypted with TCE use a TCE types (tcetext, tceint, tcedate), therefore:

  • the DDL of the tables include TCE types. These definition will be exported by dump utilities.

  • the extension is required to use the types and functions provided by TCE. It must be created in the target database.

    The extension also needs to be loaded in memory. For that purpose, it must be defined in PostgreSQL's configuration (shared_preload_libraries) or on the databases / users (session_preload_libraries).

    An invalid extension in the configuration files will prevent the instance from starting. An invalid extension on a database will prevent connections to this database. An invalid extension on a user will prevent connections using said user.

  • the data in the datafiles and the modification written to the WAL are both encrypted.

  • PostgreSQL uses the type specific functions to serialize/deserialize the data (in our case to encrypt/decrypt the data), therefore:

  • to access the data you need a valid key, if you have it the data is decrypted, if you don't, you will receive an error (or a placeholder value).

    Dumps and logical replication will only work meaningfully for users with a key. With such users, as soon as the data leaves the database, it is unencrypted. It can be protected during transit with SSL, but it's not the purview of TCE.

  • TCE's metadata are stored in the tce.envelope table. Each row holds one role's envelope and the tce.keydescriptor that sealed it.

The table is registered with pg_extension_config_dump, so pg_dump exports it with the extension data unless the extension is excluded (--exclude-extension). pg_dumpall doesn't have this option.

The descriptor travels with the database. A role can use a different key in each database. HOSTED ON KMS, FETCHED FROM FILE and FETCHED FROM URL descriptors are dumped in canonical form. An INTERNAL descriptor is written back as INTERNAL $$***$$, so its private key is not restored from the dump.

  • TCE's configuration is stored in a PostgreSQL config file and/or attached to the database or user.

If the configuration is attached to a database objects, it will be present in the dump. User parameters are dumped alongside the user definition by pg_dumpall. Database options are dumped by pg_dump -C.

User defined guc can be freely created with the syntax <namespace>.<guc name>. Hence a TCE guc can be created on an instance without the extension being present.

What does it mean for backups and replication?

Logical backup (pg_dump/pg_dumpall, pg_restore)

pg_dump requires a user with a valid key to extract the data. The output file will contain the decrypted data, the DDL will include TCE's type. Part of the configuration can also be in the dumps (configuration tied to database objects and extension data, including tce.envelope).

If the DDL is left "as is", pg_restore will try to use TCE's type and PostgreSQL will try to encrypt the data. Therefore:

  • the extension must exist on the target database;
  • the user must have a valid key, it can (should) be different on both instances.

Restoring tce.envelope requires TCE to be loaded ({shared|session}_preload_libraries) on the target database, because the tce.keydescriptor column uses a TCE type. The restored descriptor is the one that sealed that database's envelope. It is not shared with other databases.

If you want to insert the data in a database without TCE, the DDL can be edited to replace the TCE types by PostgreSQL's types.

Physical backup

Physical backups and restorations rely on physical copies of the data files and WAL. Therefore, the data remains encrypted in the backup.

When the backup is restored, if the extension is installed on the server and the private keys are accessible, everything will continue to work as before.

If the keys are not accessible, the data will not be accessible.

If the extension is missing, depending on how it was configured, the instance might fail to start, or connections to the database or with the affected user might fail.

If a database is migrated from PostgreSQL's native types to TCE types, the data in backups and WAL generated before the migration will still contain unencrypted data. Therefore, the data will be fully protected only once the backup retention period has elapsed and all older backups have been removed.

Physical replication

Physical replication works the same way as the a physical backup/restoration except that we keep replaying the WAL forever. Therefore, the same limitations apply to both.

Please note that if the library preload directive is configured on the database or user instead of PostgreSQL's configuration file, the extension is not required for replication to work. It will only be required when a connection is attempted to that database or with the user (in which case the connection will fail).

Logical replication

Logical replication setup can be done in several ways:

  • replaying the DDL from a logical backup with pg_restore / psql and doing the initial snapshot with the logical replication;

  • using a physical backup and pg_createsubscriber;

  • other hybrid methods.

Depending on the method you choose (physical or logical), the associated limitations apply.

Please note that the subscription's DDL is not required to be identical to the publication's DDL as long as it's compatible (ie the replication message can be replayed).

In other words, the publisher or the subscriber are distinct systems: either, both or none of them can use TCE.

If the data is encrypted on the publication, it will be decrypted before it's sent thru the network. On the subscription, if the column's type requires it, the data will be re-encrypted.