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), 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.

  • 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 reveive 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 PostgreSQL's catalog in security labels and in the tce.envelope table.

The security label will be dumped alonside the user definition by pg_dumpall if they are not explicitly excluded with --no-security-labels.

The tce.envelope table will be dumped alongiste the user data by pg_dump if the extension is not explicitly excluded from the dump (--exclude-extension). pg_dumpall doesn't have this option.

  • 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, extension data and security labels).

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.

Security labels must have a valid policy. TCE creates the tce_user_key policy for that puprose. Therefore, restoring a user with a TCE security label requires TCE to be loaded ({shared|session}_preload_library) on the target database.

Currently, a security label cannot be created on a user who already has a security label and an envoloppe in another database.

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 a physical copy of the data files. Therefore, the data is encrypted in the backup.

When the backup is restored, if the extension is installed on the server and the key are accessible, everything will continue to work as it used to.

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

If the extension is missing, depending on where it was defined the instance might no start or the connection to the database/with the user might not work.

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.

Pease 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.