Password encryption

How Genie stores and verifies user passwords, how the database-side cipher objects are deployed, and how to configure the master secret. This is reversible AES encryption performed in the database — not a one-way hash — so the engine can decrypt-and-compare at login.

TL;DR

Set Security:MasterKeyPassword plus Security:SeedAdminPassword for both SQL Server and PostgreSQL. Security:PasswordEncryptionKey still works as a legacy PostgreSQL fallback when MasterKeyPassword is not set. The cipher objects deploy themselves at startup (EngineSqlBootstrapper), gated on a content hash so they re-run only when the SQL changes. The keys are read from configuration in memory at execution time and never persisted.

How it works

At runtime AesPasswordEncryptor (an ASP.NET Core Identity IPasswordHasher<ApplicationUser>) calls database objects rather than hashing in-process:

• Set/change a password → Encrypt(plain) → the DB encrypts it with an AES-256 key that lives in the database.
• Verify at login → Decrypt(stored) and compare to the supplied password.

The cipher objects live in the Identity schema and differ per datasource:

	SQL Server	PostgreSQL
Key material	DB master key (password-protected) → certificate → AES-256 symmetric key	pgcrypto, AES-256-CBC with a base64 AES key derived from the master secret
Encrypt/decrypt objects	[Identity].[sp_encrypt_password] / [Identity].[sp_decrypt_password] (stored procs)	"Identity".fn_encrypt_password / fn_decrypt_password (functions)
Where the key comes from	Security:MasterKeyPassword (protects the key hierarchy)	Security:MasterKeyPassword (SHA-256 → 32-byte AES key); legacy fallback Security:PasswordEncryptionKey

The SQL for both dialects is held as raw string literals in the feature’s PasswordEncryptionSqlContributor (no .sql file assets).

How it’s deployed — hash-gated at startup

The cipher objects are deployed at startup by EngineSqlBootstrapper (an IHostedService that runs before model migration and before any request can hit login). It picks the dialect from the active datasource, substitutes the key/seed from configuration in memory, executes via raw ADO.NET, and records the run in the Genie.ExecutedScripts table keyed by a content hash. On the next boot, an unchanged script (same hash) is skipped — so the heavy DDL runs only when the SQL (or the unit’s own Version) actually changes, not on every boot. Each unit of engine SQL is contributed by a per-feature IEngineSqlContributor (here PasswordEncryptionSqlContributor); the bootstrapper only orchestrates and hash-gates.

The SQL itself is held as raw string literals in PasswordEncryptionSqlContributor (no .sql file assets, no EF migration). Secrets are never written to ExecutedScripts.Script — the stored text and the hash use the tokenised template; only the executed batches carry the substituted key/password. See Integration — engine SQL objects deployed at startup.

The seeded admin

The engine seeds a system admin whose PasswordHash is the placeholder __SEED_PENDING_SQL_ENCRYPTION__. The bootstrapper’s seed-resolution step replaces that placeholder with the encrypted value of Security:SeedAdminPassword — only while it still equals the placeholder, so an already-resolved password is never overwritten. After the first boot, sign in with that password.

Configuration

All keys live under the Security section.

Key	Datasource	Required	Notes
Security:SeedAdminPassword	both	for the seed admin	The system admin’s initial password. Dev default Admin@123.
Security:MasterKeyPassword	both	yes	Single master secret. SQL Server uses it as the DB master-key password; PostgreSQL derives a base64 AES-256 key from SHA-256(UTF-8 password).
Security:PasswordEncryptionKey	PostgreSQL fallback	no	Legacy base64-encoded 32-byte AES-256 key used only when MasterKeyPassword is not set.

SQL Server

"AppSettings": { "Datasource": "SqlServer" },
"Security": {
  "SeedAdminPassword": "Admin@123",
  "MasterKeyPassword": "<a strong secret>"
}

PostgreSQL

"AppSettings": { "Datasource": "PostgreSql" },
"Security": {
  "SeedAdminPassword": "Admin@123",
  "MasterKeyPassword": "<a strong secret>"
}

Legacy PostgreSQL deployments can continue to use Security:PasswordEncryptionKey when MasterKeyPassword is not set. Generate a valid legacy AES-256 key with:

# any one of these prints a 44-char base64 string (32 bytes)
openssl rand -base64 32
python -c "import os,base64; print(base64.b64encode(os.urandom(32)).decode())"
pwsh -c "[Convert]::ToBase64String((1..32 | %{Get-Random -Max 256}))"

Note

If MasterKeyPassword is set, it wins. Otherwise, an invalid PasswordEncryptionKey (not base64, or not 32 bytes) makes the pgcrypto functions fail — passwords can’t be encrypted or verified.

Overriding without committing secrets

Configuration precedence applies (later wins), so keep secrets out of appsettings.json in real deployments and supply them via:

• Environment variables: Security__MasterKeyPassword, Security__PasswordEncryptionKey, Security__SeedAdminPassword (double underscore = the : separator).
• User secrets (local dev): dotnet user-secrets set "Security:MasterKeyPassword" "…".
• Any other source the host adds (Azure Key Vault, etc.) — the bootstrapper reads the host’s IConfiguration directly, so every configured source is honored.

Operational notes & caveats

• MasterKeyPassword is required. SQL Server startup fails without it by default. PostgreSQL startup and runtime encryption/decryption require it unless the legacy PasswordEncryptionKey fallback is configured.
• Choose the key once, before the first deploy. On SQL Server CREATE MASTER KEY is guarded by IF NOT EXISTS, so changing MasterKeyPassword later won’t re-key an existing database. On PostgreSQL the runtime AES key is derived from MasterKeyPassword (or the legacy fallback key), so changing it makes previously-encrypted passwords undecryptable. Treat the master key as fixed for the life of the data (or plan a re-encryption pass).
• SeedAdminPassword is safe to change — it only affects the seed admin while the password is still the placeholder.
• Rotating the engine SQL. When a new Genie.Engine version changes these scripts, the content hash changes and the bootstrapper re-applies them on the next boot automatically — every object is CREATE OR ALTER / CREATE OR REPLACE, so re-application is safe. Bump the contributor’s Version to force a re-run even when the SQL text is unchanged.

Restoring onto a different SQL Server

Restoring an encrypted database onto a different SQL Server requires a manual one-time re-encryption of the master key on the new server:

OPEN MASTER KEY DECRYPTION BY PASSWORD = '...';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
CLOSE MASTER KEY;

The restored DB master key is still encrypted by the old server’s Service Master Key, so it won’t auto-open there until re-encrypted by the new server’s SMK — otherwise sp_encrypt_password / sp_decrypt_password and login break.

• This is encryption, not hashing. It’s reversible by design (the engine decrypts to verify). Protect the key material accordingly; anyone with the database and the key can recover passwords.
• Reset/dev: if you drop and recreate the database, the next boot redeploys the objects and re-resolves the seed admin from Security:SeedAdminPassword.

See also

• Integration — backend — wiring Genie into a host (incl. the startup engine-SQL deployment).
• Security model — the overall security model (RBAC, auth, disclosure boundary).
• Identity — the login flow that verifies these passwords.