Skip to content

PG-1213 Add SQL function for checking if WAL record is encrypted #514

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Open
wants to merge 2 commits into
base: TDE_REL_17_STABLE
Choose a base branch
from
Open

PG-1213 Add SQL function for checking if WAL record is encrypted #514

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
2 commits
Select commit Hold shift + click to select a range
097235c
PG-1213 Add function for checking if WAL record is encrypted
artemgavrilov Aug 8, 2025
c8e5310
PG-1213 Add function that returns WAL encryption ragnes
artemgavrilov Aug 13, 2025
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
2 changes: 1 addition & 1 deletion contrib/pg_tde/Makefile
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,7 +1,7 @@
PGFILEDESC = "pg_tde access method"
MODULE_big = pg_tde
EXTENSION = pg_tde
DATA = pg_tde--1.0.sql
DATA = pg_tde--1.0.sql pg_tde--1.0--2.0.sql

# Since meson supports skipping test suites this is a make only feature
ifndef TDE_MODE
Expand Down
19 changes: 19 additions & 0 deletions contrib/pg_tde/documentation/docs/functions.md
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -421,3 +421,22 @@ If any of the above checks fail, the function reports an error.
```sql
SELECT pg_tde_verify_default_key();
```

### pg_tde_is_wal_record_encrypted

This function checks if a WAL record is encrypted or not. It assumes provided LSN belongs to the current timeline if not specified.

```sql
SELECT pg_tde_is_wal_record_encrypted(
'wal_record_lsn',
'optional_timeline_id'
);
```

### pg_tde_get_wal_encryption_ranges
Copy link
Collaborator

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Suggested change
### pg_tde_get_wal_encryption_ranges
### pg_tde_get_wal_encryption_ranges


This function returns the ranges of WAL records that are encrypted. It returns a set of records with the start and end LSNs and TLI for each range.

```sql
SELECT * FROM pg_tde_get_wal_encryption_ranges();
```
2 changes: 1 addition & 1 deletion contrib/pg_tde/expected/version.out
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -2,7 +2,7 @@ CREATE EXTENSION pg_tde;
SELECT pg_tde_version();
pg_tde_version
----------------
pg_tde 1.0.0
pg_tde 2.0.0
(1 row)

DROP EXTENSION pg_tde;
2 changes: 2 additions & 0 deletions contrib/pg_tde/meson.build
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -78,6 +78,7 @@ endif
install_data(
'pg_tde.control',
'pg_tde--1.0.sql',
'pg_tde--1.0--2.0.sql',
kwargs: contrib_data_args,
)

