PostgreSQL Key Store

The PostgreSQL key store persists encryption keys in a PostgreSQL table using raw Npgsql. It serves two use cases:

• Local development — persistent keys across application restarts without setting up a secrets manager
• Self-managed production — for teams that don't use cloud secrets managers, with proper hardening (see Production Security Guide below)

Raw Key Storage

This key store stores raw key bytes in a database table. Without the hardening steps described in the Production Security Guide, it is not suitable for production. If you use a cloud provider, consider HashiCorp Vault, Azure Key Vault, or AWS KMS instead — they provide HSM-backed encryption, access auditing, and key wrapping out of the box.

Installation

dotnet CLI

dotnet add package Tayra.KeyStore.PostgreSql

Package Manager

Install-Package Tayra.KeyStore.PostgreSql

Basic Setup

The simplest registration takes a connection string:

var services = new ServiceCollection();

// Use PostgreSQL key store with a connection string
services.AddTayra(opts => opts.LicenseKey = licenseKey)
    .UsePostgreSqlKeyStore(
        "Host=localhost;Database=myapp;Username=postgres;Password=secret");

^anchor

Use NpgsqlDataSource for Connection Pooling

If your application already registers an NpgsqlDataSource (common with Marten or EF Core + Npgsql), pass it directly to avoid creating a second connection pool:

var services = new ServiceCollection();

// Use an existing NpgsqlDataSource for connection pooling
var dataSource = NpgsqlDataSource.Create(
    "Host=localhost;Database=myapp;Username=postgres;Password=secret");

services.AddTayra(opts => opts.LicenseKey = licenseKey)
    .UsePostgreSqlKeyStore(dataSource);

^anchor

Configuration Options

Use the options overload to customize the key store behavior:

var services = new ServiceCollection();

services.AddTayra(opts => opts.LicenseKey = licenseKey)
    .UsePostgreSqlKeyStore(
        "Host=localhost;Database=myapp;Username=postgres;Password=secret",
        options =>
        {
            // Database schema for the key store table (default: "public")
            options.Schema = "encryption";

            // Table name for storing encryption keys (default: "tayra_encryption_keys")
            options.TableName = "tayra_encryption_keys";

            // Auto-create the table on first use (default: true)
            options.AutoMigrate = true;

            // Maximum number of retries for transient failures (default: 3)
            options.MaxRetries = 5;

            // Base delay for exponential backoff between retries (default: 100ms)
            options.RetryBaseDelay = TimeSpan.FromMilliseconds(200);
        });

^anchor

Options Reference

Property	Type	Default	Description
ConnectionString	string?	null	PostgreSQL connection string. Set automatically when using the connection string overload.
Schema	string	"public"	Database schema for the key store table.
TableName	string	"tayra_encryption_keys"	Table name for storing encryption keys.
AutoMigrate	bool	true	When true, automatically creates the table on first use.
MaxRetries	int	3	Maximum number of retries for transient failures.
RetryBaseDelay	TimeSpan	100ms	Base delay for exponential backoff between retries.

Identifier Safety

To prevent identifier-injection and accidental misuse, Schema and TableName must be valid unquoted PostgreSQL identifiers:

• Start with a letter (A-Z/a-z) or underscore (_)
• Continue with letters, digits, or underscores only
• Maximum length: 63 characters

Invalid values fail fast with an ArgumentException when the key store is resolved.

Table Schema

When AutoMigrate is enabled (the default), Tayra creates the following table on first use:

CREATE TABLE IF NOT EXISTS {schema}.{table_name}
(
    key_id      VARCHAR(255) NOT NULL,
    secret_key  BYTEA NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (key_id)
);

CREATE INDEX IF NOT EXISTS idx_{table_name}_prefix
    ON {schema}.{table_name} (key_id varchar_pattern_ops);

• key_id -- The encryption key identifier (e.g., "{subjectId}:{groupName}").
• secret_key -- The raw AES-256 key bytes stored as BYTEA.
• created_at -- Timestamp for data retention policy queries.
• The varchar_pattern_ops index enables efficient prefix queries used by DeleteByPrefixAsync and ListKeyIdsAsync.

Manual Schema Creation

If you prefer to manage database schemas yourself, set AutoMigrate = false and create the table manually using the SQL above. This is recommended for production environments with strict migration workflows.

Retry Logic

The PostgreSQL key store includes built-in retry logic with exponential backoff. Transient errors (as reported by Npgsql), TimeoutException, and IOException are automatically retried.

The backoff delay doubles on each attempt: for the default settings (base delay = 100ms, max retries = 3), the delays are approximately 100ms, 200ms, and 400ms.

To customize retry behavior:

var services = new ServiceCollection();