Expand Down Expand Up @@ -128,6 +129,7 @@ tap_tests = [
't/unlogged_tables.pl',
't/wal_archiving.pl',
't/wal_encrypt.pl',
't/wal_encryption_ranges.pl',
't/wal_key_tli.pl',
't/2pc_replication.pl',
't/stream_rep.pl',
Expand Down
17 changes: 17 additions & 0 deletions contrib/pg_tde/pg_tde--1.0--2.0.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,17 @@
-- Function to check if a WAL record is encrypted
CREATE FUNCTION pg_tde_is_wal_record_encrypted(lsn pg_lsn, tli integer DEFAULT 0)
RETURNS BOOLEAN
LANGUAGE C
AS 'MODULE_PATHNAME';
REVOKE ALL ON FUNCTION pg_tde_is_wal_record_encrypted(pg_lsn, integer) FROM PUBLIC;

-- Function to get WAL encryption ranges
CREATE FUNCTION pg_tde_get_wal_encryption_ranges
(OUT start_tli integer,
OUT start_lsn pg_lsn,
OUT end_tli integer,
OUT end_lsn pg_lsn)
RETURNS SETOF RECORD
LANGUAGE C
AS 'MODULE_PATHNAME';
REVOKE ALL ON FUNCTION pg_tde_get_wal_encryption_ranges() FROM PUBLIC;
2 changes: 1 addition & 1 deletion contrib/pg_tde/pg_tde.control
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,4 +1,4 @@
comment = 'pg_tde access method'
default_version = '1.0'
default_version = '2.0'
module_pathname = '$libdir/pg_tde'
relocatable = false
2 changes: 1 addition & 1 deletion contrib/pg_tde/src/include/pg_tde.h
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -2,7 +2,7 @@
#define PG_TDE_H

#define PG_TDE_NAME "pg_tde"
#define PG_TDE_VERSION "1.0.0"
#define PG_TDE_VERSION "2.0.0"
#define PG_TDE_VERSION_STRING PG_TDE_NAME " " PG_TDE_VERSION

#define PG_TDE_DATA_DIR "pg_tde"
Expand Down
103 changes: 103 additions & 0 deletions contrib/pg_tde/src/pg_tde.c
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -15,10 +15,12 @@
#include "storage/shmem.h"
#include "utils/builtins.h"
#include "utils/percona.h"
#include "utils/pg_lsn.h"

#include "access/pg_tde_tdemap.h"
#include "access/pg_tde_xlog.h"
#include "access/pg_tde_xlog_smgr.h"
#include "access/pg_tde_xlog_keys.h"
#include "catalog/tde_global_space.h"
#include "catalog/tde_principal_key.h"
#include "encryption/enc_aes.h"
Expand All @@ -33,6 +35,8 @@

PG_MODULE_MAGIC;

#define PG_TDE_LIST_WAL_KEYS_RANGES_COLS 4

static void pg_tde_init_data_dir(void);

static shmem_startup_hook_type prev_shmem_startup_hook = NULL;
Expand All @@ -41,6 +45,8 @@ static shmem_request_hook_type prev_shmem_request_hook = NULL;
PG_FUNCTION_INFO_V1(pg_tde_extension_initialize);
PG_FUNCTION_INFO_V1(pg_tde_version);
PG_FUNCTION_INFO_V1(pg_tdeam_handler);
PG_FUNCTION_INFO_V1(pg_tde_is_wal_record_encrypted);
PG_FUNCTION_INFO_V1(pg_tde_get_wal_encryption_ranges);

static void
tde_shmem_request(void)
Expand Down Expand Up @@ -166,3 +172,100 @@ pg_tdeam_handler(PG_FUNCTION_ARGS)
{
PG_RETURN_POINTER(GetHeapamTableAmRoutine());
}

/*
* Returns true if the WAL record at the given LSN is encrypted.
*/
Datum
pg_tde_is_wal_record_encrypted(PG_FUNCTION_ARGS)
{
XLogRecPtr lsn = PG_GETARG_LSN(0);
int tli = PG_GETARG_INT32(1);
WalLocation loc;
WALKeyCacheRec *keys;

if (tli == 0)
tli = GetWALInsertionTimeLine();

/* Load all keys for the given timeline */
loc = (WalLocation)
{
.tli = tli,.lsn = 0
};

keys = pg_tde_fetch_wal_keys(loc);
Copy link
Collaborator

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

We shouldn't fetch them, as they are most likely are already in the cache. This should be pg_tde_get_wal_cache_keys

Copy link
Collaborator Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Writes may produce new WAL key, but writes don't update cache as allocations are forbidden there. So WAL reads have some logic how to check if new key was produced and refresh cache entries. After discussion with @dAdAbird we decided that doing it the same way in this function will make code too complex and we can just read keys from disk for now.

postgres/contrib/pg_tde/src/access/pg_tde_xlog_smgr.c

Lines 471 to 478 in 80e0bb0

/* write has generated a new key, need to fetch it */
if (last_key != NULL && wal_location_cmp(last_key->start, write_loc) < 0)
{
pg_tde_fetch_wal_keys(write_loc);
/* in case cache was empty before */
keys = pg_tde_get_wal_cache_keys();
}

Copy link
Collaborator

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

But pg_tde_fetch_wal_keys always adds the fetched keys to the cache, it doesn't check if it's already in the cache. So if we call it multiple times with the beginning, we end up spamming the cache with duplicate records.

Also, if the cache already has items, it doesn't return the first key in the cache, but the first item added to the cache in this read.

Copy link
Collaborator

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Writes may produce new WAL key, but writes don't update cache as allocations are forbidden there

This is also no longer true since #539 now the current key is also in the cache. So reading the cache should be enough.

Copy link
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Eah, but thinking on it, pg_tde_fetch_wal_keys() will not only fetch data from disk dub populate cache with it. In the reader, we specify the last LSN so it'll add only the absent key to the cache. Here it will pull all the keys and add them for the second/third etc, time to the backend's cache. I'm not sure what consequences it may lead to... Unfortunately, we don't have an existing function to read only from the disk and don't flood the cache...

Copy link
Collaborator Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Ahh damn, you're right. I didn't check how cached was populated 🤦

if (!keys)
PG_RETURN_BOOL(false);

loc.lsn = lsn;

for (WALKeyCacheRec *curr_key = keys; curr_key != NULL; curr_key = curr_key->next)
{
if (wal_location_cmp(loc, curr_key->start) >= 0 &&
wal_location_cmp(loc, curr_key->end) < 0)
PG_RETURN_BOOL(curr_key->key.type == WAL_KEY_TYPE_ENCRYPTED);
}

PG_RETURN_BOOL(false);
}

/*
* Returns WAL encryption ranges. WAL records within the LSN range are encrypted.
*/
Datum
pg_tde_get_wal_encryption_ranges(PG_FUNCTION_ARGS)
{
Tuplestorestate *tupstore;
TupleDesc tupdesc;
ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
MemoryContext per_query_ctx;
MemoryContext oldcontext;
WALKeyCacheRec *keys;
WalLocation loc = {.tli = 0,.lsn = 0};

/* check to see if caller supports us returning a tuplestore */
if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
ereport(ERROR,
errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("set-valued function called in context that cannot accept a set"));
if (!(rsinfo->allowedModes & SFRM_Materialize))
ereport(ERROR,
errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("materialize mode required, but it is not allowed in this context"));

/* Switch into long-lived context to construct returned data structures */
per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;
oldcontext = MemoryContextSwitchTo(per_query_ctx);

/* Build a tuple descriptor for our result type */
if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
elog(ERROR, "return type must be a row type");

tupstore = tuplestore_begin_heap(true, false, work_mem);
rsinfo->returnMode = SFRM_Materialize;
rsinfo->setResult = tupstore;
rsinfo->setDesc = tupdesc;

MemoryContextSwitchTo(oldcontext);

keys = pg_tde_fetch_wal_keys(loc);

for (WALKeyCacheRec *curr_key = keys; curr_key != NULL; curr_key = curr_key->next)
{
Datum values[PG_TDE_LIST_WAL_KEYS_RANGES_COLS] = {0};
bool nulls[PG_TDE_LIST_WAL_KEYS_RANGES_COLS] = {0};
int i = 0;

if (curr_key->key.type != WAL_KEY_TYPE_ENCRYPTED)
continue;

values[i++] = Int64GetDatum(curr_key->start.tli);
values[i++] = Int64GetDatum(curr_key->start.lsn);
values[i++] = Int64GetDatum(curr_key->end.tli);
values[i++] = Int64GetDatum(curr_key->end.lsn);

tuplestore_putvalues(tupstore, tupdesc, values, nulls);
}

return (Datum) 0;
}
2 changes: 1 addition & 1 deletion contrib/pg_tde/t/expected/basic.out
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -20,7 +20,7 @@ CREATE EXTENSION pg_tde;
SELECT extname, extversion FROM pg_extension WHERE extname = 'pg_tde';
extname | extversion
---------+------------
pg_tde | 1.0
pg_tde | 2.0
(1 row)

CREATE TABLE test_enc (id SERIAL, k INTEGER, PRIMARY KEY (id)) USING tde_heap;
Expand Down
30 changes: 30 additions & 0 deletions contrib/pg_tde/t/expected/wal_encrypt.out
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -13,6 +13,12 @@ SELECT key_name, provider_name, provider_id FROM pg_tde_server_key_info();
| |
(1 row)

SELECT pg_tde_is_wal_record_encrypted(pg_current_wal_lsn());
pg_tde_is_wal_record_encrypted
--------------------------------
f
(1 row)

SELECT pg_tde_create_key_using_global_key_provider('server-key', 'file-keyring-010');
pg_tde_create_key_using_global_key_provider
---------------------------------------------
Expand Down Expand Up @@ -53,6 +59,12 @@ SELECT slot_name FROM pg_create_logical_replication_slot('tde_slot', 'test_decod

CREATE TABLE test_wal (id SERIAL, k INTEGER, PRIMARY KEY (id));
INSERT INTO test_wal (k) VALUES (1), (2);
SELECT pg_tde_is_wal_record_encrypted(pg_current_wal_lsn());
pg_tde_is_wal_record_encrypted
--------------------------------
t
(1 row)

ALTER SYSTEM SET pg_tde.wal_encrypt = off;
-- server restart without wal encryption
SHOW pg_tde.wal_encrypt;
Expand All @@ -62,6 +74,12 @@ SHOW pg_tde.wal_encrypt;
(1 row)

INSERT INTO test_wal (k) VALUES (3), (4);
SELECT pg_tde_is_wal_record_encrypted(pg_current_wal_lsn());
pg_tde_is_wal_record_encrypted
--------------------------------
f
(1 row)

ALTER SYSTEM SET pg_tde.wal_encrypt = on;
-- server restart with wal encryption
SHOW pg_tde.wal_encrypt;
Expand All @@ -71,6 +89,12 @@ SHOW pg_tde.wal_encrypt;
(1 row)

INSERT INTO test_wal (k) VALUES (5), (6);
SELECT pg_tde_is_wal_record_encrypted(pg_current_wal_lsn());
pg_tde_is_wal_record_encrypted
--------------------------------
t
(1 row)

-- server restart with still wal encryption
SHOW pg_tde.wal_encrypt;
pg_tde.wal_encrypt
Expand All @@ -79,6 +103,12 @@ SHOW pg_tde.wal_encrypt;
(1 row)

INSERT INTO test_wal (k) VALUES (7), (8);
SELECT pg_tde_is_wal_record_encrypted(pg_current_wal_lsn());
pg_tde_is_wal_record_encrypted
--------------------------------
t
(1 row)

SELECT data FROM pg_logical_slot_get_changes('tde_slot', NULL, NULL);
data
-----------------------------------------------------------
Expand Down
15 changes: 15 additions & 0 deletions contrib/pg_tde/t/wal_encrypt.pl
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -31,6 +31,9 @@
'SELECT key_name, provider_name, provider_id FROM pg_tde_server_key_info();'
);

PGTDE::psql($node, 'postgres',
"SELECT pg_tde_is_wal_record_encrypted(pg_current_wal_lsn());");

PGTDE::psql($node, 'postgres',
"SELECT pg_tde_create_key_using_global_key_provider('server-key', 'file-keyring-010');"
);
Expand Down Expand Up @@ -60,6 +63,9 @@

PGTDE::psql($node, 'postgres', 'INSERT INTO test_wal (k) VALUES (1), (2);');

PGTDE::psql($node, 'postgres',
"SELECT pg_tde_is_wal_record_encrypted(pg_current_wal_lsn());");

PGTDE::psql($node, 'postgres', 'ALTER SYSTEM SET pg_tde.wal_encrypt = off;');

PGTDE::append_to_result_file("-- server restart without wal encryption");
Expand All @@ -69,6 +75,9 @@

PGTDE::psql($node, 'postgres', 'INSERT INTO test_wal (k) VALUES (3), (4);');

PGTDE::psql($node, 'postgres',
"SELECT pg_tde_is_wal_record_encrypted(pg_current_wal_lsn());");

PGTDE::psql($node, 'postgres', 'ALTER SYSTEM SET pg_tde.wal_encrypt = on;');

PGTDE::append_to_result_file("-- server restart with wal encryption");
Expand All @@ -78,13 +87,19 @@

PGTDE::psql($node, 'postgres', 'INSERT INTO test_wal (k) VALUES (5), (6);');

PGTDE::psql($node, 'postgres',
"SELECT pg_tde_is_wal_record_encrypted(pg_current_wal_lsn());");

PGTDE::append_to_result_file("-- server restart with still wal encryption");
$node->restart;

PGTDE::psql($node, 'postgres', "SHOW pg_tde.wal_encrypt;");

PGTDE::psql($node, 'postgres', 'INSERT INTO test_wal (k) VALUES (7), (8);');

PGTDE::psql($node, 'postgres',
"SELECT pg_tde_is_wal_record_encrypted(pg_current_wal_lsn());");

PGTDE::psql($node, 'postgres',
"SELECT data FROM pg_logical_slot_get_changes('tde_slot', NULL, NULL);");

Expand Down
Loading
Loading