services.AddTayra(opts => opts.LicenseKey = licenseKey)
    .UsePostgreSqlKeyStore(
        "Host=localhost;Database=myapp;Username=postgres;Password=secret",
        options =>
        {
            options.MaxRetries = 5;                                 // More attempts
            options.RetryBaseDelay = TimeSpan.FromMilliseconds(50); // Shorter initial delay
        });

^anchor

Custom Schema

To isolate Tayra keys in a dedicated schema:

var services = new ServiceCollection();

services.AddTayra(opts => opts.LicenseKey = licenseKey)
    .UsePostgreSqlKeyStore(
        "Host=localhost;Database=myapp;Username=postgres;Password=secret",
        options =>
        {
            options.Schema = "encryption";
            options.TableName = "data_protection_keys";
        });

^anchor

This stores keys in encryption.data_protection_keys. Make sure the schema exists before the first operation if AutoMigrate is enabled. Tayra creates the table but not the schema.

Production Security Guide

The PostgreSQL key store can be used in production if and only if you implement the following hardening measures. Each item addresses a specific GDPR Art. 32 requirement for "appropriate technical and organisational measures."

Required: Encryption at Rest

Encryption keys stored as BYTEA are only as secure as the database storage layer. You must enable encryption at rest:

• PostgreSQL TDE (Transparent Data Encryption) — available in PostgreSQL 16+ with community extensions, or via managed services (AWS RDS, Azure Database for PostgreSQL, Google Cloud SQL) which encrypt storage volumes by default.
• Disk-level encryption — LUKS (Linux), BitLocker (Windows), or cloud provider volume encryption (AWS EBS encryption, Azure Disk Encryption).

DANGER

Without encryption at rest, anyone with filesystem access to the PostgreSQL data directory can read raw key bytes directly from disk.

Required: TLS for All Connections

Enforce TLS for all connections to the key store database:

# In your connection string:
Host=db.example.com;Port=5432;Database=tayra;Username=tayra_app;Password=...;SSL Mode=Require;Trust Server Certificate=false

In postgresql.conf:

ssl = on
ssl_cert_file = '/path/to/server.crt'
ssl_key_file = '/path/to/server.key'

Required: Least-Privilege Access

Create a dedicated database role with only the permissions Tayra needs:

CREATE SCHEMA IF NOT EXISTS encryption;

CREATE ROLE tayra_key_owner LOGIN PASSWORD 'replace-with-strong-secret';
GRANT USAGE ON SCHEMA encryption TO tayra_key_owner;
GRANT SELECT, INSERT, DELETE ON TABLE encryption.tayra_encryption_keys TO tayra_key_owner;

If AutoMigrate = true, the role also needs CREATE on the target schema for first-run table creation. In tighter environments, set AutoMigrate = false, create the table through your migration pipeline, and limit runtime permissions to SELECT, INSERT, and DELETE.

TIP

Never use a superuser or the application's main database role for the key store. If the application database is compromised, the attacker should not automatically have access to encryption keys.

Required: Access Auditing

Enable pgAudit to log all access to the key store table:

-- Install pgAudit extension
CREATE EXTENSION IF NOT EXISTS pgaudit;

-- Audit all operations on the key store table
ALTER TABLE encryption.tayra_encryption_keys SET (pgaudit.log = 'all');

This creates a GDPR Art. 30-compliant audit trail of who accessed encryption keys and when.

Recommended: Separate Database

Store encryption keys in a separate PostgreSQL database (or at minimum a separate schema) from your application data. This ensures:

• A SQL injection in your application database cannot read key bytes
• Database backups can be managed independently (key backups require stricter access controls)
• Access controls are enforced at the database boundary, not just the table level

Recommended: Row-Level Security

For multi-tenant deployments, consider PostgreSQL Row-Level Security (RLS) to isolate keys by tenant:

ALTER TABLE encryption.tayra_encryption_keys ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON encryption.tayra_encryption_keys
    USING (key_id LIKE current_setting('app.tenant_id') || '%');

Production Checklist

Before deploying the PostgreSQL key store to production, verify:

#	Requirement	Status
1	Encryption at rest enabled (TDE, disk encryption, or managed service default)	Required
2	TLS enforced for all database connections (SSL Mode=Require)	Required
3	Dedicated database role with least-privilege permissions	Required
4	AutoMigrate = false — table created via migration pipeline	Required
5	pgAudit enabled on the key store table	Required
6	Key store in a separate database or schema from application data	Recommended
7	Database backups encrypted and access-restricted	Recommended
8	Connection string stored in a secrets manager (not in source code)	Required
9	Regular key rotation policy configured	Recommended

If You Cannot Complete This Checklist

If your infrastructure team cannot implement all required items above, do not use the PostgreSQL key store in production. Use HashiCorp Vault, Azure Key Vault, or AWS KMS instead — they handle all of these concerns by default.

See Also

• Key Stores Overview -- Comparison of all providers
• HashiCorp Vault -- Enterprise secrets management
• Custom Key Store -- Build your own IKeyStore